Top 50 Tricky SQL Queries for Interview

Hello friends, in this SQL hands-on tutorial, you’ll find 50 tricky SQL queries for practice with their answers. Practicing tricky SQL queries is like sharpening your tools for working with data. SQL skills matter because they help you handle and understand information in databases, something that’s valuable in many jobs and industries.

Tricky SQL Queries Interview Questions

We’ll provide you with a set of tricky SQL queries along with some demo tables. Please note that the complexity of these queries may vary, and they are designed to test various aspects of SQL knowledge. Before running these queries, make sure to create the necessary tables.

Moreover, we have thoroughly tested these queries by running them individually over a MySQL instance. They all run fine and produce results. For this, we have added enough demo data that you can also use.

Create Demo Tables

Create the following database schema, which will be used in the SQL queries you’ll practice with later.

CREATE TABLE depts (
    dept_code INT PRIMARY KEY,
    dept_title VARCHAR(255)
);

CREATE TABLE emps (
    emp_code INT PRIMARY KEY,
    emp_fname VARCHAR(255),
    dept_code INT,
    manager_id INT,
    join_date DATE,
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_code) REFERENCES depts(dept_code),
    FOREIGN KEY (manager_id) REFERENCES emps(emp_code)
);

CREATE TABLE salaries (
    emp_code INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (emp_code) REFERENCES emps(emp_code)
);

Seed Demo SQL Data

The following is the demo SQL data you should feed into the above tables. Please add or modify as per your will.

-- Departments
INSERT INTO depts VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Marketing');

-- Employees
INSERT INTO emps VALUES
(1, 'Robert', 1, NULL, '2022-01-01', 60000.00),
(2, 'Silvia', 2, 1, '2020-02-15', 80000.00),
(3, 'Amar', 2, 1, '2022-01-10', 80000.00),
(4, 'Akbar', 3, 2, '2022-04-20', 30000.00),
(5, 'Anthony', 3, 2, '2022-05-05', 60000.00),
(6, 'David', 4, 3, '2022-06-15', 80000.00),
(7, 'Grace', 4, 3, '2022-07-01', 80000.00),
(8, 'Frank', 1, NULL, '2022-08-10', 65000.00),
(9, 'Helen', 3, 2, CURDATE() - INTERVAL 1 YEAR, 70000.00),
(10, 'Salman', 4, 3, CURDATE() - INTERVAL 6 MONTH, 60000.00),
(11, 'Variko', 2, 1, CURDATE() - INTERVAL 1 YEAR, 80000.00),
(12, 'Aparichita', 3, 1, '2024-03-15', 67916),
(13, 'Saksham', NULL, 1, '2024-02-10', 80000),
(14, 'Vidushi', 1, 1, '2024-01-12', 60000);

-- Salaries
INSERT INTO salaries VALUES
(1, 60000.00),
(2, 70000.00),
(3, 70000.00),
(4, 55000.00),
(5, 60000.00),
(6, 80000.00),
(7, 80000.00),
(8, 65000.00),
(9, 70000.00),
(10, 75000.00),
(11, 100000.00),
(12, 67916.60),
(13, 80000.00),
(14, 60000.00);

Practice with the Tricky SQL Queries

Get an SQL compiler handy to start practicing with the below tricky queries.

1. Retrieve all employees and their departments, including those without a department.

Answer:

SELECT e.emp_code,
       e.emp_fname,
       d.dept_code,
       d.dept_title
FROM emps e
LEFT JOIN depts d ON e.dept_code = d.dept_code;

2. Find the second highest salary from the “salaries” table.

Answer:

SELECT MAX(salary) AS second_highest_salary
FROM salaries
WHERE salary < (SELECT MAX(salary) FROM salaries);

3. Calculate the average salary for each department.

Answer:

SELECT d.dept_code,
       d.dept_title,
       AVG(s.salary) AS avg_salary
FROM depts d
JOIN emps e ON d.dept_code = e.dept_code
JOIN salaries s ON e.emp_code = s.emp_code
GROUP BY d.dept_code, d.dept_title;

