SQL and MySQL course Β· Module 11: Capstone project
Back to the Gate - SELECT, WHERE, ORDER BY
In this lesson4
Before we reach for the hardest tricks, let us return to the fundamentals. Every great query begins with three simple tools: selection, filter and order.
SELECT - what we want to see
SELECT names the columns, and FROM names the shelf we read from:
SELECT tytul, cena FROM ksiazki;WHERE - filtering rows
WHERE lets through only the rows that meet a condition. We can use the operators =, >, <, >=, <=, <>:
SELECT tytul, cena FROM ksiazki WHERE dostepna = 1;This question returns only books that are on the shelf.
ORDER BY - sorting results
ORDER BY arranges results by a chosen column. ASC is ascending order (the default), DESC is descending:
SELECT tytul, cena FROM ksiazki ORDER BY cena DESC;LIMIT - only the top
LIMIT restricts the number of returned rows. Together with sorting it produces rankings:
SELECT tytul, cena FROM ksiazki ORDER BY cena DESC LIMIT 3;This query shows the three most expensive books in the Library. Filter, order and limit - the everyday tools of every scribe!
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 purpose of the WHERE clause in a SELECT query?
2. How do you sort results in descending order (from largest to smallest)?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table only for books that are available (dostepna = 1), sorted ascending by price (cena).
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table, sorts the results descending by price and shows only the first 3 rows. Use ORDER BY ... DESC and LIMIT.
- Click in order
Click the elements in order to build a query filtering available books:
- Code editor
Write a query that retrieves tytul and rok_wydania from the ksiazki table only for books published after the year 2000 (rok_wydania > 2000).
- Horizontal ordering
Arrange in one line the clause that sorts results descending by price: