Bazy danych i SQL
Model relacyjny, klucze, zapytania SELECT, JOIN, GROUP BY, HAVING, LIKE, podzapytania.
Teoria, wzory i przykłady
Model relacyjny
Tabele (relacje) mają klucz główny (unikalny identyfikator) i klucze obce (odwołania do innych tabel). Dobra struktura eliminuje powtarzanie danych.
Składnia zapytania
SELECT kolumny
FROM tabela
[JOIN inna ON warunek]
WHERE warunek_na_wierszach
GROUP BY kolumny
HAVING warunek_na_grupach
ORDER BY kolumna [DESC]
LIMIT n;
Przykłady
Wszystkie zakupione produkty z kategorii "spozywcze", których opis zawiera dany fragment:
SELECT DISTINCT p.IdProduktu, p.Nazwa
FROM Produkty p
JOIN Kategorie k ON p.IdKategorii = k.IdKategorii
JOIN opis_transakcji o ON o.IdProduktu = p.IdProduktu
WHERE k.NazwaKategorii = 'spozywcze'
AND p.Opis LIKE '%do ekspresu kolbowego%';
Liczba transakcji na klienta, najwięcej pierwsze:
SELECT IdKlienta, COUNT(*) AS liczba
FROM transakcje
GROUP BY IdKlienta
ORDER BY liczba DESC;
Funkcje agregujące
COUNT, SUM, AVG, MIN, MAX. WHERE filtruje wiersze przed grupowaniem, HAVING — grupy po agregacji. LIKE z symbolem % oznacza dowolny ciąg znaków; _ — jeden znak. Klient, który nic nie kupił: LEFT JOIN + warunek IS NULL po stronie transakcji.
Typowe błędy
- Brak DISTINCT, gdy produkt występuje w wielu transakcjach, a arkusz prosi o „wszystkie produkty” (każdy raz).
- Filtrowanie agregatów w WHERE zamiast w HAVING.
- Pominięcie warunku złączenia (iloczyn kartezjański).
Średnie zadania
Podaj, ile kobiet (K) oraz ilu mężczyzn (M) spośród klientów sieci sklepów nie kupiło niczego w całym analizowanym okresie (dane z zadania 8.1).
Wpisz dwie liczby oddzielone spacją: kobiety mężczyźni.
Podaj liczbę różnych sklepów, w których dokonano transakcji w kasach samoobsługowych (brak IdSprzedawcy), oraz łączną kwotę zapłaconą za zakupy w tych kasach (cena × liczba sztuk).
Wpisz: liczba_sklepów kwota (kwotę z przecinkiem dziesiętnym).
Do bazy danych utworzonej z opisanych plików dodano tabele Produkty (IdProduktu, IdKategorii, Nazwa, Opis) oraz Kategorie (IdKategorii, NazwaKategorii).
Napisz w języku SQL zapytanie, w wyniku którego zostaną wypisane wszystkie zakupione produkty (IdProduktu i Nazwa) z kategorii (NazwaKategorii) „spozywcze”, zawierające w opisie fragment „do ekspresu kolbowego”.
Poszukiwanie wody na Marsie. Trzy pliki tekstowe (pierwszy wiersz to nagłówek, dane rozdzielone tabulatorami): laziki.txt (nr_lazika, nazwa_lazika, rok_wyslania, wsp_ladowania np. 50.51N, 70.01E), obszary.txt (kod_obszaru, nazwa_obszaru), pomiary.txt (nr_lazika, data_pomiaru, kod_obszaru, wspolrzedne, glebokosc [m], ilosc [m³]). Pliki z danymi (Dane-NF-2505.zip) pobierzesz na stronie arkusza.
Podaj nazwę obszaru, na którym znaleziono łącznie we wszystkich pomiarach najwięcej m³ wody na głębokości do 100 metrów włącznie.
Podaj nazwę łazika, który wykonywał pomiary w najdłuższym okresie (od najwcześniejszego do najpóźniejszego pomiaru), oraz daty pierwszego i ostatniego pomiaru tego łazika (format dd.mm.rrrr).
Wpisz: nazwa_łazika data_pierwszego data_ostatniego.
Do bazy dodano tabelę Producent (kod_producenta, nazwa, kraj), a do tabeli Laziki pole kod_producenta. Napisz w języku SQL zapytanie, które da listę nazw producentów, których łaziki badały obszar Marsa o nazwie „Arcadia” w roku 2060. Nazwy producentów nie mogą się powtarzać.
Rejestr wykroczeń. W trzech plikach (średnik jako separator, pierwszy wiersz to nagłówek) zapisano dane o przekroczeniach prędkości w 2023 r.: kierowcy.txt (IdOsoby, Imie, Nazwisko, NrRejestracyjny — 1000 osób), taryfikator.txt (IdWykroczenia 1–6, Wykroczenie, Punkty, Kwota mandatu) i rejestr.txt (IdZdarzenia, Data, IdOsoby, IdWykroczenia — 5000 zdarzeń). Pliki z danymi (Dane-NF-2405.zip) pobierzesz na stronie arkusza.
Podaj imię i nazwisko kierowcy, dla którego suma kwot za wszystkie mandaty była największa, oraz tę największą sumę. Jest tylko jeden taki kierowca.
Wpisz: Imię Nazwisko suma.
W którym miesiącu kierowcy otrzymali najmniej punktów karnych (łącznie) za wykroczenia polegające na przekroczeniu dozwolonej prędkości o więcej niż 20 km/h (wykroczenia o identyfikatorach od 3 do 6)? Podaj miesiąc (nazwę) i łączną liczbę punktów karnych z tego miesiąca.
Wpisz: miesiąc liczba_punktów (np. maj 100).
Wykonaj zestawienie numerów rejestracyjnych samochodów wraz z imionami i nazwiskami ich właścicieli, którzy nie figurują w rejestrze wykroczeń. Zestawienie posortuj alfabetycznie według numerów rejestracyjnych.
Baza danych rejestru wykroczeń została zmodyfikowana: dodano nową tabelę Fotoradar (pola IdFotoradaru, Miejscowosc, DozwolonaPredkosc), a do tabeli Rejestr dodano pole IdFotoradaru, w którym dla każdego rekordu zapisano identyfikator fotoradaru zarejestrowującego wykroczenie. W bazie istnieją fotoradary, które nie zarejestrowały żadnych wykroczeń.
Zapisz w języku SQL zapytanie, w wyniku którego otrzymasz identyfikatory tych fotoradarów.
Trudne zadania
Niektórzy sprzedawcy pracowali w ciągu miesiąca w różnych sklepach sieci. Podaj IdSprzedawcy, który obsługiwał klientów w największej liczbie różnych sklepów w jednym miesiącu, oraz ten miesiąc (nazwa).
Wpisz: IdSprzedawcy nazwa_miesiąca.
Podaj nazwy obszarów na Marsie, na których żaden z łazików nie wykonał ani jednego pomiaru w tym samym roku, w którym został wysłany z Ziemi (rok_wyslania).
Podaj nazwy łazików, które wylądowały na półkuli południowej (współrzędna szerokości z literą S), ale wykonywały pomiary na obu półkulach: północnej (N) i południowej (S).