4. List the employees who have the same salary as the second highest-paid employee.

Answer:

-- Find the second-highest-paid salary
SELECT MAX(salary) AS second_highest_salary
FROM salaries
WHERE salary < (SELECT MAX(salary) FROM salaries);

-- List employees with the same salary as the second-highest-paid employee
SELECT e.emp_code,
       e.emp_fname,
       s.salary
FROM emps e
JOIN salaries s ON e.emp_code = s.emp_code
WHERE s.salary = (SELECT MAX(salary) AS second_highest_salary 
FROM salaries WHERE salary < (SELECT MAX(salary) FROM salaries));

5. Retrieve the employees who joined before their manager.

Answer:

SELECT e.emp_fname
FROM emps e
JOIN emps m ON e.manager_id = m.emp_code
WHERE e.join_date < m.join_date;

6. Find the top 3 departments with the highest average salary.

Answer:

SELECT d.dept_title, AVG(e.salary) AS avg_salary
FROM emps e
JOIN depts d ON e.dept_code = d.dept_code
GROUP BY d.dept_title
ORDER BY avg_salary DESC
LIMIT 3;

7. List the departments where the average salary is above the overall average salary.

Answer:

-- Calculate overall average salary
SELECT AVG(salary) AS overall_avg_salary FROM salaries;

-- List departments with average salary above overall average salary
SELECT d.dept_code,
       d.dept_title,
       AVG(s.salary) AS avg_salary
FROM depts d
JOIN emps e ON d.dept_code = e.dept_code
JOIN salaries s ON e.emp_code = s.emp_code
GROUP BY d.dept_code, d.dept_title
HAVING avg_salary > (SELECT AVG(salary) FROM salaries);

8. Update employee salaries to the maximum salary within their department. Also, print the old salary vs the new salary.

Answer:

-- Temporary table to store max salaries by department
CREATE TEMPORARY TABLE max_salaries AS
SELECT e.dept_code, MAX(s.salary) AS max_salary
FROM emps e
JOIN salaries s ON e.emp_code = s.emp_code
GROUP BY e.dept_code;

-- Display old salary, old max salary, and new salary for each employee
SELECT e.emp_code,
       e.emp_fname,
       s.salary AS old_salary,
       ms.max_salary AS old_max_salary,
       ms.max_salary AS new_salary
FROM emps e
JOIN salaries s ON e.emp_code = s.emp_code
JOIN max_salaries ms ON e.dept_code = ms.dept_code;

-- Update employee salaries to the maximum salary within their department
UPDATE salaries s
JOIN emps e ON s.emp_code = e.emp_code
JOIN max_salaries ms ON e.dept_code = ms.dept_code
SET s.salary = ms.max_salary;

9. Find the employees who have the same salary and department as their manager.

Answer:

SELECT e.emp_fname
FROM emps e
JOIN emps m ON e.manager_id = m.emp_code
WHERE e.salary = m.salary AND e.dept_code = m.dept_code;

10. Retrieve the cumulative salary for each employee considering the running total within their department.

Answer:

SELECT emp_fname, dept_code, salary, SUM(salary)
OVER (PARTITION BY dept_code ORDER BY salary)
AS cumulative_salary FROM emps;

Absolutely! Here are the modified SQL queries with each question and its corresponding answer separated:

11. Find the third maximum salary from the “salaries” table without using the LIMIT clause.

Answer:

SELECT salary
FROM salaries
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

12. List the employees who have never been assigned to a department.

Answer:

SELECT emp_fname
FROM emps
WHERE dept_code IS NULL;

13. Retrieve the employees with the highest salary in each department.

Answer:

SELECT emp_fname, dept_code, salary
FROM (
    SELECT emp_fname, dept_code, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY salary DESC) AS salary_rank
    FROM emps
) ranked
WHERE salary_rank = 1;

14. Calculate the median salary for each department.

Answer:

SELECT dept_code,
       AVG(salary) AS median_salary
