Oracle Interview Questions with Answers
1. What is a CO-RELATED SUBQUERY
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);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. 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.
3. What are various constraints used in SQL
NULL
NOT NULL
CHECK
DEFAULT
4. What are different Oracle database objects
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc
5. 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.
6. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
7. What are various privileges that a user can grant to another user
SELECT
CONNECT
RESOURCE
8. What is difference between UNIQUE and PRIMARY KEY constraints
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.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT
1. What is a CO-RELATED SUBQUERY
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);
(The subquery in a correlated subquery is revaluated for every row of the table or view named in the outer query.)
2. 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.
3. What are various constraints used in SQL
NULL
NOT NULL
CHECK
DEFAULT
4. What are different Oracle database objects
TABLES
VIEWS
INDEXES
SYNONYMS
SEQUENCES
TABLESPACES etc
5. 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.
6. What is a view
A view is stored procedure based on one or more tables, its a virtual table.
7. What are various privileges that a user can grant to another user
SELECT
CONNECT
RESOURCE
8. What is difference between UNIQUE and PRIMARY KEY constraints
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.
9. Can a primary key contain more than one columns
Yes
10. How you will avoid duplicating records in a query
By using DISTINCT