SQL and MySQL course · Module 1: Introduction to databases
Map of the Library shelves
In this lesson5
Before we go on, let us get to know our treasury in detail - the biblioteka database. These five shelves will stay with you throughout the journey (in the Table Workshop you will build a few new ones). It is worth knowing what each one holds and which fields it has.
Five shelves
| Table | What it stores |
|---|---|
ksiazki | books: title, author, category, year, price, availability |
autorzy | the creators of the books: first name, last name, country, birth year |
kategorie | thematic sections: name |
czytelnicy | enrolled people: first name, last name, e-mail, city, enrollment date |
wypozyczenia | borrowing history: which book, by whom, when |
Fields of the ksiazki shelf
The ksiazki shelf is our main collection. It has the columns:
id- primary key (the scroll's catalog number),tytul- the title of the book,autor_id- a cross-reference to the authors shelf,kategoria_id- a cross-reference to the categories shelf,rok_wydania- the year of publication,cena- the price of the copy,dostepna- whether the book is on the shelf.
The autorzy and kategorie shelves
The autorzy shelf has the columns id, imie, nazwisko, kraj, rok_urodzenia, and the kategorie shelf has id and nazwa.
The czytelnicy shelf
The czytelnicy shelf stores the people enrolled in the Library. Its columns are id, imie, nazwisko, email, miasto, data_zapisu.
The wypozyczenia shelf
The wypozyczenia shelf records who took which book and when. Columns: id, ksiazka_id, czytelnik_id, data_wypozyczenia, data_zwrotu.
Remember these names - you will use them in almost every query in this land!
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. Which table stores information about who borrowed which book and when?
2. Which columns will we find in the autorzy table?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns and all rows from the kategorie table.
- Vertical ordering
Arrange the steps of building a simple data-retrieval query (from first to last):
- Code editor
Write a query that retrieves only the nazwa column from the kategorie table.
- Click in order
Click the elements in order to build a query that retrieves all borrowings: