SQL from Zero › Module 8: Final project
Project: add reviews to the bookshop
Use every command family to design, fill and check a new feature: book reviews.
Lesson 32 of 33 · about 25 minutes
Video: Your first feature, end to endThe narrated video for this lesson is coming soon. The lesson below covers the same ideas.
The lesson
Time to put it all together. The bookshop wants customers to leave reviews: a star rating from 1 to 5, and an optional comment. You'll build the feature from start to finish, the way a real developer would.
The plan
- Design (DDL). A new
reviewstable. Each review belongs to one book and one customer, so it needs two foreign keys. - Fill (DML), safely (TCL). Add the first reviews inside a transaction.
- Protect. The database itself should refuse a rating of 0 or 6, whatever the website sends.
- Ask (DQL). Show each review with its book's title.
- Control (DCL). On a real server, you'd finish with
GRANT SELECT, INSERT ON reviews TO shop_website;, so the website can add and read reviews, but never delete them.
The design
| Column | Type | Rules |
|---|---|---|
| id | INTEGER | primary key |
| book_id | INTEGER | required, references books(id) |
| customer_id | INTEGER | required, references customers(id) |
| rating | INTEGER | required, CHECK (rating BETWEEN 1 AND 5) |
| comment | TEXT | optional |
CHECK is a rule you write yourself. The database tests it on every insert and update, and refuses any row that breaks it.
Each exercise below starts from the original bookshop, so later exercises include the CREATE TABLE for you. Foreign keys are switched on here, so a review for a book that doesn't exist is refused too.
Key ideas
- A feature touches every command family: DDL to design, DML to fill, TCL to keep it safe, DQL to ask, DCL to control access.
- CHECK (condition) lets the database enforce your own rules, like ratings from 1 to 5.
- Design on paper first: columns, types, keys and rules.
Practise
Loading practice…