SQL Coding Questions & Solutions (2025)

1. Select all columns from a table

Easy

Table: employees
Write a query to select all columns from employees table.

Output: All rows and columns from employees table

SELECT * FROM employees;

2. Select specific columns

Easy

Table: employees (id, name, salary, department)
Write a query to select only name and salary columns.

Output: name | salary

SELECT name, salary 
FROM employees;

3. Filter records using WHERE clause

Easy

Table: employees (id, name, salary, department)
Write a query to find employees with salary greater than 50000.

Output: All employees with salary > 50000

SELECT * 
FROM employees 
WHERE salary > 50000;

4. Order results by a column

Easy

Table: employees (id, name, salary, department)
Write a query to select all employees ordered by salary in descending order.

Output: Employees sorted by salary (highest first)

SELECT * 
FROM employees 
ORDER BY salary DESC;

5. Count total records

Easy

Table: employees
Write a query to count total number of employees.

Output: count
        100

SELECT COUNT(*) AS count 
FROM employees;

6. Find maximum value

Easy

Table: employees (id, name, salary, department)
Write a query to find the maximum salary.

Output: max_salary
        95000

SELECT MAX(salary) AS max_salary 
FROM employees;

7. Find minimum value

Easy

Table: employees (id, name, salary, department)
Write a query to find the minimum salary.

Output: min_salary
        25000

SELECT MIN(salary) AS min_salary 
FROM employees;

8. Calculate sum of a column

Easy

Table: orders (id, product_id, quantity, price)
Write a query to find total revenue (quantity * price).

Output: total_revenue
        250000

SELECT SUM(quantity * price) AS total_revenue 
FROM orders;

9. Select distinct values

Easy

Table: employees (id, name, salary, department)
Write a query to find all unique departments.

Output: department
        HR
        IT
        Finance

SELECT DISTINCT department 
FROM employees;

10. Filter using LIKE operator

Easy

Table: employees (id, name, salary, department)
Write a query to find employees whose name starts with 'A'.

Output: All employees with names starting with 'A'

SELECT * 
FROM employees 
WHERE name LIKE 'A%';

11. Group by with aggregate function

Medium

Table: employees (id, name, salary, department)
Write a query to find average salary per department.

Output: department | avg_salary
        HR         | 55000
        IT         | 75000

SELECT department, AVG(salary) AS avg_salary 
FROM employees 
GROUP BY department;

12. Filter grouped results using HAVING

Medium

Table: employees (id, name, salary, department)
Write a query to find departments with more than 5 employees.

Output: department | employee_count
        IT         | 8
        Sales      | 12

SELECT department, COUNT(*) AS employee_count 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

13. Inner join two tables

Medium

Table: employees (id, name, dept_id)
Table: departments (id, dept_name)
Write a query to get employee names with their department names.

Output: name    | dept_name
        John    | IT
        Sarah   | HR

SELECT e.name, d.dept_name 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.id;

14. Left join with NULL check

Medium

Table: employees (id, name, dept_id)
Table: departments (id, dept_name)
Write a query to find employees without a department.

Output: id | name  | dept_id
        5  | Mike  | NULL

SELECT e.* 
FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id 
WHERE d.id IS NULL;

15. Subquery in WHERE clause

Medium

Table: employees (id, name, salary, department)
Write a query to find employees earning more than average salary.

Output: Employees with salary > average

SELECT * 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

16. Find duplicate records

Medium

Table: employees (id, name, email)
Write a query to find duplicate email addresses.

Output: email              | count
        john@test.com      | 2
        sarah@test.com     | 3

SELECT email, COUNT(*) AS count 
FROM employees 
GROUP BY email 
HAVING COUNT(*) > 1;

17. Update records based on condition

Medium

Table: employees (id, name, salary, department)
Write a query to increase salary by 10% for IT department.

Output: Salary updated for IT employees

UPDATE employees 
SET salary = salary * 1.10 
WHERE department = 'IT';

18. Delete records with condition

Medium

Table: employees (id, name, salary, is_active)
Write a query to delete inactive employees.

Output: Inactive employees deleted

DELETE FROM employees 
WHERE is_active = 0;

19. Use CASE statement

Medium

Table: employees (id, name, salary)
Write a query to categorize salary as Low, Medium, High.

Output: name  | salary | category
        John  | 30000  | Low
        Sarah | 75000  | High

SELECT name, salary,
  CASE 
    WHEN salary < 40000 THEN 'Low'
    WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
    ELSE 'High'
  END AS category
FROM employees;

20. Find second highest salary

Medium

Table: employees (id, name, salary)
Write a query to find the second highest salary.

Output: second_highest
        85000

SELECT MAX(salary) AS second_highest 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

21. Self join to find pairs

Medium

Table: employees (id, name, manager_id)
Write a query to find employees with their manager names.

Output: employee | manager
        John     | Sarah
        Mike     | Sarah

SELECT e1.name AS employee, e2.name AS manager 
FROM employees e1 
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

22. Use UNION to combine results

Medium

Table: current_employees (id, name)
Table: former_employees (id, name)
Write a query to get all employee names (current and former).

Output: All unique employee names

SELECT name FROM current_employees
UNION
SELECT name FROM former_employees;

23. Calculate running total

Medium

Table: sales (id, sale_date, amount)
Write a query to calculate running total of sales.

