BITEDU.PL

Przykładowe rozwiązania do zadań 3-6 SLQ AVG

Zadanie 1 – funkcje agregujące

-- 1.1 Policz wszystkich klientów
SELECT COUNT(*) AS liczba_klientow
FROM Klienci;

-- 1.2 Oblicz średnią cenę produktów
SELECT ROUND(AVG(cena), 2) AS srednia_cena_produktow
FROM Produkty;

-- 1.3 Najdroższy i najtańszy produkt (ceny)
SELECT MAX(cena) AS najdrozsza_cena, MIN(cena) AS najtansza_cena
FROM Produkty;

-- (opcjonalnie: z nazwą produktu)
-- Najdroższy:
SELECT nazwa, cena
FROM Produkty
ORDER BY cena DESC
LIMIT 1;

-- Najtańszy:
SELECT nazwa, cena
FROM Produkty
ORDER BY cena ASC
LIMIT 1;

-- 1.4 Ilu klientów podało adres e-mail
-- (COUNT(email) pomija NULL-e)
SELECT COUNT(email) AS klienci_z_mailem
FROM Klienci;

Zadanie 2 – grupowanie danych

-- 2.1 Suma zamówień dla każdego klienta
SELECT k.imie, k.nazwisko,
       SUM(p.cena) AS suma_zamowien
FROM Zamowienia z
JOIN Klienci  k ON k.id_klienta  = z.id_klienta
JOIN Produkty p ON p.id_produktu = z.id_produktu
GROUP BY k.id_klienta, k.imie, k.nazwisko
ORDER BY suma_zamowien DESC;

-- 2.2 Średnia cena produktów w każdej kategorii
SELECT kat.nazwa_kategorii,
       ROUND(AVG(p.cena), 2) AS srednia_cena
FROM Produkty p
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii
GROUP BY kat.id_kategorii, kat.nazwa_kategorii
ORDER BY srednia_cena DESC;

-- 2.3 Ile zamówień złożył każdy klient
SELECT k.imie, k.nazwisko,
       COUNT(z.id_zamowienia) AS liczba_zamowien
FROM Zamowienia z
JOIN Klienci k ON k.id_klienta = z.id_klienta
GROUP BY k.id_klienta, k.imie, k.nazwisko
ORDER BY liczba_zamowien DESC, k.nazwisko, k.imie;

-- 2.4 Ilu klientów pochodzi z każdego miasta
SELECT miasto,
       COUNT(*) AS liczba_klientow
FROM Klienci
GROUP BY miasto
ORDER BY liczba_klientow DESC, miasto;

Zadanie 3 – HAVING i sortowanie

-- 3.1 Kategorie, których średnia cena > 200 zł, malejąco
SELECT kat.nazwa_kategorii,
       ROUND(AVG(p.cena), 2) AS srednia_cena
FROM Produkty p
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii
GROUP BY kat.id_kategorii, kat.nazwa_kategorii
HAVING AVG(p.cena) > 200
ORDER BY srednia_cena DESC;

-- 3.2 Klienci z więcej niż jednym zamówieniem
SELECT k.imie, k.nazwisko,
       COUNT(z.id_zamowienia) AS liczba
FROM Klienci k
JOIN Zamowienia z ON k.id_klienta = z.id_klienta
GROUP BY k.imie, k.nazwisko
HAVING COUNT(z.id_zamowienia) > 1
ORDER BY liczba DESC, k.nazwisko, k.imie;

Zadanie 3 – HAVING i sortowanie

  1. Kategorie, gdzie średnia cena > 200 zł, posortowane malejąco:
SELECT kat.nazwa_kategorii,
       AVG(p.cena) AS srednia_cena
FROM Produkty p
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii
GROUP BY kat.nazwa_kategorii
HAVING AVG(p.cena) > 200
ORDER BY srednia_cena DESC;
  1. Klienci z > 1 zamówieniem:
SELECT k.imie, k.nazwisko,
       COUNT(z.id_zamowienia) AS liczba
FROM Klienci k
JOIN Zamowienia z ON k.id_klienta = z.id_klienta
GROUP BY k.imie, k.nazwisko
HAVING COUNT(z.id_zamowienia) > 1
ORDER BY liczba DESC, k.nazwisko, k.imie;

Zadanie 4 – JOIN + filtry na datach (doprecyzowane)

  1. Lista wszystkich zamówień (imię, nazwisko, produkt, cena, data):
SELECT k.imie, k.nazwisko,
       p.nazwa AS produkt, p.cena,
       z.data_zamowienia
FROM Zamowienia z
JOIN Klienci k  ON k.id_klienta  = z.id_klienta
JOIN Produkty p ON p.id_produktu = z.id_produktu
ORDER BY z.data_zamowienia, k.nazwisko, k.imie;
  1. Tylko wrzesień 2025:
