SQL Zone is brought to you in partnership with:

Sandeep is a ITA at Tata Consultancy Services. He also is the author of java blog. Sandeep has posted 24 posts at DZone. You can read more from them at their website. View Full User Profile

30 SQL PL/SQL Questions

05.21.2011
| 9914 views |
  • submit to reddit

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?

From http://extreme-java.blogspot.com/2011/05/30-sql-plsql-questions.html

Published at DZone with permission of its author, Sandeep Bhandari.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Balázs Bessenyei replied on Sun, 2011/05/22 - 4:25am

Isn`t DUAL Oracle specific?

Michal Xorty replied on Sun, 2011/05/22 - 8:12am

@Balázs: Yes, it is

Liran Mendelovich replied on Sun, 2011/05/22 - 12:46pm

Some issues (Mainly concern MYSQL): 1. Can you please answer and elaborate on 16 ? 2. The answer for 11 is sub-query ? 3. 15 - There are few ways, e.g. mysqldump, CSV/XML export. Can you elaborate too ? Thanks

Balázs Bessenyei replied on Sun, 2011/05/22 - 1:11pm in response to: Michal Xorty

Good, in this case it should be in the PL/SQL section and not in the general SQL.

Otherwise I think the questions are  good.

Emma Watson replied on Fri, 2012/03/30 - 12:26pm

I assume these interview questions are for DBA positions and not Java developers. If it is for a developer, then 90% of the questions should be just bonuses. In practice as developers, they almost never use them and eventually forgetten.

Swing

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.