SQL and MySQL course · Module 5: Modifying data (DML)

A great tidy-up in the Library - final project

2 min read ·
In this lesson3

The day of the great tidy-up has come. A new crate of books has arrived, a few prices need correcting, and damaged scrolls must be taken off the shelves. This is your final project - in it you will combine everything you have learned at the Scribe's Desk.

The scribe's task

Imagine you must carry out the following sequence of operations on the biblioteka database:

1. Write in new books (multi-row INSERT)

INSERT INTO ksiazki (tytul, autor_id, rok_wydania, cena, dostepna)
VALUES
  ('Almagest', 5, 150, 89.90, 1),
  ('Stozkowe', 6, -200, 64.50, 1);

2. Write in a new reader

INSERT INTO czytelnicy (imie, nazwisko, email, miasto, data_zapisu)
VALUES ('Hypatia', 'z Aleksandrii', 'hypatia@biblioteka.aleksandria', 'Aleksandria', '2024-04-01');

3. Correct a price (UPDATE with WHERE)

UPDATE ksiazki
SET cena = 99.90
WHERE id = 5;

4. Mark expensive books as unavailable (UPDATE with a condition)

UPDATE ksiazki
SET dostepna = 0
WHERE cena > 100;

5. Remove damaged scrolls (DELETE with WHERE)

DELETE FROM ksiazki
WHERE dostepna = 0;

The golden rule at the end

Notice that every UPDATE and DELETE has its own WHERE clause. That is no accident - it is the scribe's discipline. Before each command, ask yourself: which exact rows do I want to change? - and write the matching condition.

What you have achieved in this location

  • INSERT INTO ... VALUES (...) - you write in single and many rows at once.
  • UPDATE ... SET ... WHERE ... - you correct selected rows, also by conditions with >, AND, OR.
  • DELETE FROM ... WHERE ... - you remove exactly the indicated scrolls.
  • TRUNCATE TABLE ... - you clear a whole shelf and reset the id counter.
  • NULL - you record the absence of a value where data does not exist yet.
  • The scribe's golden rule - UPDATE and DELETE always with WHERE!

Congratulations! You have mastered the Scribe's Desk. The Library is now in your hands - treat this power with respect.

Spotted a mistake in this lesson?

  1. VideoModule summary

Useful articles