CREATE OR REPLACE PROCEDURE CW_01
BEGIN
/* Ptaszek Hubert Pyk Przemysław 212A zespół ID56*/
/* 2.2 c Podać do jakie grupy należy każdy student, dane posortować według nazwy grupy. (NR_ALBUMU, NAZWISKO, NR_GR,NAZWA)

SELECT S.NR_ALBUMU, S.NAZWISKO, S.NR_GR, G.NAZWA FROM STUDENT S
JOIN GRUPA G ON S.NR_GR = G.NR_GR
ORDER BY G.NAZWA;

NR_ALBUMU NAZWISKO  NR_GR NAZWA
        1 Angielska     1 Grupa 01
        2 Borek         1 Grupa 01
        3 Cukierek      1 Grupa 01
        4 Drozd         1 Grupa 01
        5 Eklerek       1 Grupa 01
        6 Fokus         1 Grupa 01
        7 Gubala        1 Grupa 01
        8 Halski        1 Grupa 01
        9 Inka          1 Grupa 01
       10 Jawor         1 Grupa 01
       11 Amur          2 Grupa 02
       12 Burak         2 Grupa 02
       13 Czosnek       2 Grupa 02
       14 Alaska        3 Grupa 03
       15 Boston        3 Grupa 03
*/

/* 2.2 f Podać na jakie zajęcia chodzą poszczególne grupy (dane posortować według nazwy grupy, nazwy przedmiotu). (NR_GR,NAZWA, ID_PRZED,NAZWA)

SELECT GZ.NR_GR, G.NAZWA, GZ.ID_PRZED, P.NAZWA FROM GR_ZAJ GZ
JOIN GRUPA G
ON G.NR_GR = GZ.NR_GR
JOIN PRZEDMIOT P
ON P.ID_PRZED = GZ.ID_PRZED
ORDER BY G.NAZWA, P.NAZWA;

NR_GR NAZWA    ID_PRZED NAZWA
    1 Grupa 01        8 BIOL-L
    1 Grupa 01        1 FIZYKA
    1 Grupa 01        1 FIZYKA
    1 Grupa 01        3 FIZYKA-C
    1 Grupa 01        2 FIZYKA-L
    2 Grupa 02        8 BIOL-L
    2 Grupa 02        7 BIOLOGIA
    2 Grupa 02        6 FIZYKA
    2 Grupa 02        4 POLSKI
    2 Grupa 02        5 POLSKI
    3 Grupa 03        9 CHEMIA
    3 Grupa 03        2 FIZYKA-L
    3 Grupa 03        5 POLSKI

*/

/* 2.2 i Podać jakich przedmiotów nikt nie prowadzi. (ID_PRZED, NAZWA)

SELECT P.ID_PRZED, P.NAZWA FROM PRZEDMIOT P
WHERE NOT EXISTS (
  SELECT PZ.ID_PRZED FROM PRO_ZAJ PZ
  WHERE P.ID_PRZED = PZ.ID_PRZED
  );

  ID_PRZED NAZWA
      10 INFORMATYKA
*/

/* 2.2 l Podać jaki nauczyciel nie uczy żadnego przedmiotu. (NR_WYKL, NAZWISKO)

SELECT W.NR_WYKL, W.NAZWISKO FROM WYKLADOWCA W
WHERE NOT EXISTS (
  SELECT PZ.NR_WYKL FROM PRO_ZAJ PZ
  WHERE W.NR_WYKL = PZ.NR_WYKL
);

NR_WYKL NAZWISKO
      6 Fwyk

*/

