--2.2 Wyszukiwanie informacji

--a) Podać listę przedmiotów.
SELECT id_przed, Nazwa FROM PRZEDMIOT;

--b) Wyświetlić listę studentów z grupy o nazwie „Grupa 2”.
SELECT nr_albumu, nazwisko FROM STUDENT,GRUPA
WHERE grupa.nr_gr = student.nr_gr
AND grupa.nazwa LIKE 'Grupa 2';

SELECT nr_albumu,nazwisko FROM STUDENT
INNER JOIN GRUPA ON grupa.nr_gr = student.nr_gr
AND grupa.nazwa LIKE 'Grupa 2';

--c) Podać do jakie grupy należy każdy student, dane posortować według nazwy grupy.
SELECT student.nr_albumu,student.nazwisko,student.nr_gr,grupa.nazwa FROM STUDENT,GRUPA
WHERE grupa.nr_gr = student.nr_gr
ORDER BY grupa.nazwa;

SELECT student.nr_albumu,student.nazwisko,student.nr_gr,grupa.nazwa FROM STUDENT
INNER JOIN GRUPA ON grupa.nr_gr = student.nr_gr
ORDER BY grupa.nazwa;

--d) Podać jaki nauczyciel uczy jakiego przedmiotu.
SELECT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa
FROM WYKLADOWCA,PRO_ZAJ,PRZEDMIOT
WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl
AND pro_zaj.id_przed = przedmiot.id_przed;

SELECT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa
FROM WYKLADOWCA
INNER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
INNER JOIN PRZEDMIOT ON pro_zaj.id_przed = przedmiot.id_przed;

--e) Podać jakie przedmioty prowadzi każdy nauczyciel.
SELECT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa
FROM WYKLADOWCA,PRO_ZAJ,PRZEDMIOT
WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl(+) AND pro_zaj.id_przed = przedmiot.id_przed(+);

SELECT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa
FROM WYKLADOWCA
LEFT OUTER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
LEFT OUTER JOIN PRZEDMIOT ON pro_zaj.id_przed = przedmiot.id_przed;

--f) Podać na jakie zajęcia chodzą poszczególne grupy (dane posortować według nazwy grupy, nazwy przedmiotu).
SELECT grupa.nr_gr,grupa.nazwa,przedmiot.id_przed,przedmiot.nazwa
FROM GRUPA,PRZEDMIOT,GR_ZAJ
WHERE grupa.nr_gr = gr_zaj.nr_gr
AND przedmiot.id_przed = gr_zaj.id_przed
ORDER BY grupa.nazwa,przedmiot.nazwa;

SELECT grupa.nr_gr,grupa.nazwa,przedmiot.id_przed,przedmiot.nazwa
FROM GRUPA
INNER JOIN GR_ZAJ ON grupa.nr_gr = gr_zaj.nr_gr
INNER JOIN PRZEDMIOT ON przedmiot.id_przed = gr_zaj.id_przed
ORDER BY grupa.nazwa,przedmiot.nazwa;

--g) Podać jakich przedmiotów uczą wykładowcy ze stopniem doktora.
SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa
FROM PRZEDMIOT,WYKLADOWCA,PRO_ZAJ
WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl
AND przedmiot.id_przed = pro_zaj.id_przed
AND wykladowca.stopien = 'dr';

SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa
FROM PRZEDMIOT
INNER JOIN PRO_ZAJ ON przedmiot.id_przed = pro_zaj.id_przed
INNER JOIN WYKLADOWCA ON wykladowca.nr_wykl = pro_zaj.nr_wykl
AND wykladowca.stopien = 'dr';

--h) Dla każdego przedmiotu podać przez jakiego nauczyciela są wykładane.
SELECT przedmiot.id_przed,przedmiot.nazwa,wykladowca.nr_wykl,wykladowca.nazwisko
FROM PRZEDMIOT,PRO_ZAJ,WYKLADOWCA
WHERE pro_zaj.id_przed(+) = przedmiot.id_przed AND wykladowca.nr_wykl(+) = pro_zaj.nr_wykl;

