-- ZADANIE 2

-- instrukcja wyswietlajaca nazwy oraz wagi wszystkich przedmiotow, ktorych nazwa konczy sie na 'tyka' lub 'anie', a waga jest powyzej
-- sredniej wyliczonej ze wszystkich wag, np Matematyka 10

SELECT przedmiot.nazwa
  FROM przedmiot
  WHERE (nazwa LIKE '%tyka' OR nazwa LIKE '%anie') AND waga > (SELECT AVG(waga) FROM przedmiot);

-- instrukcja wyswietlajaca wszystkie informacje o zaliczeniu (nazwisko, imie studenta, nazwa przedmiotu oraz oceny z trzech terminow),
-- gdzie ocena z dowolnego z terimnow ma wartosc NULL, np Kowalski Jan, Matematyka, 4.0

SELECT student.nazwisko,student.imie,przedmiot.nazwa,zaliczenie.termin1,zaliczenie.termin2,zaliczenie.termin3
  FROM student, przedmiot, zaliczenie
  WHERE (termin1 IS NULL OR termin2 IS NULL OR termin3 IS NULL) AND student.id_student = zaliczenie.id_student AND zaliczenie.id_przedmiot = przedmiot.id_przedmiot;

-- instrukcja wyswietlajaca informacje o liczbie zaliczen, do ktorych przystapil kazdy student, pod warunkiem, ze liczba zaliczen byla wieksza niz 3
-- np Jan Kowalski 10

SELECT s.imie,s.nazwisko, info.liczba
  FROM student s, (SELECT student.id_student,COUNT(zaliczenie.id_zaliczenie) Liczba
                    FROM student, zaliczenie
                    WHERE student.id_student = zaliczenie.id_student
                    GROUP BY student.id_student) info
  WHERE info.liczba > 3 AND info.id_student = s.id_student;

-- instrukcja tworzaca widok zawierajacy informacje o nazwie grupy wraz z iloscia studentow w danej grupie, instrukcja powinna uwzgledniac
-- takze grupy, do ktorych nie zostali jeszcze przypisani studenci, dane powinny byc uporzadkowane wedlug nazw grup malejaco

CREATE OR REPLACE VIEW grupyistudenci AS

  SELECT grupa.nazwa, liczba
    FROM grupa, (SELECT grupa.id_grupa,COUNT(student.id_student) Liczba
                    FROM grupa, student
                    WHERE student.id_grupa (+) = grupa.id_grupa
                    GROUP BY grupa.id_grupa) info
    WHERE info.id_grupa = grupa.id_grupa
    ORDER BY grupa.nazwa DESC;

SELECT * FROM grupyistudenci;


==================================================ZAD3========================================================================
-- funkcja wyliczajaca roznice pomiedzy minimalna, a maksymalna waga przedmiotu, w funkcji powinna zostac wykorzystana zmienna %TYPE
-- lub %ROWTYPE, nalezy podaj instrukcje, ktora spowoduje uruchomienie funkcji

CREATE OR REPLACE FUNCTION roznica

RETURN NUMBER

IS

minimalna przedmiot.waga%TYPE;
maksymalna przedmiot.waga%TYPE;
roznica przedmiot.waga%TYPE;

BEGIN

SELECT MIN(waga)
  INTO minimalna
  FROM przedmiot;

SELECT MAX(waga)
  INTO maksymalna
  FROM przedmiot;

roznica:= maksymalna - minimalna;

RETURN roznica;

END;
/

-- uruchomienie

SELECT roznica() FROM dual;


-- procedure przegladajaca wszystkie przedmioty, jesli nazwa przedmiotu jest NULL powinien zostac zgloszony wyjatek, jesli natomiast
-- waga jest NULL, to waga powinna zostac ustawiona na 1, nalezy podac instrukcje, ktora spowoduje uruchomienie procedury

CREATE OR REPLACE PROCEDURE wszystkie IS

CURSOR przedmioty IS
  SELECT *
  FROM przedmiot
  FOR UPDATE OF przedmiot.waga;

wyjatek EXCEPTION;

zm_przedmiot przedmioty%ROWTYPE;

BEGIN

FOR zm_przedmiot IN przedmioty LOOP

IF zm_przedmiot.nazwa IS NULL THEN
  RAISE wyjatek;
END IF;

IF zm_przedmiot.waga IS NULL THEN
  UPDATE przedmiot p
  SET p.waga = 1
  WHERE CURRENT OF przedmioty;
END IF;

END LOOP;

EXCEPTION

WHEN wyjatek THEN DBMS_OUTPUT.PUT_LINE('Nazwa przedmiotu jest rowna NULL!');

END;
/

-- sprawdzenie czy dziala

SELECT * FROM przedmiot;

INSERT INTO przedmiot(id_przedmiot,nazwa,waga) VALUES (16,'Testowy',NULL);

SELECT * FROM przedmiot;

CALL wszystkie();

SELECT * FROM przedmiot;

INSERT INTO przedmiot(id_przedmiot,nazwa,waga) VALUES (17,NULL,3);

CALL wszystkie();


-- wyzwalacz dotyczacy dodawania wierszy do tabeli student, jezeli konto jest rozne od 0, nalezy zglosic blad aplikacji o poprawnie
-- wybranym numerze, jezeli student jest dodawany do grupy, w ktorej jest juz co najmniej 15 osob, nalezy zglosic inny blad aplikacji
-- o poprawnie wybranym numerze, nalezy podac instrukcje, ktora spowoduje uruchomienie wyzwalacza

CREATE OR REPLACE TRIGGER wyzwalacz

BEFORE INSERT ON student FOR EACH ROW

DECLARE liczba NUMBER;

BEGIN

IF :NEW.konto <> 0 THEN RAISE_APPLICATION_ERROR(-10000,'Konto jest rozne od zera!');
END IF;

SELECT COUNT(*)
  INTO liczba
  FROM student, grupa
  WHERE student.id_grupa = grupa.id_grupa AND grupa.id_grupa = :NEW.id_grupa;

IF liczba > 14 THEN
  Raise_Application_Error(-10001,'Nie da rady stary! Gdzie chcesz zmiescic taki tlum?');
END IF;

END;
/

INSERT INTO student(id_student,nazwisko,imie,srednia,konto,id_grupa) VALUES (11,'Marcinkowska','Paulina',3.95,2,5);

DROP TRIGGER wyzwalacz;