-- KOLOKWIUM 1
--create table
--alter table
--tworzenie i dodawanie wiezow integralnosci
--select distinct
--where
--join
--group by
--having
-- bedzie zadanie z data
-- case tez moze sie pojawic
-- Przyklad: ktory egzaminator egzaminowal wiecej niz 20 osob

-- 1. Ile punktow uzyskal z egzaminow KAZDY student?
-- Dla kazdego studenta (id nazwisko imie)
-- podac liczbe uzyskanych punktow

SELECT s.id_student, s.nazwisko, s.imie, SUM(e.punkty) AS "Suma punktow"
FROM egzaminy e
RIGHT JOIN studenci s
ON s.id_student = e.id_student
GROUP BY s.id_student, s.nazwisko, s.imie
ORDER BY s.id_student

-- 2. Ile punktow uzyskali poszczegolni studenci
-- z egzaminow z poszczegolnych przedmiotow?
-- Dla kazdego studenta ktory zdawal juz egzamin
-- (id nazwisko imie) podac liczbe uzyskanych
-- punktow z egzaminow dla psozczegolnych zdwanych przedmiotow

SELECT s.id_student, s.nazwisko, s.imie,
    SUM(e.punkty) AS "Suma punktow",
    p.nazwa_p AS "Nazwa przedmiotu"
FROM egzaminy e
INNER JOIN studenci s ON s.id_student = e.id_student
INNER JOIN przedmioty p ON e.id_przedmiot = p.id_przedmiot
GROUP BY s.id_student, s.nazwisko, s.imie, p.nazwa_p
ORDER BY s.id_student, p.nazwa_p;

-- 3. Ile egzaminow przeprowadzono
-- z kazdego przedmiotu?
-- Podac nazwe przedmiotu oraz liczbe egzaminow.
-- Opisac poprawnie kolumne z liczba egzaminwo.
-- Posortowac wg nazwy przemdiotu

SELECT p.nazwa_p AS "Nazwa przedmiotu", COUNT(e.nr_egz) AS "Liczba egzaminow"
FROM przedmioty p
INNER JOIN egzaminy e ON p.id_przedmiot = e.id_przedmiot
GROUP BY p.nazwa_p
ORDER BY p.nazwa_p

-- 4. Z ktorego przedmiotu przeprowadzono wiecej niz
-- 220 egzaminow. Podac nazwe przedmiotu oraz liczbe egz.

SELECT p.nazwa_p AS "Nazwa przedmiotu", COUNT(e.nr_egz) AS "Liczba egzaminow"
FROM przedmioty p
LEFT JOIN egzaminy e ON p.id_przedmiot = e.id_przedmiot
GROUP BY p.nazwa_p
HAVING COUNT(e.nr_egz) > 220
ORDER BY p.nazwa_p

-- 5. Ilu studentow zdawalo egzaminy z kazdego przedmiotu?
-- Podac nazwe przedmiotuoraz liczbe studentow.
-- Poprawnie opisac kolumnes
-- Posortowac wg nazwy przedmiotu

SELECT p.nazwa_p AS "Nazwa przedmiotu", COUNT(DISTINCT id_student) AS "Liczba studentow"
FROM przedmioty p
LEFT JOIN egzaminy e ON p.id_przedmiot = e.id_przedmiot
GROUP BY p.nazwa_p
ORDER BY p.nazwa_p

-- 6. Ilu studentow bylo egzaminowanych
-- przez poszczegolnych egzaminatorow
-- z poszczegolnych przedmiotow?
-- Dla kazdego egzaminatora
-- ktory(id nazwisko imie) przeprowadzil juz egzamin
-- oraz przedmiotu (nazwa) podac liczbe studentow.
-- Posortowac wg id egzaminatora i nazwy przedmiotu

SELECT g.id_egzaminator, g.nazwisko, g.imie, p.nazwa_p, COUNT(DISTINCT e.id_student)
    AS "Liczba studentow"
FROM przedmioty p
INNER JOIN egzaminy e ON p.id_przedmiot = e.id_przedmiot
INNER JOIN egzaminatorzy g ON g.id_egzaminator = e.id_egzaminator
GROUP BY g.id_egzaminator, g.nazwisko, g.imie, p.nazwa_p
ORDER BY g.id_egzaminator, p.nazwa_p

-- 7. Ile gezaminow przeprowadzono
-- w poszczegolnych miesiacach w kolejnych latach?
-- Dla kazdego roku i miesiaca egzaminowania
-- podac liczbe egzmainow. Opisac poprawnie kolumny.
-- Posortowac wg lat

SELECT EXTRACT(YEAR FROM e.data_egz) AS "Rok",
        EXTRACT(MONTH FROM e.data_egz) AS "Miesiac",
        COUNT(e.nr_egz) AS "Liczba studentow"
FROM egzaminy e
GROUP BY EXTRACT(YEAR FROM e.data_egz), EXTRACT(MONTH FROM e.data_egz)
ORDER BY 1,2;

-- wersja dodatkowa z wyswietlaniem nazwy miesiecy
-- ale brzydka o.o

SELECT EXTRACT(YEAR FROM data_egz) AS Rok,
        CASE EXTRACT(MONTH FROM data_egz)   WHEN 1 THEN 'Styczeń'
                                            WHEN 2 THEN 'Luty'
                                            WHEN 3 THEN 'Marzec'
                                            WHEN 4 THEN 'Kwiecień'
                                            WHEN 5 THEN 'Maj'
                                            WHEN 6 THEN 'Czerwiec'
                                            WHEN 7 THEN 'Lipiec'
                                            WHEN 8 THEN 'Sierpień'
                                            WHEN 9 THEN 'Wrzesień'
                                            WHEN 10 THEN 'Październik'
                                            WHEN 11 THEN 'Listopad'
                                            ELSE 'Grudzień'
        END AS Miesiac,
        COUNT(e.nr_egz) AS "Liczba egzaminów"
FROM egzaminy e
GROUP BY EXTRACT(YEAR FROM data_egz), CASE EXTRACT(MONTH FROM data_egz) WHEN 1 THEN 'Styczeń'
                                            WHEN 2 THEN 'Luty'
                                            WHEN 3 THEN 'Marzec'
                                            WHEN 4 THEN 'Kwiecień'
                                            WHEN 5 THEN 'Maj'
                                            WHEN 6 THEN 'Czerwiec'
                                            WHEN 7 THEN 'Lipiec'
                                            WHEN 8 THEN 'Sierpień'
                                            WHEN 9 THEN 'Wrzesień'
                                            WHEN 10 THEN 'Październik'
                                            WHEN 11 THEN 'Listopad'
                                            ELSE 'Grudzień'
        END,
        EXTRACT(MONTH FROM data_egz)
ORDER BY 1, EXTRACT(MONTH FROM data_egz);


-- 8. W ktorym miesiacu kazdego roku egzaminowania
-- przeprowadzono wiecej niz 100 egzaminow

SELECT EXTRACT(YEAR FROM e.data_egz) AS "Rok",
        EXTRACT(MONTH FROM e.data_egz) AS "Miesiac",
        COUNT(e.nr_egz) AS "Liczba studentow"
FROM egzaminy e
GROUP BY EXTRACT(YEAR FROM e.data_egz), EXTRACT(MONTH FROM e.data_egz)
HAVING COUNT(e.nr_egz) > 100
ORDER BY 1,2;