SELECT przedmiot.id_przed,przedmiot.nazwa,wykladowca.nr_wykl,wykladowca.nazwisko
FROM PRZEDMIOT
LEFT OUTER JOIN PRO_ZAJ ON przedmiot.id_przed = pro_zaj.id_przed
LEFT OUTER JOIN WYKLADOWCA ON wykladowca.nr_wykl = pro_zaj.nr_wykl;

--i) Podać jakich przedmiotów nikt nie prowadzi.
SELECT przedmiot.id_przed,przedmiot.nazwa
FROM PRZEDMIOT WHERE NOT EXISTS
(SELECT pro_zaj.id_przed
FROM PRO_ZAJ WHERE przedmiot.id_przed = pro_zaj.id_przed);

--j) Podać na jakie zajęcia prowadzone przez wykładowców nie chodzi żadna grupa.
--UPDATE GR_ZAJ SET nr_gr = NULL WHERE id_przed = 9;
--UPDATE GR_ZAJ SET nr_gr = NULL WHERE id_przed = 8;

SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa
FROM PRZEDMIOT,GR_ZAJ
WHERE EXISTS (SELECT DISTINCT gr_zaj.id_przed, przedmiot.nazwa
FROM GR_ZAJ WHERE przedmiot.id_przed = gr_zaj.id_przed AND gr_zaj.nr_gr IS NULL)
AND EXISTS (SELECT DISTINCT gr_zaj.nr_wykl FROM WYKLADOWCA,GR_ZAJ WHERE wykladowca.nr_wykl = gr_zaj.nr_wykl);

--k) Podać na jakie przedmioty nie chodzi żaden student.
--UPDATE GR_ZAJ SET nr_gr = NULL WHERE id_przed = 9;
--UPDATE GR_ZAJ SET nr_gr = NULL WHERE id_przed = 8;

SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa
FROM PRZEDMIOT,GR_ZAJ,STUDENT
WHERE NOT EXISTS (SELECT przedmiot.id_przed, przedmiot.nazwa FROM GR_ZAJ,STUDENT
WHERE przedmiot.id_przed = gr_zaj.id_przed AND gr_zaj.nr_gr IS NOT NULL AND student.nr_gr = gr_zaj.nr_gr);

--l) Podać jaki nauczyciel nie uczy żadnego przedmiotu.
SELECT wykladowca.nr_wykl, wykladowca.nazwisko
FROM WYKLADOWCA WHERE NOT EXISTS
(SELECT PRO_ZAJ.NR_WYKL FROM PRO_ZAJ
WHERE WYKLADOWCA.NR_WYKL = PRO_ZAJ.NR_WYKL);

--m) Podać na jakie zajęcia chodzi każda grupa.

--INSERT INTO GR_ZAJ VALUES(1,2,2,'400');
--INSERT INTO GR_ZAJ VALUES(1,2,4,'400');
--DELETE GR_ZAJ WHERE Sala = '400';

SELECT przedmiot.id_przed, przedmiot.nazwa FROM PRZEDMIOT,GR_ZAJ,GRUPA
WHERE przedmiot.id_przed = gr_zaj.id_przed AND grupa.nr_gr = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed, przedmiot.nazwa HAVING COUNT(grupa.nr_gr)=(SELECT COUNT(grupa.nr_gr) FROM GRUPA);

SELECT przedmiot.id_przed, przedmiot.nazwa FROM PRZEDMIOT
INNER JOIN gr_zaj ON przedmiot.id_przed = gr_zaj.id_przed
INNER JOIN grupa ON grupa.nr_gr = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed, przedmiot.nazwa HAVING COUNT(grupa.nr_gr)=(SELECT COUNT(grupa.nr_gr) FROM GRUPA);

--n) Podać nazwy przedmiotów które odbywają się w sali 405.
SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa FROM PRZEDMIOT
WHERE EXISTS(SELECT gr_zaj.id_przed FROM GR_ZAJ
WHERE przedmiot.id_przed = gr_zaj.id_przed AND gr_zaj.sala = '405');

SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa FROM PRZEDMIOT
INNER JOIN GR_ZAJ ON przedmiot.id_przed = gr_zaj.id_przed
AND gr_zaj.sala = '405';

--o) Podać listę studentów którzy mają zajęcia w sali 405.
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT
WHERE EXISTS(SELECT gr_zaj.nr_gr FROM GR_ZAJ
WHERE student.nr_gr = gr_zaj.nr_gr AND gr_zaj.sala = '405');

SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT
INNER JOIN GR_ZAJ ON student.nr_gr = gr_zaj.nr_gr AND gr_zaj.sala = '405';

--p) Podać listę studentów którzy nie mają zajęć w sali 405.
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT
WHERE NOT EXISTS(SELECT gr_zaj.nr_gr FROM GR_ZAJ
WHERE student.nr_gr = gr_zaj.nr_gr AND gr_zaj.sala = '405');

--q) Podać listę studentów którzy chodzą na przedmiot o nazwie FIZYKA.
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT,GR_ZAJ,PRZEDMIOT
WHERE student.nr_gr = gr_zaj.nr_gr AND przedmiot.id_przed = gr_zaj.id_przed AND przedmiot.nazwa = 'FIZYKA';

SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT
INNER JOIN GR_ZAJ ON student.nr_gr = gr_zaj.nr_gr
INNER JOIN PRZEDMIOT ON przedmiot.id_przed = gr_zaj.id_przed
AND przedmiot.nazwa = 'FIZYKA';

--r) Podać na jakie zajęcia chodzą studenci o imieniu Astor.
SELECT DISTINCT przedmiot.id_przed,przedmiot.nazwa FROM PRZEDMIOT,GR_ZAJ,STUDENT
WHERE student.nr_gr = gr_zaj.nr_gr AND przedmiot.id_przed = gr_zaj.id_przed
AND student.imie = 'Astor';

SELECT DISTINCT przedmiot.id_przed,przedmiot.nazwa FROM PRZEDMIOT
INNER JOIN GR_ZAJ ON przedmiot.id_przed = gr_zaj.id_przed
INNER JOIN STUDENT ON student.nr_gr = gr_zaj.nr_gr AND student.imie = 'Astor';

--s) Podać nazwiska studentów których uczy mgr Bwyk.
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT,GR_ZAJ,PRO_ZAJ,WYKLADOWCA
WHERE pro_zaj.nr_wykl = gr_zaj.nr_wykl AND student.nr_gr = gr_zaj.nr_gr
AND wykladowca.nr_wykl = pro_zaj.nr_wykl AND wykladowca.nazwisko = 'Bwyk';

SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM STUDENT
INNER JOIN GR_ZAJ ON student.nr_gr = gr_zaj.nr_gr
INNER JOIN PRO_ZAJ ON pro_zaj.nr_wykl = gr_zaj.nr_wykl
INNER JOIN WYKLADOWCA ON wykladowca.nr_wykl = pro_zaj.nr_wykl
AND wykladowca.nazwisko = 'Bwyk';


--2.3 Agregacja danych

--a) Podać ile każdy wykładowca prowadzi przedmiotów.
SELECT wykladowca.nr_wykl,wykladowca.nazwisko,COUNT(pro_zaj.id_przed) AS ILOSC
FROM WYKLADOWCA,PRO_ZAJ
WHERE pro_zaj.nr_wykl(+) = wykladowca.nr_wykl
GROUP BY wykladowca.nr_wykl,wykladowca.nazwisko;

SELECT wykladowca.nr_wykl,wykladowca.nazwisko,COUNT(pro_zaj.id_przed) AS ILOSC
FROM WYKLADOWCA
LEFT OUTER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
GROUP BY wykladowca.nr_wykl,wykladowca.nazwisko;

--b) Podać nazwy grup dla których grupa.liczba_st <> zliczona ilość wierszy.
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(STUDENT.NR_GR) AS "ZLICZONA ILOSC WIERSZY"
FROM STUDENT,GRUPA
WHERE student.nr_gr = grupa.nr_gr
GROUP BY grupa.nr_gr, grupa.nazwa,grupa.liczba_st HAVING COUNT(STUDENT.NR_GR) <> GRUPA.LICZBA_ST;

SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, "ZLICZONA ILOSC WIERSZY"
FROM GRUPA
INNER JOIN (SELECT student.nr_gr, COUNT(student.nr_gr) AS "ZLICZONA ILOSC WIERSZY"
FROM STUDENT GROUP BY student.nr_gr)x ON x.nr_gr = grupa.nr_gr
AND GRUPA.LICZBA_ST <> "ZLICZONA ILOSC WIERSZY";

