Cel lekcji
Uczeń potrafi:
- łączyć dane z kilku tabel za pomocą
JOIN; - tworzyć złożone warunki filtrowania z wykorzystaniem
WHERE,AND,OR,NOTiLIKE; - stosować funkcje agregujące
COUNT(),SUM(),AVG(),MIN()iMAX()w zapytaniach łączących tabele; - grupować dane według jednej lub kilku kolumn za pomocą
GROUP BY; - filtrować wyniki grupowania za pomocą
HAVING; - łączyć warunki
WHEREiHAVINGw jednym zapytaniu; - sortować wyniki według kilku kolumn, w różnych kierunkach;
- tworzyć rozbudowane zapytania SQL na podstawie opisu problemu.
1. Przygotowanie bazy danych
Podczas lekcji będziemy pracować na bazie danych przedstawiającej sklep internetowy. Baza zawiera informacje o klientach, kategoriach produktów, produktach, zamówieniach oraz pozycjach zamówień.
Uruchom cały poniższy skrypt w MySQL, np. w phpMyAdmin lub MySQL Workbench. Jeżeli baza o podanej nazwie już istnieje, skrypt ją usunie i utworzy ponownie.
Skrypt tworzący bazę danych
DROP DATABASE IF EXISTS sklep_join_advanced;
CREATE DATABASE sklep_join_advanced
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE sklep_join_advanced;
CREATE TABLE klienci (
id_klienta INT PRIMARY KEY,
imie VARCHAR(40) NOT NULL,
nazwisko VARCHAR(50) NOT NULL,
miasto VARCHAR(50) NOT NULL,
typ_klienta VARCHAR(20) NOT NULL
);
CREATE TABLE kategorie (
id_kategorii INT PRIMARY KEY,
nazwa VARCHAR(50) NOT NULL
);
CREATE TABLE produkty (
id_produktu INT PRIMARY KEY,
nazwa VARCHAR(100) NOT NULL,
id_kategorii INT NOT NULL,
cena DECIMAL(10,2) NOT NULL,
stan_magazynowy INT NOT NULL,
FOREIGN KEY (id_kategorii)
REFERENCES kategorie(id_kategorii)
);
CREATE TABLE zamowienia (
id_zamowienia INT PRIMARY KEY,
id_klienta INT NOT NULL,
data_zamowienia DATE NOT NULL,
status VARCHAR(20) NOT NULL,
metoda_platnosci VARCHAR(20) NOT NULL,
FOREIGN KEY (id_klienta)
REFERENCES klienci(id_klienta)
);
CREATE TABLE pozycje_zamowien (
id_pozycji INT PRIMARY KEY,
id_zamowienia INT NOT NULL,
id_produktu INT NOT NULL,
ilosc INT NOT NULL,
cena_sztuki DECIMAL(10,2) NOT NULL,
FOREIGN KEY (id_zamowienia)
REFERENCES zamowienia(id_zamowienia),
FOREIGN KEY (id_produktu)
REFERENCES produkty(id_produktu)
);
INSERT INTO klienci
(id_klienta, imie, nazwisko, miasto, typ_klienta) VALUES
(1, 'Adam', 'Kowalski', 'Warszawa', 'indywidualny'),
(2, 'Anna', 'Nowak', 'Krakow', 'firmowy'),
(3, 'Piotr', 'Wisniewski', 'Gdansk', 'indywidualny'),
(4, 'Ewa', 'Wojcik', 'Warszawa', 'firmowy'),
(5, 'Tomasz', 'Kaminski', 'Poznan', 'indywidualny'),
(6, 'Katarzyna', 'Lewandowska', 'Krakow', 'indywidualny'),
(7, 'Michal', 'Zielinski', 'Lublin', 'firmowy'),
(8, 'Magdalena', 'Szymanska', 'Gdansk', 'indywidualny'),
(9, 'Pawel', 'Dabrowski', 'Warszawa', 'indywidualny'),
(10, 'Joanna', 'Kozlowska', 'Poznan', 'firmowy'),
(11, 'Marcin', 'Jankowski', 'Lublin', 'indywidualny'),
(12, 'Agnieszka', 'Mazur', 'Krakow', 'firmowy'),
(13, 'Robert', 'Piotrowski', 'Gdansk', 'indywidualny'),
(14, 'Natalia', 'Grabowska', 'Warszawa', 'firmowy');
INSERT INTO kategorie (id_kategorii, nazwa) VALUES
(1, 'Komputery'),
(2, 'Akcesoria'),
(3, 'Gaming'),
(4, 'Smartfony'),
(5, 'AGD'),
(6, 'Sieci');
INSERT INTO produkty
(id_produktu, nazwa, id_kategorii, cena, stan_magazynowy) VALUES
(1, 'Laptop Lenovo', 1, 3500.00, 12),
(2, 'Laptop Dell', 1, 4200.00, 8),
(3, 'Monitor 27 cali', 2, 1100.00, 18),
(4, 'Klawiatura mechaniczna', 2, 350.00, 25),
(5, 'Mysz Logitech', 2, 120.00, 50),
(6, 'Sluchawki gamingowe', 3, 280.00, 30),
(7, 'Konsola Xbox', 3, 2200.00, 10),
(8, 'Konsola PlayStation', 3, 2500.00, 6),
(9, 'Smartfon Samsung', 4, 2800.00, 20),
(10, 'Smartfon Xiaomi', 4, 1900.00, 15),
(11, 'Ekspres do kawy', 5, 1600.00, 9),
(12, 'Odkurzacz', 5, 850.00, 14),
(13, 'Router TP-Link', 6, 320.00, 19),
(14, 'Kamera internetowa', 2, 250.00, 35),
(15, 'Tablet Samsung', 1, 1800.00, 11),
(16, 'Glosnik Bluetooth', 2, 450.00, 0),
(17, 'Drukarka HP', 1, 750.00, 16),
(18, 'Smartwatch Apple', 4, 1700.00, 4),
(19, 'Blender Bosch', 5, 420.00, 13),
(20, 'Mikrofon USB', 2, 390.00, 22);
INSERT INTO zamowienia
(id_zamowienia, id_klienta, data_zamowienia, status, metoda_platnosci) VALUES
(1, 1, '2026-01-10', 'zrealizowane', 'karta'),
(2, 2, '2026-01-15', 'zrealizowane', 'przelew'),
(3, 3, '2026-01-20', 'wyslane', 'karta'),
(4, 1, '2026-02-05', 'zrealizowane', 'blik'),
(5, 4, '2026-02-10', 'anulowane', 'karta'),
(6, 5, '2026-02-15', 'zrealizowane', 'gotowka'),
(7, 6, '2026-02-20', 'wyslane', 'przelew'),
(8, 7, '2026-03-01', 'zrealizowane', 'karta'),
(9, 8, '2026-03-05', 'zrealizowane', 'blik'),
(10, 9, '2026-03-10', 'oczekujace', 'przelew'),
(11, 10, '2026-03-15', 'zrealizowane', 'karta'),
(12, 2, '2026-03-20', 'wyslane', 'blik'),
(13, 3, '2026-04-01', 'zrealizowane', 'przelew'),
(14, 5, '2026-04-05', 'anulowane', 'karta'),
(15, 11, '2026-04-10', 'zrealizowane', 'gotowka'),
(16, 12, '2026-04-15', 'oczekujace', 'przelew'),
(17, 4, '2026-04-20', 'zrealizowane', 'karta'),
(18, 6, '2026-05-01', 'zrealizowane', 'blik'),
(19, 7, '2026-05-05', 'wyslane', 'przelew'),
(20, 8, '2026-05-10', 'zrealizowane', 'gotowka'),
(21, 1, '2026-05-15', 'zrealizowane', 'karta'),
(22, 9, '2026-05-20', 'anulowane', 'blik'),
(23, 10, '2026-06-01', 'zrealizowane', 'przelew'),
(24, 11, '2026-06-05', 'wyslane', 'karta'),
(25, 12, '2026-06-10', 'zrealizowane', 'blik'),
(26, 3, '2026-06-15', 'zrealizowane', 'karta'),
(27, 4, '2026-06-20', 'oczekujace', 'gotowka'),
(28, 5, '2026-07-01', 'zrealizowane', 'przelew'),
(29, 6, '2026-07-05', 'zrealizowane', 'karta'),
(30, 7, '2026-07-10', 'wyslane', 'blik'),
(31, 8, '2026-07-15', 'zrealizowane', 'gotowka'),
(32, 9, '2026-07-20', 'zrealizowane', 'przelew'),
(33, 10, '2026-08-01', 'anulowane', 'karta'),
(34, 11, '2026-08-05', 'zrealizowane', 'blik'),
(35, 12, '2026-08-10', 'wyslane', 'przelew'),
(36, 1, '2026-08-15', 'zrealizowane', 'karta'),
(37, 2, '2026-08-20', 'zrealizowane', 'blik'),
(38, 3, '2026-09-01', 'zrealizowane', 'przelew'),
(39, 4, '2026-09-05', 'wyslane', 'karta'),
(40, 5, '2026-09-10', 'zrealizowane', 'gotowka'),
(41, 6, '2026-09-15', 'zrealizowane', 'blik'),
(42, 13, '2026-09-20', 'oczekujace', 'przelew'),
(43, 14, '2026-09-22', 'zrealizowane', 'karta');
INSERT INTO pozycje_zamowien
(id_pozycji, id_zamowienia, id_produktu, ilosc, cena_sztuki) VALUES
(1, 1, 1, 1, 3500.00),
(2, 1, 5, 1, 120.00),
(3, 2, 3, 2, 1100.00),
(4, 2, 4, 1, 350.00),
(5, 3, 2, 1, 4200.00),
(6, 3, 9, 1, 2800.00),
(7, 4, 7, 1, 2200.00),
(8, 4, 6, 2, 280.00),
(9, 5, 9, 1, 2800.00),
(10, 5, 18, 1, 1700.00),
(11, 6, 12, 2, 850.00),
(12, 6, 19, 1, 420.00),
(13, 7, 10, 2, 1900.00),
(14, 8, 13, 2, 320.00),
(15, 8, 14, 1, 250.00),
(16, 9, 6, 3, 280.00),
(17, 9, 8, 1, 2500.00),
(18, 10, 9, 1, 2800.00),
(19, 11, 2, 1, 4200.00),
(20, 11, 4, 1, 350.00),
(21, 12, 15, 1, 1800.00),
(22, 12, 5, 2, 120.00),
(23, 13, 1, 1, 3500.00),
(24, 13, 3, 1, 1100.00),
(25, 14, 9, 1, 2800.00),
(26, 14, 10, 1, 1900.00),
(27, 15, 17, 2, 750.00),
(28, 15, 14, 2, 250.00),
(29, 16, 18, 1, 1700.00),
(30, 17, 2, 1, 4200.00),
(31, 17, 3, 1, 1100.00),
(32, 17, 4, 2, 350.00),
(33, 18, 7, 1, 2200.00),
(34, 18, 6, 1, 280.00),
(35, 19, 13, 3, 320.00),
(36, 19, 20, 2, 390.00),
(37, 20, 12, 1, 850.00),
(38, 20, 19, 2, 420.00),
(39, 21, 1, 1, 3500.00),
(40, 21, 15, 1, 1800.00),
(41, 22, 8, 1, 2500.00),
(42, 22, 6, 2, 280.00),
(43, 23, 10, 2, 1900.00),
(44, 23, 5, 3, 120.00),
(45, 24, 17, 1, 750.00),
(46, 24, 13, 2, 320.00),
(47, 25, 11, 1, 1600.00),
(48, 25, 19, 2, 420.00),
(49, 26, 2, 1, 4200.00),
(50, 26, 9, 1, 2800.00),
(51, 27, 3, 2, 1100.00),
(52, 27, 14, 1, 250.00),
(53, 28, 7, 1, 2200.00),
(54, 28, 6, 2, 280.00),
(55, 29, 10, 1, 1900.00),
(56, 29, 18, 1, 1700.00),
(57, 30, 1, 1, 3500.00),
(58, 30, 4, 2, 350.00),
(59, 31, 12, 1, 850.00),
(60, 31, 5, 2, 120.00),
(61, 32, 9, 1, 2800.00),
(62, 32, 13, 2, 320.00),
(63, 33, 2, 1, 4200.00),
(64, 33, 20, 2, 390.00),
(65, 34, 15, 2, 1800.00),
(66, 34, 14, 1, 250.00),
(67, 35, 8, 1, 2500.00),
(68, 35, 6, 2, 280.00),
(69, 36, 1, 1, 3500.00),
(70, 36, 3, 2, 1100.00),
(71, 37, 4, 3, 350.00),
(72, 37, 5, 5, 120.00),
(73, 38, 11, 1, 1600.00),
(74, 38, 12, 1, 850.00),
(75, 39, 2, 1, 4200.00),
(76, 39, 13, 2, 320.00),
(77, 40, 17, 1, 750.00),
(78, 40, 20, 3, 390.00),
(79, 41, 7, 1, 2200.00),
(80, 41, 8, 1, 2500.00),
(81, 42, 9, 1, 2800.00),
(82, 42, 5, 2, 120.00),
(83, 43, 1, 1, 3500.00),
(84, 43, 15, 2, 1800.00);2. Ćwiczenia
Wykonaj wszystkie zadania samodzielnie. Do każdego ćwiczenia przygotuj osobne zapytanie SQL. Nie zmieniaj danych w bazie. W wynikach wyświetlaj tylko te kolumny, które są potrzebne do rozwiązania zadania.
Ćwiczenie 1 – Produkty w określonych kategoriach
Wyświetl nazwy produktów, nazwy kategorii oraz ceny produktów należących do kategorii Komputery, Gaming lub Sieci. Uwzględnij wyłącznie produkty kosztujące więcej niż 300 zł. Posortuj wynik najpierw alfabetycznie według nazwy kategorii, a następnie malejąco według ceny.
Ćwiczenie 2 – Zamówienia klientów z wybranych miast
Wyświetl imiona i nazwiska klientów, miasta, identyfikatory zamówień, daty zamówień oraz ich statusy. Uwzględnij klientów z Warszawy lub Krakowa, ale pomiń zamówienia anulowane. Pokaż wyłącznie zamówienia z okresu od 1 marca do 30 czerwca 2026 roku. Wyniki posortuj od najnowszego zamówienia, a przy tej samej dacie alfabetycznie według nazwiska klienta.
Ćwiczenie 3 – Szczegółowa lista zakupów
Wyświetl identyfikator zamówienia, datę zamówienia, imię i nazwisko klienta, nazwę produktu, kategorię, liczbę sztuk oraz cenę jednostkową z pozycji zamówienia. Pokaż wyłącznie zamówienia o statusie zrealizowane lub wyslane, w których kupiono co najmniej 2 sztuki danego produktu. Uwzględnij produkty kosztujące mniej niż 1000 zł lub więcej niż 2500 zł. Posortuj wyniki malejąco według identyfikatora zamówienia, a następnie rosnąco według nazwy produktu.
Ćwiczenie 4 – Wartość poszczególnych pozycji
Wyświetl identyfikator zamówienia, datę, nazwisko klienta, nazwę produktu, liczbę sztuk oraz wartość pozycji zamówienia obliczoną na podstawie liczby sztuk i ceny jednostkowej zapisanej w pozycje_zamowien. Uwzględnij tylko zamówienia niezakończone anulowaniem, złożone po 31 marca 2026 roku, zawierające produkty z kategorii Akcesoria, AGD lub Sieci. Pokaż pozycje o wartości przekraczającej 500 zł i posortuj je malejąco według wartości pozycji.
Ćwiczenie 5 – Podsumowanie kategorii produktów
Dla każdej kategorii wyświetl jej nazwę, liczbę produktów, najniższą cenę, najwyższą cenę oraz średnią cenę produktów. Uwzględnij tylko produkty, których stan magazynowy jest większy od zera. Pokaż wyłącznie kategorie, w których znajdują się co najmniej 3 dostępne produkty. Wyniki posortuj malejąco według liczby produktów, a następnie według średniej ceny.
Ćwiczenie 6 – Aktywność klientów
Dla każdego klienta, który złożył przynajmniej jedno zamówienie, wyświetl imię, nazwisko, miasto, liczbę wszystkich zamówień oraz liczbę zamówień zrealizowanych. Uwzględnij wyłącznie klientów firmowych z Warszawy, Krakowa lub Gdańska. Pokaż tylko tych klientów, którzy złożyli co najmniej 3 zamówienia, z czego co najmniej 2 zostały zrealizowane. Posortuj wynik malejąco według liczby zamówień, a następnie alfabetycznie według nazwiska.
Ćwiczenie 7 – Kategorie i sprzedane sztuki
Dla każdej kategorii oblicz łączną liczbę sztuk produktów znajdujących się w pozycjach zamówień, liczbę różnych produktów, które pojawiły się w zamówieniach, oraz liczbę różnych zamówień zawierających produkty z tej kategorii. Uwzględnij wyłącznie zamówienia o statusie zrealizowane lub wyslane, złożone w drugim i trzecim kwartale 2026 roku. Pokaż kategorie, dla których sprzedano co najmniej 8 sztuk. Wyniki posortuj malejąco według liczby sprzedanych sztuk.
Ćwiczenie 8 – Klienci i ich wydatki
Dla każdego klienta oblicz łączną wartość zakupów, średnią wartość zamówienia oraz liczbę zamówień. Wartość pojedynczego zamówienia oblicz na podstawie jego pozycji, a nie na podstawie danych z tabeli zamówień. Uwzględnij wyłącznie zamówienia o statusie zrealizowane. Pokaż klientów, którzy wydali łącznie ponad 5000 zł i złożyli co najmniej 2 takie zamówienia. Wyświetl imię, nazwisko, miasto, liczbę zamówień, sumę zakupów i średnią wartość zamówienia. Posortuj wynik malejąco według sumy zakupów.
Ćwiczenie 9 – Analiza sprzedaży według miesięcy
Dla każdego miesiąca 2026 roku wyświetl liczbę zamówień zrealizowanych, łączną wartość ich pozycji oraz średnią wartość zamówienia. Wartość zamówienia oblicz na podstawie pozycji zamówienia. Uwzględnij tylko zamówienia o statusie zrealizowane. Pokaż miesiące, w których zrealizowano co najmniej 3 zamówienia, a łączna wartość sprzedaży przekroczyła 8000 zł. Posortuj wynik chronologicznie.
Ćwiczenie 10 – Produkty kupowane przez wielu klientów
Dla każdego produktu wyświetl nazwę, kategorię, liczbę różnych klientów, którzy go kupili, łączną liczbę sprzedanych sztuk oraz średnią cenę jednostkową zapisaną w pozycjach zamówień. Uwzględnij wyłącznie zamówienia zrealizowane lub wysłane. Pokaż produkty kupione przez co najmniej 3 różnych klientów, dla których łączna liczba sprzedanych sztuk wynosi co najmniej 5. Wyniki posortuj malejąco według liczby różnych klientów, a następnie według liczby sprzedanych sztuk.
Ćwiczenie 11 – Porównanie typów klientów
Porównaj klientów indywidualnych i firmowych. Dla każdego typu klienta wyświetl liczbę różnych klientów, liczbę zamówień, liczbę zamówień zrealizowanych oraz łączną wartość pozycji w zamówieniach zrealizowanych. Uwzględnij zamówienia z okresu od 1 stycznia do 30 września 2026 roku. Pokaż tylko te typy klientów, dla których liczba zamówień zrealizowanych wynosi co najmniej 10. Posortuj wynik malejąco według wartości sprzedaży.
Ćwiczenie 12 – Kategorie o dużej wartości sprzedaży
Dla każdej kategorii oblicz łączną wartość sprzedanych produktów, liczbę różnych produktów sprzedanych w tej kategorii oraz liczbę różnych zamówień, w których wystąpiły te produkty. Uwzględnij wyłącznie zamówienia zrealizowane. Pokaż kategorie, których wartość sprzedaży przekracza 10 000 zł, a liczba różnych zamówień jest większa niż 5. Wyniki posortuj malejąco według wartości sprzedaży.
Ćwiczenie 13 – Klienci kupujący w różnych kategoriach
Dla każdego klienta oblicz liczbę różnych kategorii, w których kupował produkty, łączną liczbę zamówień zrealizowanych oraz łączną wartość zakupów. Uwzględnij tylko klientów, którzy kupowali w co najmniej 3 różnych kategoriach i złożyli co najmniej 2 zamówienia zrealizowane. Wyświetl imię, nazwisko, miasto, liczbę kategorii, liczbę zamówień i wartość zakupów. Posortuj wynik malejąco według liczby kategorii, a następnie według wartości zakupów.
Ćwiczenie 14 – Analiza sprzedaży według miasta i kategorii
Dla każdej pary miasto klienta – kategoria produktu oblicz liczbę różnych klientów, liczbę różnych zamówień oraz łączną wartość sprzedanych produktów. Uwzględnij tylko zamówienia zrealizowane lub wysłane, złożone od 1 kwietnia do 30 września 2026 roku. Pokaż tylko te pary miasto – kategoria, dla których wartość sprzedaży przekracza 3000 zł i liczba różnych klientów jest większa niż 1. Posortuj wynik malejąco według wartości sprzedaży, a następnie alfabetycznie według miasta.
Ćwiczenie 15 – Najważniejsze produkty w poszczególnych kategoriach
Dla każdego produktu oblicz łączną wartość sprzedaży, łączną liczbę sprzedanych sztuk oraz liczbę różnych klientów, którzy go kupili. Uwzględnij wyłącznie zamówienia zrealizowane. Następnie wyświetl tylko te produkty, których łączna wartość sprzedaży przekracza 3000 zł, a liczba różnych klientów wynosi co najmniej 2. Wynik ma zawierać również nazwę kategorii. Posortuj go najpierw alfabetycznie według kategorii, a w obrębie każdej kategorii malejąco według wartości sprzedaży.
Ćwiczenie 16 – Raport sprzedaży dla sklepu
Przygotuj raport pokazujący wyniki sprzedaży w podziale na miasto klienta i typ klienta. Dla każdej takiej grupy wyświetl miasto, typ klienta, liczbę różnych klientów, liczbę zamówień zrealizowanych, łączną wartość sprzedaży, średnią wartość zamówienia oraz liczbę różnych kategorii produktów, które pojawiły się w tych zamówieniach.
Uwzględnij wyłącznie zamówienia zrealizowane w okresie od 1 kwietnia do 30 września 2026 roku. Wartości zamówień oblicz na podstawie pozycji zamówień. Pozostaw w raporcie tylko te grupy, dla których liczba zamówień wynosi co najmniej 2, łączna wartość sprzedaży przekracza 5000 zł, a liczba różnych kategorii jest większa niż 1. Posortuj wynik malejąco według wartości sprzedaży, a następnie rosnąco według miasta i typu klienta.