Full SELECT with DVD Rental

🎯Learning Objectives

• Describe the DVD Rental database schema and identify the main tables
• Write a basic SELECT … FROM … WHERE query to retrieve specific columns and rows
• Use ORDER BY with multiple columns and both ASC / DESC directions
• Apply LIMIT and OFFSET to paginate query results
• Create column aliases with AS and use expressions inside the SELECT list
• Explain when SELECT * is appropriate and when it should be avoided
• Use DISTINCT to eliminate duplicate values from a result set

📖Theory

1. The DVD Rental Schema

DVD Rental is a sample PostgreSQL database that models a movie rental store. You will use it throughout this course. The key tables for this lesson are:

You can explore any table's columns with:

-- List all columns in a table
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'film'
ORDER BY ordinal_position;

2. The Basic SELECT Statement

The most fundamental SQL statement retrieves rows from a table. The minimal syntax is:

SELECT column1, column2
FROM table_name;

PostgreSQL reads this as: "go to table_name, and for each row return only column1 and column2."

-- Retrieve three columns from the film table
SELECT title, rental_rate, length
FROM film;

This returns one row per film (1 000 rows in DVD Rental) with only the three requested columns. Without a WHERE clause, every row is returned.

3. SELECT * vs. Explicit Column Lists

SELECT * means "give me every column." It is useful for quick exploration but problematic in application code.

-- Exploration: fine in psql during development
SELECT * FROM film LIMIT 5;

-- Production: always name your columns
SELECT film_id, title, rental_rate, rating, length
FROM film;

4. Filtering Rows with WHERE

WHERE restricts which rows come back. The database evaluates the condition for every row and only returns rows where it is TRUE.

-- Only active customers (active = 1 means true in this schema)
SELECT first_name, last_name, email
FROM customer
WHERE active = 1;

Comparison operators available: =, <> (not equal), <, <=, >, >=.

-- Films with rental rate below $2
SELECT title, rental_rate
FROM film
WHERE rental_rate < 2.00;

-- Films with exactly the 'PG-13' rating
SELECT title, rating
FROM film
WHERE rating = 'PG-13';

5. Sorting Results with ORDER BY

Without ORDER BY, PostgreSQL returns rows in an unspecified order — it can vary between runs. Use ORDER BY whenever order matters.

-- Actors sorted alphabetically by last name, then first name
SELECT last_name, first_name
FROM actor
ORDER BY last_name ASC, first_name ASC;

ASC (ascending, A→Z, 1→9) is the default, so you can omit it. Use DESC to reverse:

-- Most expensive films first
SELECT title, rental_rate
FROM film
ORDER BY rental_rate DESC;

You can sort by a column that is not in the SELECT list — the column just needs to exist in the table.

6. Limiting Results with LIMIT and OFFSET

LIMIT n tells the database to return at most n rows. OFFSET k tells it to skip the first k rows before returning results. Together they implement pagination.

-- Page 1: films 1–10 alphabetically
SELECT title
FROM film
ORDER BY title
LIMIT 10 OFFSET 0;

-- Page 3: films 21–30 (skip first 20)
SELECT title
FROM film
ORDER BY title
LIMIT 10 OFFSET 20;

7. Column Aliases with AS

AS renames a column in the result set. The alias appears as the column header and can be used in ORDER BY, but not in WHERE (the database evaluates WHERE before it applies aliases).

-- Rename rental_rate to something friendlier
SELECT title,
       rental_rate        AS price,
       rental_duration    AS days_to_keep
FROM film;

Aliases are especially useful when you compute expressions in the SELECT list:

-- Convert dollars to cents using arithmetic
SELECT title,
       rental_rate * 100  AS rate_cents,
       length / 60.0      AS length_hours
FROM film;

PostgreSQL evaluates any valid expression here: arithmetic (*, /, +, -), string concatenation (||), and function calls.

8. Removing Duplicates with DISTINCT

DISTINCT removes duplicate rows from the result. It compares the entire row — or in SELECT DISTINCT col, just that column.

-- How many unique ratings exist?
SELECT DISTINCT rating
FROM film
ORDER BY rating;

Result: G, NC-17, PG, PG-13, R — five distinct values from 1 000 rows.

-- Unique combinations of rating AND rental_rate
SELECT DISTINCT rating, rental_rate
FROM film
ORDER BY rating, rental_rate;

💻Code Examples

Example A — Browsing the Film Catalogue

-- Show all films sorted by rental price (cheapest first),
-- then alphabetically within the same price tier.
-- Display price in cents for easier reading.

SELECT title,
       rating,
       rental_rate             AS price_usd,
       rental_rate * 100       AS price_cents,
       length                  AS length_min
FROM film
ORDER BY rental_rate ASC, title ASC
LIMIT 20;

