Oracle SQL Interview questions with aswer

1. What are the type of indexes in oracle?

        B-tree indexes: the default and the most common
        B-tree cluster indexes: defined specifically for cluster
        Bitmap indexes: compact; work best for columns with a small set of values
        Function-based indexes: contain the precomputed value of a function/expression Domain indexes: specific to an application or cartridge.
    Reverse key indexes: most useful for Oracle Real Application Clusters applications
        Hash cluster indexes: defined specifically for a hash cluster
        Global and local indexes: relate to partitioned tables and indexes
       

2.What is a pseudo column in oracle. Give some examples ?

It is a column that is not an actual column in the table.

eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.


3.difference between TRUNCATE and DELETE in oracle?

    Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate

command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will

produce rollback data and thus take longer to complete.


4.what are the aggregate functions in oracle?

Max(sum_of_cost), min(sum_of_cost), count(item_no), item_no The only column that can be grouped on is the "item_no" column, the rest have aggregate functions

associated with them.

5.Which datatype is used for storing graphics and images in oracle?

LONG RAW data type is used for storing BLOB's (binary large objects).


6. What is difference between UNIQUE and PRIMARY KEY constraints in oracle?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column

that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.


7. What is a CO-RELATED SUBQUERY in oracle?

   After execution of main query result will be used by the Subquery will be execute.

A CO-RELATED SUBQUERY is one that has a correlation name as table or view designator in the FROM clause of the outer query and the same correlation name as a qualifier

of a search condition in the WHERE clause of the subquery.
eg
SELECT field1 from table1 X
WHERE field2>(select avg(field2) from table1 Y
where
field1=X.field1);

8.What command would you use to encrypt a PL/SQL application?
    WRAP

9.Do View contain Data in oracle?
     Views do not contain or store data.


10.What are the type of Synonyms in oracle?
There are two types of Synonyms Private and Public.

11.What is the View in oracle?
A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)


12.What are the advantages of views?
- Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Hide data complexity.
- Simplify commands for the user.
- Present the data in a different perspective from that of the base table.
- Store complex queries.


13.What is difference between UNIQUE constraint and PRIMARY KEY constraint in oracle?

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can't contain Nulls.

14.What is Index Cluster in oracle?
A Cluster with an index on the Cluster Key

15.When does a Transaction end ?
When it is committed or Rollbacked.


16. How will you convert string to a date in oracle database?
 This Oracle Interview questions is some time asked as follow up of previous Oracle Interview questions related to converting date to char in Oracle. By the way  to_

date function is used to convert string to a date function.

Syntax :  to_date(string, format)
Example:   to_date('2012/06/12', 'yyyy/mm/dd')  It will return June 12, 2012

17. What are various joins used while writing SUBQUERIES?

Self join-Its a join foreign key of a table references the same table.

Outer Join-Its a join condition used where One can query all the rows of one of the tables in the join condition even though they don't satisfy the join condition.

Equi-join-Its a join condition that retrieves rows from one or more tables in which one or more columns in one table are equal to one or more columns in the second

table.

18. What are various constraints used in oracle?
NULL
NOT NULL
CHECK
DEFAULT

19. What are different Oracle database objects in oracle?

TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc

20.Compare and contrast TRUNCATE and DELETE for a table?

    Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate

command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will

produce rollback data and thus take longer to complete.

21. What is difference between Rename and Alias?

Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is

executed.

22. Explain the view?

A view is stored procedure based on one or more tables, its a virtual table.

23. What are various privileges that a user can grant to another user?

SELECT
CONNECT
RESOURCE


24. What is difference between SUBSTR and INSTR in oracle?

SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE INSTR provides character position in which a pattern is found in a string.

eg INSTR('ABC-DC-F','-',2) output 7 (2nd occurence of '-')



25. Can a primary key contain more than one columns?

Yes


26. How you will avoid duplicating records in a query?

By using DISTINCT


27. Which is more faster in oracle - IN or EXISTS?

EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN returns a value.

Appropriate answer will be....

Result of the subquery is small Then "IN" is typicaly more appropriate. and Result of the subquery is big/large/long Then "EXIST" is more appropriate.


28.When you use WHERE clause and when you use HAVING clause ?

HAVING clause is used when you want to specify a condition for a group function and it is written after GROUP BY clause The WHERE clause is used when you want to

specify a condition for columns, single row functions except group functions and it is written before GROUP BY clause if it is used.


29. Difference between an implicit & an explicit cursor in oracle?

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row

you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL

statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE

and single row SELECT. .INTO statements.

30. What are cursor attributes in oracle?

    %ISOPEN
    %ROWCOUNT    
    %NOTFOUND    
    %FOUND