Test and solidify your subquery engineering expertise with 15 production-grade practice problems categorized into Easy, Medium, and Hard engineering tiers. All problems run against the standard handbook schema (employes, departments, locations).
Find all employees whose hire date is earlier than the earliest hire date in Department 2.
Find all employees working in departments located in either ‘Nagpur’ or ‘Pune’ using an IN subquery.
Find all departments that currently have no assigned employees using NOT EXISTS.
Project every employee’s emp_name, dept_id, and the overall count of employees in the company as total_company_headcount.
Find all employees who report to the same manager as ‘Ammar’ (excluding ‘Ammar’ himself).
Find the employee(s) who were hired earliest within their own respective department using a correlated subquery.
Refactor Problem 6 into an optimized JOIN against a pre-aggregated Derived Table to eliminate SubPlan row-by-row execution.
Retrieve emp_name, dept_id, hire_date, and the minimum hire date of their department using a Window Function (OVER (PARTITION BY ...)).
> ANY)Find all employees hired after at least one employee in Department 1 using > ANY.
NOT IN Set MatchingFind all departments that have zero employees, safely utilizing NOT IN with an explicit IS NOT NULL guard predicate.
Find all employees whose department is located in ‘Nagpur’ AND who report to a manager whose department is located in ‘Mumbai’.
Find all managers who manage strictly more employees than the overall company average span-of-control (average direct reports per manager).
Find all location records (locations) that have no departments assigned to them using NOT EXISTS.
Retrieve departments whose headcount is strictly greater than the average headcount across all active departments.
Calculate the seniority rank (1 = earliest hire date) of each employee within their department using a correlated subquery counting earlier hires.
Complete, production-tested SQL solutions for all 15 practice problems are available in 16_SOLUTIONS.sql.