SQL and MySQL course Β· Module 4: Creating tables (DDL)
A full example - the recenzje shelf
In this lesson3
Time to combine everything you have learned: types and constraints in one complete statement. We will build the recenzje (reviews) shelf - here readers will leave ratings of books.
The whole shelf in one statement
CREATE TABLE recenzje (
id INT AUTO_INCREMENT PRIMARY KEY,
ksiazka_id INT NOT NULL,
ocena INT,
tresc TEXT,
data_dodania DATE
);Breakdown line by line
id INT AUTO_INCREMENT PRIMARY KEY- the primary key, numbered automatically. Each review gets its own catalog number.ksiazka_id INT NOT NULL- points to which book the review concerns. A required field - a review without a book does not exist.ocena INT- a numeric rating (e.g. from 1 to 5). A plain whole number.tresc TEXT- the content of the review, that is a long text without a strict limit.data_dodania DATE- when the review was added.
What to watch out for
- Each column is on a separate line - that reads most comfortably.
- After each definition there is a comma, except for the last one (
data_dodania DATE). - The whole thing is closed by a parenthesis
)and a semicolon ";".
This is a model shelf - remember its shape. We build most tables in the Library exactly like this: the primary key on top, then columns with appropriate types and constraints.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. After which column definition in the recenzje table do we NOT put a comma?
Hands-on tasks in the game
- Vertical ordering
Arrange the lines of the CREATE TABLE recenzje statement in the correct order, as they appear in the editor (top to bottom):
- Code editor
Write a CREATE TABLE statement that creates the recenzje table with columns: id of type INT with AUTO_INCREMENT and PRIMARY KEY, ksiazka_id of type INT NOT NULL, ocena of type INT, tresc of type TEXT and data_dodania of type DATE.
- Click in order
Click the elements in the order in which we close a CREATE TABLE statement (one line):