🛒 Ćwiczenia z Baz Danych: Struktura Sklepu Internetowego (MySQL / XAMPP)

🛠️ Wymagana Inicjalizacja DDL i DML

Najpierw utwórz bazę danych sklep i tabele, a potem dane testowe. Wykonaj kod w phpMyAdmin → baza sklep → zakładka SQL.

Ćwiczenie 1: Tworzenie głównej tabeli klientów

CREATE DATABASE i CREATE TABLE (klienci)

Zadanie: Utwórz bazę danych sklep i tabelę klienci.

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)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;
    

Ćwiczenie 2: Tworzenie tabeli zamówień i relacji

CREATE TABLE i FOREIGN KEY (zamowienia)

Zadanie: Utwórz tabelę zamowienia z kluczem obcym do klienci.

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

Ćwiczenie 2.1: Wstawienie danych testowych (DML)

INSERT INTO

Zadanie: Dodaj klientów i zamówienia.

INSERT INTO klienci (imie, nazwisko, email, telefon) VALUES
('Jan',   'Kowalski', 'jan@test.pl',   '500100200'),
('Anna',  'Nowak',    'anna@test.pl',  '501202303'),
('Piotr', 'Zajac',    'piotr@test.pl', '503404505');

INSERT INTO zamowienia (id_klienta, data_zamowienia, wartosc_zamowienia, status) VALUES
(1, '2023-12-15', 120.50, 'Zakonczone'),
(1, '2024-03-20', 650.00, 'Realizacja'),
(2, '2024-01-05', 45.99,  'Zakonczone'),
(3, '2024-05-01', 890.00, 'Nowe');
    

Ćwiczenie 3: Modyfikowanie struktury bazy

ALTER TABLE i ENUM

Zadanie: Dodaj adres do klientów, upewnij się, że tabela zamowienia ma kolumnę status, wydłuż telefon.

ALTER TABLE klienci
  ADD COLUMN adres VARCHAR(255);

-- jeśli status już jest, ten ALTER można pominąć
-- ALTER TABLE zamowienia
--   ADD COLUMN status ENUM('Nowe', 'Realizacja', 'Zakonczone') DEFAULT 'Nowe';

ALTER TABLE klienci
  MODIFY COLUMN telefon VARCHAR(20);
    

Ćwiczenie 4: Usuwanie i ponowne tworzenie

DROP TABLE

Zadanie: Usuń tabelę zamowienia i stwórz ją ponownie z kolumną metoda_platnosci.

DROP TABLE IF EXISTS zamowienia;

CREATE TABLE 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') DEFAULT 'Nowe',
    FOREIGN KEY (id_klienta) REFERENCES klienci(id_klienta)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_polish_ci;
    

Ćwiczenie 5: Analiza i optymalizacja

CREATE INDEX i GROUP BY

Zadanie: Utwórz indeks po emailu i policz wydatki klientów.

CREATE INDEX idx_email ON klienci(email);

SELECT id_klienta,
       SUM(wartosc_zamowienia) AS suma_wydatkow
FROM zamowienia
GROUP BY id_klienta
ORDER BY suma_wydatkow DESC;
    

Ćwiczenie 6: Dodanie tabeli produktów

CREATE TABLE (produkty)

Zadanie: Stwórz tabelę produktów.

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;
    

Ćwiczenie 7: Relacja N:M zamowienia–produkty

CREATE TABLE (szczegoly_zamowienia)

Zadanie: Tabela łącząca zamówienia z produktami.

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;
    

Ćwiczenie 8: Dodanie klientów VIP

ALTER TABLE i BOOLEAN/TINYINT

Zadanie: Dodaj kolumnę vip dla klientów.

ALTER TABLE klienci
  ADD COLUMN vip BOOLEAN DEFAULT 0;
    

Ćwiczenie 9: Aktualizacja statusów zamówień

UPDATE i WHERE

Zadanie: Ustaw status = 'Zakonczone' dla zamówień sprzed 2024‑01‑01.

UPDATE zamowienia
SET status = 'Zakonczone'
WHERE data_zamowienia < '2024-01-01';
    

Ćwiczenie 10: Usunięcie klientów bez zamówień

DELETE i subzapytanie (NOT IN)

Zadanie: Usuń klientów, którzy nie występują w tabeli zamowienia.

DELETE FROM klienci
WHERE id_klienta NOT IN (
    SELECT DISTINCT id_klienta FROM zamowienia
);
    

Ćwiczenie 11: Widok aktywnych zamówień

CREATE VIEW

Zadanie: Stwórz widok dla zamówień, które nie są zakończone.

CREATE OR REPLACE VIEW aktywne_zamowienia AS
SELECT *
FROM zamowienia
WHERE status <> 'Zakonczone';
    

Ćwiczenie 12: Liczba zamówień klientów

SELECT, COUNT, GROUP BY

Zadanie: Policz liczbę zamówień każdego klienta.

SELECT id_klienta,
       COUNT(*) AS liczba_zamowien
FROM zamowienia
GROUP BY id_klienta;
    

Ćwiczenie 13: Kolumna rabat

ALTER TABLE i DECIMAL

Zadanie: Dodaj kolumnę rabat do klienci.

ALTER TABLE klienci
  ADD COLUMN rabat DECIMAL(5,2) DEFAULT 0.00;
    

Ćwiczenie 14: Procedura dodająca zamówienie

CREATE PROCEDURE

Zadanie: Procedura przyjmująca ID klienta i wartość zamówienia.

DELIMITER //

CREATE PROCEDURE DodajZamowienie(
    IN klient_id INT,
    IN wartosc   DECIMAL(10,2)
)
BEGIN
    INSERT INTO zamowienia (id_klienta, data_zamowienia, wartosc_zamowienia, status)
    VALUES (klient_id, NOW(), wartosc, 'Nowe');
END //

DELIMITER ;
    

Ćwiczenie 15: Trigger aktualizujący status „VIP”

CREATE TRIGGER (Automatyzacja)

Zadanie: Jeśli wartość zamówienia przekroczy 500 zł, ustaw status zamówienia na specjalny status, np. 'VIP' (rozszerzamy ENUM).

-- najpierw rozszerz ENUM o 'VIP', jeśli trzeba:
ALTER TABLE zamowienia
  MODIFY COLUMN status ENUM('Nowe', 'Realizacja', 'Zakonczone', 'VIP')
  DEFAULT 'Nowe';

DELIMITER //

CREATE TRIGGER AktualizujStatusVIP
BEFORE INSERT ON zamowienia
FOR EACH ROW
BEGIN
    IF NEW.wartosc_zamowienia > 500 THEN
        SET NEW.status = 'VIP';
    END IF;
END //

DELIMITER ;