BD2

--------SELECTY----------------------
--------2.2--------
--A--
SELECT id_przed, nazwa FROM przedmiot;
--B--
SELECT nr_albumu, nazwisko FROM student WHERE nr_gr = 2;
--C--
SELECT student.nr_albumu, student.nazwisko, student.nr_gr, grupa.nazwa FROM student, grupa WHERE student.nr_gr = grupa.nr_gr ORDER BY grupa.nazwa;
--D--
SELECT wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.Nazwa FROM wykladowca, przedmiot, pro_zaj WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl AND przedmiot.id_przed=pro_zaj.id_przed;
--E--
SELECT wykladowca.nr_wykl, wykladowca.nazwisko, przedmiot.id_przed, przedmiot.nazwa FROM wykladowca, przedmiot, pro_zaj WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl AND przedmiot.id_przed = pro_zaj.id_przed;
--F--
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;
--G--
SELECT przedmiot.id_przed, przedmiot.nazwa FROM przedmiot, wykladowca, pro_zaj WHERE przedmiot.id_przed = pro_zaj.id_przed AND wykladowca.nr_wykl = pro_zaj.nr_wykl AND (wykladowca.nr_wykl = 3 OR wykladowca.nr_wykl = 4);
--H--
SELECT przedmiot.id_przed, przedmiot.nazwa, wykladowca.nr_wykl, wykladowca.nazwisko FROM przedmiot, wykladowca, pro_zaj WHERE przedmiot.id_przed = pro_zaj.id_przed AND wykladowca.nr_wykl = pro_zaj.nr_wykl;
--I--
SELECT przedmiot.id_przed, przedmiot.nazwa FROM przedmiot, pro_zaj WHERE przedmiot.id_przed = pro_zaj.id_przed AND przedmiot.id_przed = 10;
--J--Podać na jakie zajęcia prowadzone przez wykadowców nie chodzi żadna grupa

--K-- Podać na jakie przedmioty nie chodzi żaden student

--L--Podać jaki nauczyciel nie uczy żadnego przedmiotu
SELECT wykladowca.nr_wykl, wykladowca.nazwisko FROM wykladowca WHERE NOT EXISTS (SELECT  wykladowca_2.nr_wykl, wykladowca_2.nazwisko FROM wykladowca wykladowca_2, pro_zaj prozaj_2 WHERE wykladowca_2.nr_wykl = prozaj_2.nr_wykl AND wykladowca_2.nr_wykl = wykladowca.nr_wykl);
--M--
SELECT przedmiot.id_przed, grupa.nazwa FROM przedmiot, grupa, gr_zaj WHERE przedmiot.id_przed = gr_zaj.id_przed AND grupa.nr_gr = gr_zaj.nr_gr;
--N--
SELECT przedmiot.id_przed, przedmiot.nazwa FROM przedmiot, gr_zaj WHERE przedmiot.id_przed = gr_zaj.id_przed AND gr_zaj.Sala = '405';
--O--
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM student, gr_zaj WHERE student.nr_gr = gr_zaj.nr_gr AND gr_zaj.Sala = '405' ORDER BY student.NR_ALBUMU;
--P-- Podać listę studentów którzy nie maja zajec w sali 405;
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM student, gr_zaj WHERE student.nr_gr = gr_zaj.nr_gr AND gr_zaj.Sala != '405' ORDER BY student.NR_ALBUMU;
--Q-- Podac liste studentów którzy chodza 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 gr_zaj.id_przed = przedmiot.id_przed AND przedmiot.Nazwa = 'FIZYKA' ORDER BY student.NR_ALBUMU;
--R-- Podac na jakie zajecia chodza studenci o imieniu Astor
SELECT przedmiot.id_przed, przedmiot.nazwa FROM przedmiot, gr_zaj, student WHERE student.nr_gr = gr_zaj.nr_gr AND gr_zaj.id_przed = przedmiot.id_przed AND student.imie LIKE 'Astor';
--S-- Podac nazwiska studentoww których uczy mgr. Bwyk
SELECT DISTINCT student.nr_albumu, student.nazwisko, student.imie FROM student, wykladowca, gr_zaj WHERE student.nr_gr = gr_zaj.nr_gr AND wykladowca.nr_wykl = gr_zaj.nr_wykl AND wykladowca.stopien LIKE 'mgr' AND wykladowca.nazwisko LIKE 'Bwyk' ORDER BY student.NR_ALBUMU;

