/*
Bazy danych
Labolatorium 5
Karol Dobrakowski
Gr. Lab 7
*/

-- BRAK ZADANIA 21


/* Zadanie 1

W oparciu o dane zgromadzone w tabeli Studenci wyœwietl dane zwi¹zane z najstarszym studentem
(ew. studentami) studiuj¹cym na kierunku informatyka.

*/

SELECT * FROM studenci WHERE kierunek LIKE 'INFORMATYKA' AND data_urodzenia=
(SELECT MIN(data_urodzenia) FROM studenci WHERE kierunek LIKE 'INFORMATYKA');

/* Zadanie 2

W oparciu o dane zwarte w tabelach Rejestry, Wedkarze, Gatunki i Lowiska wyœwietl informacje
dotycz¹ce po³owu najd³u¿szej ryby danego gatunku w danym roku. W prezentowanej informacji
nale¿y uwzglêdniæ datê po³owu, nazwisko ³owcy (wêdkarza) i miejsce po³owu (nazwa ³owiska).
Dane uporz¹dkuj wed³ug roku a w dalszej kolejnoœci wed³ug nazwy gatunku.

*/
SELECT
    EXTRACT(YEAR FROM czas) AS rok,
    gatunki.nazwa Gatunki,
    NVL(dlugosc,0),
    trunc(czas) AS Kiedy,
    wedkarze.nazwisko,
    lowiska.nazwa Lowisko
FROM
    rejestry
    JOIN wedkarze USING(id_wedkarza)
    JOIN lowiska USING(id_lowiska)
    LEFT JOIN gatunki USING(id_gatunku)
WHERE (id_wedkarza, EXTRACT(YEAR FROM czas), NVL(dlugosc,0))
IN (SELECT id_wedkarza, EXTRACT(YEAR FROM czas) AS rok, NVL(MAX(dlugosc),0) max_dlugosc FROM rejestry  GROUP BY id_wedkarza, EXTRACT(YEAR FROM czas))
ORDER BY 1,2;


/* Zadanie 5

W oparciu o dane zgromadzone w tabeli Studenci wyœwietl najlepsze studentki (panie) i najlepszych
studentów (panów) studiuj¹cych na danym kierunku studiów (o ile to mo¿liwe to przynajmniej po 1-ym
studencie i 1-ej studentce na ka¿dym kierunku). Dane wyœwietl uporz¹dkowane alfabetycznie wg nazwy
kierunku, œredniej (nierosn¹co) i trybu studiów.

*/

SELECT
    kierunek,
    MAX(srednia),
    nazwisko,
    imiona,
    nr_indeksu,
    stopien,
    rok,
    tryb
FROM
    (SELECT
        kierunek,
        NVL(srednia, 0) AS srednia,
        nazwisko,
        imiona,
        nr_indeksu,
        stopien,
        rok,
        tryb
    FROM studenci)
GROUP BY kierunek, nazwisko, imiona, nr_indeksu, stopien, rok, tryb
ORDER BY 1, 2 DESC, 8;


/* Zadanie 8
W oparciu o dane zgromadzone w tabelach Studenci i Oceny wyœwietl dane studenta/ów, który uzyska³
najwiêksz¹ liczbê ocen 5.0 (na przestrzeni cyklu dydaktycznego).
*/

SELECT t1.NR_INDEKSU, t1.NAZWISKO, t1.IMIONA, t1.KIERUNEK, t1.ROK, t2.max_ocen AS "Liczba ocen 5"  FROM
(SELECT * FROM studenci) t1
JOIN
(SELECT nr_indeksu, COUNT(ocena) max_ocen FROM oceny WHERE ocena LIKE '5' GROUP BY nr_indeksu)t2
ON(t1.NR_INDEKSU=t2.NR_INDEKSU) ORDER BY 6 DESC;


/* Zadanie 9

Rozszerzenie zadania 8. W oparciu o dane zgromadzone w tabelach Studenci i Oceny wyœwietl dane
studentów, którzy uzyskali najwiêksz¹ liczbê poszczególnych ocen, z uwzglêdnieniem pozycji Brak oceny.
Dane wyœwietl w sposób uporz¹dkowany w trybie nierosn¹cym po kolumnie liczba ocen

*/

