⚙️ Zaawansowane Ćwiczenia z Baz Danych (Transakcje, Widoki, Procedury) – baza sklep

🔹 Sekcja startowa: dodatkowe dane testowe (DML)

Produkty, zamówienia i szczegóły zamówień

Poniższe INSERT‑y zakładają, że masz już utworzone tabele klienci, zamowienia, produkty, szczegoly_zamowienia z poprzedniej części oraz w tabeli klienci istnieją klienci o ID 1, 2 i 3.

USE sklep;

-- Dane testowe do tabeli produkty
INSERT INTO produkty (nazwa, cena, kategoria) VALUES
('Klawiatura mechaniczna RGB', 249.99, 'Periferia'),
('Mysz bezprzewodowa',         89.90,  'Periferia'),
('Monitor 24" FullHD',        699.00, 'Monitory'),
('Laptop 15" i5/16GB/512GB',  3499.00,'Laptopy'),
('Sluchawki nauszne BT',       199.00, 'Audio');

-- Przykładowe zamówienia (jeśli tabela zamowienia została na nowo utworzona)
INSERT INTO zamowienia (id_klienta, data_zamowienia, wartosc_zamowienia, metoda_platnosci, status) VALUES
(1, '2024-06-01', 249.99,  'Karta',    'Zakonczone'),
(1, '2024-06-10', 788.90,  'Przelew',  'Realizacja'),
(2, '2024-06-15', 3499.00, 'Karta',    'Nowe'),
(3, '2024-06-20', 288.90,  'Gotowka',  'Zakonczone');

-- Zakładamy, że powyższe INSERT‑y dają id_zamowienia = 1,2,3,4
-- oraz produkty mają id_produktu od 1 do 5

INSERT INTO szczegoly_zamowienia (id_zamowienia, id_produktu, ilosc) VALUES
-- Zamówienie 1: jedna klawiatura
(1, 1, 1),
-- Zamówienie 2: mysz + monitor
(2, 2, 1),
(2, 3, 1),
-- Zamówienie 3: jeden laptop
(3, 4, 1),
-- Zamówienie 4: mysz + sluchawki
(4, 2, 1),
(4, 5, 1);
    

Ćwiczenie 16: Tworzenie transakcji

Cel: Gwarancja spójności danych przy masowej aktualizacji

Zadanie: Używając transakcji, przyznaj rabat 10% tylko klientom VIP. Jeśli cokolwiek pójdzie nie tak, wycofaj zmiany.

START TRANSACTION;

-- Aktualizacja rabatu dla klientów VIP
UPDATE klienci
SET rabat = 10.00
WHERE vip = 1;

-- (opcjonalnie) sprawdzenie:
-- SELECT * FROM klienci WHERE vip = 1;

-- jeśli wszystko OK:
COMMIT;

-- gdyby był błąd zamiast COMMIT:
-- ROLLBACK;
    

Ćwiczenie 17: Złożony widok KlienciZamowienia

Cel: Uproszczenie raportowania o klientach i zamówieniach

Zadanie: Stwórz widok, który pokaże imię, nazwisko klienta i liczbę jego zamówień (także 0).

CREATE OR REPLACE VIEW KlienciZamowienia AS
SELECT k.id_klienta,
       k.imie,
       k.nazwisko,
       COUNT(z.id_zamowienia) AS liczba_zamowien
FROM klienci k
LEFT JOIN zamowienia z
       ON k.id_klienta = z.id_klienta
GROUP BY k.id_klienta, k.imie, k.nazwisko;
    

Ćwiczenie 18: Procedura z warunkiem – DodajProdukt

Cel: Reguły biznesowe po stronie serwera

Zadanie: Procedura ma dodać produkt tylko, gdy cena > 0, inaczej rzuca błąd.

DELIMITER //

CREATE PROCEDURE DodajProdukt(
    IN nazwa_prod     VARCHAR(100),
    IN cena_prod      DECIMAL(10,2),
    IN kategoria_prod VARCHAR(50)
)
BEGIN
    IF cena_prod > 0 THEN
        INSERT INTO produkty (nazwa, cena, kategoria)
        VALUES (nazwa_prod, cena_prod, kategoria_prod);
    ELSE
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Blad: Cena musi byc wieksza od zera';
    END IF;
END //

DELIMITER ;
    

Ćwiczenie 19: Funkcja użytkownika ObliczWartoscZamowien

Cel: Zwracanie wartości używalnej w SELECT

Zadanie: Funkcja ma zwrócić łączną wartość zamówień danego klienta, albo 0.

DELIMITER //

CREATE FUNCTION ObliczWartoscZamowien(klient_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE suma DECIMAL(10,2);

    SELECT SUM(wartosc_zamowienia)
    INTO suma
    FROM zamowienia
    WHERE id_klienta = klient_id;

    RETURN COALESCE(suma, 0.00);
END //

DELIMITER ;
    

Ćwiczenie 20: Indeks po cenie produktów

Cel: Optymalizacja wyszukiwania i sortowania

Zadanie: Utwórz indeks na kolumnie cena w tabeli produkty.

CREATE INDEX idx_cena ON produkty(cena);