Table of contents
SQL queries are an important part of every coding interview. When a company interviews a fresher or an experienced developer, they want to see whether the person is able to understand and extract the data correctly or not.
These questions range from basic to advanced level – like finding the second highest salary, removing duplicates, generating running totals, or writing complex joins.
In this guide, we have provided easy, medium, and hard level SQL query questions, which you will practice and be ready for the interview. The concept, approach, and answer of each question are also provided, so that you don’t just memorize – learn to solve by understanding.
Academy Pro
Master SQL and Database management with this SQL course: Practical training with guided projects, AI support, and expert instructors.
7 Hrs
2 Projects
Easy Level Questions
1. Write a query to get the second-highest salary from the employees table.
- Concept: sorting and checking top-N logic.
- Approach: sort the salaries in descending order and take the 2nd value, or find the maximum salary that is smaller than the maximum.
Answer:
-- Method 1: Using LIMIT and OFFSET
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
-- Method 2: Using subquery
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Method 3: Using ROW_NUMBER()
SELECT salary
FROM (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rn
FROM employees
) t
WHERE rn = 2;
2. Find duplicate user records in a table
- Concept: understand the use of GROUP BY and COUNT.
- Approach: count users with the same email and show where the count is >1.
Answer:
Find all duplicate email addresses in the users table.
-- Method 1: Using GROUP BY and HAVING
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Method 2: Show all duplicate rows
SELECT u1.* FROM users u1
JOIN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
) u2 ON u1.email = u2.email;
3. Delete duplicate records from the users table. Keep only the record with the lowest ID.
- Concept: check duplicate delete logic and self-join.
- Approach: delete the highest ID in the records with the same email.
Answer:
-- Method 1: Using self join
DELETE u1 FROM users u1
JOIN users u2
WHERE u1.id > u2.id AND u1.email = u2.email;
-- Method 2: Using window function
DELETE FROM users
WHERE id NOT IN (
SELECT min_id FROM (
SELECT MIN(id) as min_id
FROM users
GROUP BY email
) t
);
Also Read: SQL Commands with Examples
4. Find all employees who do not have a manager in the employees table.
- Concept: handle NULL and self-join.
- Approach: show those whose manager_id is NULL or the manager does not exist.
Answer:
SELECT * FROM employees
WHERE manager_id IS NULL;
-- Or if manager_id references employee_id in same table
SELECT e1.* FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e2.employee_id IS NULL AND e1.manager_id IS NOT NULL;
5. Calculate the running total of sales for each day.
- Concept: understand the window function.
- Approach: Calculate cumulative total from SUM() OVER(ORDER BY date).
Answer:
-- Method 1: Using window function (preferred)
SELECT
sale_date,
daily_sales,
SUM(daily_sales) OVER (ORDER BY sale_date) as running_total
FROM sales
ORDER BY sale_date;
-- Method 2: Using self join (older method)
SELECT
s1.sale_date,
s1.daily_sales,
SUM(s2.daily_sales) as running_total
FROM sales s1
JOIN sales s2 ON s2.sale_date <= s1.sale_date
GROUP BY s1.sale_date, s1.daily_sales
ORDER BY s1.sale_date;
6. Find customers who never placed an order
- Concept: LEFT JOIN and NULL logic test.
- Approach: By linking customers to orders and not showing them which orders they have.
Answer:
-- Method 1: Using LEFT JOIN
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
-- Method 2: Using NOT EXISTS
SELECT * FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Method 3: Using NOT IN (be careful with NULL values)
SELECT * FROM customers
WHERE customer_id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);
7. Write a query to find the 3rd highest salary.
- Concept: understanding ranking window functions.
- Approach: Sort the salary desk and find the 3rd value.
Answer:
-- Method 1: Using DENSE_RANK()
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank_num
FROM employees
) t
WHERE rank_num = 3;
-- Method 2: Using LIMIT and OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
8. Count the number of employees in each department. Show departments with zero employees, too.
- Concept: LEFT JOIN and GROUP BY.
- Approach: Counting by joining employees with departments.
Answer:
SELECT
d.department_name,
COUNT(e.employee_id) as employee_coundatt
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY employee_count DESC;
Also Read: SQL Joins – Inner, Left, Right & Full Join
Medium Level Questions
9. Find employees earning more than their manager
- Concept: self-join and comparison logic.
- Approach: Join the employee and his manager and compare the salary.
Answer:
SELECT
e1.employee_name,
e1.salary as employee_salary,
e2.employee_name as manager_name,
e2.salary as manager_salary
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
WHERE e1.salary > e2.salary;
10. Find all dates that appear for 3 or more consecutive days in the login_logs table.
- Concept: date gaps and row_number difference trick.
- Approach: counting the dates in continuous groups.
Answer:
WITH consecutive_groups AS (
SELECT
login_date,
login_date - INTERVAL ROW_NUMBER() OVER (ORDER BY login_date) DAY as group_id
FROM (SELECT DISTINCT login_date FROM login_logs) t
)
SELECT
MIN(login_date) as start_date,
MAX(login_date) as end_date,
COUNT(*) as consecutive_days
FROM consecutive_groups
GROUP BY group_id
HAVING COUNT(*) >= 3;
11. Convert rows to columns. Show total sales by product for each month.
Question:
- Concept: conditional aggregation system.
- Approach: Creating columns by months using CASE WHEN or PIVOT.
Answer:
-- Method 1: Using CASE statements
SELECT
product_name,
SUM(CASE WHEN MONTH(sale_date) = 1 THEN amount ELSE 0 END) as Jan,
SUM(CASE WHEN MONTH(sale_date) = 2 THEN amount ELSE 0 END) as Feb,
SUM(CASE WHEN MONTH(sale_date) = 3 THEN amount ELSE 0 END) as Mar
FROM sales
GROUP BY product_name;
-- Method 2: Using PIVOT (SQL Server)
SELECT *
FROM (
SELECT product_name, MONTH(sale_date) as month, amount
FROM sales
) t
PIVOT (
SUM(amount) FOR month IN ([1], [2], [3])
) p;
12. Find missing numbers in a sequence from 1 to 100.
- Concept: detecting missing by generating a sequence.
- Approach: Generating 1–100 numbers and joining them to the table to show nulls.
Answer:
-- Method 1: Using recursive CTE
WITH RECURSIVE numbers AS (
SELECT 1 as num
UNION ALL
SELECT num + 1 FROM numbers WHERE num < 100
)
SELECT n.num as missing_number
FROM numbers n
LEFT JOIN your_table t ON n.num = t.id
WHERE t.id IS NULL;
-- Method 2: Using generate_series (PostgreSQL)
SELECT generate_series(1, 100) as missing_number
EXCEPT
SELECT id FROM your_table
ORDER BY missing_number;
13. Calculate each employee’s salary as a percentage of the total company salary.
- Concept: window aggregation.
- Approach: Converting every salary into a percentage of the total salary.
Answer:
SELECT
employee_name,
salary,
ROUND(
(salary * 100.0 / SUM(salary) OVER()), 2
) as percentage_of_total
FROM employees;
14. Find top N records per group
Question:Â Find the top 2 highest-paid employees in each department.
- Concept: partitioned ranking.
- Approach: ROW_NUMBER partition by department and take top 2.
Answer:
-- Method 1: Using ROW_NUMBER()
SELECT
department_name,
employee_name,
salary
FROM (
SELECT
d.department_name,
e.employee_name,
e.salary,
ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as rn
FROM employees e
JOIN departments d ON e.department_id = d.department_id
) t
WHERE rn <= 2;
-- Method 2: Using RANK() (includes ties)
SELECT
department_name,
employee_name,
salary
FROM (
SELECT
d.department_name,
e.employee_name,
e.salary,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as rank_num
FROM employees e
JOIN departments d ON e.department_id = d.department_id
) t
WHERE rank_num <= 2;
15. Find all pairs of employees who work in the same department and have a salary difference of less than 5000.
- Concept: self-join and pair comparison.
- Approach: Join two employees of the same department and check the difference.
Answer:
SELECT
e1.employee_name as employee1,
e2.employee_name as employee2,
e1.salary as salary1,
e2.salary as salary2,
ABS(e1.salary - e2.salary) as salary_difference
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id < e2.employee_id -- Avoid duplicates
AND ABS(e1.salary - e2.salary) < 5000;
16. Find employees who joined in the last quarter of any year.
- Concept: date functions society.
- Approach: Filter by extracting the month or quarter of the join_date.
Answer:
SELECT
employee_name,
join_date,
YEAR(join_date) as join_year
FROM employees
WHERE MONTH(join_date) IN (10, 11, 12);
-- Or using date functions
SELECT
employee_name,
join_date
FROM employees
WHERE QUARTER(join_date) = 4;
Hard Level Questions
17. Show employee hierarchy with levels (manager, sub-manager, employee).
- Concept: recursive CTE check.
- Approach: Building manager → sub → employee chain and giving level.
Answer:
-- Recursive CTE to build hierarchy
WITH RECURSIVE employee_hierarchy AS (
-- Base case: top-level managers
SELECT
employee_id,
employee_name,
manager_id,
0 as level,
employee_name as path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive case
SELECT
e.employee_id,
e.employee_name,
e.manager_id,
eh.level + 1,
CONCAT(eh.path, ' -> ', e.employee_name)
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy
ORDER BY level, employee_name;
18. Calculate the 3-month moving average of sales.
- Concept: window frame logic.
- Approach: finding the average from ROWS BETWEEN 2 PRECEDING.
Answer:
SELECT
sale_month,
monthly_sales,
AVG(monthly_sales) OVER (
ORDER BY sale_month
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg_3_months
FROM monthly_sales
ORDER BY sale_month;
19. Find continuous ranges of available seats in a cinema.
- Concept: gap detection with row_number.
- Approach: seat_number – row_number grouping trick.
Answer:
WITH seat_groups AS (
SELECT
seat_number,
seat_number - ROW_NUMBER() OVER (ORDER BY seat_number) as group_id
FROM cinema_seats
WHERE is_available = 1
)
SELECT
MIN(seat_number) as range_start,
MAX(seat_number) as range_end,
COUNT(*) as consecutive_seats
FROM seat_groups
GROUP BY group_id
ORDER BY range_start;
20. Calculate total sales, but only count orders above the average order value per customer.
- Concept: correlated avg and conditional sum.
- Approach: Find the average of each customer and sum only his bad orders.
Answer:
WITH customer_avg AS (
SELECT
customer_id,
AVG(order_amount) as avg_order_amount
FROM orders
GROUP BY customer_id
)
SELECT
o.customer_id,
SUM(CASE
WHEN o.order_amount > ca.avg_order_amount
THEN o.order_amount
ELSE 0
END) as total_above_avg_sales
FROM orders o
JOIN customer_avg ca ON o.customer_id = ca.customer_id
GROUP BY o.customer_id;
21. Calculate median salary without using MEDIAN() function.
- Concept: ranking logic.
- Approach: choose middle rows and take avg or use percentile_cont.
Answer:
-- Method 1: Using ROW_NUMBER
WITH ranked_salaries AS (
SELECT
salary,
ROW_NUMBER() OVER (ORDER BY salary) as row_asc,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_desc
FROM employees
)
SELECT AVG(salary) as median_salary
FROM ranked_salaries
WHERE row_asc IN (row_desc, row_desc - 1, row_desc + 1);
-- Method 2: Using PERCENTILE_CONT (if available)
SELECT
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary
FROM employees;
22. Find customers who bought product A and product B in the same order.
- Concept: EXISTS/self join logic.
- Approach: checking entries of both products in the same order_id.
Answer:
SELECT DISTINCT o.customer_id, o.order_id
FROM orders o
WHERE EXISTS (
SELECT 1 FROM order_items oi1
WHERE oi1.order_id = o.order_id
AND oi1.product_name = 'Product A'
)
AND EXISTS (
SELECT 1 FROM order_items oi2
WHERE oi2.order_id = o.order_id
AND oi2.product_name = 'Product B'
);
-- Alternative using self-join
SELECT DISTINCT oi1.order_id, oi1.customer_id
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id
WHERE oi1.product_name = 'Product A'
AND oi2.product_name = 'Product B';
23. Create a query that works when you don’t know column names in advance.
- Concept: dynamic schema handling.
- Approach: converting columns into rows using UNPIVOT/UNION ALL.
Answer:
-- Using UNPIVOT to handle unknown columns
SELECT
id,
column_name,
column_value
FROM your_table
UNPIVOT (
column_value FOR column_name IN (col1, col2, col3, col4)
) u;
-- Or using UNION ALL for flexibility
SELECT id, 'col1' as column_name, col1 as column_value FROM your_table WHERE col1 IS NOT NULL
UNION ALL
SELECT id, 'col2' as column_name, col2 as column_value FROM your_table WHERE col2 IS NOT NULL
UNION ALL
SELECT id, 'col3' as column_name, col3 as column_value FROM your_table WHERE col3 IS NOT NULL;
24. Find the first and last order date for each customer, and calculate the days between them.
- Concept: window first/last value.
- Approach: partition by customer to find min-max dates and difference.
Answer:
SELECT
customer_id,
order_date,
FIRST_VALUE(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as first_order_date,
LAST_VALUE(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as last_order_date,
DATEDIFF(
LAST_VALUE(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
),
FIRST_VALUE(order_date) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
) as days_between_first_last
FROM orders;
25. Optimize this slow query that finds customers with more than 10 orders.
- Concept: performance and aggregation.
- Approach: Using GROUP BY or a window function instead of a subquery.
Original slow query:
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE (
SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
) > 10;
Optimized Answer:
-- Method 1: Using JOIN instead of subquery
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN (
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10
) o ON c.customer_id = o.customer_id;
-- Method 2: Using window function
SELECT DISTINCT customer_id, customer_name
FROM (
SELECT
c.customer_id,
c.customer_name,
COUNT(*) OVER (PARTITION BY c.customer_id) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
) t
WHERE order_count > 10;
-- Method 3: Simple JOIN with GROUP BY
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 10;
Key Tips for Interview Success
Important Concepts to Remember:
- Window Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD()
- CTEs (Common Table Expressions): Great for complex queries
- Recursive CTEs: For hierarchical data
- JOIN Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS
- Subqueries: Correlated vs Non-correlated
- Performance: Use indexes, avoid SELECT *, use LIMIT
Common Mistakes to Avoid:
- Not handling NULL values properly
- Using NOT IN with NULL values
- Forgetting to use DISTINCT when needed
- Not considering performance implications
- Wrong JOIN types
- Incorrect GROUP BY usage
Best Practices:
- Always test your queries with sample data
- Consider edge cases (empty tables, NULL values)
- Use proper formatting and indentation
- Add comments for complex logic
- Think about performance and scalability
- Know your database-specific functions
This guide covers the most common tricky SQL query questions asked in interviews. Practice these patterns, and you will be ready for most SQL challenges.
Also Check:
