SELECT Practice

Goals

• Apply SELECT, WHERE, ORDER BY, LIMIT, and DISTINCT to answer real questions about DVD Rental data
• Use LIKE / ILIKE for flexible text search on film titles and actor names
• Combine BETWEEN, IN, and compound AND/OR conditions in a single query
• Identify and filter rows with NULL values using IS NULL and IS NOT NULL
• Write multi-column ORDER BY with mixed ASC / DESC directions

Warm-up

You have 5 minutes. Without checking notes, write a query from memory that:

• Selects title, rating, and length from the film table
• Filters for films rated 'PG'
• Orders by length descending
• Returns only the first 5 rows

Run it. If you get results, you are ready for the session. If not, review lesson 26 before continuing.

SELECT title, rating, length
FROM film
WHERE rating = 'PG'
ORDER BY length DESC
LIMIT 5;

Steps

Step 1 — Cheap Films, Longest First (Easy)

Context: The store wants to promote its cheapest films. List all films with a rental rate of exactly $0.99, showing the title, length, and rating. Present them from longest to shortest so customers can find full-length movies in the budget tier.

Task: Write a query on the film table that:

• Filters for rental_rate = 0.99
• Shows title, length, and rating
• Orders by length descending, then by title ascending when lengths are equal

Expected shape: Many rows (roughly 340 films at the $0.99 price point). The first row should be the longest cheap film.

SELECT title, length, rating
FROM film
WHERE rental_rate = 0.99
ORDER BY length DESC, title ASC;

Step 2 — Customers with Last Name Starting with 'S' (Easy)

Context: A customer calls in and says their last name starts with "S" but they cannot remember if their account is registered under their first name or a nickname. List all customers with last names beginning with 'S' so the agent can find the account quickly.

Task: Write a query on the customer table that:

• Filters for last names starting with the letter 'S'
• Shows first_name, last_name, and email
• Orders by last_name ascending, then first_name ascending

Expected shape: Around 50 customers. Rows ordered A→Z by last name.

SELECT first_name, last_name, email
FROM customer
WHERE last_name LIKE 'S%'
ORDER BY last_name ASC, first_name ASC;

Step 3 — Count Family Films in a Specific Length Range (Medium)

Context: A parent asks how many films the store has that are appropriate for children (rated 'PG' or 'G') and fit into a roughly 90-minute sitting (between 80 and 100 minutes).

Task: Write two queries:

1. A SELECT COUNT(*) query that returns the exact count of films meeting both criteria.
2. A full listing query showing title, rating, and length for all matching films, sorted by length ascending.

Expected shape for query 1: A single number. For query 2: Each qualifying film on its own row.

-- Query 1: count only
SELECT COUNT(*)
FROM film
WHERE rating IN ('PG', 'G')
  AND length BETWEEN 80 AND 100;

-- Query 2: full listing
SELECT title, rating, length
FROM film
WHERE rating IN ('PG', 'G')
  AND length BETWEEN 80 AND 100
ORDER BY length ASC;

Step 4 — Actors Whose First Name Contains 'an' (Medium)

Context: Someone is looking for an actor they saw credited as "Ann" or "Juan" but cannot remember the exact spelling. Search for all actors whose first name contains the letters 'an' anywhere (case-insensitive), since the name could be capitalised differently in the database.

Task: Write a query on the actor table that:

• Uses a case-insensitive pattern match on first_name for 'an' anywhere in the name
• Shows first_name and last_name
• Orders by last_name ascending

Expected shape: A list of actors — names like Ann, Anna, Juanita, Dan, Sandra, etc.

SELECT first_name, last_name
FROM actor
WHERE first_name ILIKE '%an%'
ORDER BY last_name ASC;

Step 5 — Customers Without an Email Address (Hard)

Context: The marketing team wants to send a promotional email campaign but first needs to identify which customers are missing an email address so they can be excluded from the mailing list (or contacted by phone instead).

Task: Write a query on the customer table that:

• Returns only customers where email is NULL
• Shows customer_id, first_name, and last_name
• Orders by last_name ascending

Also write a second query that returns the total count of customers without an email.

Expected shape: If DVD Rental has all emails populated, query 1 returns 0 rows — that is a valid result. The count query will confirm it with 0.

-- Query 1: listing
SELECT customer_id, first_name, last_name
FROM customer
WHERE email IS NULL
ORDER BY last_name ASC;

-- Query 2: count
SELECT COUNT(*)
FROM customer
WHERE email IS NULL;

Step 6 — Films Without an 'Epic' Description (Hard)

Context: A film critic who reviews epic blockbusters wants a list of all films that are either completely undescribed OR whose description does not mention the word 'epic' — so they can find films outside their usual genre.

Task: Write a query on the film table that returns films where:

• description is NULL, OR
• description does not contain the word 'epic' (case-insensitive)

Show title and rating. Order by title ascending. Also add a third column called has_description that shows 'Yes' when description is not NULL and 'No' when it is NULL — use a CASE expression for this.

Expected shape: Most films in the database, since only a subset will have 'epic' in their description. Two-column output plus the derived has_description flag.

SELECT title,
       rating,
       CASE WHEN description IS NULL THEN 'No' ELSE 'Yes' END  AS has_description
FROM film
WHERE description IS NULL
   OR description NOT ILIKE '%epic%'
ORDER BY title ASC;

Bonus Tasks

Bonus 1 — Three Cheapest Films per Rating

Find the three cheapest films (by rental_rate) for each rating category. For each rating, show title, rental_rate, and length, sorted by rental_rate ascending and then title ascending. (Hint: you can do this with a separate query per rating, or challenge yourself to look up the LIMIT trick inside a subquery — we cover subqueries fully in lesson 34.)

-- Example for 'G' rating — repeat for PG, PG-13, R, NC-17
SELECT title, rental_rate, length
FROM film
WHERE rating = 'G'
ORDER BY rental_rate ASC, title ASC
LIMIT 3;

Bonus 2 — Active Customers Without a Recent Address

The customer table has an address_id column linking to the address table. Write a query that lists active customers (active = 1) whose email contains neither '.com' nor '.org' (case-insensitive). Show customer_id, first_name, last_name, and email. Order by customer_id.

SELECT customer_id, first_name, last_name, email
FROM customer
WHERE active = 1
  AND email NOT ILIKE '%.com%'
  AND email NOT ILIKE '%.org%'
ORDER BY customer_id;

Bonus 3 — Film Length Extremes by Rating

For each rating (G, PG, PG-13, R, NC-17), write a single query that shows the shortest and longest film. Show rating, title, length, and a label column called extreme with value 'Shortest' or 'Longest'. (This is a union challenge — combine two queries with UNION ALL.)

-- Shortest per rating
SELECT rating, title, length, 'Shortest' AS extreme
FROM film
WHERE (rating, length) IN (
    SELECT rating, MIN(length) FROM film GROUP BY rating
)
UNION ALL
-- Longest per rating
SELECT rating, title, length, 'Longest' AS extreme
FROM film
WHERE (rating, length) IN (
    SELECT rating, MAX(length) FROM film GROUP BY rating
)
ORDER BY rating, extreme;