SQL and MySQL course Β· Module 2: Querying data (SELECT)

The Catalog Hall - summary

4 min read Β·
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 Tytul gives a field a pretty label in the answer.
  • DISTINCT - SELECT DISTINCT kraj FROM autorzy returns only unique values.
  • ORDER BY - ASC (ascending, the default) and DESC (descending); you can sort by several fields: ORDER BY rok_wydania DESC, tytul ASC.
  • LIMIT and OFFSET - LIMIT 5 takes five rows; LIMIT 5 OFFSET 10 paginates 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 questionWhat 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":

TytulCena
Elementy89.00
Polityka61.20
Etyka58.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:

  1. A missing comma between fields. SELECT tytul cena returns one column with the alias "cena". Correct: SELECT tytul, cena.
  2. LIMIT before ORDER BY. ... LIMIT 3 ORDER BY cena DESC is a syntax error. Correct: ... ORDER BY cena DESC LIMIT 3.
  3. DESC at the wrong field. In ORDER BY rok_wydania, tytul DESC only the title descends. If the year should descend, write ORDER BY rok_wydania DESC, tytul.
  4. Swapped numbers in LIMIT and OFFSET. LIMIT 10 OFFSET 5 shows ten rows starting from the sixth. The third page of five entries is LIMIT 5 OFFSET 10.
  5. An alias with a space and no quotes. AS Rok wydania ends 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:

tytulcena
Ajaks18.50
Antygona19.90
Medea21.00
Dziady24.50
Hamlet27.00
Uczta29.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?

  1. VideoModule summary

Useful articles