I like to ask this series of questions in interviews when people say that they “know” Oracle SQL. Granted, none of this exemplifies the hardest parts of SQL, but it’s nice to see how hard someone is thinking when they answer.

Question 1: Can you nest a select inside the from clause?

Answer: Of course! This is essentially like querying from a view.

Simple Example:

SELECT t1.columnA FROM (SELECT columnA FROM tableX) t1

Question 2: Can you nest a select inside the where clause?

Answer: Yes, you can perform match data with an “in” or equals (“=”) against a select statement.

Simple Example:

SELECT t1.columnA FROM tableX WHERE columnA IN (SELECT columnB FROM tableY)

Question 3: Can you nest a select inside the select clause?

Answer: Yes, though I don’t find myself using it often, you can certainly do this.

Simple Example:

SELECT t1.columnA, (SELECT MAX(columnB) FROM tableY) AS columnB FROM tableX

Have an Oracle DB? Try it for yourself.

SELECT
(SELECT 1 FROM dual) one
FROM dual