Bazy – sklep zoologiczny

CREATE DATABASE IF NOT EXISTS sklep_zoo
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_polish_ci;
USE sklep_zoo;

-- KLIENCI
CREATE TABLE klienci (
  klient_id INT PRIMARY KEY AUTO_INCREMENT,
  imie VARCHAR(50) NOT NULL,
  nazwisko VARCHAR(80) NOT NULL,
  miasto VARCHAR(80) NOT NULL,
  email VARCHAR(120) UNIQUE
);

-- ZWIERZĘTA (produkty-zwierzęta)
CREATE TABLE zwierzeta (
  zwierze_id INT PRIMARY KEY AUTO_INCREMENT,
  gatunek VARCHAR(80) NOT NULL,      -- np. kot, pies, papuga
  rasa VARCHAR(120) NULL,            -- np. labrador, brytyjski
  cena DECIMAL(10,2) NOT NULL CHECK (cena >= 0)
);

-- AKCESORIA (produkty-akcesoria)
CREATE TABLE akcesoria (
  akcesorium_id INT PRIMARY KEY AUTO_INCREMENT,
  nazwa VARCHAR(120) NOT NULL,
  kategoria VARCHAR(80) NOT NULL,    -- np. smycze, karma, kuwety
  cena DECIMAL(10,2) NOT NULL CHECK (cena >= 0)
);

-- ZAMÓWIENIA (pozycje) – każdy wiersz to jedna pozycja w zamówieniu
CREATE TABLE zamowienia (
  pozycja_id INT PRIMARY KEY AUTO_INCREMENT,
  numer_zamowienia VARCHAR(20) NOT NULL,     -- grupuje pozycje jednego zamówienia
  data_zamowienia DATE NOT NULL,
  klient_id INT NOT NULL,
  typ_produktu ENUM('zwierze','akcesorium') NOT NULL,
  produkt_id INT NOT NULL,                    -- id z odpowiedniej tabeli
  ilosc INT NOT NULL DEFAULT 1 CHECK (ilosc > 0),
  cena_jednostkowa DECIMAL(10,2) NOT NULL CHECK (cena_jednostkowa >= 0),
  FOREIGN KEY (klient_id) REFERENCES klienci(klient_id)
  -- (świadomie bez FK na produkt_id, bo zależy od typ_produktu)
);

INSERT INTO klienci (imie, nazwisko, miasto, email) VALUES
('Anna','Nowak','Warszawa','anna.nowak@example.com'),
('Piotr','Kowalski','Gdańsk','piotr.k@example.com'),
('Maria','Zielińska','Kraków','maria.z@example.com'),
('Jan','Wiśniewski','Poznań','jan.w@example.com');

INSERT INTO zwierzeta (gatunek, rasa, cena) VALUES
('Kot','Brytyjski krótkowłosy',1500.00),
('Pies','Labrador retriever',2500.00),
('Papuga','Nimfa',400.00);

INSERT INTO akcesoria (nazwa, kategoria, cena) VALUES
('Smycz nylonowa','smycze',45.00),
('Kuweta duża','kuwety',60.00),
('Karma premium 5kg','karma',120.00),
('Klatka dla papugi','klatki',300.00);

-- Zamówienia (pozycje)
-- ZAM-001 (Anna, 2025-09-15): papuga + klatka + karma
INSERT INTO zamowienia (numer_zamowienia, data_zamowienia, klient_id, typ_produktu, produkt_id, ilosc, cena_jednostkowa) VALUES
('ZAM-001','2025-09-15',1,'zwierze',3,1,400.00),
('ZAM-001','2025-09-15',1,'akcesorium',4,1,300.00),
('ZAM-001','2025-09-15',1,'akcesorium',3,1,120.00);

-- ZAM-002 (Piotr, 2025-09-16): pies + smycz + 2x karma
INSERT INTO zamowienia VALUES
(NULL,'ZAM-002','2025-09-16',2,'zwierze',2,1,2500.00),
(NULL,'ZAM-002','2025-09-16',2,'akcesorium',1,1,45.00),
(NULL,'ZAM-002','2025-09-16',2,'akcesorium',3,2,120.00);

-- ZAM-003 (Maria, 2025-09-20): kot + kuweta
INSERT INTO zamowienia VALUES
(NULL,'ZAM-003','2025-09-20',3,'zwierze',1,1,1500.00),
(NULL,'ZAM-003','2025-09-20',3,'akcesorium',2,1,60.00);

-- ZAM-004 (Jan, 2025-09-22): smycz + karma
INSERT INTO zamowienia VALUES
(NULL,'ZAM-004','2025-09-22',4,'akcesorium',1,1,45.00),
(NULL,'ZAM-004','2025-09-22',4,'akcesorium',3,1,120.00);

Zadania – zapytania SQL dla ucznia

1. Wyświetl wszystkie zwierzęta tańsze niż 1000 zł.
2. Wyświetl listę klientów posortowaną alfabetycznie według miasta, a w obrębie miasta według nazwiska.
3. Wyświetl wszystkie pozycje zamówień z nazwą produktu (zwierzę lub akcesorium) oraz wartością (ilość × cena).
4. Oblicz sumę wartości każdego zamówienia (dla numeru ZAM-001, ZAM-002 itd.).
5. Podaj łączną wartość sprzedaży z podziałem na typ produktu (zwierzęta i akcesoria).
6. Znajdź zamówienie o najwyższej wartości (suma całego zamówienia).
7. Wyświetl listę klientów, którzy kupili jakiekolwiek zwierzę.
8. Podaj trzy najczęściej kupowane akcesoria (wg liczby sztuk).
9. Oblicz średnią wartość zamówienia dla każdego klienta.
10. Znajdź numery zamówień, które zawierały wyłącznie akcesoria (bez żadnego zwierzęcia).

---