The task: a DVD rental chain with two stores wants answers about customers, stock, films and profit. The data sits in a 15-table relational database.
What I did: wrote 17 PostgreSQL queries, one per question, as part of the DigiGirls data programme.
Questions answered
- Customers: who pays the most, who has never rented, which customers share an address
- Films: most rented titles, films never rented, rentals per film and per rating
- Actors: film counts per actor, and actors in more than 20 films
- Stores: titles in one store but not the other, in either, or in both, and profit per store
Sample: films that have never been rented
SELECT f.title
FROM film AS f
LEFT JOIN inventory AS i ON f.film_id = i.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE r.inventory_id IS NULL;The left joins keep every film, and the IS NULL filter keeps only those with no rental record. That includes films the stores don't stock at all.
Skills
- Inner and left joins across several tables
- Grouped aggregates with
GROUP BYandHAVING - Subqueries for "top" and "not in" questions
- Reading an entity-relationship diagram to plan queries