One fact, one place

SQL · Unit 12: Designing tables

Full plan
SQL
9 exercises

Another database, the neighborhood library. Ann borrowed "The Lighthouse" on January 2, "Three Winters" on the 10th, and "The Blue House" on the 13th. Is it the same Ann all three times? Two units ago you counted her visits to the front desk assuming it was.

The library treated its people two different ways. It gave the authors a table, and Erin Reilly's two books store a 1 in author_id. The members got none. Whoever lends a book types the name in by hand, and member is plain text in each row of loans.

That table can't answer the question. All it knows about Ann is three letters, typed three times.

SELECT id, member, borrowed
FROM loans
WHERE member = 'Ann';

Returns

id	member	borrowed
1	Ann	2026-01-02
4	Ann	2026-01-10
6	Ann	2026-01-13

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Today, January 15, another Ann becomes a member, a neighbor of the first, and borrows "Short Stories," book 8. The first Ann has "The Blue House" out. The librarian wants to know how many books Ann has out, and runs this. What does the SELECT return?
2. Multiple choice
members now exists. To stop typing names by hand, loans would have to swap its member column for another one. Which?
3. Predict the output
With that same table, the librarian wants to know how many books each member has borrowed. What does the SELECT return?
4. Find the bug
Starting today Rose goes by Rose Bell, and the school secretary changes it in the database. The change was meant for all her courses. The SELECT shows that Math is taught by Rose Bell and Science is still taught by Rose. Which line has the problem?
5. Predict the output
The school cancels Music, course 4, which Irene taught. First its grades go and then the course, because of the foreign key rule. Which teachers does the database know about afterward?
6. Complete the code
The school moves its teachers out to a table of their own, starting with Rose. Then Rose starts going by Rose Bell. Fill in the blanks in the UPDATE that changes it.
7. Multiple choice
In products, the word Accessories is written in three rows, the same as Rose in two courses. Should the categories move out to a table of their own?
8. Multiple choice
In the store, what an order cost is calculated with the price the product has today. If the Mouse goes up from 19.9 to 25, this month's orders for it become worth more, even though they were already paid for. Someone suggests storing in each order the price it sold at. Does that break the rule of one fact, one place?
9. Write the code
The school starts after-school workshops. The first draft was a single table, with the workshop's name and, next to it, its teacher's name and phone number, repeated in every workshop that teacher teaches. Build it as two tables, with the columns listed in the comments, in that order. Each table's id is the usual id INTEGER PRIMARY KEY. Write the types as INTEGER and TEXT.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →