SQL Coding Problems Questions

Coding Practice

7 coding problems. Review solutions and explanations below each problem.

Question 1

Write an SQL query to find the second highest salary from an employee table.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use MAX salary less than the overall MAX salary, or use ranking functions if supported.

Question 2

Write an SQL query to display the names of employees who earn more than the average salary in their department.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use a correlated subquery or JOIN with a derived table that calculates average salary per department.

Question 3

Write an SQL query to find employees who joined in the last 30 days from a table with employee_id, name, and join_date columns.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use DATE_SUB(CURDATE(), INTERVAL 30 DAY) or INTERVAL '30' DAY depending on the database. Filter with WHERE join_date >= date_sub(curdate(), interval 30 day).

Question 4

Write an SQL query to find duplicate email addresses in a users table.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use GROUP BY email HAVING COUNT(*) > 1 to find duplicate emails.

Question 5

Write an SQL query to fetch the top 3 salaries from an employees table using a window function.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk and filter WHERE rnk <= 3.

Question 6

Write an SQL query to find departments that have more than 5 employees using JOIN and GROUP BY.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

JOIN employees with departments, GROUP BY department_id, HAVING COUNT(*) > 5.

Question 7

Write an SQL query to delete all records from a table while keeping the table structure.

Solve this coding problem in your preferred language. Review the solution below once you're done.

Solution

Use DELETE FROM table_name or TRUNCATE TABLE table_name. TRUNCATE is faster but cannot be rolled back in some databases.