--c) Podać z iloma grupami każdy wykładowca ma zajęcia.
SELECT wykladowca.nr_wykl, wykladowca.nazwisko, COUNT(gr_zaj.nr_gr) AS "ILOSC GRUP"
FROM WYKLADOWCA,GR_ZAJ
WHERE gr_zaj.nr_wykl(+) = wykladowca.nr_wykl
GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko;

SELECT wykladowca.nr_wykl, wykladowca.nazwisko, "ILOSC GRUP"
FROM WYKLADOWCA
LEFT OUTER JOIN (SELECT gr_zaj.nr_wykl,COUNT(gr_zaj.nr_gr) AS "ILOSC GRUP"
FROM GR_ZAJ GROUP BY gr_zaj.nr_wykl)x ON x.nr_wykl = wykladowca.nr_wykl;

--d) Podać ile każdy student ma przedmiotów.
SELECT student.nr_albumu, student.nazwisko, COUNT(gr_zaj.id_przed) AS "ILOSC PRZEDMIOTOW"
FROM STUDENT,GR_ZAJ
WHERE gr_zaj.nr_gr(+) = student.nr_gr
GROUP BY student.nr_albumu, student.nazwisko ORDER BY student.nr_albumu;

SELECT student.nr_albumu, student.nazwisko, "ILOSC PRZEDMIOTOW"
FROM STUDENT
LEFT OUTER JOIN
(SELECT gr_zaj.nr_gr, COUNT(gr_zaj.id_przed) AS "ILOSC PRZEDMIOTOW"
FROM GR_ZAJ GROUP BY gr_zaj.nr_gr)x ON x.nr_gr = student.nr_gr;

--e) Podać ilu studentów uczęszcza na każdy przedmiot.
SELECT przedmiot.id_przed, przedmiot.nazwa, COUNT(student.nr_albumu) AS "ILOSC STUDENTOW"
FROM STUDENT,PRZEDMIOT,GR_ZAJ
WHERE gr_zaj.id_przed(+) = przedmiot.id_przed  AND student.nr_gr(+) = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed, przedmiot.nazwa;

SELECT DISTINCT przedmiot.id_przed, przedmiot.nazwa,COUNT(student.nr_albumu) AS "ILOSC STUDENTOW"
FROM PRZEDMIOT
LEFT OUTER JOIN GR_ZAJ ON gr_zaj.id_przed = przedmiot.id_przed
LEFT OUTER JOIN STUDENT ON student.nr_gr = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed, przedmiot.nazwa;

--f) Podać liczbą studentów w grupach.
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(STUDENT.NR_GR) AS "ZLICZONA ILOSC WIERSZY"
FROM STUDENT,GRUPA
WHERE student.nr_gr(+)=grupa.nr_gr
GROUP BY grupa.nr_gr, grupa.nazwa,grupa.liczba_st;

SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, "ZLICZONA ILOSC WIERSZY"
FROM GRUPA
LEFT OUTER JOIN (SELECT student.nr_gr, COUNT(student.nr_gr) AS "ZLICZONA ILOSC WIERSZY"
FROM STUDENT GROUP BY student.nr_gr)x ON x.nr_gr = grupa.nr_gr;

--g) Podać nazwy grup dla których grupa.liczba_st = zliczona ilość wierszy.
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(STUDENT.NR_GR) AS "ZLICZONA ILOSC WIERSZY"
FROM STUDENT,GRUPA
WHERE student.nr_gr(+) = grupa.nr_gr
GROUP BY grupa.nr_gr, grupa.nazwa,grupa.liczba_st HAVING COUNT(STUDENT.NR_GR) = GRUPA.LICZBA_ST;

SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, SUM(STUDENT.NR_GR) AS "ZLICZONA ILOSC WIERSZY"
FROM GRUPA
LEFT OUTER JOIN STUDENT ON student.nr_gr = grupa.nr_gr
GROUP BY grupa.nr_gr, grupa.nazwa,grupa.liczba_st HAVING SUM(STUDENT.NR_GR) = GRUPA.LICZBA_ST;

