SQL od podstaw — w stronę INF.03

Każdy temat ma tę samą budowę: krótkie wyjaśnienie, potem przykład z gotowym rozwiązaniem (rozwiń, żeby zobaczyć zapytanie), a obok analogiczne zadanie — bez rozwiązania, do napisania samodzielnie tą samą metodą. Wszystkie zapytania działają na tej samej, prostej bazie danych, więc od razu widać, jak kolejne polecenia się na siebie nakładają.

◆ przykład + rozwiązanie ◆ zadanie do zrobienia samemu
00

Jak wykonujemy zapytania SQL?

Zapytanie SQL można wykonać na dwa sposoby: wpisując je bezpośrednio w narzędziu do zarządzania bazą, np. phpMyAdmin (szybkie testy), albo umieszczając je w kodzie PHP, który łączy się z bazą i przetwarza wynik (tak wygląda to na egzaminie INF.03, gdzie strona wyświetla dane z bazy).

SPOSÓB 1 — PHPMYADMIN

W panelu phpMyAdmin wybierasz bazę biblioteka, wchodzisz w zakładkę SQL i wpisujesz zapytanie. Wynik pojawia się od razu w tabeli poniżej.

SELECT * FROM ksiazki;

To dobry sposób na testowanie zapytań, zanim wstawisz je do kodu PHP.

SPOSÓB 2 — PHP + SQL (INF.03)

Tak łączymy się z bazą i wykonujemy zapytanie w kodzie PHP — to standardowy szkielet na egzaminie:

<?php
$polaczenie = mysqli_connect("localhost", "root", "", "biblioteka");
$wynik = mysqli_query($polaczenie, "SELECT * FROM ksiazki");

while ($wiersz = mysqli_fetch_assoc($wynik)) {
    echo $wiersz["tytul"] . " (" . $wiersz["rok_wydania"] . ")<br>";
}
?>

Zasada: mysqli_connect(host, użytkownik, hasło, baza) łączy się z bazą, mysqli_query() wykonuje zapytanie, a mysqli_fetch_assoc() pobiera po jednym wierszu jako tablicę asocjacyjną.

-- Baza danych używana w sekcjach 1-11: biblioteka

CREATE TABLE ksiazki (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tytul VARCHAR(150),
    autor VARCHAR(100),
    rok_wydania INT,
    cena DECIMAL(6,2),
    dostepna TINYINT(1)
);

CREATE TABLE czytelnicy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie_nazwisko VARCHAR(100),
    email VARCHAR(100),
    miasto VARCHAR(50)
);

CREATE TABLE wypozyczenia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_ksiazki INT,
    id_czytelnika INT,
    data_wypozyczenia DATE,
    data_zwrotu DATE NULL,
    FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id),
    FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id)
);
Schemat 00 — trzy tabele: ksiazki, czytelnicy i wypozyczenia (łączy pierwsze dwie). Ta sama baza wraca w sekcjach 1–11 — warto ją sobie utworzyć i wypełnić przykładowymi wierszami zanim zaczniesz.
01

SELECT — wybieranie kolumn

SELECT określa, które kolumny chcemy zobaczyć, a FROM — z jakiej tabeli. Gwiazdka * oznacza „wszystkie kolumny”.

PRZYKŁAD

Wybierz tylko tytuł i autora z tabeli ksiazki.

Rozwiązanie
SELECT tytul, autor FROM ksiazki;
TWOJE ZADANIE

Wybierz kolumny imie_nazwisko i miasto z tabeli czytelnicy.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Nazwy kolumn w wyniku można zmienić za pomocą AS — to nie zmienia danych, tylko nagłówki w wyniku.

Rozwiązanie
SELECT tytul AS "Nazwa książki", autor AS "Autor"
FROM ksiazki;
TWOJE ZADANIE

Wyświetl kolumnę cena jako "Cena" i rok_wydania jako "Rok", z tabeli ksiazki.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

02

WHERE — filtrowanie wierszy

WHERE ogranicza wynik do wierszy spełniających warunek. Operatory: =, >, <, >=, <=, <> (różne od). Warunki można łączyć za pomocą AND, OR i NOT.

PRZYKŁAD

Wybierz wszystkie książki wydane po roku 2010.

