create database clasa;
use clasa;

CREATE TABLE Studenti (
    IdStudent INT PRIMARY KEY AUTO_INCREMENT,
    Nume VARCHAR(50) NOT NULL,
    Prenume VARCHAR(50) NOT NULL,
    Adresa VARCHAR(50) NOT NULL
);

CREATE TABLE Profesori (
    IdProfesor INT PRIMARY KEY AUTO_INCREMENT,
    Nume VARCHAR(20) NOT NULL,
    Prenume VARCHAR(20) NOT NULL,
    Materia VARCHAR(50)
);

CREATE TABLE Legatura (
    IdProfesor INT,
    IdStudent INT,
    FOREIGN KEY (IdProfesor)
        REFERENCES Profesori (IdProfesor),
    FOREIGN KEY (IdStudent)
        REFERENCES Studenti (IdStudent)
);

insert into Profesori values( NULL , 'Pupezescu', 'Valentin' , 'Baze');
insert into Profesori values( NULL , 'Tache', 'Gurmandu' , 'Televiziune');
insert into Profesori values( NULL , 'Boul', 'Dracu' , 'Proiect');
insert into Profesori values( NULL , 'Zoican', 'Sorin' , 'SDA/AMP');
insert into Profesori values( NULL , 'Petrescu', 'Theodor' , 'SS');
insert into Profesori values( NULL , 'Adi', 'GGD' , 'SS');

insert into Studenti values( NULL , 'Adi', 'GGD' , 'Branc');
insert into Studenti values( NULL , 'Gao', 'GPlanet' , 'Racov');
insert into Studenti values( NULL , 'Titu', 'Saturn' , 'Big');
insert into Studenti values( NULL , 'Marius', 'Mars' , 'Rahov');
insert into Studenti values( NULL , 'Petre', 'TGar' , 'Berce');

insert into Legatura values (1,1);
insert into Legatura values (2,2);
insert into Legatura values (3,3);
insert into Legatura values (4,4);

SELECT
    Studenti.Nume, Studenti.Prenume
FROM
    Studenti,
    Profesori,
    Legatura
WHERE
    Studenti.IdStudent = Legatura.IdStudent
        AND Profesori.IdProfesor = Legatura.IdProfesor
        AND Profesori.Nume = 'Pupezescu';

SELECT
    Nume, Prenume
FROM
    Studenti
WHERE
    Adresa = 'Racov'
UNION SELECT
    Nume, Prenume
FROM
    Profesori
WHERE
    Materia = 'Baze';

SELECT DISTINCT
    Studenti.Nume, Studenti.Prenume
FROM
    Studenti
WHERE
    NOT EXISTS( SELECT
            *
        FROM
            Profesori
        WHERE
            Studenti.nume = Profesori.nume
                AND Studenti.prenume = Profesori.prenume);


delimiter $$
create trigger trig
BEFORE INSERT ON Profesori
FOR EACH ROW
begin
declare mesaj varchar(20);
if NEW.Nume='Tudorache' then
signal sqlstate '45000' set MESSAGE_TEXT="EROARE DF";
end if;
end$$

insert into Profesori values( NULL , 'Tudorache', 'GGD' , 'SS');


delimiter $$
CREATE PROCEDURE proc(p_idstudent INT, p_nume varchar(50), p_prenume varchar(50), p_adresa varchar(50))
BEGIN
IF p_prenume = 'Andrei' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "EROARE DF";
ELSE
INSERT INTO Studenti VALUES (NULL, p_nume, p_prenume,p_adresa);
END IF;
END$$

CALL proc(NULL,'Marius','Andrei','Adr')