--h) Podać średnią ocenę każdego studenta z każdego przedmiotu.
SELECT student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa,
ROUND(AVG(zaliczenie.ocena),2) AS SREDNIA FROM STUDENT,PRZEDMIOT,ZALICZENIE
WHERE zaliczenie.nr_albumu(+)=student.nr_albumu AND przedmiot.id_przed(+) = zaliczenie.id_przed
GROUP BY student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa;

SELECT student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, SREDNIA
FROM STUDENT
LEFT OUTER JOIN (SELECT zaliczenie.nr_albumu,zaliczenie.id_przed,ROUND(AVG(zaliczenie.ocena),2) AS SREDNIA
FROM ZALICZENIE GROUP BY zaliczenie.nr_albumu,zaliczenie.id_przed)x ON x.nr_albumu = student.nr_albumu
LEFT OUTER JOIN PRZEDMIOT ON x.id_przed = przedmiot.id_przed;

--i) Podać nazwę przedmiotu którego najwięcej studentów nie zaliczyło w pierwszym terminie.
SELECT przedmiot.id_przed,przedmiot.nazwa FROM PRZEDMIOT
LEFT OUTER JOIN (SELECT zaliczenie.id_przed, COUNT(zaliczenie.nr_albumu) AS x FROM ZALICZENIE
WHERE EXISTS (SELECT z.termin FROM ZALICZENIE z WHERE z.termin > 1 AND z.id_przed = zaliczenie.id_przed)
AND zaliczenie.termin = (SELECT MAX(z.termin) FROM ZALICZENIE z WHERE z.id_przed = zaliczenie.id_przed)
GROUP BY zaliczenie.id_przed)y ON y.id_przed = przedmiot.id_przed
WHERE x = (SELECT MAX(x) FROM (SELECT zaliczenie.id_przed, COUNT(zaliczenie.nr_albumu) AS x FROM ZALICZENIE
WHERE EXISTS (SELECT z.termin FROM ZALICZENIE z WHERE z.termin > 1 AND z.id_przed = zaliczenie.id_przed)
AND zaliczenie.termin = (SELECT MAX(z.termin) FROM ZALICZENIE z WHERE z.id_przed = zaliczenie.id_przed)
GROUP BY zaliczenie.id_przed));

--j) Podać ostatnią ocenę każdego studenta z każdego przedmiotu (według termin).
SELECT DISTINCT zaliczenie.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena FROM ZALICZENIE,STUDENT,PRZEDMIOT
WHERE student.nr_albumu(+) = zaliczenie.nr_albumu AND przedmiot.id_przed(+) = zaliczenie.id_przed AND zaliczenie.termin = (SELECT MAX(z.termin) FROM ZALICZENIE z
WHERE z.id_przed = zaliczenie.id_przed AND z.nr_albumu = zaliczenie.nr_albumu);

SELECT DISTINCT zaliczenie.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena FROM ZALICZENIE
LEFT OUTER JOIN STUDENT ON student.nr_albumu = zaliczenie.nr_albumu
LEFT OUTER JOIN PRZEDMIOT ON przedmiot.id_przed = zaliczenie.id_przed
WHERE zaliczenie.termin = (SELECT MAX(z.termin) FROM ZALICZENIE z
WHERE z.id_przed = zaliczenie.id_przed AND z.nr_albumu = zaliczenie.nr_albumu);

--k) Podać w której sali odbywa się najwięcej zajęć.
SELECT sala FROM (SELECT gr_zaj.sala, COUNT(gr_zaj.id_przed) AS x
FROM GR_ZAJ GROUP BY gr_zaj.sala)
WHERE x IN (SELECT MAX(x) FROM (SELECT gr_zaj.sala, COUNT(gr_zaj.id_przed) AS x
FROM GR_ZAJ GROUP BY gr_zaj.sala));

--l) Podać ile każdy prowadzący z każdego przedmiotu postawił jakich ocen.
SELECT DISTINCT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa,zaliczenie.ocena,
COUNT(zaliczenie.ocena) AS ILOSC FROM WYKLADOWCA,PRZEDMIOT,ZALICZENIE,PRO_ZAJ
WHERE pro_zaj.nr_wykl(+) = wykladowca.nr_wykl AND przedmiot.id_przed(+) = pro_zaj.id_przed
AND zaliczenie.id_przed(+) = pro_zaj.id_przed  GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena;