Rozwiązanie
SELECT * FROM ksiazki
WHERE rok_wydania > 2010;
TWOJE ZADANIE

Wybierz wszystkie książki, których cena jest niższa niż 30 zł.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Wybierz książki wydane po roku 2000, które są aktualnie dostępne.

Rozwiązanie
SELECT * FROM ksiazki
WHERE rok_wydania > 2000 AND dostepna = 1;
TWOJE ZADANIE

Wybierz książki, które zostały wydane przed rokiem 2000 albo kosztują więcej niż 100 zł.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

03

ORDER BY i LIMIT

ORDER BY sortuje wynik: ASC rosnąco (domyślnie), DESC malejąco. LIMIT ogranicza liczbę zwróconych wierszy.

PRZYKŁAD

Wyświetl tytuł i rok wydania, sortując od najnowszej książki.

Rozwiązanie
SELECT tytul, rok_wydania FROM ksiazki
ORDER BY rok_wydania DESC;
TWOJE ZADANIE

Wyświetl tytuł i cenę wszystkich książek, sortując od najtańszej do najdroższej.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Wyświetl 3 najdroższe książki.

Rozwiązanie
SELECT tytul, cena FROM ksiazki
ORDER BY cena DESC
LIMIT 3;
TWOJE ZADANIE

Wyświetl 5 najstarszych książek w bibliotece (najniższy rok wydania).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

04

Wzorce z LIKE, IN i BETWEEN

LIKE szuka tekstu według wzorca: % zastępuje dowolny ciąg znaków, _ jeden znak. IN sprawdza przynależność do listy wartości, a BETWEEN — czy wartość leży w zakresie.

PRZYKŁAD

Znajdź wszystkie książki, których autor zaczyna się od „Sienkiewicz”.

Rozwiązanie
SELECT * FROM ksiazki
WHERE autor LIKE 'Sienkiewicz%';
TWOJE ZADANIE

Znajdź wszystkie książki, których tytuł zawiera gdziekolwiek słowo „Pan” (np. „Pan Tadeusz”).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Wybierz książki wydane między rokiem 1990 a 2000 (włącznie).

Rozwiązanie
SELECT * FROM ksiazki
WHERE rok_wydania BETWEEN 1990 AND 2000;
TWOJE ZADANIE

Wybierz czytelników, którzy mieszkają w Warszawie, Krakowie albo Gdańsku (użyj IN).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

05

Funkcje agregujące

Funkcje agregujące liczą jedną wartość na podstawie wielu wierszy: COUNT() liczba wierszy, SUM() suma, AVG() średnia, MIN()/MAX() wartość najmniejsza/największa.

PRZYKŁAD

Policz, ile książek jest w bazie.

Rozwiązanie
SELECT COUNT(*) AS liczba_ksiazek
FROM ksiazki;
TWOJE ZADANIE

Policz, ile czytelników mieszka w Warszawie (połącz COUNT() z WHERE).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Policz średnią cenę książki w bibliotece.

Rozwiązanie
SELECT AVG(cena) AS srednia_cena
FROM ksiazki;
TWOJE ZADANIE

Znajdź najstarszy i najnowszy rok wydania książki w jednym zapytaniu (użyj MIN() i MAX()).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

06

GROUP BY i HAVING

GROUP BY dzieli wiersze na grupy według wartości kolumny i liczy funkcję agregującą dla każdej grupy osobno. HAVING filtruje grupy — działa jak WHERE, ale po zgrupowaniu.

PRZYKŁAD

Policz, ile książek ma w bazie każdy autor.

Rozwiązanie
SELECT autor, COUNT(*) AS liczba_ksiazek
FROM ksiazki
GROUP BY autor;
TWOJE ZADANIE

Policz, ile czytelników jest zarejestrowanych w każdym mieście.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Wyświetl tylko tych autorów, którzy mają więcej niż 1 książkę w bazie.

Rozwiązanie
SELECT autor, COUNT(*) AS liczba_ksiazek
FROM ksiazki
GROUP BY autor
HAVING liczba_ksiazek > 1;
TWOJE ZADANIE

Wyświetl tylko te miasta, w których jest więcej niż 2 czytelników.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

07

