------------ VIEW_1 ------------
CREATE VIEW zajecia_dod(imie, nazwisko, tematyka)
    AS
    (
    SELECT n.imie,
           n.nazwisko,
           z.tematyka
    FROM nauczyciele n inner join Zajecia_dodatkowe z
    ON z.id_nauczyciela = n.PESEL
    )
GO


SELECT *
FROM zajecia_dod;


DROP VIEW zajecia_dod;


------------ VIEW_2 ------------

CREATE VIEW IIIa(imie, nazwisko)
    AS
    (
        SELECT imie, nazwisko
        FROM uczniowie
        WHERE id_klasy = 'IIIa'
    )
GO


SELECT *
FROM IIIa
ORDER BY nazwisko;


DROP VIEW IIIa;


------------ Procedura_1 ------------
CREATE PROCEDURE usuwanie_ocen
@id_o  INT
    AS
    BEGIN
         IF EXISTS (SELECT *
                    FROM Oceny
                    WHERE id_oceny=@id_o)
    DELETE FROM Oceny
    WHERE id_oceny = @id_o
    ELSE
   RAISERROR('Nie ma takiej oceny', 11, 1);
END;
GO


EXEC usuwanie_ocen 4;


DROP PROCEDURE usuwanie_ocen;

------------ Procedura_2 ------------
CREATE PROCEDURE nowe_nazwisko
@pesel VARCHAR(11) ,
@nazwisko VARCHAR (20)
    AS
    BEGIN
    IF EXISTS (SELECT *
              FROM Uczniowie
              WHERE @pesel=PESEL)
    UPDATE uczniowie
    SET uczniowie.nazwisko=@nazwisko
    WHERE pesel=@pesel
      ELSE
       RAISERROR('Nie ma takiego dziecka', 11, 1);
       END;
GO


EXECUTE nowe_nazwisko 95041029264,niezabazdanych;


DROP PROCEDURE nowe_nazwisko;


------------ Procedura_3 ------------

CREATE PROCEDURE dodaj_przedmiot
    @id_przedmiotu VARCHAR(5),
    @nazwa_przedmiotu VARCHAR(20),
    @poziom VARCHAR(20)
AS
BEGIN
     IF NOT EXISTS (SELECT *
                    FROM Przedmioty
                    WHERE id_przedmiotu=@id_przedmiotu and @nazwa_przedmiotu=nazwa and @poziom=poziom)
    INSERT INTO Przedmioty VALUES
    (@id_przedmiotu,@nazwa_przedmiotu,@poziom)
    ELSE
        RAISERROR('Taki przedmiot juz istnieje', 11, 1);

END;
GO


EXECUTE dodaj_przedmiot 'His','Historia',1;


DROP PROCEDURE dodaj_przedmiot;


------------ Procedura_4 ------------

CREATE OR ALTER PROCEDURE  Rodzice_dzieciakow
    @PESEL VARCHAR(11),
    @imie_r VARCHAR(20) OUTPUT
AS
BEGIN
    IF EXISTS (SELECT *
               FROM Rodzice_dzieci
               WHERE id_dziecka=@PESEL)
       BEGIN
            SELECT *
            FROM Rodzice_dzieci
            Where id_dziecka=@PESEL
       END;
    ELSE
       RAISERROR('Nie ma takiego dziecka', 11, 1);
END;
GO


BEGIN TRY
    DECLARE  @imie_rodzica VARCHAR(20);

    EXEC Rodzice_dzieciakow     -- EXEC jest aliasem dla EXECUTE
       97020345161,
         @imie_rodzica OUTPUT;

    PRINT @imie_rodzica;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()  AS 'NUMER BLEDU',
           ERROR_MESSAGE() AS 'KOMUNIKAT';
END CATCH;


DROP PROCEDURE Rodzice_dzieciakow;


------------ Funkcja_1 ------------

CREATE FUNCTION ile_nb
    (
    @PESEL VARCHAR(11)
    )
    RETURNS INT
    AS
    BEGIN RETURN(SELECT COUNT(id_ucznia)
                         FROM Frekwencja
                         WHERE id_ucznia=@PESEL and obecnosc='nb')
END;
GO


SELECT  dbo.ile_nb (97080714219) AS 'liczba niebecnosci';

DROP FUNCTION ile_nb;

------------ Funkcja_2 ------------

CREATE FUNCTION Ocenki
(
    @PESEL VARCHAR(11)
)
    RETURNS TABLE
    AS
        RETURN   SELECT ocena, id_przedmiotu, data_oc
                 FROM   Oceny
                 WHERE  Oceny.id_ucznia = @PESEL;

GO


Select *
FROM Ocenki (97080714219)
ORDER BY id_przedmiotu, data_oc;


DROP FUNCTION Ocenki;


------------ Trigger_1 ------------

CREATE TRIGGER nowa_ocena
ON Oceny
AFTER INSERT
    AS
    PRINT 'Pracownik ' + user_name() + ' dodal oceny'
GO


INSERT INTO Oceny(ocena,id_ucznia,id_nauczyciela,id_przedmiotu,typ_oceny,waga,data_oc) VALUES
(4,'97080714219','75010482946','Mat','sprawdzian',2,'2018-06-12');


DROP TRIGGER nowa_ocena;


------------ Trigger_2 ------------

CREATE TRIGGER delete_ocena
on Oceny
AFTER DELETE
    AS
    PRINT 'Pracownik ' + user_name() + ' usuna oceny'
GO


DELETE FROM Oceny
WHERE id_ucznia = '97080714219';


DROP TRIGGER delete_ocena


------------ Trigger_3 ------------

CREATE TRIGGER trig_oceny
on Oceny
AFTER INSERT, UPDATE, DELETE
    AS
    SELECT * FROM deleted;
    SELECT * FROM inserted
GO


UPDATE Oceny
SET ocena = 5
WHERE id_ucznia = '97012511534'
AND id_przedmiotu = 'Inf';


DROP TRIGGER trig_oceny


------------ Trigger_4 ------------
CREATE TRIGGER nowy_uczen
ON Uczniowie
AFTER INSERT
    AS
    PRINT 'Pracownik ' + user_name() + ' dodaje ucznia'
GO


INSERT INTO Uczniowie VALUES
('97122654895','Marek','Kalina','1997-12-26','IIa');


DROP TRIGGER nowy_uczen;