Top 5 Oracle Database PL/SQL Job Interview Questions

Interviewing for a PL/SQL developer position may turn into a quiz, specifically about writing SQL and extracting information from a set of tables. The following questions, while not exact, are actual questions I’ve been asked in the past when interviewing for both a DBA position and a PL/SQL developer position. Clearly, there is a need for your potential employer to feel confident that you cannot only read a data model but also write SQL and talk intelligently about modifications to that model.

The following tables represent a basic set of tables that might be used in an order entry system. Granted I’ve eliminated many columns and attributes but that isn’t really important for this exercise. Again, as you read these, especially if you’re getting ready for an interview, remember that these questions are not hard and fast, many of them beg for further investigation, many of them you should try within your own sandbox, and you should extend into other methods of filtering within your SQL.

1. Identify the primary key and foreign keys within the table structures

One of the very first things for any proficient SQL coder to do is properly understand the data model they are working with. The given model is actually quite easy as it is one that most everyone has seen in some form or fashion, a quick customer order system. All primary keys are of a single column except for the table ORDERLINE, which has a concatenated primary key (ORDERID, LINEID) to uniquely identify a given row. Foreign keys are also straight forward as the CUSTOMERID in CUSTOMERORDER references CUSTOMERID in CUSTOMER and PARTID in ORDERLINE references PARTID in PRODUCT.

2. Where might I put the total amount of an order (which table)? Why?

Again, understanding and having simple design skills is important to be able to talk intelligently during code reviews and data modeling sessions with DAs and DBAs. Here, to put a total order amount clearly breaks some of the normalization rules but would be put in the CUSTOMERORDER table to give us quick access to a total amount without having to look up each order line and calculating price, shipping, etc.; clearly for performance reasons.

3. If I were writing an application what would have to be the order of population for this set of tables?

The order of population is dependent upon the referential integrity between the tables. Here CUSTOMERORDER references CUSTOMER and ORDERLINE references PRODUCT and CUSTOMERORDER. Therefore, before CUSTOMERORDER can be populated, the CUSTOMER table must have an entry for the customer. Likewise, populating ORDERLINE is dependent on having an open order (CUSTOMERORDER) and some product to order (PRODUCT). Therefore, proper population of these tables would dictate something like PRODUCT, CUSTOMER, CUSTOMERORDER, and then ORDERLINE. Please note that I put PRODUCT first before CUSTOMER as we more than likely would have walk-in customers and product is clearly something we should have before a customer.

4. For the following SQL (A through F) identify which SQL is the best for displaying the customers that have placed an order

A)

select customerid
from customer
where customerid
in (select customerid from customerorder);

B)

select customer.customerid
from customer, customerorder
where customer.customerid = customerorder.customerid;

C)

select customer.customerid
from customer inner join customerorder
on customer.customerid = customerorder.customerid;

D)

select unique customer.customerid
from customer inner join customerorder
on customer.customerid = customerorder.customerid;

E)

select customerid
from customer
where exists
( select customerid
from customerorder
where customer.customerid = customerorder.customerid);

F)

 select customerid 
from customer
where customerid =
( select customerid 
from customerorder
where customer.customerid = customerorder.customerid);

This is a great question as it, when you’re under pressure in an interview, allows you to really stand out and articulate that you know something about SQL processing. The key word in the question is “best”. I would highly recommend, if you’re rusty on SQL coding (including ANSI syntax standards) to brush up on some SQL. It will go a long way on a test.

Let’s first begin by saying that all of these SQL statements will run without error EXCEPT SQL statement F, as the subquery could possibly return more than one row. SQL statements B & C produce multiple rows for the same customer (not the best, as we would have to filter in the application). SQL statement D is the same as C except for the UNIQUE keyword that, while it produces one row for each customer will incur unnecessary sorting. SQL statement A, while producing the proper results unfortunately (within the IN clause) will first produce a complete list of customer. SQL statement E, on the other hand, will through the subquery immediately return to the main part of the query as soon as an existence condition is met, thus the best SQL statement.

5. If you had to put any indexes on these tables, where would you put them?

If I were asked to create indexes on these tables, about the only place I’d add an index, not knowing the application, would be on the foreign keys. This helps safeguard against full table scans when validating the referential integrity between the two tables when INSERTing, DELETEing, and UPDATEing rows.

Well, there are my “top” 5 questions that I’ve had asked of me about my SQL writing skills. They really aren’t that bad and everyone should quickly pick up on the answers here. Remember, these questions are not hard-n-fast questions and if I were the interviewer I’d most definitely jump off into some additional areas such as SQL tuning like the reading of explain plans, different indexes available, and some of the more common functions you might use within a SQL statement.