/* 2.2 o Podać listę studentów którzy mają zajęcia w sali 405.(NR_ALBUMU, NAZWISKO, IMIE)

SELECT DISTINCT S.NR_ALBUMU, S.NAZWISKO, S.IMIE
FROM STUDENT S
JOIN GRUPA G
ON G.NR_GR = S.NR_GR
JOIN GR_ZAJ GZ
ON GZ.NR_GR = G.NR_GR
WHERE SALA = '405';


 SELECT DISTINCT S.NR_ALBUMU, S.NAZWISKO, S.IMIE FROM STUDENT S
WHERE EXISTS (
    SELECT GZ.NR_GR FROM GR_ZAJ GZ
    WHERE S.NR_GR = GZ.NR_GR AND GZ.SALA LIKE '405'
);

NR_ALBUMU NAZWISKO  IMIE
        1 Angielska Arek
        2 Borek     Bartek
        3 Cukierek  Czesiek
        4 Drozd     Darek
        5 Eklerek   Edek
        6 Fokus     Fabian
        7 Gubala    Gabi
        8 Halski    Hanna
        9 Inka      Iza
       10 Jawor     Jarek
       11 Amur      Anna
       12 Burak     Bella
       13 Czosnek   Czarek
       14 Alaska    Alicja
       15 Boston    Bill

*/

/* 2.2 r Podać na jakie zajęcia chodzą studenci o imieniu Astor. (ID_PRZED, NAZWA)

SELECT P.ID_PRZED, P.NAZWA
FROM PRZEDMIOT P
JOIN GR_ZAJ GZ
ON GZ.ID_PRZED = P.ID_PRZED
JOIN GRUPA G
ON G.NR_GR = GZ.NR_GR
JOIN STUDENT S
ON S.NR_GR = G.NR_GR
WHERE S.IMIE = 'Astor';

SELECT P.ID_PRZED, P.NAZWA FROM PRZEDMIOT P
WHERE EXISTS (
  SELECT GZ.NR_GR FROM GR_ZAJ GZ
  JOIN STUDENT S ON GZ.NR_GR = S.NR_GR
  WHERE P.ID_PRZED = GZ.ID_PRZED AND S.IMIE LIKE 'Astor'
);

Brak wyników!

*/

/* 2.3 b Podać nazwy grup dla których grupa.liczba_st <> zliczona ilość wierszy. (NR_GR,NAZWA,.LICZBA_ST, ZLICZONA ILOŚĆ WIERSZY)

SELECT G.NR_GR, G.NAZWA, G.LICZBA_ST, Count(S.NR_GR) AS "ZLICZONA ILOSC WIERSZY" FROM GRUPA G
JOIN STUDENT S ON G.NR_GR = S.NR_GR
GROUP BY G.NR_GR, G.NAZWA, G.LICZBA_ST
HAVING Count(S.NR_GR) <> G.LICZBA_ST;

SELECT G.NR_GR, G.NAZWA, G.LICZBA_ST, Count(S.NR_GR) AS "ZLICZONA ILOSC WIERSZY" FROM GRUPA G, STUDENT S
WHERE G.NR_GR = S.NR_GR
GROUP BY G.NR_GR, G.NAZWA, G.LICZBA_ST
HAVING Count(S.NR_GR) <> G.LICZBA_ST;

SELECT G.NR_GR, G.NAZWA, G.LICZBA_ST, "ZLICZONA ILOSC WIERSZY" FROM GRUPA G
INNER JOIN (
  SELECT S.NR_GR, Count(S.NR_GR) AS "ZLICZONA ILOSC WIERSZY" FROM STUDENT S
  GROUP BY S.NR_GR
) R ON G.NR_GR = R.NR_GR AND G.LICZBA_ST <> "ZLICZONA ILOSC WIERSZY";

NR_GR NAZWA    LICZBA_ST ZLICZONA ILOSC WIERSZY
    3 Grupa 03         8                      2
*/

/* 2.3 e Podać ilu studentów uczęszcza na każdy przedmiot. (ID_PRZED, NAZWA, LICZBA STUDENTÓW)

SELECT R.ID_PRZED, R.NAZWA, "LICZBA STUDENTOW" FROM (
  SELECT P.ID_PRZED, P.NAZWA, Count(S.NR_ALBUMU) AS "LICZBA STUDENTOW" FROM STUDENT S, PRZEDMIOT P, GR_ZAJ GZ
  WHERE GZ.ID_PRZED (+)= P.ID_PRZED AND S.NR_GR (+)= GZ.NR_GR
  GROUP BY P.ID_PRZED, P.NAZWA
)R;

ID_PRZED NAZWA       LICZBA STUDENTOW
       1 FIZYKA                    20
       2 FIZYKA-L                  12
       3 FIZYKA-C                  10
       4 POLSKI                     3
       5 POLSKI                     5
       6 FIZYKA                     3
       7 BIOLOGIA                   3
       8 BIOL-L                    13
       9 CHEMIA                     2
      10 INFORMATYKA                0
*/

