Exercises

Objectives

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

• Build department summaries and sales reports using GROUP BY and aggregate functions
• Filter grouped results with HAVING
• Use COUNT(DISTINCT ...) to answer “how many unique” questions

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

Setup

This lesson uses both the employees table from earlier modules and a new orders table:

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

INSERT INTO employees (first_name, last_name, email, department, salary, hire_date) VALUES
  ('Erin', 'Castillo', 'erin.castillo@example.com', 'Engineering', 74000, '2021-03-15'),
  ('Jordan', 'Blake', 'jordan.blake@example.com', 'Sales', 61000, '2022-06-01'),
  ('Maya', 'Fischer', 'maya.fischer@example.com', 'Marketing', 67000, '2020-11-20'),
  ('Priya', 'Desai', 'priya.desai@example.com', 'Engineering', 89000, '2019-01-10'),
  ('Sam', 'Whitfield', 'sam.whitfield@example.com', 'Sales', 63000, '2023-02-14'),
  ('Devon', 'Ellery', 'devon.ellery@example.com', NULL, 58000, '2023-08-30'),
  ('Taylor', 'Nakamura', 'taylor.nakamura@example.com', 'Engineering', 95000, '2018-05-05'),
  ('Alexis', 'Moreno', 'alexis.moreno@example.com', 'Marketing', 71000, '2021-09-12');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    region VARCHAR(50) NOT NULL,
    product VARCHAR(50) NOT NULL,
    amount NUMERIC(10,2) NOT NULL
);

INSERT INTO orders (customer_name, region, product, amount) VALUES
  ('Riley Nguyen', 'West', 'Widget', 120.00),
  ('Riley Nguyen', 'West', 'Gadget', 45.00),
  ('Casey Brooks', 'East', 'Widget', 120.00),
  ('Morgan Alvarez', 'West', 'Widget', 120.00),
  ('Drew Patel', 'East', 'Gizmo', 75.00),
  ('Jamie Kowalski', 'East', 'Widget', 120.00),
  ('Riley Nguyen', 'West', 'Widget', 120.00);

Exercise 1: Department Summaries

Using employees:

a) Write a query showing each department and its headcount, including the NULL department group. Expected result: Engineering 3, Marketing 2, Sales 2, and 1 more with no department (Devon).

b) Write a query showing each department and its total payroll (SUM(salary)). Expected result: Engineering 258000.00, Marketing 138000.00, Sales 124000.00, no department 58000.00.

c) Write a query showing only departments with more than 1 employee, using HAVING. Expected result: Engineering, Marketing, and Sales, the no-department group (headcount 1) is excluded.

d) Write a query showing each department’s highest salary, keeping only departments where that maximum exceeds 80000. Expected result: Engineering only, at 95000.00.

Exercise 2: Sales Reports

Using orders:

a) Write a query showing total sales and order count per region. Expected result: East 315.00 across 3 orders, West 405.00 across 4 orders.

b) Write a query showing total sales per region and product combination. Expected result includes East/Gizmo 75.00, East/Widget 240.00, West/Gadget 45.00, West/Widget 360.00.

c) Write a query showing only region/product combinations with more than 1 order. Expected result: East/Widget (2 orders) and West/Widget (3 orders).

Exercise 3: Customer Statistics

Using orders:

a) Write a query showing each customer_name, their order count, and total amount spent. Expected result: Riley Nguyen has 3 orders totaling 285.00, everyone else has exactly 1 order.

b) Write a query showing only customers with more than 1 order. Expected result: Riley Nguyen only.

c) Write a query showing each customer and the number of distinct products they’ve ordered, using COUNT(DISTINCT product). Expected result: Riley Nguyen ordered 2 distinct products (Widget and Gadget), everyone else ordered 1.

Exercise 4: Product Analysis

Using orders:

a) Write a query showing each product, its order count, and total revenue. Expected result: Gadget 1 order/45.00, Gizmo 1 order/75.00, Widget 5 orders/600.00.

b) Write a query showing each product and the number of distinct regions it sold in, using COUNT(DISTINCT region). Expected result: Gadget and Gizmo each sold in 1 region, Widget sold in 2.

c) Explain, in your own words, why COUNT(DISTINCT region) for Widget (2) is different from its total order count (5), and what real business question this distinction answers.

Recap

This module covered every core grouping and summarizing tool: GROUP BY to form groups, aggregate functions to summarize each one, HAVING to filter groups after they’re formed, multi-column grouping for finer-grained summaries, and COUNT(DISTINCT ...) to count unique values within a group. Together, these turn a raw table into the kind of summary report a real business question actually asks for.

Next module: database relationships, how separate tables connect to each other through keys, setting up everything joins (Module 10) will build on.