SQL and MySQL course Β· Module 11: Capstone project
The Chamber of Riddles - subqueries and HAVING
In this lesson3
The deepest questions require a question within a question. In the Chamber of Riddles you learned subqueries and HAVING - a filter for whole groups.
Subquery - a query inside a query
A subquery is a query nested inside another, usually in parentheses. It lets you compare a row with the result of a separate calculation:
SELECT tytul, cena
FROM ksiazki
WHERE cena > (SELECT AVG(cena) FROM ksiazki);First the database computes the average price of all books, then returns only those more expensive than it.
HAVING - a filter after grouping
WHERE filters single rows before grouping. HAVING filters whole groups after GROUP BY - usually based on the result of an aggregate function:
SELECT czytelnik_id, COUNT(*) AS liczba
FROM wypozyczenia
GROUP BY czytelnik_id
HAVING COUNT(*) > 2;This query returns only readers who borrowed more than two books.
WHERE or HAVING?
- Filtering single rows? β
WHERE. - Filtering groups after counting (e.g.
COUNT,AVG)? βHAVING.
These two tools together let you ask the Library truly wise questions!
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. How does HAVING differ from WHERE?
2. What is a subquery?
Hands-on tasks in the game
- Code editor
Write a query that retrieves tytul and cena from the ksiazki table only for those books whose cena is greater than the average price of all books. Compute the average with a subquery using the AVG(cena) function.
- Code editor
Write a query that groups the rows of the wypozyczenia table by czytelnik_id and shows czytelnik_id and the number of loans as liczba, but ONLY for readers with more than 2 loans. Use GROUP BY and HAVING.
- Code editor
Write a query that groups the rows of the ksiazki table by kategoria_id and shows kategoria_id and the number of books as liczba, but ONLY for categories with more than 5 books. Use GROUP BY and HAVING.
- Vertical ordering
Arrange the clauses of the query with HAVING in the correct order (top to bottom):