Connecting the Labyrinth's corridors

You now know all the tools of the Labyrinth. It is time to learn to put them together - because real questions to the Library rarely fit into a single simple subquery.

Two conditions at once

Nothing stops us from combining a scalar subquery with the IN operator in one query. We simply join the conditions with

AND

:

1SELECT tytul
2FROM ksiazki
3WHERE cena < (SELECT AVG(cena) FROM ksiazki)
4  AND autor_id IN (SELECT id FROM autorzy WHERE kraj = 'Grecja');

This query finds cheap scrolls by Greek authors - cheaper than the average and written by a creator from Greece.

Nested EXISTS

Subqueries can be nested - one inside another. This is how we ask about authors who have books, but none of them has been borrowed:

1SELECT a.imie, a.nazwisko
2FROM autorzy AS a
3WHERE EXISTS (
4  SELECT 1 FROM ksiazki AS k
5  WHERE k.autor_id = a.id
6    AND NOT EXISTS (
7      SELECT 1 FROM wypozyczenia AS w WHERE w.ksiazka_id = k.id
8    )
9);

EXISTS

checks that the author has a book, and the nested

NOT EXISTS

- that no one has borrowed that book.

Subquery in SELECT

A scalar subquery can also be placed on the column list of SELECT - as a single computed value next to ordinary columns:

1SELECT tytul, cena, (SELECT AVG(cena) FROM ksiazki) AS srednia
2FROM ksiazki;

Each row will show the title, the book's price and - next to it - the average price of the whole Library. It is a convenient way to compare a value with its background without losing the details.

@name