FROM (
    SELECT dept_code,
           salary,
           ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY salary) AS row_num,
           COUNT(*) OVER (PARTITION BY dept_code) AS total_rows
    FROM emps
) ranked
WHERE row_num IN ((total_rows + 1) DIV 2, (total_rows + 2) DIV 2)
GROUP BY dept_code;

15. Find the employees who have the same manager as the employee with ID 3.

Answer:

SELECT emp_fname
FROM emps
WHERE manager_id = (SELECT manager_id FROM emps WHERE emp_code = 3);

16. Retrieve the employees who have the highest salary in their respective department and joined in the last 6 months.

Answer:

SELECT emp_fname, dept_code, salary
FROM (
    SELECT emp_fname, dept_code, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY salary DESC) AS salary_rank
    FROM emps
    WHERE join_date >= CURDATE() - INTERVAL 6 MONTH
) ranked
WHERE salary_rank = 1;

17. List the departments with more than 3 employees.

Answer:

SELECT dept_code, COUNT(emp_code) AS employee_count
FROM emps
GROUP BY dept_code
HAVING COUNT(emp_code) > 3;

18. Retrieve the employees with the second lowest salary.

Answer:

SELECT emp_code, emp_fname, salary
FROM emps
ORDER BY salary
LIMIT 1 OFFSET 1;

19. Find the departments where the highest and lowest salaries differ by more than $10,000.

Answer:

SELECT dept_code
FROM (
    SELECT dept_code, MAX(salary) - MIN(salary) AS salary_difference
    FROM emps
    GROUP BY dept_code
) diff
WHERE salary_difference > 10000;

20. Update the salaries of all employees in the “IT” department to be 10% higher. Also, make sure to print records displaying old vs new salary.

Answer:

-- Create a temporary table to store old salaries
CREATE TEMPORARY TABLE temp_old_salaries AS
SELECT emp_code, emp_fname, salary AS old_salary
FROM emps
WHERE dept_code = (SELECT dept_code FROM depts WHERE dept_title = 'IT');

-- Update the salaries of all employees in the "IT" department to be 10% higher
UPDATE emps
SET salary = salary * 1.1
WHERE dept_code = (SELECT dept_code FROM depts WHERE dept_title = 'IT');

-- Print old and new salaries after increment
SELECT o.emp_code, o.emp_fname, o.old_salary, e.salary AS new_salary
FROM temp_old_salaries o
JOIN emps e ON o.emp_code = e.emp_code;

-- Drop the temporary table
DROP TEMPORARY TABLE IF EXISTS temp_old_salaries;

Feel free to let us know if you have any specific queries you’d like us to answer. Here are the next 20 tricky SQL queries along with their answers.

21. Retrieve the employees who have the same salary as the employee with ID 2 in a different department.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 2 AND dept_code <> 1);

22. Calculate the difference in days between the hire dates of each employee and their manager.

Answer:

SELECT e.emp_fname, e.join_date, m.emp_fname AS manager_name, m.join_date AS manager_join_date,
       DATEDIFF(e.join_date, m.join_date) AS days_difference
FROM emps e
JOIN emps m ON e.manager_id = m.emp_code;

23. Find the departments where the sum of salaries is greater than the overall average salary.

Answer:

SELECT d.dept_title
FROM depts d
JOIN emps e ON d.dept_code = e.dept_code
GROUP BY d.dept_title
HAVING SUM(e.salary) > (SELECT AVG(salary) FROM emps);

24. List the employees who have the same salary as at least one other employee.

Answer:

SELECT e.emp_fname
FROM emps e
WHERE EXISTS (
    SELECT 1
    FROM emps
    WHERE salary = e.salary AND emp_code <> e.emp_code
);

25. Retrieve the employees with the highest and lowest salary in each department.

Answer:

SELECT dept_code, MAX(salary) AS max_salary, MIN(salary) AS min_salary
FROM emps
GROUP BY dept_code;

26. Find the employees who have the same salary as the employee with ID 2 and are in the same department.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 2) 
AND dept_code = (SELECT dept_code FROM emps WHERE emp_code = 2);

27. Calculate the average salary excluding the highest and lowest salaries in each department.

Answer:

SELECT dept_code,
       AVG(salary) AS average_salary
FROM (
    SELECT dept_code, salary,
           RANK() OVER (PARTITION BY dept_code ORDER BY salary) AS salary_rank_asc,
           RANK() OVER (PARTITION BY dept_code ORDER BY salary DESC) AS salary_rank_desc
    FROM emps
) ranked
WHERE salary_rank_asc > 1 
  AND salary_rank_desc > 1 
  AND salary_rank_asc < (SELECT COUNT(emp_code) FROM emps WHERE dept_code = ranked.dept_code) 
  AND salary_rank_desc < (SELECT COUNT(emp_code) FROM emps WHERE dept_code = ranked.dept_code) 
GROUP BY dept_code;

28. List the employees who have a higher salary than their manager.

Answer:

SELECT e.emp_fname
FROM emps e
JOIN emps m ON e.manager_id = m.emp_code
WHERE e.salary > m.salary;

29. Retrieve the top 5 departments with the highest salary sum.

Answer:

SELECT dept_code, SUM(salary) AS total_salary
FROM emps
GROUP BY dept_code
ORDER BY total_salary DESC
LIMIT 5;

30. Find the employees who have the same salary as the average salary in their department.

Answer:

SELECT emp_fname
FROM emps e
WHERE e.salary = (SELECT AVG(salary) FROM emps WHERE dept_code = e.dept_code);

SELECT e.emp_code, e.emp_fname, e.salary, d.dept_code, d.dept_title
FROM emps e
JOIN depts d ON e.dept_code = d.dept_code
WHERE e.salary = ROUND((SELECT AVG(salary) FROM emps WHERE dept_code = e.dept_code), 2);

31. Calculate the moving average salary for each employee over the last 3 months.

Answer:

SELECT emp_fname, join_date, salary,
       AVG(salary) OVER (PARTITION BY emp_code 
       ORDER BY join_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
       AS moving_avg_salary
FROM emps;

32. List the employees who have joined in the same month as their manager.

Answer:

SELECT e.emp_fname
FROM emps e
JOIN emps m ON e.manager_id = m.emp_code
WHERE MONTH(e.join_date) = MONTH(m.join_date) AND YEAR(e.join_date) = YEAR(m.join_date);

33. Retrieve the employees with salaries in the top 10% within their department.

Answer:

SELECT emp_fname, dept_code, salary
FROM (
    SELECT emp_fname, dept_code, salary,
           PERCENT_RANK() OVER (PARTITION BY dept_code ORDER BY salary) AS percentile_rank
    FROM emps
) ranked
WHERE percentile_rank >= 0.9;

34. Find the departments where the number of employees is greater than the number of employees in the “IT” department.

Answer:

SELECT dept_code
FROM (
    SELECT dept_code, COUNT(emp_code) AS employee_count
    FROM emps
    GROUP BY dept_code
) counts
WHERE employee_count > (SELECT COUNT(emp_code) FROM emps 
WHERE dept_code = (SELECT dept_code FROM depts WHERE dept_title = 'IT'));

35. Retrieve the employees who have the same salary as the employee with ID 2 in a different department and joined in the last year.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 2 AND dept_code <> 1) 
AND join_date >= CURDATE() - INTERVAL 1 YEAR;

36. Calculate the difference in salary between each employee and the employee with ID 1.

Answer:

SELECT emp_fname, salary,
       salary - (SELECT salary FROM emps WHERE emp_code = 1) AS salary_difference
FROM emps;

37. List the employees who have the same salary as the employee with ID 3 in the same department.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 3 
AND dept_code = (SELECT dept_code FROM emps WHERE emp_code = 3));

38. Retrieve the employees who have the same manager as the employee with ID 2 and are in a different department.

Answer:

SELECT emp_fname
FROM emps
WHERE manager_id = (SELECT manager_id FROM emps WHERE emp_code = 2) 
AND dept_code <> (SELECT dept_code FROM emps WHERE emp_code = 2);

39. Calculate the difference in years between the hire dates of each employee and the average hire date in their department.

Answer:

