Ćwiczenia SQL - Sklep Internetowy

Baza danych: sklep · zakres: DDL, DML, DQL, relacje, widoki, procedury

0. Inicjalizacja bazy sklep (przypomnienie)

Jeśli nie masz jeszcze bazy sklep i głównych tabel, wykonaj ten blok w phpMyAdmin (zakładka SQL).

Pokaż kod DDL (baza + główne tabele) 
CREATE DATABASE IF NOT EXISTS sklep
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_polish_ci;

USE sklep;

CREATE TABLE IF NOT EXISTS klienci (
    id_klienta INT AUTO_INCREMENT PRIMARY KEY,
    imie       VARCHAR(50),
    nazwisko   VARCHAR(50),
    email      VARCHAR(100),
    telefon    VARCHAR(20),
    adres      VARCHAR(255),
    vip        BOOLEAN DEFAULT 0,
    rabat      DECIMAL(5,2) DEFAULT 0.00
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

CREATE TABLE IF NOT EXISTS zamowienia (
    id_zamowienia      INT AUTO_INCREMENT PRIMARY KEY,
    id_klienta         INT,
    data_zamowienia    DATE,
    wartosc_zamowienia DECIMAL(10,2),
    metoda_platnosci   ENUM('Gotowka','Karta','Przelew'),
    status             ENUM('Nowe','Realizacja','Zakonczone','VIP') DEFAULT 'Nowe',
    FOREIGN KEY (id_klienta) REFERENCES klienci(id_klienta)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

CREATE TABLE IF NOT EXISTS produkty (
    id_produktu INT AUTO_INCREMENT PRIMARY KEY,
    nazwa       VARCHAR(100),
    cena        DECIMAL(10,2),
    kategoria   VARCHAR(50)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

CREATE TABLE IF NOT EXISTS szczegoly_zamowienia (
    id_szczegolu  INT AUTO_INCREMENT PRIMARY KEY,
    id_zamowienia INT,
    id_produktu   INT,
    ilosc         INT,
    FOREIGN KEY (id_zamowienia) REFERENCES zamowienia(id_zamowienia),
    FOREIGN KEY (id_produktu)   REFERENCES produkty(id_produktu)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

Po utworzeniu struktury możesz wczytać dane testowe z poprzednich stron (klienci, zamówienia, produkty, szczegóły).

Ćwiczenia 16–20: Rozszerzanie Struktury Danych (DDL)

Ćwiczenie 16: Dodanie tabeli płatności

Zadanie: Stwórz tabelę platnosci przechowującą szczegóły płatności za zamówienia (kwota, metoda, data, powiązanie z zamowienia).

Pokaż rozwiązanie DDL 
CREATE TABLE IF NOT EXISTS platnosci (
    id_platnosci   INT AUTO_INCREMENT PRIMARY KEY,
    id_zamowienia  INT NOT NULL,
    data_platnosci DATETIME DEFAULT CURRENT_TIMESTAMP,
    kwota          DECIMAL(10,2) NOT NULL,
    metoda_platnosci ENUM('Gotowka','Karta','Przelew') NOT NULL,
    FOREIGN KEY (id_zamowienia) REFERENCES zamowienia(id_zamowienia)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

Użycie DATETIME DEFAULT CURRENT_TIMESTAMP pozwala automatycznie zapisać moment płatności; ENUM ogranicza metody do ustalonej listy.

Ćwiczenie 17: Dodanie historii zamówień

Zadanie: Stwórz tabelę historia_zamowien logującą zmiany statusu zamówień.

Pokaż rozwiązanie DDL 
CREATE TABLE IF NOT EXISTS historia_zamowien (
    id_historii    INT AUTO_INCREMENT PRIMARY KEY,
    id_zamowienia  INT NOT NULL,
    zmiana_statusu ENUM('Nowe','Realizacja','Zakonczone','Anulowane','VIP') NOT NULL,
    data_zmiany    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_zamowienia) REFERENCES zamowienia(id_zamowienia)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

Każdy wpis w historii mówi, jaki status został ustawiony i kiedy – przydatne do audytu i raportów SLA.

Ćwiczenie 18: 10 najdroższych zamówień

Zadanie: Wyświetl 10 najdroższych zamówień.

Pokaż rozwiązanie DQL 
SELECT *
FROM zamowienia
ORDER BY wartosc_zamowienia DESC
LIMIT 10;

ORDER BY ... DESC sortuje od najwyższej kwoty, a LIMIT 10 ogranicza wynik do top 10.

Ćwiczenie 19: Tabela kuponów rabatowych

Zadanie: Stwórz tabelę kupony z unikalnym kodem, rabatem i datą ważności.

Pokaż rozwiązanie DDL 
CREATE TABLE IF NOT EXISTS kupony (
    id_kuponu    INT AUTO_INCREMENT PRIMARY KEY,
    kod_kuponu   VARCHAR(50) UNIQUE,
    rabat        DECIMAL(5,2) NOT NULL,
    data_waznosci DATE NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;

UNIQUE na kod_kuponu uniemożliwia duplikaty, a DECIMAL(5,2) dobrze nadaje się na rabat w procentach lub kwocie.

Ćwiczenie 20: Powiązanie kuponów z zamówieniami

Zadanie: Dodaj do zamowienia kolumnę id_kuponu jako klucz obcy.

Pokaż rozwiązanie DDL 
ALTER TABLE zamowienia
  ADD COLUMN id_kuponu INT NULL;

ALTER TABLE zamowienia
  ADD CONSTRAINT fk_zamowienia_kupony
  FOREIGN KEY (id_kuponu) REFERENCES kupony(id_kuponu);

Dzięki temu możesz analizować, jak kupony wpływają na sprzedaż (np. ile zamówień użyło danego kodu).

Ćwiczenia 21–24: Operacje DML i Złożone Zapytania (DQL)

Ćwiczenie 21: Aktualizacja cen produktów

Zadanie: Podnieś ceny produktów w kategorii 'Elektronika' o 10%.

Pokaż rozwiązanie DML 
UPDATE produkty
SET cena = cena * 1.10
WHERE kategoria = 'Elektronika';

Uważaj na powtórne wykonywanie – każde uruchomienie dodaje kolejne 10%, to nie jest operacja idempotentna.

Ćwiczenie 22: Liczba produktów w zamówieniu

Zadanie: Dla każdego zamówienia policz, ile pozycji (produktów) się w nim znajduje.

Pokaż rozwiązanie DQL 
SELECT z.id_zamowienia,
       COUNT(sz.id_produktu) AS liczba_produktow
FROM zamowienia z
JOIN szczegoly_zamowienia sz
  ON z.id_zamowienia = sz.id_zamowienia
GROUP BY z.id_zamowienia;

Liczymy liczbę pozycji w koszyku, nie ilość sztuk – do zliczania sztuk można użyć SUM(ilosc) zamiast COUNT().

Ćwiczenie 23: Zamówienia z rabatem klienta

Zadanie: Wyświetl zamówienia złożone przez klientów z rabatem > 0, wraz z rabatem.

Pokaż rozwiązanie DQL 
SELECT z.id_zamowienia,
       z.wartosc_zamowienia,
       k.rabat
FROM zamowienia z
JOIN klienci k
  ON z.id_klienta = k.id_klienta
WHERE k.rabat > 0;

Na bazie tego wyniku można obliczać np. „utracony” przychód przez udzielone rabaty.

Ćwiczenie 24: Usuwanie kuponów wygasłych

Zadanie: Usuń kupony, których data ważności minęła.

Pokaż rozwiązanie DML 
DELETE FROM kupony
WHERE data_waznosci < CURDATE();

CURDATE() zwraca dzisiejszą datę w formacie YYYY‑MM‑DD, więc warunek usuwa wszystkie kupony starsze niż dziś.

Ćwiczenie 24.1: Top klienci wg wartości zamówień

Zadanie: Wyświetl 5 klientów o największej łącznej wartości zamówień (ID, imię, nazwisko, suma).

Pokaż rozwiązanie DQL 
SELECT k.id_klienta,
       k.imie,
       k.nazwisko,
       SUM(z.wartosc_zamowienia) AS suma_zamowien
FROM klienci k
JOIN zamowienia z
  ON k.id_klienta = z.id_klienta
GROUP BY k.id_klienta, k.imie, k.nazwisko
ORDER BY suma_zamowien DESC
LIMIT 5;

Ćwiczenie 24.2: Lista zamówień z nazwami produktów

Zadanie: Wyświetl listę zamówień z wypisanymi nazwami produktów oraz ilością.

Pokaż rozwiązanie DQL 
SELECT z.id_zamowienia,
       p.nazwa,
       sz.ilosc,
       z.data_zamowienia
FROM zamowienia z
JOIN szczegoly_zamowienia sz
  ON z.id_zamowienia = sz.id_zamowienia
JOIN produkty p
  ON sz.id_produktu = p.id_produktu
ORDER BY z.id_zamowienia, p.nazwa;

Ćwiczenie 25: Procedury Składowane (Automatyzacja)

Ćwiczenie 25: Procedura dodawania produktu do zamówienia

Zadanie: Stwórz procedurę DodajProduktDoZamowienia, która wstawia rekordu do szczegoly_zamowienia.

Pokaż rozwiązanie (Procedura) 
DELIMITER //

CREATE PROCEDURE DodajProduktDoZamowienia(
    IN zamowienie_id INT,
    IN produkt_id    INT,
    IN ilosc_sztuk   INT
)
BEGIN
    INSERT INTO szczegoly_zamowienia (id_zamowienia, id_produktu, ilosc)
    VALUES (zamowienie_id, produkt_id, ilosc_sztuk);
END //

DELIMITER ;

Przykład wywołania: CALL DodajProduktDoZamowienia(1, 3, 2); – doda 2 sztuki produktu 3 do zamówienia 1.

Ćwiczenie 25.1: Procedura z automatycznym przeliczeniem wartości

Rozszerzenie: Zmodyfikuj procedurę, aby po dodaniu produktu przeliczała wartosc_zamowienia na podstawie cen produktów.

Pokaż przykładowe rozwiązanie 
DELIMITER //

CREATE PROCEDURE DodajProduktDoZamowieniaAuto(
    IN zamowienie_id INT,
    IN produkt_id    INT,
    IN ilosc_sztuk   INT
)
BEGIN
    DECLARE cena_prod DECIMAL(10,2);
    DECLARE nowa_wartosc DECIMAL(10,2);

    -- 1. dodaj pozycje
    INSERT INTO szczegoly_zamowienia (id_zamowienia, id_produktu, ilosc)
    VALUES (zamowienie_id, produkt_id, ilosc_sztuk);

    -- 2. pobierz cene produktu
    SELECT cena INTO cena_prod
    FROM produkty
    WHERE id_produktu = produkt_id;

    -- 3. przelicz wartosc zamowienia
    SELECT wartosc_zamowienia + (cena_prod * ilosc_sztuk)
    INTO nowa_wartosc
    FROM zamowienia
    WHERE id_zamowienia = zamowienie_id;

    UPDATE zamowienia
    SET wartosc_zamowienia = nowa_wartosc
    WHERE id_zamowienia = zamowienie_id;
END //

DELIMITER ;

Ta wersja pokazuje, jak procedura może obsłużyć zarówno logikę koszyka, jak i przeliczanie sumy zamówienia w jednym wywołaniu.