JOIN — łączenie dwóch tabel

Tabela wypozyczenia sama w sobie nie mówi wiele — przechowuje same identyfikatory. JOIN ... ON (czyli INNER JOIN) łączy ją z tabelami ksiazki i czytelnicy, żeby wyświetlić czytelne dane.

PRZYKŁAD

Wyświetl tytuł wypożyczonej książki wraz z datą wypożyczenia.

Rozwiązanie
SELECT k.tytul, w.data_wypozyczenia
FROM wypozyczenia w
JOIN ksiazki k ON w.id_ksiazki = k.id;
TWOJE ZADANIE

Wyświetl imię i nazwisko czytelnika wraz z datą zwrotu, łącząc wypozyczenia z czytelnicy.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

08

LEFT JOIN i łączenie wielu tabel

INNER JOIN gubi wiersze, które nie mają dopasowania w drugiej tabeli. LEFT JOIN zachowuje wszystkie wiersze z tabeli po lewej stronie, a tam gdzie nie ma dopasowania — wstawia NULL. Można też łączyć więcej niż dwie tabele naraz.

PRZYKŁAD

Znajdź książki, które nigdy nie zostały wypożyczone (nie mają żadnego dopasowania w wypozyczenia).

Rozwiązanie
SELECT k.tytul
FROM ksiazki k
LEFT JOIN wypozyczenia w ON k.id = w.id_ksiazki
WHERE w.id IS NULL;
TWOJE ZADANIE

Znajdź czytelników, którzy jeszcze nigdy nic nie wypożyczyli.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Wyświetl tytuł książki, imię i nazwisko czytelnika oraz datę wypożyczenia — trzy tabele naraz.

Rozwiązanie
SELECT k.tytul, c.imie_nazwisko, w.data_wypozyczenia
FROM wypozyczenia w
JOIN ksiazki k ON w.id_ksiazki = k.id
JOIN czytelnicy c ON w.id_czytelnika = c.id;
TWOJE ZADANIE

Wyświetl tytuł książki, imię i nazwisko czytelnika oraz datę zwrotu — tylko dla wypożyczeń, które już zostały zwrócone.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

09

INSERT, UPDATE, DELETE

INSERT dodaje nowy wiersz, UPDATE zmienia istniejące wiersze, DELETE je usuwa. UPDATE i DELETE bez WHERE działają na całej tabeli — zawsze sprawdź warunek przed uruchomieniem.

PRZYKŁAD

Zarejestruj nowego czytelnika.

Rozwiązanie
INSERT INTO czytelnicy (imie_nazwisko, email, miasto)
VALUES ('Anna Kowalska', 'anna@example.com', 'Poznań');
TWOJE ZADANIE

Dodaj nową książkę do tabeli ksiazki: dowolny tytuł, autor, rok wydania, cena i dostepna = 1.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Oznacz książkę o id = 3 jako niedostępną.

Rozwiązanie
UPDATE ksiazki
SET dostepna = 0
WHERE id = 3;
TWOJE ZADANIE

Usuń z tabeli czytelnicy czytelnika o id = 10 (użyj DELETE z WHERE — bez warunku usuniesz wszystkich!).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

10

CREATE TABLE — typy danych i klucze

Najczęstsze typy danych: INT (liczba całkowita), VARCHAR(n) (tekst do n znaków), DECIMAL(p,s) (liczba z częścią dziesiętną), DATE (data). PRIMARY KEY jednoznacznie identyfikuje wiersz, FOREIGN KEY łączy tabelę z inną poprzez klucz główny.

PRZYKŁAD

Stwórz tabelę gatunki z automatycznie numerowanym identyfikatorem i nazwą.

Rozwiązanie
CREATE TABLE gatunki (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(50)
);
TWOJE ZADANIE

Stwórz tabelę wydawnictwa z kolumnami: id (klucz główny, autonumeracja), nazwa i kraj.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD

Klucz obcy wymusza, że id_ksiazki musi odpowiadać istniejącemu id z tabeli ksiazki (tak zbudowana jest tabela z sekcji 00).