SELECT t1.nr_indeksu, t1.nazwisko, t1.imiona, t1.kierunek, t1.rok, t2.ocena, MAX(t2.ocena_max) AS LICZBA_OCEN FROM
(SELECT nr_indeksu, nazwisko, imiona, kierunek, rok,  max_ocen FROM
(SELECT nr_indeksu, nazwisko, imiona, kierunek, rok, Decode(ocena, NULL, 'Brak ocen', ocena) AS ocena, COUNT(*) AS max_ocen FROM oceny JOIN studenci USING(nr_indeksu) GROUP BY nr_indeksu, nazwisko, imiona, kierunek, rok, ocena
) GROUP BY nr_indeksu, nazwisko, imiona, kierunek, rok, max_ocen) t1
JOIN
(SELECT ocena, MAX(max_ocen) AS ocena_max FROM
(SELECT nr_indeksu, Decode(ocena, NULL, 'Brak ocen', ocena) AS ocena, COUNT(*) AS max_ocen FROM oceny GROUP BY nr_indeksu, ocena
)GROUP BY ocena) t2
ON (t1.max_ocen = t2.ocena_max) GROUP BY t1.nr_indeksu, t1.nazwisko, t1.imiona, t1.kierunek, t1.rok, t2.ocena ORDER BY 7 DESC;

/* Zadanie 13

W oparciu o dane zgromadzone w tabeli Pojazdy wyœwietl uporz¹kowan¹ listê (wg kolumny liczebnosc
w trybie nierosn¹cym) zawieraj¹c¹ informacjê o najbardziej popularnych modelach (najliczniejszych)
pojazdów w ramach poszczególnych typów pojazdów. Zaproponuj rozwi¹zanie niezawieraj¹ce
klauzuli Having

*/

    SELECT TYP, MARKA, MODELL, COUNT(TYP) AS Liczbnosc
      FROM POJAZDY
      GROUP BY TYP, MARKA, MODELL
      ORDER BY Liczbnosc DESC;

/* Zadanie 15

W oparciu o dane zgromadzone w tabelach Rejestry, Gatunki, Lowiska i Wedkarze wyœwietl zdarzenia
po³owów ryb, w ramach których z³owiona ryba by³a d³u¿sza od œredniej d³ugoœci ryb tego gatunku w danym
roku po³owu (np. d³ugoœæ danego sandacza z³owionego w 2018 roku nale¿y porównaæ ze œredni¹ obliczon¹
z d³ugoœci wszystkich sandaczy z³owionych w 2018 r.). Dane wyœwietl uporz¹dkowane kolejno
wg id_gatunku, roku po³owu (chronologicznie) i d³ugoœci ryb (nierosn¹co; patrz Rys. 15).

*/

SELECT
    LR.KIEDY,
    LR.ID_WEDKARZA,
    LR.NAZWISKO,
    GG.NAZWA,
    LR.LOWISKO,
    LR.DLUGOSC,
    LR.SREDNIA
FROM
    (SELECT
        ID_GATUNKU,
        NAZWA
    FROM gatunki) GG
JOIN
    (SELECT
        t2.kiedy AS KIEDY,
        t1.id_wedkarza AS ID_WEDKARZA,
        t1.nazwisko AS NAZWISKO,
        t2.ID_GATUNKU AS GATUNEK,
        t2.NAZWA AS LOWISKO,
        t1.dlugosc AS DLUGOSC,
        t2.srednia AS SREDNIA
    FROM
        (SELECT
            ID_WEDKARZA,
            nazwisko,
            ID_GATUNKU,
            DLUGOSC
        FROM rejestry
        JOIN wedkarze
        USING(id_wedkarza)
    ) t1
    JOIN
    (SELECT
        ID_WEDKARZA,
        EXTRACT(YEAR FROM CZAS) AS kiedy,
        ID_GATUNKU,
        ID_LOWISKA,
        NAZWA,
        round(avg(DLUGOSC), 2) AS srednia
    FROM REJESTRY
        JOIN LOWISKA
        USING(ID_LOWISKA)
        GROUP BY ID_WEDKARZA, EXTRACT(YEAR FROM CZAS), ID_GATUNKU, ID_LOWISKA, NAZWA
    ) t2
    ON (t1.ID_GATUNKU = t2.ID_GATUNKU)
    WHERE t1.dlugosc > t2.srednia ORDER BY 4)LR
ON GG.ID_GATUNKU = LR.GATUNEK ORDER BY 1, 6 DESC;


/* Zadanie 16

W oparciu o dane zgromadzone w tabelach Rejestry i Lowiska wyœwietl listê najlepszych ³owisk w ramach
poszczególnych okrêgów PZW pod wzglêdem ³¹cznej wagi wszystkich z³owionych na nich ryb.
Dane wyœwietl uporz¹dkowane w trybie nierosn¹cym wg ³¹cznej wagi z³owionych ryb (patrz Rys. 16).
Zaproponuj rozwi¹zanie niezawieraj¹ce klauzuli Having

*/

