No questions found.
Try different keywords.
01.
Find the second highest salary from the Employee table.
Subqueries
SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
);
02.
Find duplicate records in a table.
GROUP BY / HAVING
SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;
03.
Retrieve employees who earn more than their manager.
Self Join
SELECT e.name AS Employee, e.salary, m.name AS Manager, m.salary AS ManagerSalary FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;
04.
Find employees who joined in the last 6 months.
Date Functions
SELECT * FROM employees WHERE join_date >= CURRENT_DATE - INTERVAL '6 months';
05.
Get departments with no employees.
LEFT JOIN / NULL
SELECT d.department_name FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.id IS NULL;
06.
Running total of salaries by department.
Window Functions
SELECT name, department_id, salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY id) AS running_total
FROM employees;
07.
Find gaps in a sequence of numbers (missing IDs).
NOT EXISTS
SELECT (id + 1) AS missing_id FROM employees e1 WHERE NOT EXISTS ( SELECT 1 FROM employees e2 WHERE e2.id = e1.id + 1 ) ORDER BY missing_id;
08.
Rank employees based on salary with ties handled properly.
Window Functions
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
09.
Identify overlapping date ranges for bookings.
Self Join
SELECT b1.booking_id, b2.booking_id FROM bookings b1 JOIN bookings b2 ON b1.booking_id <> b2.booking_id WHERE b1.start_date <= b2.end_date AND b1.end_date >= b2.start_date;
10.
Find departments with the highest average salary.
CTEs / Aggregation
WITH avg_salaries AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) SELECT * FROM avg_salaries WHERE avg_salary = (SELECT MAX(avg_salary) FROM avg_salaries);
11.
Find the most recent purchase per customer.
ROW_NUMBER()
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rn
FROM sales
) sub
WHERE rn = 1;
12.
Find customers who made purchases in every category available.
Relational Division
SELECT customer_id FROM sales s GROUP BY customer_id HAVING COUNT(DISTINCT category_id) = (SELECT COUNT(DISTINCT category_id) FROM sales);
13.
Calculate the percentage change in sales compared to the previous month.
LAG() Window Function
SELECT product_id, sale_month, total_sales,
(total_sales - LAG(total_sales) OVER (PARTITION BY product_id ORDER BY sale_month)) * 100.0 /
LAG(total_sales) OVER (PARTITION BY product_id ORDER BY sale_month) AS pct_change
FROM (
SELECT product_id, DATE_TRUNC('month', sale_date) AS sale_month, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id, sale_month
) monthly_sales;
14.
Retrieve the last 5 orders for each customer.
ROW_NUMBER()
SELECT * FROM ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders o ) sub WHERE rn <= 5;
15.
Recursive query to compute the total budget under each manager (including subordinates).
RECURSIVE CTE
WITH RECURSIVE manager_budget AS ( SELECT id, manager_id, budget FROM departments UNION ALL SELECT d.id, d.manager_id, mb.budget FROM departments d JOIN manager_budget mb ON d.manager_id = mb.id ) SELECT manager_id, SUM(budget) AS total_budget FROM manager_budget GROUP BY manager_id;
Showing 15 of 300 Real Interview Questions. More updates added regularly to keep the database current.
Expand Your Preparation
We have curated comprehensive manuals, cheatsheets, and comparison articles to help you master SQL and database testing.