30 SQL PL/SQL Questions

Here are a mix of SQL and PL/SQL questions. These can be treated as SQL and PL/SQL interview questions or a Database FAQ.

Many of them may be specific to Oracle database. Please  drop comment if you are unable to get an answer to any of them.

Also please note that it is better to know the answers to them even if you are a Java developer as database interaction is very much required in most of the applications.

SQL

1. What are the various types of indexes?
2. Is there any advantage of using views? On what kind of tables and columns, a view be created?
3. What are the various kinds of constraints which can be applied on a column?
4. What is the disadvantage of using indexes?
5. What are the various techniques you have used for optimizing your tables? What about De-normalization?
6. What is the purpose of DUAL table?
7. How will you convert a date to String in SQL?
8. How will you copy only the table structure from one table to another compatible table?
9. How will you copy the table structure and data from one table to another compatible table?
10. Which is the faster method of retrieving data using a SQL query? WHERE, ROWID, CURSOR
11. What is better to use? A subquery or a Join?
12. What is the purpose of NOCACHE and NOLOGGING keywords?
13. What does DELETE CASCADE do in a constraint?
14. When should one use Auto Increment of a column than using a sequence?
15. How will you take the backup of the data present in your tables?
16. What is the difference between a Data Warehouse and Data Mining?
17. What is the disadvantage of having constraints and integrity checks in triggers or procedures than to have them in the application using the database?
18. How will you delete duplicate rows from a table based on a particular column?
19. Can a view be created on another view?
20. Can a SQL query have an alias and be used as a column name for the enclosing parent SQL query? Give example?

PL/SQL

1. Is AVG a function or procedure?
2. What is the advantage of using packages for storing PL/SQL objects?
3. How will you raise an exception and an error in PL/SQL procedure?
4. How will you call a PL/SQL procedure from a SQL statement?
5. What is the default value being assigned to variables in a PL/SQL variables?
6. What is the difference between NVL and NVL2 functions?
7. What is the difference between Count and Count(*)?
8. What is the difference between Procedure and Function?
9. What is overloading of procedures?
10. What is a ROW and STATEMENT triggers?