Sub Query Practice Problems

3-Table SQL Subquery Practice — Department / Employee / Project (20 Examples)

This guide contains 20 ready-to-run subquery problems (with schema + sample data). Use Oracle, PostgreSQL or MySQL (minor syntax tweaks may apply).

Schema

-- Department table
CREATE TABLE Department (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR2(50)
);

-- Employee table
CREATE TABLE Employee (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR2(50),
  salary NUMBER(10,2),
  dept_id INT,
  hire_date DATE,
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

-- Project table
CREATE TABLE Project (
  proj_id INT PRIMARY KEY,
  proj_name VARCHAR2(50),
  dept_id INT,
  budget NUMBER(12,2),
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

Sample Data

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


-- Employees (Bangladeshi names)
INSERT INTO Employee VALUES (101, 'Ahsan', 55000, 1, TO_DATE('2020-01-15','YYYY-MM-DD'));
INSERT INTO Employee VALUES (102, 'Shuvo', 72000, 2, TO_DATE('2019-03-12','YYYY-MM-DD'));
INSERT INTO Employee VALUES (103, 'Mehedi', 48000, 3, TO_DATE('2021-06-01','YYYY-MM-DD'));
INSERT INTO Employee VALUES (104, 'Tania', 60000, 2, TO_DATE('2018-11-23','YYYY-MM-DD'));
INSERT INTO Employee VALUES (105, 'Farzana', 90000, 3, TO_DATE('2017-09-10','YYYY-MM-DD'));
INSERT INTO Employee VALUES (106, 'Sabbir', 40000, 4, TO_DATE('2022-02-05','YYYY-MM-DD'));
INSERT INTO Employee VALUES (107, 'Nusrat', 75000, 1, TO_DATE('2020-05-25','YYYY-MM-DD'));


-- Projects
INSERT INTO Project VALUES (201, 'Payroll System', 2, 150000);
INSERT INTO Project VALUES (202, 'Recruitment Portal', 1, 80000);
INSERT INTO Project VALUES (203, 'ERP Upgrade', 3, 250000);
INSERT INTO Project VALUES (204, 'Ad Campaign', 4, 60000);
INSERT INTO Project VALUES (205, 'AI Research', 3, 500000);

20 Practice Subquery Problems

Try solving these on your own first. Each block shows the full SQL solution.

1. Employees with salary above the overall average

SELECT emp_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

2. Employees in the same department as 'Eva'

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id = (SELECT dept_id FROM Employee WHERE emp_name = 'Eva');

3. Highest paid employee in each department

SELECT emp_name, dept_id, salary
FROM Employee e
WHERE salary = (SELECT MAX(salary) FROM Employee WHERE dept_id = e.dept_id);

4. Departments with no employees

SELECT dept_name
FROM Department
WHERE dept_id NOT IN (SELECT DISTINCT dept_id FROM Employee);

5. Projects in departments where average salary > 60,000

SELECT proj_name, dept_id, budget
FROM Project
WHERE dept_id IN (
  SELECT dept_id
  FROM Employee
  GROUP BY dept_id
  HAVING AVG(salary) > 60000
);

6. Employees in the department with the largest project budget

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id = (
  SELECT dept_id
  FROM Project
  WHERE budget = (SELECT MAX(budget) FROM Project)
);

7. Employees who joined before the earliest IT hire

SELECT emp_name, hire_date
FROM Employee
WHERE hire_date < (
  SELECT MIN(hire_date)
  FROM Employee
  WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'IT')
);

8. Employees in departments that have at least 2 projects

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id IN (
  SELECT dept_id
  FROM Project
  GROUP BY dept_id
  HAVING COUNT(proj_id) >= 2
);

9. Employees in departments with a project budget < 70,000

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id IN (
  SELECT dept_id
  FROM Project
  WHERE budget < 70000
);

10. Departments that have projects but no employees

SELECT dept_name
FROM Department
WHERE dept_id IN (SELECT dept_id FROM Project)
  AND dept_id NOT IN (SELECT dept_id FROM Employee);

11. Employees who earn more than the highest-paid HR employee

SELECT emp_name, salary
FROM Employee
WHERE salary > (
  SELECT MAX(salary)
  FROM Employee
  WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'HR')
);

12. Projects in the department with the maximum number of employees

SELECT proj_name, budget
FROM Project
WHERE dept_id = (
  SELECT dept_id
  FROM Employee
  GROUP BY dept_id
  ORDER BY COUNT(emp_id) DESC
  FETCH FIRST 1 ROWS ONLY
);

13. Employees in departments where total project budget > 300,000

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id IN (
  SELECT dept_id
  FROM Project
  GROUP BY dept_id
  HAVING SUM(budget) > 300000
);

14. Employees whose department has both employees and projects

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id IN (
  SELECT dept_id FROM Employee
  INTERSECT
  SELECT dept_id FROM Project
);

15. Employees in the department that owns the most expensive project

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id = (
  SELECT dept_id
  FROM Project
  WHERE budget = (SELECT MAX(budget) FROM Project)
);

16. Employees whose department has the least total project budget

SELECT emp_name, dept_id
FROM Employee
WHERE dept_id = (
  SELECT dept_id
  FROM Project
  GROUP BY dept_id
  ORDER BY SUM(budget) ASC
  FETCH FIRST 1 ROWS ONLY
);

17. Projects managed by departments with more than 3 employees

SELECT proj_name, budget
FROM Project
WHERE dept_id IN (
  SELECT dept_id
  FROM Employee
  GROUP BY dept_id
  HAVING COUNT(emp_id) > 3
);

18. Employees who joined after all HR employees

SELECT emp_name, hire_date
FROM Employee
WHERE hire_date > (
  SELECT MAX(hire_date)
  FROM Employee
  WHERE dept_id = (SELECT dept_id FROM Department WHERE dept_name = 'HR')
);

19. Youngest employee (latest hire_date) in each department

SELECT emp_name, dept_id, hire_date
FROM Employee e
WHERE hire_date = (
  SELECT MAX(hire_date)
  FROM Employee
  WHERE dept_id = e.dept_id
);

20. Projects in the department with the highest average salary

SELECT proj_name, budget
FROM Project
WHERE dept_id = (
  SELECT dept_id
  FROM Employee
  GROUP BY dept_id
  ORDER BY AVG(salary) DESC
  FETCH FIRST 1 ROWS ONLY
);