/* 2.3 h Podać średnią ocenę każdego studenta z każdego przedmiotu.(NR_ALBUMU, NAZWISKO, ID_PRZED, NAZWA,ŚREDNIA )

SELECT S.NR_ALBUMU, S.NAZWISKO, P.ID_PRZED, P.NAZWA, Nvl(Round(Avg(Z.OCENA), 2), 0) AS "SREDNIA" FROM STUDENT S, PRZEDMIOT P, ZALICZENIE Z
WHERE Z.NR_ALBUMU (+)= S.NR_ALBUMU AND P.ID_PRZED (+)= Z.ID_PRZED
GROUP BY S.NR_ALBUMU, S.NAZWISKO, P.ID_PRZED, P.NAZWA;

NR_ALBUMU NAZWISKO  ID_PRZED NAZWA       SREDNIA
        1 Angielska        1 FIZYKA         2,33
        1 Angielska        2 FIZYKA-L       2,33
        1 Angielska        4 POLSKI            2
        1 Angielska        5 POLSKI            3
        1 Angielska        6 FIZYKA            2
        1 Angielska        7 BIOLOGIA          2
        1 Angielska        8 BIOL-L            3
        2 Borek            1 FIZYKA         2,33
        2 Borek            2 FIZYKA-L       2,33
        2 Borek            4 POLSKI            2
        2 Borek            5 POLSKI            3
        2 Borek            6 FIZYKA            2
        2 Borek            7 BIOLOGIA          2
        2 Borek            8 BIOL-L            3
        3 Cukierek         2 FIZYKA-L       2,33
        3 Cukierek         3 FIZYKA-C          2
        3 Cukierek         4 POLSKI          2,5
        3 Cukierek         7 BIOLOGIA       2,33
        3 Cukierek         9 CHEMIA         2,33
        4 Drozd                                0
        5 Eklerek          1 FIZYKA            5
        5 Eklerek          2 FIZYKA-L          5
        5 Eklerek          3 FIZYKA-C          5
        5 Eklerek          4 POLSKI            5
        5 Eklerek          5 POLSKI            5
        5 Eklerek          6 FIZYKA            5
        5 Eklerek          7 BIOLOGIA          5
        5 Eklerek          8 BIOL-L            5
        5 Eklerek          9 CHEMIA            5
        5 Eklerek         10 INFORMATYKA       5
        6 Fokus            1 FIZYKA            5
        6 Fokus            2 FIZYKA-L          5
        6 Fokus            3 FIZYKA-C          5
        6 Fokus            4 POLSKI            4
        6 Fokus            5 POLSKI            5
        6 Fokus            6 FIZYKA            4
        6 Fokus            7 BIOLOGIA          5
        6 Fokus            8 BIOL-L            4
        6 Fokus            9 CHEMIA            5
        6 Fokus           10 INFORMATYKA       4
        7 Gubala           1 FIZYKA            5
        7 Gubala           2 FIZYKA-L          5
        7 Gubala           3 FIZYKA-C          5
        7 Gubala           4 POLSKI            4
        7 Gubala           5 POLSKI            5
        7 Gubala           6 FIZYKA            4
        7 Gubala           7 BIOLOGIA          5
        7 Gubala           8 BIOL-L            4
        7 Gubala           9 CHEMIA            5
        7 Gubala          10 INFORMATYKA       4
        8 Halski                               0
        9 Inka                                 0
       10 Jawor                                0
       11 Amur                                 0
       12 Burak                                0
       13 Czosnek                              0
       14 Alaska                               0
       15 Boston                               0

 */
END;