The Chamber of Riddles - subqueries and HAVING

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:

1SELECT tytul, cena
2FROM ksiazki
3WHERE 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:

1SELECT czytelnik_id, COUNT(*) AS liczba
2FROM wypozyczenia
3GROUP BY czytelnik_id
4HAVING 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!