Rozwiązanie
CREATE TABLE wypozyczenia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_ksiazki INT,
    id_czytelnika INT,
    data_wypozyczenia DATE,
    FOREIGN KEY (id_ksiazki) REFERENCES ksiazki(id),
    FOREIGN KEY (id_czytelnika) REFERENCES czytelnicy(id)
);
TWOJE ZADANIE

Dodaj do istniejącej tabeli ksiazki nową kolumnę id_gatunku jako klucz obcy do tabeli gatunki (podpowiedź: ALTER TABLE ... ADD COLUMN ..., potem ALTER TABLE ... ADD FOREIGN KEY ...).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

11

Mini-projekt: raport biblioteczny

Zadanie łączy wszystko z sekcji 1–10 na bazie biblioteka. Jak zawsze w mini-projekcie — brak gotowego rozwiązania, tylko kroki do samodzielnej realizacji.

PROJEKT DO SAMODZIELNEJ REALIZACJI

Cel: zestaw zapytań, które razem tworzą prosty raport dla bibliotekarza.

Krok 1. Wstaw do tabel ksiazki, czytelnicy i wypozyczenia po kilka (5–8) przykładowych wierszy, tak żeby część książek była już zwrócona (data_zwrotu wypełniona), a część jeszcze nie (data_zwrotu równe NULL).

Krok 2 — zaległości. Napisz zapytanie, które znajdzie wszystkie wypożyczenia bez zwrotu (data_zwrotu IS NULL), wraz z tytułem książki i danymi czytelnika.

Krok 3 — najpopularniejsze książki. Napisz zapytanie z JOIN i GROUP BY, które policzy, ile razy każda książka została wypożyczona, sortując od najczęściej wypożyczanej.

Krok 4 — aktywność czytelników. Napisz zapytanie, które dla każdego czytelnika policzy liczbę wypożyczeń (użyj LEFT JOIN, żeby nie zgubić czytelników z zerem wypożyczeń).

Krok 5 — wartość zbioru. Policz łączną wartość (SUM(cena)) wszystkich książek aktualnie wypożyczonych (bez zwrotu).

Krok 6 (dodatkowo). Dodaj kolumnę liczba_dni obliczaną jako różnica dni między data_wypozyczenia a dniem dzisiejszym (DATEDIFF(CURDATE(), data_wypozyczenia)) i wybierz tylko wypożyczenia trwające dłużej niż 30 dni bez zwrotu.

BLOK EGZAMINACYJNY

Zapytania typowe dla egzaminu INF.03

Poniższe pięć sekcji ćwiczy zapytania w formie, w jakiej najczęściej pojawiają się w arkuszu egzaminacyjnym — z rozbudowanymi warunkami, obliczeniami w SELECT, raportami z kilku tabel naraz i podzapytaniami. Zmieniamy tu bazę danych na sklep — schemat typowy dla zadań o zamówieniach i klientach.

12

Warunki złożone i wzorce

Egzaminacyjne zadania rzadko mają jeden prosty warunek — zwykle trzeba połączyć kilka z odpowiednim nawiasowaniem, albo dopasować tekst wzorcem.

-- Baza danych używana w sekcjach 12-16: sklep

CREATE TABLE produkty (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(100),
    cena DECIMAL(8,2),
    kategoria VARCHAR(50),
    ilosc_magazyn INT
);

CREATE TABLE klienci (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie_nazwisko VARCHAR(100),
    email VARCHAR(100),
    miasto VARCHAR(50)
);

CREATE TABLE zamowienia (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_klienta INT,
    data_zamowienia DATE,
    status VARCHAR(20),
    FOREIGN KEY (id_klienta) REFERENCES klienci(id)
);

CREATE TABLE zamowienia_produkty (
    id_zamowienia INT,
    id_produktu INT,
    ilosc INT,
    FOREIGN KEY (id_zamowienia) REFERENCES zamowienia(id),
    FOREIGN KEY (id_produktu) REFERENCES produkty(id)
);
Schemat 12 — cztery tabele: produkty, klienci, zamowienia i zamowienia_produkty (tabela łącząca zamówienia z produktami, bo jedno zamówienie może zawierać wiele produktów). Ta baza obowiązuje w sekcjach 12–16.
PRZYKŁAD EGZAMINACYJNY

