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

All together - building a report

4 min read ·
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:

tytulcena
Iliada39.90
Odyseja42.00
Państwo55.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:

TytulCena
Iliada39.90
Odyseja42.00
Państwo55.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:

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

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

tytulCena
Ajaks18.50
Antygona19.90
Medea21.00
Dziady24.50
Hamlet27.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. 1. What is the correct order of clauses in an extended SELECT query?

  2. 2. What will the query "SELECT tytul AS Tytul, cena AS Cena FROM ksiazki ORDER BY cena DESC LIMIT 3;" do?

  3. 3. What is NOT true about an alias created with the word AS?

  4. 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).

  1. QuestionOrder of clauses in a query
  2. QuestionCombining an alias with sorting
  3. Put in orderClauses of a full report
  4. Put in orderA query for cities without repeats
  5. Coding taskFive cheapest books with an alias
  6. QuestionWhat the AS alias does NOT do
  7. Click in orderThe five newest books
  8. Put in orderThird page of 5 entries
  9. Coding taskThe masterful report
  10. Put in orderThird page of the cheapest books
  11. QuestionWhat comes after the Catalog Hall
  12. Coding taskUnique countries alphabetically

Useful articles