What to notice:

• Two ORDER BY columns: primary sort is price, secondary is title.
• An arithmetic expression (* 100) in the SELECT list.
• LIMIT 20 keeps the result manageable for review.

Example B — Finding Active Customers

-- List all active customers with their emails,
-- sorted alphabetically by last name then first name.

SELECT customer_id,
       last_name || ', ' || first_name  AS full_name,
       email
FROM customer
WHERE active = 1
ORDER BY last_name ASC, first_name ASC;

What to notice:

• String concatenation with || to build a formatted full name.
• The alias full_name is used in the result but not in WHERE or ORDER BY (we sort by the original column names to stay safe).
• Filtering by active = 1 before sorting avoids processing inactive customers.

Example C — Paginated Film List

-- Simulate a "Films" page: show page 3 of films sorted by title.
-- Page size = 15 films per page.
-- Page 3 starts at row 31 (OFFSET = (page - 1) * page_size = 2 * 15 = 30).

SELECT film_id,
       title,
       rating,
       rental_rate
FROM film
ORDER BY title ASC
LIMIT 15 OFFSET 30;

What to notice:

• OFFSET 30 skips the first 30 rows, so the result starts at the 31st film.
• The formula for page n with page size s is: LIMIT s OFFSET (n-1) * s.
• ORDER BY title ASC ensures the same films always appear on the same page.

✏️Practice Tasks

SELECT title, rating, rental_rate
FROM film
ORDER BY rental_rate DESC, title ASC
LIMIT 10;

SELECT upper(last_name) || ', ' || initcap(first_name) AS actor_name
FROM actor
ORDER BY last_name ASC;

-- Query 1: page 4
SELECT film_id, title, length, rental_rate
FROM film
ORDER BY film_id
LIMIT 12 OFFSET 36;

-- Query 2a: distinct ratings
SELECT DISTINCT rating
FROM film
ORDER BY rating;

-- Query 2b: distinct rental rates
SELECT DISTINCT rental_rate
FROM film
ORDER BY rental_rate;

⚠️Common Mistakes

Using double quotes for string values

Writing WHERE rating = "PG-13" causes a syntax error. PostgreSQL interprets double quotes as identifiers (table/column names), not strings. Always use single quotes for literal values: WHERE rating = 'PG-13'.

Forgetting ORDER BY with LIMIT

SELECT title FROM film LIMIT 10 returns 10 rows, but which 10? Without an ORDER BY, the database engine picks whatever is fastest to retrieve — this can differ between runs. Add ORDER BY title (or any meaningful column) to get consistent, deterministic results.

Column alias in WHERE clause

Writing WHERE price_cents > 200 after aliasing rental_rate * 100 AS price_cents fails because WHERE is evaluated before the SELECT list is processed. Use the original expression: WHERE rental_rate * 100 > 200, or move the condition to a subquery.

Assuming SELECT * is always equivalent

SELECT * and SELECT col1, col2, col3 may return the same data today, but if a column is added to the table next month, SELECT * will include it automatically — potentially breaking downstream code that expects a fixed number of columns.

Not quoting mixed-case identifiers

PostgreSQL folds unquoted identifiers to lowercase. If a column was created as "firstName" (with quotes), you must always refer to it as "firstName" — not firstname or firstName. The DVD Rental schema uses lowercase snake_case throughout, so this is not an issue here, but keep it in mind for your own schemas.

🎓Instructor Notes

How to run this lesson (~80 min)

• [10 min] Schema overview. Open the DVD Rental ER diagram or run \dt in psql. Walk through the five key tables. Ask the class: "If you were running a video rental store, what data would you track?" Let them name things — film titles, customers, prices — then show where each lives in the schema.
• [20 min] Live code: sections 2–4. Start with SELECT * FROM film LIMIT 5 so students can see the column names. Then switch to an explicit column list. Add a WHERE clause. Each step, ask: "What do you expect the result to look like before I run it?"
• [15 min] Live code: sections 5–8. Add ORDER BY, then LIMIT/OFFSET. Do the pagination example live — ask "how do I get to page 2?" and let a student answer before running the query. Finish with DISTINCT on the rating column.
• [25 min] Task 1 in class. Task 1 is straightforward — most students finish in 5 minutes. Use the extra time to try variations: "What if you want the 10 cheapest films instead?" Walk around while they work.
• [10 min] Wrap-up. Review Tasks 2–3 as homework. Write the pagination formula OFFSET = (page - 1) * page_size on the board — students will need it for the assignment.

Discussion questions

• "Why would a web application use LIMIT and OFFSET instead of fetching all 1 000 films at once?"
• "Can you think of a situation where SELECT * is actually the right choice?"
• "If you sort by rental_rate DESC and two films have the same rate, what order do they appear in? How do you control that?"