SQL and MySQL course Β· Module 11: Capstone project

The Chamber of Riddles - subqueries and HAVING

1 min read Β·
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. 1. How does HAVING differ from WHERE?

  2. 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):

  1. Coding taskBooks more expensive than average
  2. QuestionDifference between WHERE and HAVING
  3. Coding taskActive readers - HAVING
  4. QuestionWhat a subquery is
  5. Coding taskCategories with many books
  6. Put in orderReaders with more than two loans

Useful articles