SQL and MySQL course Β· Module 2: Querying data (SELECT)
The Catalog Hall - summary
In this lesson5
Well done! You leave the Catalog Hall a true scribe. Your queries no longer merely fetch data - they shape it.
What you have learned
- Choosing columns - take only the fields you need instead of
*. The order on the SELECT list decides the order in the result. - Aliases (AS) -
SELECT tytul AS Tytulgives a field a pretty label in the answer. - DISTINCT -
SELECT DISTINCT kraj FROM autorzyreturns only unique values. - ORDER BY -
ASC(ascending, the default) andDESC(descending); you can sort by several fields:ORDER BY rok_wydania DESC, tytul ASC. - LIMIT and OFFSET -
LIMIT 5takes five rows;LIMIT 5 OFFSET 10paginates results.
A decision map
When a reader comes with a request, break it down into simple questions. Each of them has its own place in the query:
| The reader's question | What you write in the query |
|---|---|
| Which fields? | a column list after SELECT |
| Which headers? | AS |
| Without repeats? | DISTINCT right after SELECT |
| In what order? | ORDER BY with ASC or DESC |
| How many rows and which page? | LIMIT and OFFSET |
The first three answers go into the SELECT part, and the last two at the very end of the query. If the request lacks one of the questions, you simply leave that clause out.
A masterful spell
Everything together in a single scroll - fields with headers, sorting and a limit:
SELECT tytul AS Tytul, cena AS Cena
FROM ksiazki
ORDER BY cena DESC
LIMIT 3;"Show the three most expensive books, with pretty headers Tytul and Cena":
| Tytul | Cena |
|---|---|
| Elementy | 89.00 |
| Polityka | 61.20 |
| Etyka | 58.00 |
Beautiful, is it not? And the shelf still holds seventeen scrolls with the original field names - only the answer has changed.
Five traps of this hall
These are the mistakes I see most often among young scribes. Each has a simple fix:
- A missing comma between fields.
SELECT tytul cenareturns one column with the alias "cena". Correct:SELECT tytul, cena. - LIMIT before ORDER BY.
... LIMIT 3 ORDER BY cena DESCis a syntax error. Correct:... ORDER BY cena DESC LIMIT 3. - DESC at the wrong field. In
ORDER BY rok_wydania, tytul DESConly the title descends. If the year should descend, writeORDER BY rok_wydania DESC, tytul. - Swapped numbers in LIMIT and OFFSET.
LIMIT 10 OFFSET 5shows ten rows starting from the sixth. The third page of five entries isLIMIT 5 OFFSET 10. - An alias with a space and no quotes.
AS Rok wydaniaends in an error. Correct:AS "Rok wydania".
When the editor does not accept your solution, go through this list from the top. Usually one of these five slips is to blame, and fixing it takes a moment.
What's next?
In the next location you will learn to filter scrolls with the WHERE clause - to pick only those that meet a condition (e.g. books cheaper than 30, or published after the year 2000). WHERE will stand right after FROM, still before ORDER BY. Here is a preview - books cheaper than 30, cheapest first:
SELECT tytul, cena
FROM ksiazki
WHERE cena < 30
ORDER BY cena ASC;This time it is not a field that drops out, but whole rows. Only the scrolls whose price meets the condition remain:
| tytul | cena |
|---|---|
| Ajaks | 18.50 |
| Antygona | 19.90 |
| Medea | 21.00 |
| Dziady | 24.50 |
| Hamlet | 27.00 |
| Uczta | 29.99 |
I will show you how to build such conditions in the next hall. The catalog is ready; it is time to learn to search with precision. Forward!
Spotted a mistake in this lesson?