Exercises

Objectives

By the end of this lesson, you should be able to:

• Build reporting queries with subqueries and CTEs
• Add conditional logic and NULL handling to a query
• Combine two queries’ results with UNION, INTERSECT, or EXCEPT

⚠️ A note on verification: every query and result in this lesson was run against a real, live PostgreSQL 18 database.

Setup

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10,2) NOT NULL,
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
  ('Erin', 'Castillo', 'Engineering', 74000, '2021-03-15'),
  ('Jordan', 'Blake', 'Sales', 61000, '2022-06-01'),
  ('Maya', 'Fischer', 'Marketing', 67000, '2020-11-20'),
  ('Priya', 'Desai', 'Engineering', 89000, '2019-01-10'),
  ('Sam', 'Whitfield', 'Sales', 63000, '2023-02-14'),
  ('Devon', 'Ellery', NULL, 58000, '2023-08-30'),
  ('Taylor', 'Nakamura', 'Engineering', 95000, '2018-05-05'),
  ('Alexis', 'Moreno', 'Marketing', 71000, '2021-09-12');

CREATE TABLE contractors (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);

INSERT INTO contractors (first_name, department) VALUES ('Erin', 'Engineering'), ('Robin', 'Design');

Exercise 1: Subqueries and CTEs

a) Write a query for every employee earning below the company average, using a plain subquery. Expected result: Alexis, Devon, Jordan, Maya, and Sam.

b) Write a CTE computing total payroll per department (excluding NULL), then select only departments with total payroll over 130000. Expected result: Engineering (258000.00) and Marketing (138000.00).

c) Write a correlated subquery for every employee earning more than their own department’s average. Expected result: Alexis, Priya, Sam, and Taylor.

Exercise 2: CASE, COALESCE, and NULLIF

a) Write a query labeling every employee 'Veteran' (hired before 2020-01-01) or 'Recent' (hired on or after), using CASE. Expected result: only Priya and Taylor are Veteran, everyone else is Recent.

b) Write a query showing every employee’s department, replacing NULL with 'Unassigned' using COALESCE. Expected result: Devon shows 'Unassigned', everyone else shows their real department.

c) Write a query for 58000 / NULLIF(0, 0), and confirm it returns NULL rather than raising a division-by-zero error.

Exercise 3: EXISTS, ANY, and ALL

a) Write a query using EXISTS to find every department with at least one employee hired before 2020-01-01. Expected result: Engineering only.

b) Write a query using ANY for every employee earning more than at least one Marketing employee. Expected result: Alexis, Erin, Priya, and Taylor.

c) Write the same question using ALL instead. Expected result: Erin, Priya, and Taylor, explain in your own words why Alexis drops out compared to (b).

Exercise 4: UNION, INTERSECT, and EXCEPT

a) Write a UNION combining every first name from employees and contractors. Expected result: 9 distinct names, including Robin and Erin exactly once each.

b) Write an INTERSECT finding names present in both tables. Expected result: Erin only.

c) Write an EXCEPT finding contractors who are not also employees. Expected result: Robin only.

Recap

This module covered every core advanced-query tool: subqueries and correlated subqueries for multi-step questions, CTEs for naming and chaining them readably, CASE for conditional logic, COALESCE and NULLIF for graceful NULL handling, EXISTS/ANY/ALL for existence and comparison checks, and UNION/INTERSECT/EXCEPT for combining separate query results. Together with everything from Modules 6 through 10, this is a complete toolkit for answering real, complex questions against a PostgreSQL database.

Next module: database objects and performance, packaging queries as views and speeding them up with indexes.