Znajdź produkty z kategorii „Elektronika” lub „AGD”, które kosztują między 100 a 500 zł. Zwróć uwagę na nawiasy — bez nich AND/OR działałyby inaczej niż zamierzone.

Rozwiązanie
SELECT * FROM produkty
WHERE (kategoria = 'Elektronika' OR kategoria = 'AGD')
  AND cena BETWEEN 100 AND 500;
TWOJE ZADANIE

Znajdź produkty, których cena jest niższa niż 50 zł lub których nie ma na magazynie (ilosc_magazyn = 0).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD EGZAMINACYJNY

Znajdź produkty, których nazwa zawiera gdziekolwiek słowo „pro” (niezależnie od wielkości liter — LIKE w MySQL domyślnie nie rozróżnia wielkości liter).

Rozwiązanie
SELECT nazwa FROM produkty
WHERE nazwa LIKE '%pro%';
TWOJE ZADANIE

Znajdź klientów, których adres e-mail kończy się na @gmail.com.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

13

Obliczenia i aliasy w SELECT

W SELECT można umieścić wyrażenie arytmetyczne albo wywołanie funkcji tekstowej/numerycznej — SQL policzy je dla każdego wiersza. Przydatne funkcje: ROUND() (zaokrąglanie), CONCAT() (łączenie tekstów).

PRZYKŁAD EGZAMINACYJNY

Wyświetl nazwę, cenę i cenę z 23% VAT dla każdego produktu.

Rozwiązanie
SELECT nazwa, cena, cena * 1.23 AS cena_z_vat
FROM produkty;
TWOJE ZADANIE

Wyświetl nazwę i cenę produktu po 10% rabacie, jako kolumnę cena_po_rabacie.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD EGZAMINACYJNY

Zbuduj jedną kolumnę tekstową z imienia, nazwiska i miasta klienta.

Rozwiązanie
SELECT CONCAT(imie_nazwisko, ' (', miasto, ')') AS opis
FROM klienci;
TWOJE ZADANIE

Wyświetl nazwę produktu oraz jego cenę zaokrągloną do pełnych złotych, jako kolumnę cena_zaokraglona (użyj ROUND()).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

14

Złączenia z agregacją — raporty

Najbardziej „egzaminacyjny” typ zapytania: JOIN kilku tabel razem z GROUP BY, żeby policzyć konkretny raport, np. liczbę zamówień na klienta albo sprzedaż na produkt.

PRZYKŁAD EGZAMINACYJNY

Dla każdego klienta policz, ile zamówień złożył.

Rozwiązanie
SELECT k.imie_nazwisko, COUNT(z.id) AS liczba_zamowien
FROM klienci k
JOIN zamowienia z ON k.id = z.id_klienta
GROUP BY k.imie_nazwisko;
TWOJE ZADANIE

Dla każdego produktu policz łączną sprzedaną ilość (SUM(ilosc) z tabeli zamowienia_produkty, połączonej z produkty).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD EGZAMINACYJNY

Wyświetl tylko tych klientów, którzy złożyli więcej niż 2 zamówienia.

Rozwiązanie
SELECT k.imie_nazwisko, COUNT(z.id) AS liczba_zamowien
FROM klienci k
JOIN zamowienia z ON k.id = z.id_klienta
GROUP BY k.imie_nazwisko
HAVING liczba_zamowien > 2;
TWOJE ZADANIE

Wyświetl produkty, które sprzedały się w łącznej ilości większej niż 10 sztuk.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

15

Podzapytania

Podzapytanie (subquery) to zapytanie umieszczone w środku innego zapytania — najczęściej wewnątrz WHERE jako wartość do porównania, albo wewnątrz FROM jako „tabela wynikowa”.

PRZYKŁAD EGZAMINACYJNY

Znajdź produkty droższe niż średnia cena wszystkich produktów.

Rozwiązanie
SELECT nazwa, cena FROM produkty
WHERE cena > (SELECT AVG(cena) FROM produkty);
TWOJE ZADANIE

Znajdź klientów, którzy nigdy nie złożyli żadnego zamówienia (podpowiedź: WHERE id NOT IN (SELECT id_klienta FROM zamowienia)).

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

PRZYKŁAD EGZAMINACYJNY

