SQL and MySQL course · Module 11: Capstone project

The Magnum Opus - Callimachus's challenge

2 min read ·
In this lesson2

Welcome one last time! Look how far you have come. When you stood at the Library Gate, you could not write a single SELECT. Today you know filtering (WHERE), sorting (ORDER BY), joins (JOIN), grouping (GROUP BY), subqueries, indexes and transactions. I am proud of you.

But a true master does not merely read the catalog - they create it. So I set before you one final challenge: you will design your own small library system.

What we will build together

In this location we will revisit the whole art of data management, but this time from the architect's side:

  • You will design a new table and link it to an existing one through a foreign key.
  • You will fill it with data using the INSERT statement.
  • You will ask wise questions by joining tables and grouping results.
  • You will reach for subqueries and HAVING to draw out hidden knowledge.

Foreign key - a short reminder

A foreign key (FOREIGN KEY) is a cross-reference between shelves. It points to a column in another table and ensures the value always matches an existing row:

CREATE TABLE recenzje (
  id INT PRIMARY KEY AUTO_INCREMENT,
  ksiazka_id INT,
  tresc TEXT,
  FOREIGN KEY (ksiazka_id) REFERENCES ksiazki(id)
);

Here ksiazka_id always points to a real book on the ksiazki shelf. Thanks to this, no "ghost" review will ever exist - an opinion about a book that is not there.

Take a deep breath. It is time to create something of your own. THE TREASURY OPENS FOR YOU!

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. What is the purpose of a foreign key (FOREIGN KEY) in the recenzje table?

  2. 2. What is the purpose of a primary key (PRIMARY KEY) in a table?

Hands-on tasks in the game

  • Code editor

    Write a CREATE TABLE statement that creates the recenzje table with columns: id (INT, primary key, AUTO_INCREMENT), ksiazka_id (INT), tresc (TEXT) and ocena (INT). Add a foreign key: the ksiazka_id column must reference the id column in the ksiazki table.

  • Code editor

    Write an INSERT statement that adds TWO rows to the recenzje table (columns ksiazka_id, tresc, ocena): (1, 'Wspaniała lektura', 5) and (2, 'Trudna, ale wartościowa', 4).

  • Horizontal ordering

    Arrange in one line the beginning of the statement that creates a new shelf ulubione:

  1. Coding taskDesign the reviews shelf
  2. QuestionThe role of a foreign key
  3. QuestionThe role of a primary key
  4. Coding taskInsert the first reviews
  5. Put in orderA new ulubione shelf

Useful articles