SQL and MySQL course Β· Module 4: Creating tables (DDL)

A full example - the recenzje shelf

2 min read Β·
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. 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):

  1. Put in orderLines of the recenzje table
  2. QuestionComma in the definition
  3. Coding taskBuild the whole recenzje shelf
  4. Click in orderClosing a CREATE TABLE statement

Useful articles