Output: sale_date  | amount | running_total
        2024-01-01 | 100    | 100
        2024-01-02 | 150    | 250

SELECT sale_date, amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

24. Find employees with same salary

Medium

Table: employees (id, name, salary)
Write a query to find employees who have the same salary.

Output: salary | count
        50000  | 3
        60000  | 2

SELECT salary, COUNT(*) AS count 
FROM employees 
GROUP BY salary 
HAVING COUNT(*) > 1;

25. Get top N records per group

Medium

Table: employees (id, name, salary, department)
Write a query to get top 2 highest paid employees per department.

Output: department | name  | salary
        IT         | John  | 95000
        IT         | Sarah | 85000

SELECT department, name, salary 
FROM (
  SELECT department, name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked 
WHERE rn <= 2;

26. Complex join with aggregation

Hard

Table: orders (id, customer_id, order_date, amount)
Table: customers (id, name, city)
Write a query to find total spending per customer in 2024.

Output: name   | city      | total_spent
        John   | New York  | 5000
        Sarah  | Boston    | 7500

SELECT c.name, c.city, SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE YEAR(o.order_date) = 2024
GROUP BY c.id, c.name, c.city
ORDER BY total_spent DESC;

27. Find consecutive dates

Hard

Table: logins (user_id, login_date)
Write a query to find users who logged in for 3+ consecutive days.

Output: user_id | start_date | end_date
        101     | 2024-01-01 | 2024-01-03
        102     | 2024-01-15 | 2024-01-18

WITH ranked_logins AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
    DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp
  FROM logins
),
consecutive_groups AS (
  SELECT user_id, grp, MIN(login_date) AS start_date, 
    MAX(login_date) AS end_date, COUNT(*) AS days
  FROM ranked_logins
  GROUP BY user_id, grp
)
SELECT user_id, start_date, end_date
FROM consecutive_groups
WHERE days >= 3;

28. Pivot table transformation

Hard

Table: sales (product, quarter, amount)
Write a query to pivot quarterly sales data.

Output: product | Q1    | Q2    | Q3    | Q4
        Laptop  | 10000 | 12000 | 11000 | 15000
        Phone   | 8000  | 9000  | 8500  | 10000

SELECT product,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4
FROM sales
GROUP BY product;

29. Recursive CTE for hierarchy

Hard

Table: employees (id, name, manager_id)
Write a query to show employee hierarchy with levels.

Output: id | name  | level
        1  | CEO   | 0
        2  | VP    | 1
        3  | Mgr   | 2

WITH RECURSIVE emp_hierarchy AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  SELECT e.id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN emp_hierarchy eh ON e.manager_id = eh.id
)
SELECT id, name, level
FROM emp_hierarchy
ORDER BY level, id;

30. Calculate median salary

Hard

Table: employees (id, name, salary)
Write a query to calculate the median salary.

Output: median_salary
        65000

WITH ordered_salaries AS (
  SELECT salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS row_num,
    COUNT(*) OVER () AS total_count
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered_salaries
WHERE row_num IN (FLOOR((total_count + 1) / 2), CEIL((total_count + 1) / 2));

31. Find Nth highest salary per department

Hard

Table: employees (id, name, salary, department)
Write a query to find 3rd highest salary in each department.

Output: department | third_highest
        IT         | 75000
        Sales      | 68000

WITH ranked_salaries AS (
  SELECT department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT department, salary AS third_highest
FROM ranked_salaries
WHERE salary_rank = 3;

32. Find gaps in sequential data

Hard

Table: invoices (invoice_num)
Write a query to find missing invoice numbers.

Output: missing_start | missing_end
        103           | 105
        110           | 110

WITH invoice_ranges AS (
  SELECT invoice_num,
    LEAD(invoice_num) OVER (ORDER BY invoice_num) AS next_num
  FROM invoices
)
SELECT invoice_num + 1 AS missing_start,
  next_num - 1 AS missing_end
FROM invoice_ranges
WHERE next_num - invoice_num > 1;

33. Calculate year-over-year growth

Hard

Table: revenue (year, amount)
Write a query to calculate YoY growth percentage.

Output: year | amount | yoy_growth
        2023 | 100000 | NULL
        2024 | 120000 | 20.00

SELECT year, amount,
  ROUND(
    ((amount - LAG(amount) OVER (ORDER BY year)) * 100.0 / 
    LAG(amount) OVER (ORDER BY year)), 2
  ) AS yoy_growth
FROM revenue
ORDER BY year;

34. Find customers with orders in all categories

Hard

Table: orders (customer_id, category)
Table: categories (category_name)
Write a query to find customers who ordered from all categories.

Output: customer_id
        101
        105

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT category) = (
  SELECT COUNT(*) FROM categories
);

35. Complex date range analysis

Hard

Table: subscriptions (user_id, start_date, end_date)
Write a query to find overlapping subscription periods.

Output: user_id | overlap_days
        101     | 15
        102     | 30

SELECT s1.user_id,
  DATEDIFF(
    LEAST(s1.end_date, s2.end_date),
    GREATEST(s1.start_date, s2.start_date)
  ) + 1 AS overlap_days
FROM subscriptions s1
INNER JOIN subscriptions s2 
  ON s1.user_id = s2.user_id 
  AND s1.id < s2.id
WHERE s1.start_date <= s2.end_date 
  AND s2.start_date <= s1.end_date;