SELECT k.imie, k.nazwisko, p.nazwa, p.cena, z.data_zamowienia
FROM Zamowienia z
JOIN Klienci k  ON k.id_klienta  = z.id_klienta
JOIN Produkty p ON p.id_produktu = z.id_produktu
WHERE z.data_zamowienia BETWEEN '2025-09-01' AND '2025-09-30'
ORDER BY z.data_zamowienia;
  1. Dzienna wartość sprzedaży:
SELECT z.data_zamowienia,
       SUM(p.cena) AS wartosc_dnia
FROM Zamowienia z
JOIN Produkty p ON p.id_produktu = z.id_produktu
GROUP BY z.data_zamowienia
ORDER BY z.data_zamowienia;
  1. 3 najdroższe pojedyncze zamówienia (po cenie produktu):
SELECT z.id_zamowienia, k.imie, k.nazwisko,
       p.nazwa, p.cena, z.data_zamowienia
FROM Zamowienia z
JOIN Klienci k  ON k.id_klienta  = z.id_klienta
JOIN Produkty p ON p.id_produktu = z.id_produktu
ORDER BY p.cena DESC, z.id_zamowienia DESC
LIMIT 3;

bitedu.pl

Zadanie 5 – podzapytania i „brak danych” (uzupełnione)

  1. Klienci bez zamówień (anti-join):
SELECT k.*
FROM Klienci k
LEFT JOIN Zamowienia z ON z.id_klienta = k.id_klienta
WHERE z.id_zamowienia IS NULL
ORDER BY k.nazwisko, k.imie;
  1. Produkty droższe niż średnia w ich kategorii:
SELECT p.*
FROM Produkty p
WHERE p.cena > (
  SELECT AVG(p2.cena)
  FROM Produkty p2
  WHERE p2.id_kategorii = p.id_kategorii
)
ORDER BY p.cena DESC;
  1. Produkty, których nikt nie zamówił:
SELECT p.*
FROM Produkty p
LEFT JOIN Zamowienia z ON z.id_produktu = p.id_produktu
WHERE z.id_zamowienia IS NULL
ORDER BY p.nazwa;
  1. Klienci, którzy kupili ≥ 2 różne produkty w tej samej kategorii:
SELECT k.imie, k.nazwisko, kat.nazwa_kategorii,
       COUNT(DISTINCT p.id_produktu) AS rozne_produkty
FROM Zamowienia z
JOIN Klienci k    ON k.id_klienta     = z.id_klienta
JOIN Produkty p   ON p.id_produktu    = z.id_produktu
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii
GROUP BY k.id_klienta, kat.id_kategorii
HAVING COUNT(DISTINCT p.id_produktu) >= 2
ORDER BY k.nazwisko, k.imie, kat.nazwa_kategorii;

bitedu.pl

Zadanie 6 – raporty, ranking i widok (uzupełnione)

  1. Wartość sprzedaży per kategoria:
SELECT kat.nazwa_kategorii,
       SUM(p.cena) AS wartosc_sprzedazy
FROM Zamowienia z
JOIN Produkty p   ON p.id_produktu    = z.id_produktu
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii
GROUP BY kat.nazwa_kategorii
ORDER BY wartosc_sprzedazy DESC;
  1. Średnia cena zamawianych produktów per miasto:
SELECT k.miasto,
       ROUND(AVG(p.cena), 2) AS srednia_cena
FROM Zamowienia z
JOIN Klienci k  ON k.id_klienta  = z.id_klienta
JOIN Produkty p ON p.id_produktu = z.id_produktu
GROUP BY k.miasto
ORDER BY srednia_cena DESC, k.miasto;
  1. Miasto z największą liczbą zamówień (TOP 1):
SELECT k.miasto, COUNT(*) AS liczba_zamowien
FROM Zamowienia z
JOIN Klienci k ON k.id_klienta = z.id_klienta
GROUP BY k.miasto
ORDER BY liczba_zamowien DESC
LIMIT 1;
  1. Widok szczegółów zamówień:
CREATE OR REPLACE VIEW Widok_Zamowienia_Szczegoly AS
SELECT z.id_zamowienia,
       z.data_zamowienia,
       k.imie, k.nazwisko, k.miasto,
       p.nazwa AS produkt, p.cena,
       kat.nazwa_kategorii AS kategoria
FROM Zamowienia z
JOIN Klienci k    ON k.id_klienta     = z.id_klienta
JOIN Produkty p   ON p.id_produktu    = z.id_produktu
JOIN Kategorie kat ON kat.id_kategorii = p.id_kategorii;
-- podgląd:
SELECT * FROM Widok_Zamowienia_Szczegoly
ORDER BY data_zamowienia, id_zamowienia;