SELECT DISTINCT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa,zaliczenie.ocena,
COUNT(zaliczenie.ocena) AS ILOSC FROM WYKLADOWCA
LEFT OUTER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
LEFT OUTER JOIN PRZEDMIOT ON przedmiot.id_przed = pro_zaj.id_przed
LEFT OUTER JOIN ZALICZENIE ON zaliczenie.id_przed = pro_zaj.id_przed
GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena;

--m) Podać ile każdy prowadzący z każdego przedmiotu w każdym terminie postawił jakich ocen.
SELECT DISTINCT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa,zaliczenie.ocena,zaliczenie.termin,
COUNT(zaliczenie.ocena) AS ILOSC FROM WYKLADOWCA,PRZEDMIOT,ZALICZENIE,PRO_ZAJ
WHERE pro_zaj.nr_wykl(+) = wykladowca.nr_wykl AND przedmiot.id_przed(+) = pro_zaj.id_przed AND zaliczenie.id_przed(+) = pro_zaj.id_przed
GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena,zaliczenie.termin;

SELECT DISTINCT wykladowca.nr_wykl,wykladowca.nazwisko,przedmiot.id_przed,przedmiot.nazwa,zaliczenie.ocena,zaliczenie.termin,
COUNT(zaliczenie.ocena) AS ILOSC FROM WYKLADOWCA
LEFT OUTER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
LEFT OUTER JOIN PRZEDMIOT ON przedmiot.id_przed = pro_zaj.id_przed
LEFT OUTER JOIN ZALICZENIE ON zaliczenie.id_przed = pro_zaj.id_przed
GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena,zaliczenie.termin;

--n) Podać dane studenta który ma najlepszą średnią ocen.
SELECT nr_albumu, nazwisko FROM (SELECT student.nr_albumu,student.nazwisko,ROUND(AVG(zaliczenie.ocena),2) AS x FROM STUDENT,ZALICZENIE
WHERE student.nr_albumu = zaliczenie.nr_albumu GROUP BY student.nr_albumu,student.nazwisko)
WHERE x IN (SELECT MAX(x) FROM (SELECT ROUND(AVG(zaliczenie.ocena),2) AS x FROM STUDENT,ZALICZENIE
WHERE student.nr_albumu = zaliczenie.nr_albumu GROUP BY student.nr_albumu));

SELECT nr_albumu, nazwisko FROM (SELECT student.nr_albumu,student.nazwisko,ROUND(AVG(zaliczenie.ocena),2) AS x FROM STUDENT
INNER JOIN ZALICZENIE ON student.nr_albumu = zaliczenie.nr_albumu GROUP BY student.nr_albumu,student.nazwisko)
WHERE x IN (SELECT MAX(x) FROM (SELECT ROUND(AVG(zaliczenie.ocena),2) AS x FROM STUDENT INNER JOIN ZALICZENIE
ON student.nr_albumu = zaliczenie.nr_albumu GROUP BY student.nr_albumu));

--o) Podać ilu studentów w każdym terminie zaliczyło każdy przedmiot.
SELECT przedmiot.id_przed,przedmiot.nazwa,zaliczenie.termin,COUNT(zaliczenie.nr_albumu) AS ILOSC
FROM PRZEDMIOT,ZALICZENIE,STUDENT
WHERE przedmiot.id_przed = zaliczenie.id_przed AND student.nr_albumu = zaliczenie.nr_albumu
GROUP BY przedmiot.id_przed,przedmiot.nazwa,zaliczenie.termin;

SELECT przedmiot.id_przed,przedmiot.nazwa,zaliczenie.termin,COUNT(zaliczenie.nr_albumu) AS ILOSC
FROM PRZEDMIOT
LEFT OUTER JOIN ZALICZENIE ON przedmiot.id_przed = zaliczenie.id_przed
LEFT OUTER JOIN STUDENT ON student.nr_albumu = zaliczenie.nr_albumu
GROUP BY przedmiot.id_przed,przedmiot.nazwa,zaliczenie.termin;

