SQL Interview Questions

SQL scenario-based questions and their answers for interview preparation

Over the last 10 years, I have taken many interviews and given some interviews too. In the data world, even today, for majority of the developer-level roles, SQL is a pre-requisite and atleast one round of SQL coding test is conducted. I have searched extensively on internet for hard difficulty-level SQL questions during interview prep for FAANG companies but didn’t find many resources. Hence I have compiled below set of scenario based SQL questions and their possible solutions (most of them are Oracle 11g compatible) from my interviewing experience. I will keep updating this article as I discover more questions or better optimized versions of the given solutions.

1. Write a query to get the 3rd highest salary from the employee table without using any analytical function.

SOLUTION –

select distinct salary from Employee e1 where 3 = (select count(distinct salary) from Employee e2 where e1.salary<=e2.salary)

2. For every order there will always be 3 operations — payment, ship, delivery. Write a query to pivot on ORDER_ID so that all 3 operations details and operation attempts are in single record like below: PAYMENT_ATTEMPTS, PAYMENT_FINAL_STATUS, PAYMENT_FINAL_DATETIME, SHIPPED_ATTEMPTS, SHIPPED_FINAL_STATUS, SHIPPED_FINAL_DATETIME, DELIVERED_ATTEMPTS , DELIVERED_FINAL_STATUS, DELIVERED_FINAL_DATETIME

Sample data is as below —

Sample Data

SOLUTION —

SELECT * FROM orders
PIVOT (count(OPERATION_NAME) AS ATTEMPTS,
MAX(OPERATION_STATUS) AS FINAL_STATUS,
MAX(OPERATION_DATETIME) AS FINAL_DATETIME
FOR OPERATION_NAME
IN(‘PAYMENT’ AS PAYMENT, ‘SHIPPED’ AS SHIPPED, ‘DELIVERED’ AS DELIVERED)
);

3. Convert below data such that final result set consists of following columns — customer_id, product, price

Sample data

SOLUTION-

SELECT * FROM sales
UNPIVOT [EXCLUDE NULLs | INCLUDE NULLs] (price FOR product IN (a_product AS ‘A’, b_product AS ‘B’, c_product_c AS ‘C’));

(unpivot operator converts the columns into rows)

4. Given a sales table (Sales_Date, Product_ID, Product_Group, Sales_Amount), write a query to get those products which have continuous decrease of sales year on year.

SOLUTION-

Solution Query

5. Write a query which reads table1 and prints the “name” as many times as the number in the frequency column.

Sample Input —

Sample Input

Expected Output —

Expected output

SOLUTION-

WITH RTable(ename, freq, count) as
(
SELECT ename , freq , 1
FROM table1
WHERE freq <> 0
UNION ALL
SELECT ename, freq, count + 1
FROM RTable R
WHERE R.Count < R.freq
)
SELECT ename
FROM RTable
order by ename;

6. Write a SQL query to find the sum of sales of current row and previous 2 rows in a product group.

SOLUTION-

SELECT PRODUCT_ID, QUANTITY, SUM(QUANTITY) OVER (PARTITION BY PRODUCT_ID ORDER BY QUANTITY DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) CALC_SALES
FROM SALES;

7. How to find the Minimum and maximum values of continuous sequence numbers in a group of rows?

Sample Input —

Sample Input

Expected Output —

Expected Output

SOLUTION-

SELECT DEPT_ID, MIN(EMP_SEQ) MIN_SEQ, MAX(EMP_SEQ) MAX_SEQ
FROM
(
SELECT DEPT_ID, EMP_SEQ, EMP_SEQ — ROW_NUMBER() OVER (PARTITION BY DEPT_ID ORDER BY EMP_SEQ) DEPT_SPLIT
FROM EMPLOYEES;
) A
GROUP BY DEPT_ID, DEPT_SPLIT

I usually use Oracle Live for testing the SQL queries by inputting some dummy data.

Hope this is helpful!