Podzapytanie może też służyć jako „tabela” w FROM — tu najpierw liczymy średnią cenę w każdej kategorii, a potem filtrujemy wynik tego zapytania.

Rozwiązanie
SELECT * FROM (
    SELECT kategoria, AVG(cena) AS srednia
    FROM produkty
    GROUP BY kategoria
) AS raport
WHERE srednia > 100;
TWOJE ZADANIE

Zbuduj podzapytanie, które liczy łączną wartość zamówień (SUM(ilosc * cena)) na klienta, a następnie w zewnętrznym zapytaniu wybierz tylko klientów, których suma przekracza 500 zł.

-- napisz zapytanie samodzielnie, korzystając z przykładu obok

16

Projekt egzaminacyjny: baza sklepu internetowego

Zadanie łączy wszystko z bloku egzaminacyjnego na bazie sklep: warunki złożone, obliczenia, złączenia z agregacją i podzapytania. Jak w mini-projekcie z sekcji 11 — brak gotowego rozwiązania, tylko kroki do samodzielnej realizacji.

PROJEKT DO SAMODZIELNEJ REALIZACJI

Cel: pełny zestaw zapytań raportujących dla sklepu internetowego, na bazie ze schematu z sekcji 12.

Krok 1 — dane testowe. Wstaw do tabel produkty, klienci, zamowienia i zamowienia_produkty po kilka wierszy tak, żeby niektóre zamówienia zawierały więcej niż jeden produkt.

Krok 2 — wartość zamówienia. Napisz zapytanie, które dla każdego zamówienia policzy jego łączną wartość: SUM(ilosc * cena) po złączeniu zamowienia_produkty z produkty, zgrupowane po id_zamowienia.

Krok 3 — rabat. Rozbuduj zapytanie z kroku 2 tak, żeby zamówienia o wartości powyżej 1000 zł miały wyliczoną też kolumnę z ceną po 10% rabacie (podpowiedź: wyrażenie CASE WHEN suma > 1000 THEN suma * 0.9 ELSE suma END).

Krok 4 — najlepsi klienci. Napisz zapytanie łączące klienci, zamowienia i zamowienia_produkty, które dla każdego klienta policzy łączną wartość wszystkich jego zamówień, sortując od największej.

Krok 5 — magazyn. Napisz zapytanie znajdujące produkty, których ilosc_magazyn jest niższa niż suma ilości zamówionych tego produktu (czyli zamówienia, które faktycznie wyczyściłyby magazyn) — użyj podzapytania lub złączenia z GROUP BY.

Krok 6 (dodatkowo). Napisz zapytanie z LEFT JOIN, które znajdzie produkty, które nigdy nie zostały zamówione — przydatne do wyczyszczenia oferty ze sklepu.

ARKUSZ UNIWERSALNY

Gotowa baza danych — teraz ją rozbuduj

Wszystko powyżej to pojedyncze, wyizolowane zapytania. W praktyce na egzaminie dostajesz jednak jedną, gotową bazę danych i musisz do niej dopisać konkretne zapytania albo poprawić istniejące. Dlatego obok tego zbioru ćwiczeń istnieje osobny, gotowy „arkusz uniwersalny”: plik szkola.sql, który tworzy cztery połączone tabele i wypełnia je przykładowymi danymi — gotowy do zaimportowania w phpMyAdmin.

Baza ma trzy niezależne części, każda odpowiadająca innemu typowemu zadaniu z egzaminu: uczniowie i klasy (podstawowe złączenie jeden-do-wielu), przedmioty i oceny (złączenie z tabelą pośredniczącą i agregacja) oraz raporty i statystyki (podzapytania i obliczenia na wynikach agregacji). Plik szkola.sql jest gęsto skomentowany — każda część tłumaczy krok po kroku, do czego służy dana tabela.

szkola.sql
17

Propozycje rozbudowy bazy szkolnej

Tu nie ma przykładów z gotowym rozwiązaniem — to konkretne zapytania do dopisania bezpośrednio na bazie szkola, obok istniejącego, podobnego kodu. Każda z trzech części ma zadania łatwe (jedna sztuczka), średnie (łączą dwie rzeczy) i trudne (pełny, samodzielny raport) — dokładnie tak, jak stopniowany bywa egzamin praktyczny.