SELECT ROOT.ID_OKREGU,
       ROOT.ID_LOWISKA,
       ROOT.NAZWA,
       POL.polowy,
       RYB.ryb
  FROM (
    SELECT R.ID_LOWISKA,
           L.ID_OKREGU,
           L.NAZWA
      FROM REJESTRY R
        INNER JOIN LOWISKA L ON R.ID_LOWISKA = L.ID_LOWISKA
      GROUP BY R.ID_LOWISKA, L.ID_OKREGU, L.NAZWA
  ) ROOT
    JOIN (
    SELECT ID_LOWISKA,
           COUNT(ID_LOWISKA) AS polowy
      FROM REJESTRY
      GROUP BY ID_LOWISKA
  ) POL ON ROOT.ID_LOWISKA = POL.ID_LOWISKA
    JOIN (
    SELECT ID_LOWISKA,
           COUNT(ID_LOWISKA) AS ryb
      FROM REJESTRY
      WHERE ID_GATUNKU IS NOT NULL
      GROUP BY ID_LOWISKA
  ) RYB ON ROOT.ID_LOWISKA = RYB.ID_LOWISKA;

/* Zadanie 17

W oparciu o dane zgromadzone w tabeli Studenci utwórz zestawienie zawieraj¹ce informacjê ilu studentów
studiuje w ramach danego (patrz Rys. 17):
• trybu, stopnia, kierunku, roku,
• trybu, stopnia, kierunku
• trybu, stopnia,
• trybu

*/

SELECT *
  FROM (
    SELECT
        TRYB,
        STOPIEN,
        KIERUNEK,
        ROK,
        COUNT(ROK) AS liczba_studentow
      FROM STUDENCI
      GROUP BY TRYB, STOPIEN, KIERUNEK, ROK
  )
UNION
(
  SELECT
    TRYB,
    STOPIEN,
    KIERUNEK,
    NULL AS ROK,
    COUNT(KIERUNEK) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB, STOPIEN, KIERUNEK
)
UNION
(
  SELECT
    TRYB,
    STOPIEN,
    NULL AS KIERUNEK,
    NULL AS ROK,
    COUNT(STOPIEN) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB,
             STOPIEN
)
UNION
(
  SELECT
        TRYB,
        NULL AS STOPEN,
        NULL AS KIERUNEK,
        NULL AS ROK,
        COUNT(TRYB) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB
);

/* Zadanie 18

Sk³adniê zapytania z zadania nr 17 rozszerz o dodatkow¹ kolumnê o nazwie komentarz, w ramach której
pojawi¹ siê nastêpuj¹ce informacje (patrz Rys. 18):
• Na danym roku w TSK – w przypadku informacji dotycz¹cej liczby studentów studiuj¹cych
na danym roku w ramach danego trybu, stopnia i kierunku studiów,
• Na danym kierunku w TS – w przypadku informacji dotycz¹cej liczby studentów studiuj¹cych
na danym kierunku w ramach danego tryby i stopnia studiów,
• W ramach danego stopnia w T – w przypadku informacji dotycz¹cej liczby studentów studiuj¹cych
na danym stopniu w ramach danego trybu studiów,
• W danym trybie – w przypadku informacji dotycz¹cej liczby studentów studiuj¹cych w ramach
danego trybu studiów,
• Ogolem studiuje – w przypadku informacji dotycz¹cej ogólnej liczby studentów.

*/

SELECT *
  FROM (
    SELECT
        'Na danym roku w TSK' AS KOMENTARZ,
        TRYB,
        STOPIEN,
        KIERUNEK,
        ROK,
        COUNT(ROK)AS liczba_studentow
      FROM STUDENCI
      GROUP BY TRYB, STOPIEN, KIERUNEK, ROK
  )
UNION
(
  SELECT
     'Na danym kierunku w TS' AS KOMENTARZ,
     TRYB,
     STOPIEN,
     KIERUNEK,
     NULL AS ROK,
     COUNT(KIERUNEK) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB, STOPIEN, KIERUNEK
)
UNION
(
  SELECT
    'W ramach danego stopnia w T' AS KOMENTARZ,
     TRYB,
     STOPIEN,
     NULL AS KIERUNEK,
     NULL AS ROK,
     COUNT(STOPIEN) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB,
             STOPIEN
)
UNION
(
  SELECT
    'W danym trybie' AS KOMENTARZ,
    TRYB,
    NULL AS STOPEN,
    NULL AS KIERUNEK,
    NULL AS ROK,
    COUNT(TRYB) AS liczba_studentow
    FROM STUDENCI
    GROUP BY TRYB
)
UNION
(
  SELECT
    'Ogolem studiuje' AS KOMENTARZ,
     NULL AS TRYB,
     NULL AS STOPEN,
     NULL AS KIERUNEK,
     NULL AS ROK,
     COUNT(*) AS liczba_studentow
    FROM STUDENCI
);