Exercises

Objectives

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

• Combine WHERE, logical operators, BETWEEN/IN, LIKE/ILIKE, and IS NULL to filter real data
• Sort and page through results with ORDER BY, LIMIT, and OFFSET
• Read a query and predict its result before running it

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

Setup

Three tables for this exercise set. Run this once before starting:

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 products (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price NUMERIC(8,2) NOT NULL,
    in_stock BOOLEAN NOT NULL DEFAULT true
);

INSERT INTO products (product_name, category, price, in_stock) VALUES
  ('Wireless Mouse', 'Electronics', 24.99, true),
  ('Mechanical Keyboard', 'Electronics', 89.99, true),
  ('USB-C Cable', 'Electronics', 9.99, true),
  ('Desk Lamp', 'Home', 34.50, true),
  ('Standing Desk', 'Home', 349.00, false),
  ('Notebook', 'Office', 4.99, true),
  ('Ballpoint Pen Pack', 'Office', 6.50, true),
  ('Water Bottle', 'Home', 15.00, true),
  ('Webcam', 'Electronics', 49.99, false),
  ('Desk Organizer', 'Office', 12.25, true);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    signup_date DATE NOT NULL
);

INSERT INTO customers (first_name, last_name, city, signup_date) VALUES
  ('Riley', 'Nguyen', 'Denver', '2022-01-15'),
  ('Casey', 'Brooks', 'Austin', '2021-07-02'),
  ('Morgan', 'Alvarez', 'Seattle', '2023-03-20'),
  ('Drew', 'Patel', 'Denver', '2020-11-08'),
  ('Jamie', 'Kowalski', 'Austin', '2023-09-01');

Exercise 1: Filtering Employee Records

Using the employees table:

a) Write a query for every Engineering employee earning less than 80000. Expected result: Erin Castillo (74000) only, Priya Desai (89000) and Taylor Nakamura (95000) are Engineering but earn too much.

b) Write a query for every employee who is either in Sales, or was hired after 2022-01-01 (or both). Expected result: Jordan Blake, Sam Whitfield, and Devon Ellery (Devon qualifies on hire date alone, despite having no department).

c) Write a query for every employee with a salary BETWEEN 60000 and 70000. Expected result: Jordan Blake (61000), Maya Fischer (67000), Sam Whitfield (63000).

d) Write a query for the employee(s) with no department on record, using the correct NULL check. Expected result: Devon Ellery only.

Exercise 2: Searching Products

Using the products table:

a) Write a query for every product whose name contains the word Desk anywhere in it. Expected result: Desk Lamp, Standing Desk, Desk Organizer.

b) Write a query for every product in either the Electronics or Office category, using IN. Expected result: 7 rows, Wireless Mouse, Mechanical Keyboard, USB-C Cable, Notebook, Ballpoint Pen Pack, Webcam, and Desk Organizer.

c) Write a query for every in-stock product priced BETWEEN 10 and 50. Expected result: Wireless Mouse (24.99), Desk Lamp (34.50), Water Bottle (15.00), Desk Organizer (12.25). Note that Webcam (49.99) qualifies on price but is excluded, it’s out of stock.

d) Write a query using ILIKE that finds the Webcam row regardless of how its name is capitalized in the search text (test it with 'WEBCAM'). Expected result: Webcam.

Exercise 3: Sorting Customer Lists

Using the customers table:

a) Write a query that lists every customer’s first_name and signup_date, earliest signup first. Expected order: Drew, Casey, Riley, Morgan, Jamie.

b) Write a query that sorts customers by city ascending, and within each city, by last_name ascending. Expected order: Casey Brooks (Austin), Jamie Kowalski (Austin), Riley Nguyen (Denver), Drew Patel (Denver), Morgan Alvarez (Seattle).

Exercise 4: Retrieving Top Records

a) Write a query for the 3 highest-paid employees, name and salary only. Expected result: Taylor Nakamura (95000), Priya Desai (89000), Erin Castillo (74000).

b) Write a query for the single most expensive product. Expected result: Standing Desk, 349.00.

c) Customers are listed 2 per page, sorted by signup_date ascending. Write the query for page 2. Expected result: Riley Nguyen (2022-01-15) and Morgan Alvarez (2023-03-20), the 3rd and 4th rows in signup order.

d) Explain, in your own words, why LIMIT without an ORDER BY doesn’t reliably answer “give me the top N,” and what could go wrong if a page-2 query used a different ORDER BY than the page-1 query it followed.

Recap

This module covered every core tool for narrowing down and organizing query results: WHERE with comparison operators, AND/OR/NOT, BETWEEN, IN, LIKE/ILIKE, IS NULL, ORDER BY, and LIMIT/OFFSET. Together, these turn “everything in the table” into exactly the rows needed, in exactly the order needed.

Next module: built-in functions, transforming and computing values within a query instead of just selecting them as-is.