SELECT e.emp_fname, e.join_date,
       ABS(TIMESTAMPDIFF(YEAR, (SELECT AVG(join_date) FROM emps 
       WHERE dept_code = e.dept_code), e.join_date)) AS years_difference
FROM emps e;

40. Find the employees who have a salary equal to or more than the average salary across all departments.

Answer:

SELECT emp_fname
FROM emps
WHERE salary >= (SELECT AVG(salary) FROM emps);

Here are the next 10 tricky SQL queries along with their questions and answers:

41. Retrieve the employees who have the same salary as the median salary in their department.

Answer:

SELECT emp_fname
FROM (
    SELECT
        emp_fname,
        ROW_NUMBER() OVER (PARTITION BY dept_code ORDER BY salary) AS row_num,
        COUNT(*) OVER (PARTITION BY dept_code) AS total_count
    FROM emps
) ranked
WHERE row_num = CEIL(total_count / 2.0);

42. Calculate the difference in days between the hire dates of each employee and the hire date of the employee with the earliest hire date in their department.

Answer:

SELECT e.emp_fname, e.join_date,
       DATEDIFF(e.join_date, (SELECT MIN(join_date) 
       FROM emps WHERE dept_code = e.dept_code)) AS days_difference
FROM emps e;

43. Retrieve the employees who have the same salary as the employee with ID 5 in a different department.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 5)
  AND dept_code <> (SELECT dept_code FROM emps WHERE emp_code = 5);

44. List the employees who have the same manager as the employee with ID 5 and have a higher salary.

Answer:

SELECT emp_fname
FROM emps
WHERE manager_id = (SELECT manager_id FROM emps WHERE emp_code = 5)
AND salary > (SELECT salary FROM emps WHERE emp_code = 5);

45. Calculate the difference in months between the hire dates of each employee and the hire date of the employee with the latest hire date in their department.

Answer:

SELECT e.emp_fname, e.join_date,
       TIMESTAMPDIFF(MONTH, e.join_date, (SELECT MAX(join_date) 
FROM emps WHERE dept_code = e.dept_code)) AS months_difference
FROM emps e;

46. Retrieve the employees who have the same salary as the employee with ID 1 and joined in the last 6 months.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 1) 
AND join_date >= CURDATE() - INTERVAL 6 MONTH;

47. List the departments where the highest salary is more than twice the lowest salary.

Answer:

SELECT dept_code
FROM (
    SELECT dept_code, MAX(salary) AS max_salary, MIN(salary) AS min_salary
    FROM emps
    GROUP BY dept_code
) diff
WHERE max_salary > 2 * min_salary;

48. Retrieve the employees who have the same manager as the employee with ID 3 and are in the same department.

Answer:

SELECT emp_fname
FROM emps
WHERE manager_id = (SELECT manager_id FROM emps WHERE emp_code = 3)
 AND dept_code = (SELECT dept_code FROM emps WHERE emp_code = 3);

49. Calculate the difference in hours between the hire dates of each employee and the hire date of the employee with the earliest hire date in the company.

Answer:

SELECT emp_fname, join_date,
       ABS(TIMESTAMPDIFF(HOUR, join_date, (SELECT MIN(join_date) FROM emps))) AS hours_difference
FROM emps;

50. Find the employees who have the same salary as the employee with ID 2 and joined in the last year.

Answer:

SELECT emp_fname
FROM emps
WHERE salary = (SELECT salary FROM emps WHERE emp_code = 2)
 AND join_date >= CURDATE() - INTERVAL 1 YEAR;

Summary – Top 50 Tricky SQL Queries for Interview

The SQL queries provided here should work on popular databases like MySQL, SQL Server, and Oracle with only minor adjustments. These queries use common SQL commands like SELECT, FROM, WHERE, and others that are widely supported. Still, here are a few things to keep in mind.

It’s always a good idea to test your queries on the actual database you’re using. Check the documentation for your database system if you run into any issues or need to make adjustments. If you have questions on any query, feel free to ask for help. We’ll guide you through it.

We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.

All the very best,
Team TechBeamers