Widoki - Views - Zadania

Poniższe zadania wykonujemy na bazie danych – NORTHWIND 

Zadanie 1 — Widok podstawowy produktów

Polecenie: Stwórz widok vw_produkty_podstawowe z kolumnami: id_produkt, nazwa, cena_jednostkowa.

Przykład rozwiązania:
CREATE VIEW vw_produkty_podstawowe AS
SELECT id_produkt, nazwa, cena_jednostkowa
FROM produkty;

Test:
SELECT * FROM vw_produkty_podstawowe LIMIT 10;

Zadanie 2 — Widok produktów w zapasie (in stock)

Polecenie: Stwórz widok vw_produkty_dostepne zawierający produkty, których stan_magazynu > minimum_magazynu.

Zadanie 3 — Widok szczegółów zamówień z nazwami produktów i wartością pozycji

Polecenie: Stwórz widok vw_szczegoly_zamowien zawierający: id_zamowienia, id_produkt, nazwa (produktu), ilosc, rabat, cena_jednostkowa, wartosc_pozycji (liczone: ilosc * cena_jednostkowa * (1 – rabat)).

Zadanie 4 — Widok sprzedaży dziennej (agregacja)

Polecenie: Stwórz widok vw_sprzedaz_dzienna pokazujący dla każdego data_zamowienia: data_zamowienia, laczna_ilosc, laczna_wartosc, liczba_transakcji.

Przykład rozwiązania:
CREATE VIEW vw_sprzedaz_dzienna AS
SELECT
z.data_zamowienia,
SUM(sz.ilosc) AS laczna_ilosc,
SUM(sz.ilosc * p.cena_jednostkowa * (1 – sz.rabat)) AS laczna_wartosc,
COUNT(DISTINCT z.id_zamowienia) AS liczba_transakcji
FROM zamowienia z
JOIN szczegoly_zamowien sz ON z.id_zamowienia = sz.id_zamowienia
JOIN produkty p ON sz.id_produkt = p.id_produkt
GROUP BY z.data_zamowienia;

Test:
SELECT * FROM vw_sprzedaz_dzienna ORDER BY data_zamowienia;

Zadanie 5 — Widok klientów o dużym obrocie

Polecenie: Stwórz widok vw_klienci_duzy_obrot listujący klientów z id_klienta, imie, nazwisko, suma_wydatkow — tylko ci, których suma_wydatkow > 1000.

(Dla prostoty użyjmy progu 1000; możesz zmienić w zapytaniu.)

————————————-

Zadanie 6 — Widok sprzedaży pracownika (imie + nazwisko)

Polecenie: Stwórz widok vw_sprzedaz_pracownika pokazujący id_pracownik, imie, nazwisko, liczba_zamowien, wartosc_sprzedazy.

Przykład rozwiązania:
CREATE VIEW vw_sprzedaz_pracownika AS
SELECT
p.id_pracownik,
p.imie,
p.nazwisko,
COUNT(DISTINCT z.id_zamowienia) AS liczba_zamowien,
SUM(sz.ilosc * prod.cena_jednostkowa * (1 – sz.rabat)) AS wartosc_sprzedazy
FROM pracownicy p
LEFT JOIN zamowienia z ON p.id_pracownik = z.id_pracownika
LEFT JOIN szczegoly_zamowien sz ON z.id_zamowienia = sz.id_zamowienia
LEFT JOIN produkty prod ON sz.id_produkt = prod.id_produkt
GROUP BY p.id_pracownik;

Zadanie 7 — Widok produktów niskiego poziomu (alert)

Polecenie: Stwórz widok vw_niski_stan z produktami, gdzie stan_magazynu <= minimum_magazynu (do użytku jako alert).

Zadanie 8 — Widok liczby produktów wg dostawcy

Polecenie: Stwórz widok vw_dostawca_produkty pokazujący dla każdego dostawcy: id_dostwcy, nazwa_firmy, ilosc_produktow.

Zadanie 9 — Widok produktów popularnych

Polecenie: Stwórz widok vw_produkty_popularne zawierający produkty, które łącznie sprzedano w liczbie większej niż 10 sztuk (SUM(ilosc) > 10). Widok ma zawierać: id_produkt, nazwa, suma_sprzedanych.

Przykład rozwiązania:
CREATE VIEW vw_produkty_popularne AS
SELECT p.id_produkt, p.nazwa, SUM(sz.ilosc) AS suma_sprzedanych
FROM produkty p
JOIN szczegoly_zamowien sz ON p.id_produkt = sz.id_produkt
GROUP BY p.id_produkt
HAVING SUM(sz.ilosc) > 10;

Zadanie 10 — Widok wartości zamówienia (podsumowanie zamówienia)

Polecenie: Stwórz widok vw_zamowienie_wartosc zawierający: id_zamowienia, data_zamowienia, klient (połączone imie + nazwisko), wartosc_zamowienia, koszt_przesylki, laczna_wartosc_z_kosztem (wartosc_zamowienia + koszt_przesylki).

Przykład rozwiązania:
CREATE VIEW vw_zamowienie_wartosc AS
SELECT
z.id_zamowienia,
z.data_zamowienia,
CONCAT(k.imie, ’ ’, k.nazwisko) AS klient,
SUM(sz.ilosc * p.cena_jednostkowa * (1 – sz.rabat)) AS wartosc_zamowienia,
z.koszt_przesylki,
SUM(sz.ilosc * p.cena_jednostkowa * (1 – sz.rabat)) + z.koszt_przesylki AS laczna_wartosc_z_kosztem
FROM zamowienia z
JOIN klienci k ON z.id_klienta = k.id_klienta
JOIN szczegoly_zamowien sz ON z.id_zamowienia = sz.id_zamowienia
JOIN produkty p ON sz.id_produkt = p.id_produkt
GROUP BY z.id_zamowienia;

Test:
SELECT * FROM vw_zamowienie_wartosc ORDER BY laczna_wartosc_z_kosztem DESC LIMIT 20;