SQL - DBS - cv5

USE MI_Antalik_Dominik
GO

--5.1--
CREATE TABLE student_zaloha (
id_student INT,
meno VARCHAR (50),
priezvisko VARCHAR (50)
CONSTRAINT pk_student_zaloha PRIMARY KEY (id_student)
)

CREATE TABLE znamky_zaloha (
id_student INT,
id_predmet INT,
body INT,
CONSTRAINT pk_znamky_zaloha PRIMARY KEY (id_student,id_predmet)
)

--5.2--
CREATE PROCEDURE zmaz_studenta
@id_stud INT                    --deklarovanie premennej, vstup
AS
BEGIN
    INSERT INTO student_zaloha
    SELECT * FROM student WHERE id_student=@id_stud
    INSERT INTO znamky_zaloha
    SELECT z.id_student, z.id_predmet, z.body FROM znamka z
    WHERE z.id_student=@id_stud
    DELETE FROM znamka WHERE znamka.id_student=@id_stud  -- alebo 'delete *'. To isté v tomto prípade
    DELETE FROM student WHERE student.id_student=@id_stud
END

--execute=exec
EXECUTE zmaz_studenta 1

SELECT * FROM student
SELECT * FROM znamka
SELECT * FROM Student_zaloha
SELECT * FROM Znamky_Zaloha


--5.3-- vnorené príkazy
CREATE PROCEDURE vloz_vyucujuceho
@meno VARCHAR (50),
@priezvisko VARCHAR (50),
@predmet VARCHAR (50)
AS
BEGIN
    DECLARE @id_pred INT
    SELECT @id_pred=p.id_predmet FROM predmet p WHERE p.nazov=@predmet
    IF (@id_pred IS NULL)
    BEGIN
        INSERT INTO predmet VALUES (@predmet)
        SET @id_pred=IDENT_CURRENT('predmet')
    END
    DECLARE @id_uc INT
    SELECT @id_uc=u.id_ucitel FROM ucitel u WHERE u.meno=@meno AND u.priezvisko=@priezvisko
    IF (@id_uc IS NULL)
    BEGIN
        INSERT INTO ucitel (meno, priezvisko) VALUES (@meno,@priezvisko)
        SET @id_uc=IDENT_CURRENT('ucitel')
    END
    INSERT INTO vyucujuci (id_ucitel,id_predmet) VALUES (@id_uc,@id_pred)
END

EXECUTE vloz_vyucujuceho 'Jan','Novák','Tělocvik'

--5.4--
CREATE PROCEDURE save_student
@meno VARCHAR (50),
@priezvisko VARCHAR (50),
@id_student INT OUTPUT
AS
BEGIN
    SET @id_student= (SELECT top 1 id_student FROM student s WHERE s.meno=@meno AND s.priezvisko=@priezvisko)
    IF (@id_student IS NULL)
    BEGIN
        INSERT INTO student (meno, priezvisko) VALUES (@meno,@priezvisko)
        SET @id_student=@@IDENTITY
    END
END

DECLARE @id_student INT
EXECUTE save_student 'František','Koudelka',@id_student output
print @id_student

DROP PROCEDURE save_student