SQL and MySQL course · Module 5: Modifying data (DML)
A great tidy-up in the Library - final project
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
idcounter. - NULL - you record the absence of a value where data does not exist yet.
- The scribe's golden rule -
UPDATEandDELETEalways withWHERE!
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?