Library design
- SQL course
- Intermediate
- Lesson 19/115
- 5 min
Set up book, member and loan tables and write skeleton queries.
Library design
In this project you will build a library database. First step: create books and authors tables.
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(100),
author_id INT
);Skeleton of the books table.
The table books is created.
Library design — details
Each table needs a primary key. author_id will later become a foreign key.
You first create the books and authors tables, then a loans table for borrowing records; each table has an id INT PRIMARY KEY.
Practice Library design
Create books with id, title and author_id columns.
Starter code
-- books table
CREATE TABLE booksSample solution
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(100),
author_id INT
)Expected output
Tablo oluşturuldu
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does a primary key do?
- Filters
- Uniquely identifies a row Correct answer
- Sorts them
- Counts
Explanation: A primary key uniquely identifies each row.
2 Add PRIMARY KEY
Question
CREATE TABLE books (
id INT ____,
title VARCHAR(100)
);
Answer
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(100)
);Explanation: PRIMARY KEY is the right answer.
3 Library design: match the terms
| Term | Meaning |
|---|---|
id |
primary key |
title |
title |
author_id |
foreign key |
INT |
integer |