Skip to main content

Interview questions - SQL & PL/SQL


When I interviewed developers to extend my team I asked them to evaluate themselves on scale 0-10. Then I asked them following questions. At the end I compare my notes and correct answers with their self evaluation. At the end I am not looking for perfect answers, but for the attitude and how do you handle yourself when you dont know the correct answers.

  • What is your preferred PL/SQL editor + current Oracle version?
  • How do you get number of rows affected by DML query?
  • Name all Oracle table constraint types.
  • Describe syntax for adding foreign key to table.
  • Describe differences between SQL and PL/SQL data types.
  • Describe advantages of packages over procedures.
  • What is difference between DROP, DELETE and TRUNCATE?
  • What is a materialized view?
  • What is a CROSS JOIN? What are the other types of joins?
  • How do you select the 2nd recent row from a table?
  • How do you traverse trees?
  • How do you recompile invalid objects?
  • How do you restore dropped table?
  • Name catalog views which contains info about tables and columns.
  • What does SYS_CONTEXT do?
  • What does LEAD and LAG analytical functions do?
  • What is a mutating table error and how do you fix it?
  • How do you extract tag value and attribute from XML?
  • How do you convert rows to cols in SELECT?
  • How do you measure SQL query and PL/SQL code performance?
  • Name some new SQL or PL/SQL features of Oracle 19c, 18c.
  • Do you have any experience with unit tests in Oracle database?