SQL and MySQL course · Module 2: Querying data (SELECT)
All together - building a report
In this lesson7
You have now mastered every art of the catalog scribe. It is time to bring them together into one - a true library report that selects, names, sorts and limits.
The report order
The Library Council meets tomorrow and has sent me a note with a single sentence: "The three most expensive books, with the title and price under proper headers". We will turn this sentence into a query layer by layer. After each step you will see what changed in the result.
Step 1: fields and shelf
First we choose the fields the Council asks for and point to the shelf they should come from:
SELECT tytul, cena FROM ksiazki;We have two fields, but all seventeen scrolls, in no fixed order:
| tytul | cena |
|---|---|
| Iliada | 39.90 |
| Odyseja | 42.00 |
| Państwo | 55.50 |
| … | … |
Step 2: headers
Now we give the fields proper headers. We add aliases, and the rest stays the same:
SELECT tytul AS Tytul, cena AS Cena FROM ksiazki;Only the headers have changed - the rows are the same as in step 1:
| Tytul | Cena |
|---|---|
| Iliada | 39.90 |
| Odyseja | 42.00 |
| Państwo | 55.50 |
| … | … |
Step 3: sorting
"Most expensive" means: from the highest price. We add ORDER BY with the descending direction:
SELECT tytul AS Tytul, cena AS Cena
FROM ksiazki
ORDER BY cena DESC;There are still seventeen rows, but now the most expensive stand at the top:
| Tytul | Cena |
|---|---|
| Elementy | 89.00 |
| Polityka | 61.20 |
| Etyka | 58.00 |
| Państwo | 55.50 |
| … | … |
Step 4: limit
The Council wants only three books, so at the very end we cut off the rest:
SELECT tytul AS Tytul, cena AS Cena
FROM ksiazki
ORDER BY cena DESC
LIMIT 3;Here is the finished report:
| Tytul | Cena |
|---|---|
| Elementy | 89.00 |
| Polityka | 61.20 |
| Etyka | 58.00 |
Read it like this: "Select the title and price (with pretty headers), from the shelf of books, arrange from the most expensive and show only three". The clauses always go in the order SELECT → FROM → ORDER BY → LIMIT.
Combining the arts
Nothing stops you from mixing several techniques in a single scroll. Here is the list of countries for the merchant from Rhodes - without repeats and this time surely in alphabetical order:
SELECT DISTINCT kraj
FROM autorzy
ORDER BY kraj ASC;Here DISTINCT removes the repetitions, and ORDER BY lines up the unique countries alphabetically:
| kraj |
|---|
| Grecja |
| Polska |
| USA |
When you use DISTINCT, MySQL sets one condition: you may sort only by fields from the SELECT list. With the default settings the query SELECT DISTINCT kraj FROM autorzy ORDER BY nazwisko; ends in an error. After sifting, one row "Grecja" stands for five authors, so there is no telling by which surname it should be placed.
And here is a ranking report with an alias, sorting and a limit:
SELECT tytul, cena AS Cena
FROM ksiazki
ORDER BY cena ASC
LIMIT 5;"The five cheapest books, with the price under a pretty header":
| tytul | Cena |
|---|---|
| Ajaks | 18.50 |
| Antygona | 19.90 |
| Medea | 21.00 |
| Dziady | 24.50 |
| Hamlet | 27.00 |
You can also sort by the alias: ORDER BY Cena ASC gives the same result, because MySQL recognizes it as the alias of the cena field.
The scribe's advice
Build the query in layers: first choose the columns, then point to the shelf, add the sorting, and finally limit the count. Before you click "Check solution", go through a short checklist:
- Order of clauses - SELECT → FROM → ORDER BY → LIMIT (→ OFFSET).
- Commas - yes between fields, no after the last field before FROM.
- Direction - ASC or DESC stands at every field it applies to.
- Numbers - LIMIT says how many to show, and OFFSET how many to skip.
- Aliases - letter case as in the task, and an alias with a space in quotes.
That is how clear, precise reports are made. Now try it yourself!
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. What is the correct order of clauses in an extended SELECT query?
2. What will the query "SELECT tytul AS Tytul, cena AS Cena FROM ksiazki ORDER BY cena DESC LIMIT 3;" do?
3. What is NOT true about an alias created with the word AS?
4. Which of these skills did you NOT learn in the Catalog Hall?
Hands-on tasks in the game
- Vertical ordering
Arrange the clauses of a full report in the correct order in which we write them in MySQL (top to bottom):
- Horizontal ordering
Arrange the elements in the correct order to build a query for the unique cities of readers (left to right):
- Code editor
Write a query that retrieves tytul and cena with the alias Cena from the ksiazki table, sorts ascending by price and shows the 5 cheapest books.
- Click in order
Click the elements in the order in which you write a query for the 5 newest books:
- Horizontal ordering
Arrange the elements in the correct order to build the query tail that shows the third page of 5 entries (left to right):
- Code editor
Write a query that retrieves tytul with the alias Tytul and rok_wydania from the ksiazki table, sorts descending by year of publication and shows the 10 newest books.
- Vertical ordering
Arrange the clauses of a query for the third page of cheapest books in the correct order (top to bottom):
- Code editor
Write a query that retrieves the unique values of the kraj column from the autorzy table and sorts them ascending (alphabetically).
Tasks after this lesson
- QuestionOrder of clauses in a query
- QuestionCombining an alias with sorting
- Put in orderClauses of a full report
- Put in orderA query for cities without repeats
- Coding taskFive cheapest books with an alias
- QuestionWhat the AS alias does NOT do
- Click in orderThe five newest books
- Put in orderThird page of 5 entries
- Coding taskThe masterful report
- Put in orderThird page of the cheapest books
- QuestionWhat comes after the Catalog Hall
- Coding taskUnique countries alphabetically