04b — WHERE (Part 2: Applied)

Companion to note 04. Same operators — here we build real queries on the DVD Rental DB and combine WHERE with COUNT.

The core workflow: build a query condition by condition

You don't write the whole filter at once. You start broad, then tack on conditions with AND / OR. Crucially, you write WHERE once — every extra condition just links on:

-- Start: one condition
SELECT * FROM film
WHERE rental_rate > 4;

-- Add a second (no second WHERE!)
SELECT * FROM film
WHERE rental_rate > 4
  AND replacement_cost >= 19.99;

-- Add a third
SELECT * FROM film
WHERE rental_rate > 4
  AND replacement_cost >= 19.99
  AND rating = 'R';

-- Then narrow the columns to just what you need
SELECT title FROM film
WHERE rental_rate > 4
  AND replacement_cost >= 19.99
  AND rating = 'R';

Notice numbers (4, 19.99) need no quotes, but the string 'R' does. pgAdmin color-codes these — strings, numbers, and keywords each get their own color, a quick visual sanity check.

COUNT + WHERE = "how many rows match?"

Once your filter is right, wrap it in COUNT to get a number instead of a list. This is one of the most common real-world query shapes.

-- How many films meet all three conditions?
SELECT COUNT(*) FROM film
WHERE rental_rate > 4
  AND replacement_cost >= 19.99
  AND rating = 'R';
--> 34

COUNT(*), COUNT(title), COUNT(film_id) all give 34 here — you're counting matching rows, so the column doesn't matter (assuming no NULLs — see note 03).

OR and != in action

-- Either rating (note: each side of OR is a FULL condition)
SELECT COUNT(*) FROM film
WHERE rating = 'R' OR rating = 'PG-13';
--> 418

-- Everything EXCEPT R
SELECT * FROM film
WHERE rating != 'R';   -- returns G, PG, PG-13, NC-17 — no R

The OR mistake everyone makes ⚠️

You cannot "share" the column across OR. This looks natural but is wrong:

-- ❌ ERROR / nonsense
WHERE rating = 'R' OR 'PG-13'

Each side of OR must be a complete, standalone condition:

-- ✅ correct
WHERE rating = 'R' OR rating = 'PG-13'

Cleaner alternative (coming soon): IN. When you're OR-ing the same column against several values, IN reads far better:

WHERE rating IN ('R', 'PG-13')   -- same result, less repetition

Keep this in your back pocket — the moment you write three or more ORs on one column, switch to IN.

Quirk worth knowing: the customer table

The customer table stores "is this customer active?" twice — as activebool (a true/false boolean) and active (an integer 1/0). Real databases accumulate redundant columns like this over time. Always check which column the app actually uses before filtering on it, or you may filter on the stale one.

Challenges

All use the DVD Rental database.

Challenge 1 — Find a specific person

A customer walks in and says their name is Nancy Thomas. Return all columns for that customer. (Table: customer; columns first_name, last_name.)

SELECT * FROM customer
WHERE first_name = 'Nancy'
  AND last_name = 'Thomas';

Challenge 2 — Count with conditions

How many films are rated PG and have a rental_rate of exactly 0.99?

SELECT COUNT(*) FROM film
WHERE rating = 'PG'
  AND rental_rate = 0.99;

Challenge 3 — Spot the bug 🔎

A teammate wants the count of films rated G, PG, or PG-13. They write:

SELECT COUNT(*) FROM film
WHERE rating = 'G' OR 'PG' OR 'PG-13';

It errors. Why, and what are two correct ways to write it?

SELECT COUNT(*) FROM film
WHERE rating = 'G' OR rating = 'PG' OR rating = 'PG-13';

SELECT COUNT(*) FROM film
WHERE rating IN ('G', 'PG', 'PG-13');

30-second recap