--------2.3--------
--A--Podac ile kazdy wykladowca prowadzi przedmiotow
SELECT wykladowca.nr_wykl, wykladowca.nazwisko, COUNT(pro_zaj.id_przed) AS ILOSC FROM wykladowca, pro_zaj WHERE wykladowca.nr_wykl = pro_zaj.nr_wykl GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko ORDER BY wykladowca.nr_wykl;
--B-- Podac nazwy grup dla ktorych grupa.liczna_st<> zliczona ilosc wierszy
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(student.nr_gr) AS ZLICZONA_ILOSC_WIERSZY FROM grupa, student WHERE grupa.nr_gr = student.nr_gr GROUP BY grupa.nr_gr, grupa.nazwa, grupa.liczba_st HAVING COUNT(student.nr_gr) != grupa.liczba_st;
--C-- Podac z iloma grupami kazdy wykladowca ma zajecia
SELECT wykladowca.nr_wykl, wykladowca.nazwisko, COUNT(gr_zaj.nr_gr) AS ILOSC_GRUP FROM wykladowca, gr_zaj WHERE wykladowca.nr_wykl = gr_zaj.nr_wykl GROUP BY wykladowca.nr_wykl, wykladowca.nazwisko;
--D-- Podac ile kazdy student ma przedmiotow
SELECT student.nr_albumu, student.nazwisko, COUNT(gr_zaj.id_przed) FROM student, gr_zaj WHERE student.nr_gr = gr_zaj.nr_gr GROUP BY student.nr_albumu, student.nazwisko;
--E-- Podac ilu studentow uczeszcza na kazdy przedmiot
SELECT przedmiot.id_przed, przedmiot.nazwa, COUNT(student.nr_albumu) AS LICZBA_STUDENTOW FROM przedmiot, student, gr_zaj WHERE przedmiot.id_przed = gr_zaj.id_przed AND student.nr_gr = gr_zaj.nr_gr GROUP BY przedmiot.id_przed, przedmiot.nazwa;
--F-- Podac liczbe studentow w grupach
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(student.nr_gr) AS ZLICZONA_ILOSC_WIERSZY FROM grupa, student WHERE grupa.nr_gr = student.nr_gr GROUP BY grupa.nr_gr, grupa.nazwa, grupa.liczba_st;
--G-- Podac nazwy grup dla ktorych grupa.liczba_st = zliczona ilosc wierszy
SELECT grupa.nr_gr, grupa.nazwa, grupa.liczba_st, COUNT(student.nr_gr) AS ZLICZONA_ILOSC_WIERSZY FROM grupa, student WHERE grupa.nr_gr = student.nr_gr GROUP BY grupa.nr_gr, grupa.nazwa, grupa.liczba_st HAVING COUNT(student.nr_gr) = grupa.liczba_st;
--H-- Podac srednia ocene kazdego studenta z kazdego przedmiotu
SELECT student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, AVG(zaliczenie.ocena) AS SREDNIA FROM student, przedmiot, zaliczenie WHERE przedmiot.id_przed = zaliczenie.id_przed AND zaliczenie.nr_albumu = student.nr_albumu GROUP BY student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa ORDER BY student.nazwisko;
--I-- Podac nazwe przedmiotu, z ktorego najwiecej studentow nie zaliczylo w pierwszym terminie
--SELECT przedmiot.id_przed, przedmiot.nazwa FROM przedmiot WHERE MAX(SELECT przedmiot_2.id_przed, COUNT(student_2.nr_albumu) as liczba FROM student student_2, przedmiot przedmiot_2, zaliczenie zaliczenie_2 WHERE przedmiot_2.id_przed = zaliczenie_2.id_przed AND zaliczenie_2.nr_albumu = student_2.nr_albumu AND zaliczenie_2.termin = 1 AND zaliczenie_2.ocena = 2 group by przedmiot_2.id_przed);
--J-- Podac ostatnia ocene kazdego studenta z kazdego przedmiotu (wedlug terminu)
SELECT student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena, MAX(zaliczenie.termin) FROM student, przedmiot, zaliczenie WHERE student.nr_albumu = zaliczenie.nr_albumu AND zaliczenie.id_przed = przedmiot.id_przed group by student.nr_albumu, student.nazwisko, przedmiot.id_przed, przedmiot.nazwa, zaliczenie.ocena;
--K-- Podac w ktorej sali odbywa sie najwiecej zajec
--SELECT gr_zaj.sala, count(gr_zaj.nr_gr) AS ILOSC_ZAJEC FROM gr_zaj GROUP BY gr_zaj.sala;

-- 2.4 a)

CREATE OR REPLACE VIEW imiona AS
  SELECT DISTINCT s.imie FROM student s;

SELECT * FROM imiona;


--2.4 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;

-- 2.4 c)

INSERT INTO zaliczenie
  SELECT DISTINCT 1, s.nr_albumu, 3, sysdate, 3
  FROM  student s
  WHERE NOT EXISTS (
          SELECT  z2.nr_albumu
          FROM    zaliczenie z2
          WHERE   z2.id_przed = 1
            AND   s.nr_albumu = z2.nr_albumu
        );

-- 2.4 e) (wersja 1)

CREATE OR REPLACE VIEW zal_stu_2 AS
  SELECT  z.nr_albumu, s.nazwisko, p.id_przed, p.nazwa, p.typ, z.ocena as ocena
  FROM    zaliczenie z, student s, przedmiot p
  WHERE   z.nr_albumu = s.nr_albumu
    AND   z.id_przed = p.id_przed
    AND   z.ocena = (
            SELECT  z2.ocena
            FROM    zaliczenie z2
            WHERE   z2.id_przed = z.id_przed
              AND   z2.nr_albumu = z.nr_albumu
              AND   ROWNUM <=1
          );


SELECT * FROM zal_stu_2;


2.4 e) ( wersja 2)

CREATE OR REPLACE VIEW zal_stu_2 AS
  SELECT x.nr_albumu, x.nazwisko, x.id_przed, x.nazwa, x.ocena FROM (SELECT 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))x;

SELECT * FROM zal_stu_2;
--DROP VIEW zal_stu_2;

--END;