Cheat Sheet: SQL Query Interview Questions

1. Find the Second Highest Salary

SELECT MAX(e1.salary)
FROM employee e1
WHERE e1.salary < (
    SELECT MAX(e2.salary)
    FROM employee e2
);

2. Find the Nth Highest Salary

Option 1: Using LIMIT and OFFSET

SELECT DISTINCT e.salary
FROM employee e
ORDER BY e.salary DESC
LIMIT 1 OFFSET n-1; -- Replace 'n-1' with the calculated offset number

Option 2: Using a Correlated Subquery

SELECT DISTINCT e1.salary
FROM employee e1
WHERE n-1 = ( -- Replace 'n-1' with your target ranking index
    SELECT COUNT(DISTINCT e2.salary)
    FROM employee e2
    WHERE e2.salary > e1.salary
);

3. Find Duplicate Rows in a Table

SELECT e.salary, COUNT(e.salary)
FROM employee e
GROUP BY e.salary
HAVING COUNT(*) > 1;

4. Find Employees Who Earn More Than Their Manager

SELECT e.*
FROM employee e
JOIN employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

5. Count the Number of Employees in Each Department

SELECT
    d.id,
    CASE
        WHEN d.department_name IS NULL THEN 'No department'
        ELSE d.department_name
    END AS department,
    COUNT(*)
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY d.id, d.department_name
ORDER BY d.id;

6. Find the Department with the Highest Number of Employees

SELECT
    e.department_id,
    d.department_name,
    COUNT(*) AS emp_count
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY e.department_id, d.department_name
ORDER BY emp_count DESC
LIMIT 1;

7. Find Employees Who Do Not Belong to Any Department

SELECT *
FROM employee e
WHERE e.department_id NOT IN (
    SELECT d.id FROM department d
);

8. Fetch the Top 3 Highest Paid Employees in Each Department

SELECT d.department_name, e.*
FROM (
    SELECT
        e.*,
        RANK() OVER(
            PARTITION BY department_id
            ORDER BY salary DESC
        ) rnk
    FROM employee e
) e
LEFT JOIN department d ON e.department_id = d.id
WHERE rnk <= 3
ORDER BY department_id, rnk;

9. Find Employees Hired in the Last 30 Days

SELECT *
FROM employee e
WHERE e.hire_date >= CURRENT_DATE - INTERVAL '30 day';

10. Display Employee Name Along with Their Manager Name (Self Join)

SELECT e."name" AS emp_name, m."name" AS manager_name
FROM employee e
JOIN employee m ON e.manager_id = m.id;

11. Find the Total Salary Paid in Each Department

SELECT
    e.department_id,
    d.department_name,
    SUM(e.salary)
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY e.department_id, d.department_name
ORDER BY e.department_id;

12. Find Employees Whose Name Starts with 'A'

SELECT e."name"
FROM employee e
WHERE e."name" LIKE 'A%';

13. Fetch Employees Whose Salary is Between 30,000 and 50,000

SELECT e.*
FROM employee e
WHERE e.salary BETWEEN 30000 AND 50000;

14. Simulate a 10 Percent Salary Increase Preview

SELECT *, (e.salary * 1.10) AS new_salary
FROM employee e
WHERE e.department_id = 1;

15. Find the Average Salary of Each Department

SELECT e.department_id, d.department_name, AVG(e.salary)
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY e.department_id, d.department_name
ORDER BY e.department_id;

16. List Employees with No Assigned Projects (Anti-Join Pattern)

-- Preferred Method: Optimised Hash / Merge Joins and safe against NULLs
SELECT e.*
FROM employee e
LEFT JOIN project_assignment pa ON e.id = pa.emp_id
WHERE pa.emp_id IS NULL
ORDER BY e.id;

17. Fetch Current Date and Time

SELECT NOW();          -- Timestamp with time zone
SELECT CURRENT_DATE;   -- Date only
SELECT CURRENT_TIME;   -- Time only

18. Find the Number of Days Between Two Dates

SELECT end_date::date - start_date::date AS days_diff
FROM project p;

19. Concatenate First Name and Last Name into a Full Name

SELECT
    e.id,
    CONCAT(e.first_name, ' ', e.last_name) AS full_name
FROM employee e;

20. Find Employees with the Same Salary (Group Aggregation)

SELECT
    e.salary,
    STRING_AGG(e.name, ', ') AS aggregated_names
FROM employee e
GROUP BY e.salary
HAVING COUNT(*) > 1;

21. Find Departments Having More Than 5 Employees

SELECT e.department_id, d.department_name
FROM employee e
LEFT JOIN department d ON e.department_id = d.id
GROUP BY e.department_id, d.department_name
HAVING COUNT(*) > 5;

22. Swap the Values of Two Columns in a Table

UPDATE employee
SET
    first_name = last_name,
    last_name = first_name;

23. Find Maximum Salary in a Department Without Using MAX()

SELECT
    e1.department_id,
    e1.salary
FROM employee e1
LEFT JOIN employee e2
  ON e1.department_id = e2.department_id
  AND e1.salary < e2.salary
WHERE e2.salary IS NULL
ORDER BY e1.department_id;

24. Fetch Alternate Rows from a Table (Even Rows)

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM employee e
) t
WHERE t.rn % 2 = 0;

25. Find Employees Who Joined in a Particular Month

-- Method 1: Using EXTRACT (Numeric representation)
SELECT *
FROM employee e
WHERE EXTRACT(MONTH FROM e.hire_date) = 9;

-- Method 2: Using TO_CHAR (Text pattern representation)
SELECT *
FROM employee e
WHERE TO_CHAR(e.hire_date, 'Month') LIKE 'June%';

26. Find NULL Values in a Column

SELECT *
FROM employee e
WHERE e.manager_id IS NULL;

27. Rename a Column in an Existing Table

ALTER TABLE employee RENAME COLUMN name TO full_name;

28. Find the Total Number of Records in a Table

SELECT COUNT(*) FROM employee e;

29. Fetch the First and Last Record Combined From a Table

(SELECT * FROM employee ORDER BY id ASC LIMIT 1)
UNION
(SELECT * FROM employee ORDER BY id DESC LIMIT 1);

30. Delete Duplicate Rows (Keeping the Earliest Copy)

DELETE FROM employee e
USING employee e2
WHERE e.email = e2.email
  AND e.id > e2.id;