--p) Podać dla każdego typu przedmiotu ilu studentów uzyskało jaką ocenę.
SELECT przedmiot.typ, zaliczenie.ocena, COUNT(zaliczenie.nr_albumu) AS "LICZBA STUDENTOW"
FROM PRZEDMIOT,ZALICZENIE,STUDENT
WHERE przedmiot.id_przed = zaliczenie.id_przed AND student.nr_albumu = zaliczenie.nr_albumu
GROUP BY przedmiot.typ, zaliczenie.ocena;

SELECT przedmiot.typ, zaliczenie.ocena, COUNT(zaliczenie.nr_albumu) AS "LICZBA STUDENTOW"
FROM PRZEDMIOT
LEFT OUTER JOIN ZALICZENIE ON przedmiot.id_przed = zaliczenie.id_przed
LEFT OUTER JOIN STUDENT ON student.nr_albumu = zaliczenie.nr_albumu
GROUP BY przedmiot.typ, zaliczenie.ocena;

--q) Podać dzień w którym odbyło się najwięcej zaliczeń.
SELECT DATA FROM (SELECT TO_CHAR(zaliczenie.data,'dd.mm.yyyy') AS DATA,COUNT(zaliczenie.id_przed) AS x
FROM ZALICZENIE GROUP BY TO_CHAR(zaliczenie.data,'dd.mm.yyyy'))
WHERE x IN (SELECT MAX(x) FROM (SELECT TO_CHAR(zaliczenie.data,'dd.mm.yyyy') AS DATA,COUNT(zaliczenie.id_przed) AS x
FROM ZALICZENIE GROUP BY TO_CHAR(zaliczenie.data,'dd.mm.yyyy')));

--r) Podać dzień tygodnia w który odbyło się najwięcej zaliczeń (poniedziałek, wtorek itd.).
SELECT DZIEN_TYGODNIA FROM (SELECT TO_CHAR(zaliczenie.data,'day') AS DZIEN_TYGODNIA,COUNT(zaliczenie.id_przed) AS x
FROM ZALICZENIE GROUP BY TO_CHAR(zaliczenie.data,'day'))
WHERE x IN (SELECT MAX(x) FROM (SELECT TO_CHAR(zaliczenie.data,'day') AS y,COUNT(zaliczenie.id_przed) AS x
FROM ZALICZENIE GROUP BY TO_CHAR(zaliczenie.data,'day')));

--s) Podać dzień tygodnia, w którym było postawione najwięcej ocen niedostatecznych.
SELECT DZIEN_TYGODNIA FROM (SELECT TO_CHAR(zaliczenie.data,'day') AS DZIEN_TYGODNIA,COUNT(zaliczenie.ocena) AS x
FROM ZALICZENIE WHERE zaliczenie.ocena = 2 GROUP BY TO_CHAR(zaliczenie.data,'day'));

--t) Podać dane wykładowców prowadzących więcej niż dwa przedmioty.
SELECT wykladowca.nr_wykl,wykladowca.nazwisko,COUNT(pro_zaj.id_przed) AS "LICZBA PRZEDMIOTOW"
FROM WYKLADOWCA,PRO_ZAJ,PRZEDMIOT
WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl AND przedmiot.id_przed = pro_zaj.id_przed
GROUP BY wykladowca.nr_wykl,wykladowca.nazwisko HAVING COUNT(pro_zaj.id_przed)>2;

SELECT wykladowca.nr_wykl,wykladowca.nazwisko,COUNT(pro_zaj.id_przed) AS "LICZBA PRZEDMIOTOW"
FROM WYKLADOWCA
INNER JOIN PRO_ZAJ ON wykladowca.nr_wykl = pro_zaj.nr_wykl
INNER JOIN PRZEDMIOT ON przedmiot.id_przed = pro_zaj.id_przed
GROUP BY wykladowca.nr_wykl,wykladowca.nazwisko HAVING COUNT(pro_zaj.id_przed)>2;

--u) Podać dane o przedmiotach na które chodzi więcej niż 5 studentów.
SELECT przedmiot.id_przed,przedmiot.nazwa,COUNT(gr_zaj.nr_gr) AS "LICZBA STUDENTOW"
FROM PRZEDMIOT,GR_ZAJ,STUDENT
WHERE przedmiot.id_przed = gr_zaj.id_przed AND student.nr_gr = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed,przedmiot.nazwa HAVING COUNT(gr_zaj.nr_gr) > 5;

