Nauka / Bazy danych i SQL

Bazy danych i SQL

Model relacyjny, klucze, zapytania SELECT, JOIN, GROUP BY, HAVING, LIKE, podzapytania.

Rozszerzony

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).

Łatwe zadania

Średnie zadania

Średnie2 pkt

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.

Średnie2 pkt

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).

Średnie2 pkt

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”.

Średnie2 pkt

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.

Średnie2 pkt

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.

Średnie2 pkt

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ć.

Średnie2 pkt

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.

Średnie2 pkt

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).

Średnie3 pkt

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.

Średnie2 pkt

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

Gotowy na sprawdzian z działu?
Wygeneruj krótki test z tego działu w generatorze.
Test działowy