BAZY LAB1 / Semestr 4

--------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
--INSERT INTO GR_ZAJ VALUES(1,2,NULL,406);
SELECT P.ID_PRZED, P.NAZWA FROM GR_ZAJ GZ
LEFT OUTER JOIN PRZEDMIOT P ON P.ID_PRZED = GZ.ID_PRZED
WHERE GZ.NR_GR IS NULL;

--K-- Podać na jakie przedmioty nie chodzi żaden student
SELECT ID_PRZED,NAZWA FROM PRZEDMIOT P
WHERE NOT EXISTS(
    SELECT ID_PRZED FROM GR_ZAJ GZ
    WHERE EXISTS(
        SELECT NR_ALBUMU FROM STUDENT S
        WHERE S.NR_GR = GZ.NR_GR
    ) AND GZ.ID_PRZED = P.ID_PRZED
);


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




--M--



--N--




--O--
SELECT P.ID_PRZED, P.NAZWA,PODZ.TERMIN, PODZ."LICZBA STUDENTÓW" FROM
(
  SELECT ID_PRZED, TERMIN, COUNT(*) AS "LICZBA STUDENTÓW" FROM ZALICZENIE Z
  WHERE Z.OCENA > 2
  GROUP BY ID_PRZED,TERMIN
) PODZ
INNER JOIN PRZEDMIOT P ON P.ID_PRZED = PODZ.ID_PRZED;


--P--
SELECT  p.typ, z.ocena, COUNT(*) AS liczba_studentow
FROM    zaliczenie z, przedmiot p
WHERE   z.id_przed = p.id_przed
GROUP BY p.typ, z.ocena
ORDER BY p.typ, z.ocena;


--Q--




--R--
SELECT TO_CHAR(data, 'day') AS "dzien_tygodnia"
FROM (
        SELECT  z.data, COUNT(*) l
        FROM    zaliczenie z
        GROUP BY z.data
        ORDER BY COUNT(*) DESC
      )
WHERE l = (
        SELECT MAX(l2)
        FROM (
          SELECT  z.data, COUNT(*) l2
          FROM    zaliczenie z
          GROUP BY z.data
        )
      );



--S--






--T--
SELECT  w.nr_wykl, w.nazwisko, COUNT(*) AS liczba_przedmiotow
FROM    pro_zaj pz, wykladowca w
WHERE   w.nr_wykl = pz.nr_wykl
GROUP BY  w.nr_wykl, w.nazwisko
HAVING  COUNT(*) > 2;

--U--
SELECT P.ID_PRZED, P.NAZWA,PODZ."LICZBA STUDENTÓW" FROM
(
  SELECT ID_PRZED, SUM(ILOSC) AS "LICZBA STUDENTÓW" FROM (
    SELECT ID_PRZED,PODZ.ILOSC,SALA FROM gr_zaj
    LEFT OUTER JOIN (
      SELECT NR_GR,COUNT(*) AS ILOSC FROM STUDENT
      GROUP BY NR_GR
    )PODZ USING (NR_GR)
  )
  GROUP BY ID_PRZED
  HAVING SUM(ILOSC) > 5
) PODZ
LEFT OUTER JOIN PRZEDMIOT P ON P.ID_PRZED = PODZ.ID_PRZED;




------------2.4---------

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

SELECT * FROM imiona;


---b---
CREATE TABLE IMIONA_W AS
SELECT DISTINCT IMIE FROM WYKLADOWCA;




---c---

---d---

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


---f---
CREATE OR REPLACE VIEW ZAL_STU_1 AS
SELECT Z.NR_ALBUMU AS "Nr_albumu", S.NAZWISKO, P.ID_PRZED,P.NAZWA,P.TYP,PODZPT.OCENA AS "ocena" FROM ZALICZENIE Z
LEFT OUTER JOIN STUDENT S ON Z.NR_ALBUMU = S.NR_ALBUMU
LEFT OUTER JOIN PRZEDMIOT P ON Z.ID_PRZED = P.ID_PRZED
LEFT OUTER JOIN (
  SELECT NR_ALBUMU, AVG(OCENA) OCENA FROM ZAL_STU_2
  GROUP BY NR_ALBUMU
)PODZPT ON PODZPT.NR_ALBUMU = Z.NR_ALBUMU;