SQL – sklep
internetowy

SELECT imie, nazwisko, email, miasto
FROM   klienci
ORDER BY nazwisko ASC;

SELECT nazwa, cena, producent
FROM   produkty
WHERE  cena < 500.00
ORDER BY cena ASC;

SELECT COUNT(*) AS liczba_klientow
FROM   klienci
WHERE  miasto = 'Warszawa';

SELECT nazwa, cena, stan_mag
FROM   produkty
WHERE  producent = 'Apple'
ORDER BY cena DESC;

UPDATE zamowienia
SET    status = 'w_realizacji'
WHERE  id_zamowienia = 6;

-- Sprawdzenie:
SELECT id_zamowienia, status, data_zam
FROM   zamowienia
WHERE  id_zamowienia = 6;

INSERT INTO produkty (nazwa, cena, id_kategorii, stan_mag, producent)
VALUES ('Sony WF-1000XM4', 799.00, 5, 15, 'Sony');

-- Sprawdzenie:
SELECT * FROM produkty WHERE producent = 'Sony';

SELECT
  p.nazwa,
  p.cena,
  k.nazwa_kat
FROM       produkty   p
INNER JOIN kategorie  k ON p.id_kategorii = k.id_kategorii
ORDER BY   k.nazwa_kat, p.cena;

SELECT
  miasto,
  COUNT(*) AS liczba_klientow
FROM     klienci
GROUP BY miasto
ORDER BY liczba_klientow DESC;

SELECT
  k.nazwa_kat,
  MAX(p.cena)              AS cena_max,
  MIN(p.cena)              AS cena_min,
  ROUND(AVG(p.cena), 2)  AS srednia
FROM       produkty   p
INNER JOIN kategorie  k ON p.id_kategorii = k.id_kategorii
GROUP BY   k.id_kategorii, k.nazwa_kat
HAVING     AVG(p.cena) > 500
ORDER BY   srednia DESC;

SELECT
  z.id_zamowienia,
  z.data_zam,
  z.status,
  z.wartosc_total
FROM       zamowienia  z
INNER JOIN klienci     k ON z.id_klienta = k.id_klienta
WHERE      k.imie = 'Anna' AND k.nazwisko = 'Kowalska'
ORDER BY   z.data_zam;

SELECT id_produktu, nazwa, cena, producent
FROM   produkty
WHERE  nazwa LIKE '%Sony%'
ORDER BY cena DESC;

-- Przed zmianą:
SELECT nazwa, cena FROM produkty WHERE id_kategorii = 3;

UPDATE produkty
SET    cena = cena * 1.10
WHERE  id_kategorii = 3;

-- Po zmianie:
SELECT nazwa, cena FROM produkty WHERE id_kategorii = 3;

SELECT
  CONCAT(kl.imie, ' ', kl.nazwisko) AS klient,
  pr.nazwa                           AS produkt,
  pz.ilosc,
  pz.cena_jedn,
  pz.ilosc * pz.cena_jedn            AS wartosc
FROM       pozycje_zam pz
INNER JOIN zamowienia  z  ON pz.id_zamowienia = z.id_zamowienia
INNER JOIN klienci     kl ON z.id_klienta    = kl.id_klienta
INNER JOIN produkty    pr ON pz.id_produktu   = pr.id_produktu
WHERE      pz.id_zamowienia = 6;

-- Krok 1: usuń pozycje anulowanych zamówień (klucz obcy!)
DELETE FROM pozycje_zam
WHERE       id_zamowienia IN (
  SELECT id_zamowienia
  FROM   zamowienia
  WHERE  status = 'anulowane'
);

-- Krok 2: usuń zamówienia anulowane
DELETE FROM zamowienia
WHERE       status = 'anulowane';

SELECT
  CONCAT(k.imie, ' ', k.nazwisko)    AS klient,
  COUNT(z.id_zamowienia)             AS liczba_zam,
  COALESCE(SUM(z.wartosc_total), 0) AS laczna_wartosc
FROM      klienci     k
LEFT JOIN zamowienia  z
  ON  k.id_klienta = z.id_klienta
  AND z.status = 'dostarczone'
GROUP BY  k.id_klienta, k.imie, k.nazwisko
ORDER BY  laczna_wartosc DESC;

-- Podgląd średniej:
SELECT ROUND(AVG(cena), 2) AS srednia_cena FROM produkty;

-- Produkty powyżej średniej:
SELECT nazwa, cena, producent
FROM   produkty
WHERE  cena > (
  SELECT AVG(cena) FROM produkty
)
ORDER BY cena DESC;

CREATE VIEW v_sprzedaz_produktow AS
SELECT
  pr.id_produktu,
  pr.nazwa,
  pr.producent,
  COALESCE(SUM(pz.ilosc), 0)               AS sprzedana_ilosc,
  COALESCE(SUM(pz.ilosc * pz.cena_jedn), 0) AS przychod
FROM      produkty    pr
LEFT JOIN pozycje_zam pz ON pr.id_produktu = pz.id_produktu
GROUP BY  pr.id_produktu, pr.nazwa, pr.producent;

-- Użycie widoku — TOP 5 produktów wg przychodu:
SELECT * FROM v_sprzedaz_produktow
ORDER BY przychod DESC
LIMIT 5;

-- Usuwanie widoku:
-- DROP VIEW v_sprzedaz_produktow;

START TRANSACTION;

-- 1. Dodaj zamówienie
INSERT INTO zamowienia (id_klienta, status, wartosc_total)
VALUES (3, 'nowe', 4198.00);

-- 2. Pobierz ID nowego zamówienia
SET @nowe_id = LAST_INSERT_ID();

-- 3. Dodaj pozycje zamówienia
INSERT INTO pozycje_zam (id_zamowienia, id_produktu, ilosc, cena_jedn) VALUES
  (@nowe_id, 1, 1, 3999.00),
  (@nowe_id, 7, 3, 29.99),
  (@nowe_id, 8, 2, 49.99);

-- 4. Zmniejsz stany magazynowe
UPDATE produkty SET stan_mag = stan_mag - 1 WHERE id_produktu = 1;
UPDATE produkty SET stan_mag = stan_mag - 3 WHERE id_produktu = 7;
UPDATE produkty SET stan_mag = stan_mag - 2 WHERE id_produktu = 8;

-- 5. Zatwierdź (lub cofnij w razie błędu)
COMMIT;
-- ROLLBACK;  ← cofnięcie wszystkich kroków