Lab 07 — Subqueries

Queries inside queries. Use subqueries to filter by computed values, create derived tables, and answer complex HR questions.

⏳ Loading SQL engine...

📋 AVAILABLE TABLES: departments, employees, projects, assignmentsdepartments(id, name, budget, location) employees(id, name, dept_id, salary, years, manager_id, hire_date) projects(id, name, dept_id, budget, status, start_date) assignments(employee_id, project_id, hours_per_week, role) Key relationships: employees.dept_id --> departments.id projects.dept_id --> departments.id assignments.employee_id --> employees.id assignments.project_id --> projects.id

📖 Concept Recap: Subqueries

A subquery is a SELECT statement nested inside another query, enclosed in parentheses.
Subquery in WHERE: filter rows by a computed value — WHERE salary > (SELECT AVG(salary) FROM employees)
Subquery in FROM: treat the result as a temporary table (derived table) — FROM (SELECT ...) AS sub
Correlated subquery: inner query references the outer row — executes once per outer row.
Use IN with subqueries that return a list: WHERE dept_id IN (SELECT id FROM departments WHERE ...)
Use NOT IN / NOT EXISTS to find rows with no matching counterpart.

👀 Worked Example (read-only)

Find employees earning above the company-wide average.

-- Employees earning above the overall average salary
SELECT name, salary, dept_id
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

✏ Guided

Exercise 1 — Employees in the Highest-Budget Department

Find all employees who work in the department with the largest budget. Fill in the aggregate function name to complete the nested subquery.

💡 Hint: The inner query finds the department with the highest budget: WHERE budget = (SELECT MAX(budget) FROM departments)

SELECT e.name, e.salary, e.years, d.name AS department, d.budget
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.dept_id = (
    SELECT id FROM departments
    WHERE budget = (SELECT ___(budget) FROM departments)
)
ORDER BY e.salary DESC;

💪 Independent

Exercise 2 — Correlated Subquery: Above Their Own Department Average

Find employees who earn more than the average salary of their own specific department. The subquery must reference the outer row's dept_id.

💡 Hint: WHERE e.salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id)

SELECT e.name,
       e.salary,
       d.name AS department,
       ROUND((SELECT AVG(salary) FROM employees e2
              WHERE e2.dept_id = e.dept_id), 0) AS dept_avg
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > (
    SELECT AVG(salary) FROM employees e2
    WHERE e2.dept_id = e.dept_id
)
ORDER BY d.name, e.salary DESC;

🔥 Challenge

Exercise 3 — Departments Where ALL Employees Earn Above $80,000

Use NOT IN to find departments that do not appear in the set of department IDs where any employee earns $80,000 or less. Show department stats.

💡 Hint: WHERE d.id NOT IN (SELECT DISTINCT dept_id FROM employees WHERE salary <= 80000)

-- Departments where every single employee earns above $80,000
SELECT d.id, d.name AS department, d.budget, d.location,
       COUNT(e.id) AS headcount,
       ROUND(AVG(e.salary), 0) AS avg_salary,
       MIN(e.salary) AS lowest_salary
FROM departments d
INNER JOIN employees e ON d.id = e.dept_id
WHERE d.id NOT IN (
    SELECT DISTINCT dept_id FROM employees
    WHERE salary <= 80000
)
GROUP BY d.id, d.name, d.budget, d.location
ORDER BY avg_salary DESC;

🏆 Mini Project

Mini Project — HR Subquery Analysis

Answer these four HR questions using subqueries:

Which employees are assigned to the highest-budget project?
Which departments have an above-average budget (above the mean of all departments)?
Which employees are assigned to more projects than the average number of assignments per employee (use a derived table subquery in FROM)?
The top earner in each department (correlated subquery in WHERE).

-- Q1: Employees on the highest-budget project
SELECT e.name, e.salary, p.name AS project, p.budget, a.role
FROM employees e
INNER JOIN assignments a ON e.id = a.employee_id
INNER JOIN projects p ON a.project_id = p.id
WHERE p.id = (SELECT id FROM projects WHERE budget = (SELECT MAX(budget) FROM projects))
ORDER BY e.salary DESC;

-- Q2: Departments with above-average budget
SELECT name, budget, location
FROM departments
WHERE budget > (SELECT AVG(budget) FROM departments)
ORDER BY budget DESC;

-- Q3: Employees assigned to more projects than average (derived table)
SELECT e.name, d.name AS dept, sub.proj_count AS projects_assigned
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN (
    SELECT employee_id, COUNT(*) AS proj_count
    FROM assignments GROUP BY employee_id
) sub ON e.id = sub.employee_id
WHERE sub.proj_count > (
    SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM assignments GROUP BY employee_id)
)
ORDER BY projects_assigned DESC;

-- Q4: Top earner in each department
SELECT e.name, e.salary, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary = (
    SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id
)
ORDER BY e.salary DESC;

🏁 Lab 07 Complete!

You can now write scalar subqueries in WHERE, derived table subqueries in FROM, and correlated subqueries that reference the outer query.

Next: manipulating text and dates with SQL functions.

Continue to Lab 08 — String & Date Functions →