Podzapytania SQL — zadania od prostych do złożonych

Czasem odpowiedź na pytanie wymaga najpierw znalezienia innej wartości: „którzy uczniowie mają wynik powyżej średniej?” wymaga średniej, a „kto nie pisał sprawdzianu?” — listy uczniów, którzy go pisali. Podzapytanie to zapytanie SELECT zagnieżdżone w innym zapytaniu, które dostarcza taką wartość albo listę. Na lekcji uczniowie budują je od najprostszego do złożonego.

Scenariusz jest przeznaczony na 45 minut dla klasy znającej SELECT, WHERE, funkcje agregujące i IN — na przykład po lekcji o COUNT, SUM i AVG i lekcji o LIKE, IN i BETWEEN.

Przygotowanie stanowisk

Potrzebna jest baza MySQL 8 lub MariaDB (na przykład w XAMPP) i narzędzie do wykonywania zapytań, np. phpMyAdmin. Utwórz pustą bazę ćwiczeniową i zaimportuj raz plik baza.sql z pakietu. Plik nie usuwa tabel, więc ponowny import zgłosi błąd istniejącej tabeli. Zapytania zapisuj w start.sql; rozwiązania są w rozwiazania.sql.

Cele i plan lekcji

Po zajęciach uczeń potrafi:

• zapisać podzapytanie zwracające jedną wartość (np. średnią) i użyć go w WHERE;
• użyć podzapytania zwracającego listę w IN i NOT IN;
• zagnieździć podzapytanie w podzapytaniu;
• użyć podzapytania skorelowanego w SELECT i wyjaśnić pułapkę NOT IN z wartością NULL.

Tabele uczniowie i sprawdziany

Tabela uczniowie ma kolumny id, imie i klasa, a sprawdziany — id, uczen_id (numer ucznia) i punkty. Uczeń może mieć kilka sprawdzianów (Anna ma dwa), a niektórzy nie mają żadnego (Filip).

Rodzaje podzapytań

Podzapytanie zapisujemy w nawiasach. Zwracające jedną wartość (np. (SELECT AVG(punkty) FROM sprawdziany)) można porównać zwykłym operatorem: WHERE punkty > (SELECT AVG(...)). Zwracające listę stosujemy z IN lub NOT IN. Podzapytania można zagnieżdżać: najpierw wykonuje się najgłębsze.

Podzapytanie skorelowane odwołuje się do kolumn zapytania zewnętrznego i wykonuje się dla każdego wiersza zewnętrznego (np. WHERE s.uczen_id = u.id).

Uwaga na NOT IN: jeśli lista z podzapytania zawiera NULL, wynik NOT IN jest zawsze pusty, ponieważ porównanie z NULL nie daje prawdy ani fałszu. W naszych tabelach kolumna uczen_id jest NOT NULL, więc zapytanie działa poprawnie, ale w innych bazach warto użyć NOT EXISTS.

Zadania

Zadanie 1. Wypisz numer i wynik sprawdzianów, w których uczeń zdobył więcej punktów niż wynosi średnia ze wszystkich sprawdzianów, od najlepszego.

Zadanie 2. Wypisz imiona uczniów, którzy mają najwyższy wynik ze wszystkich sprawdzianów, alfabetycznie.

Zadanie 3. Wypisz imiona uczniów, którzy nie pisali żadnego sprawdzianu (NOT IN).

Zadanie 4. Wypisz alfabetycznie imiona uczniów z klasy 2A, którzy mają przynajmniej jeden wynik powyżej średniej.

Dla chętnych: dla każdego ucznia wypisz jego najlepszy wynik za pomocą podzapytania skorelowanego w SELECT (uczeń bez wyniku ma mieć NULL).

Rozwiązanie i wyniki

-- Zadanie 1
SELECT id, punkty
FROM sprawdziany
WHERE punkty > (SELECT AVG(punkty) FROM sprawdziany)
ORDER BY punkty DESC, id;

-- Zadanie 2
SELECT imie
FROM uczniowie
WHERE id IN (SELECT uczen_id FROM sprawdziany
              WHERE punkty = (SELECT MAX(punkty) FROM sprawdziany))
ORDER BY imie;

-- Zadanie 3
SELECT imie
FROM uczniowie
WHERE id NOT IN (SELECT uczen_id FROM sprawdziany);

-- Zadanie 4
SELECT imie
FROM uczniowie
WHERE klasa = '2A'
  AND id IN (SELECT uczen_id FROM sprawdziany
             WHERE punkty > (SELECT AVG(punkty) FROM sprawdziany))
ORDER BY imie;

-- Dla chetnych
SELECT imie,
       (SELECT MAX(punkty) FROM sprawdziany s WHERE s.uczen_id = u.id) AS najlepszy
FROM uczniowie u
ORDER BY id;

Wyniki (sprawdzone na SQLite z tymi samymi tabelami; średnia wszystkich sprawdzianów to 224 : 6 ≈ 37,33):

Zadanie 1 — cztery wiersze:

Zadanie 2 — dwa wiersze: Anna, Celina (obie mają 42 punkty).

Zadanie 3 — jeden wiersz: Filip.

Zadanie 4 — dwa wiersze: Anna, Ewa (Bartek ma tylko 35 punktów, czyli poniżej średniej).

Dla chętnych:

Typowe błędy

Pytania kontrolne i punktacja

1. Czym różni się podzapytanie z jedną wartością od podzapytania zwracającego listę?
2. Dlaczego NOT IN bywa niebezpieczne przy kolumnach z NULL?
3. Co oznacza „skorelowane” podzapytanie?

Odpowiedzi: Pierwsze porównujemy zwykłym operatorem (>, =), drugie stosujemy z IN lub NOT IN. Bo porównanie z NULL nie daje prawdy, więc wynik NOT IN jest wtedy pusty. Takie, które odwołuje się do kolumn zapytania zewnętrznego i wykonuje się dla każdego jego wiersza.

Proponowana punktacja (razem 10 punktów): zadanie 1 — podzapytanie z AVG 2 pkt; zadanie 2 — zagnieżdżone MAX i IN 3 pkt; zadanie 3 — NOT IN 2 pkt; zadanie 4 — połączenie warunku klasy i podzapytania 3 pkt. Zadanie dla chętnych nie jest wymagane do pełnej oceny.

Materiały do pobrania

• Pakiet SQL: materiały do lekcji, rozwiązanie i karta pracy
• Karta pracy ucznia — otwórz i wydrukuj

Rozpakuj ZIP przed otwarciem plików. Materiały są dostępne bez logowania; publiczne rozwiązanie służy nauce i samodzielnemu sprawdzeniu, a nie poufnemu sprawdzianowi.

Co dalej?

Następne kroki to JOIN, EXISTS i widoki, które często zastępują zagnieżdżone zapytania. Ten materiał rozwija dział o zapytaniach w kursie SQL w TablicaPro, gdzie znajdziesz prezentacje, ćwiczenia i testy do prowadzenia lekcji.

Źródła techniczne: podzapytania w dokumentacji MySQL. Zobacz też lekcję o JOIN.