-- szkola.sql (fragment)

CREATE TABLE klasy (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(10),
    wychowawca VARCHAR(100)
);

CREATE TABLE uczniowie (
    id INT PRIMARY KEY AUTO_INCREMENT,
    imie_nazwisko VARCHAR(100),
    id_klasy INT,
    FOREIGN KEY (id_klasy) REFERENCES klasy(id)
);

CREATE TABLE przedmioty (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nazwa VARCHAR(50)
);

CREATE TABLE oceny (
    id INT PRIMARY KEY AUTO_INCREMENT,
    id_ucznia INT,
    id_przedmiotu INT,
    ocena DECIMAL(3,2),
    data_wystawienia DATE,
    FOREIGN KEY (id_ucznia) REFERENCES uczniowie(id),
    FOREIGN KEY (id_przedmiotu) REFERENCES przedmioty(id)
);
Schemat 17 — klasy → uczniowie → oceny ← przedmioty. Pełny plik szkola.sql zawiera też przykładowe INSERT dla każdej tabeli.
DO ROZBUDOWY — CZĘŚĆ 1

Uczniowie i klasy

Łatwe

  • Napisz zapytanie wyświetlające wszystkich uczniów wraz z nazwą ich klasy (JOIN z klasy).
  • Policz, ile uczniów jest w każdej klasie (GROUP BY).

Średnie

  • Wyświetl tylko te klasy, w których jest mniej niż 20 uczniów (HAVING).
  • Dodaj nową klasę i przenieś do niej wybranego ucznia za pomocą UPDATE na kolumnie id_klasy.

Trudne

  • Znajdź klasy, które nie mają przypisanego żadnego ucznia — użyj LEFT JOIN z klasy jako tabelą główną i sprawdzenia IS NULL.
DO ROZBUDOWY — CZĘŚĆ 2

Przedmioty i oceny

Łatwe

  • Wyświetl wszystkie oceny konkretnego ucznia (po imieniu i nazwisku) wraz z nazwą przedmiotu.

Średnie

  • Policz średnią ocen każdego ucznia ze wszystkich przedmiotów (AVG + GROUP BY), zaokrągloną do dwóch miejsc (ROUND()).
  • Wyświetl uczniów, których średnia ocen jest niższa niż 3.5 — wykorzystaj poprzednie zapytanie jako podzapytanie w FROM.

Trudne

  • Policz średnią ocen z każdego przedmiotu w podziale na klasy (złączenie oceny + uczniowie + klasy + przedmioty, GROUP BY po klasie i przedmiocie).
DO ROZBUDOWY — CZĘŚĆ 3

Raporty i statystyki

Łatwe

  • Znajdź ucznia (lub uczniów) z najwyższą pojedynczą oceną w całej bazie (podpowiedź: podzapytanie z MAX(ocena)).

Średnie

  • Wyświetl przedmiot, z którego wystawiono najwięcej ocen w bieżącym roku (WHERE YEAR(data_wystawienia) = YEAR(CURDATE()), GROUP BY, ORDER BY, LIMIT 1).

Trudne

  • Zbuduj raport rankingowy: dla każdej klasy policz jej średnią ocen ze wszystkich przedmiotów i ucznia z najwyższą średnią w tej klasie — połącz podzapytanie liczące średnią ucznia z zewnętrznym zapytaniem grupującym po klasie.

To już komplet materiału

Siedemnaście sekcji: jedenaście od pierwszego SELECT po mini-projekt na bazie biblioteki, kolejne pięć — wyróżnione czerwienią i osobnym blokiem — to zapytania typowe dla egzaminu praktycznego INF.03 (warunki złożone, obliczenia w SELECT, złączenia z agregacją, podzapytania, projekt egzaminacyjny), a ostatnia, fioletowa sekcja to zadania rozbudowy gotowej bazy szkolnej (szkola.sql), traktowanej jako jeden, spójny projekt. Najlepiej działa to tak: przeczytaj przykład, rozwiń rozwiązanie dopiero gdy utkniesz, a zadanie obok napisz samodzielnie w phpMyAdmin albo w pliku .sql — a zadania z ostatniej sekcji dopisz wprost do bazy szkolnej.