SELECT przedmiot.id_przed,przedmiot.nazwa,COUNT(gr_zaj.nr_gr) AS "LICZBA STUDENTOW"
FROM PRZEDMIOT
INNER JOIN GR_ZAJ ON przedmiot.id_przed = gr_zaj.id_przed
INNER JOIN STUDENT ON student.nr_gr = gr_zaj.nr_gr
GROUP BY przedmiot.id_przed,przedmiot.nazwa HAVING COUNT(gr_zaj.nr_gr) > 5;

--2.4 Wstawianie danych.

--a) Utwórz widok imiona, który będzie zawierał listę imion studentów.
CREATE OR REPLACE VIEW imiona AS
  SELECT DISTINCT student.imie FROM STUDENT ORDER BY student.imie;
--SELECT * FROM imiona;

--b) Utwórz tabele imiona_w. Wpisz do niej imiona wszystkich wykładowców.
CREATE TABLE imiona_w AS SELECT DISTINCT IMIE FROM WYKLADOWCA;
--SELECT * FROM imiona_w;

--c) Napisz zapytanie, które każdemu studentowi, który jeszcze nie ma zaliczenia z przedmiotu fizyka,
--   wpisze je w trzecim terminie ocena 3, z dzisiejszą datą.
INSERT INTO ZALICZENIE SELECT DISTINCT 1,nr_albumu,3,SYSDATE,3 FROM STUDENT
WHERE NOT EXISTS (SELECT zaliczenie.nr_albumu FROM zaliczenie
WHERE zaliczenie.nr_albumu = student.nr_albumu AND zaliczenie.id_przed = 1) ORDER BY nr_albumu;

--d) Napisz zapytanie które studentom z grupy o nazwie „grupa 1” usunie zaliczenia z przedmiotu o nazwie polski.
DELETE FROM ZALICZENIE WHERE EXISTS (SELECT student.nr_albumu FROM STUDENT,GRUPA
WHERE student.nr_gr(+) = grupa.nr_gr AND zaliczenie.nr_albumu = student.nr_albumu AND grupa.nazwa = 'Grupa 1')
AND EXISTS (SELECT przedmiot.id_przed FROM PRZEDMIOT WHERE zaliczenie.id_przed = przedmiot.id_przed
AND przedmiot.nazwa = 'POLSKI');

--e) Utwórz widok zal_stu_2, który będzie zawierał informacje o zaliczeniach studenta.
--   [Nr_albumu, nazwisko, id_przed, nazwa, typ, ocena]
--   Ocena – ostatnia ocena z zaliczenia
CREATE OR REPLACE VIEW zal_stu_2 AS
  SELECT  zaliczenie.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena FROM ZALICZENIE
  LEFT OUTER JOIN STUDENT ON student.nr_albumu = zaliczenie.nr_albumu
  LEFT OUTER JOIN PRZEDMIOT ON przedmiot.id_przed = zaliczenie.id_przed
  WHERE zaliczenie.termin = (SELECT MAX(z.termin) FROM ZALICZENIE z
  WHERE z.id_przed = zaliczenie.id_przed AND z.nr_albumu = zaliczenie.nr_albumu);
--SELECT * FROM zal_stu_2;

--f) Utwórz widok zal_stu_1, który będzie zawierał informacje o zaliczeniach studenta.
--   [Nr_albumu, nazwisko, id_przed, nazwa, typ, ocena]
--   Ocena – średnia ocen z zaliczenia
CREATE OR REPLACE VIEW zal_stu_1 AS
  SELECT student.nr_albumu,student.nazwisko,przedmiot.id_przed,przedmiot.nazwa,przedmiot.typ,
  ROUND(AVG(zaliczenie.ocena),2) AS ocena FROM STUDENT,ZALICZENIE,PRZEDMIOT
  WHERE student.nr_albumu = zaliczenie.nr_albumu AND przedmiot.id_przed = zaliczenie.id_przed
  GROUP BY student.nr_albumu,student.nazwisko,przedmiot.id_przed,przedmiot.nazwa,przedmiot.typ
  ORDER BY student.nr_albumu,przedmiot.id_przed;
--SELECT * FROM zal_stu_1;