GGAZWAfJ

1create database clasa;
2use clasa;
3
4CREATE TABLE Studenti (
5    IdStudent INT PRIMARY KEY AUTO_INCREMENT,
6    Nume VARCHAR(50) NOT NULL,
7    Prenume VARCHAR(50) NOT NULL,
8    Adresa VARCHAR(50) NOT NULL
9);
10
11CREATE TABLE Profesori (
12    IdProfesor INT PRIMARY KEY AUTO_INCREMENT,
13    Nume VARCHAR(20) NOT NULL,
14    Prenume VARCHAR(20) NOT NULL,
15    Materia VARCHAR(50)
16);
17
18CREATE TABLE Legatura (
19    IdProfesor INT,
20    IdStudent INT,
21    FOREIGN KEY (IdProfesor)
22        REFERENCES Profesori (IdProfesor),
23    FOREIGN KEY (IdStudent)
24        REFERENCES Studenti (IdStudent)
25);
26
27insert into Profesori values( NULL , 'Pupezescu', 'Valentin' , 'Baze');
28insert into Profesori values( NULL , 'Tache', 'Gurmandu' , 'Televiziune');
29insert into Profesori values( NULL , 'Boul', 'Dracu' , 'Proiect');
30insert into Profesori values( NULL , 'Zoican', 'Sorin' , 'SDA/AMP');
31insert into Profesori values( NULL , 'Petrescu', 'Theodor' , 'SS');
32insert into Profesori values( NULL , 'Adi', 'GGD' , 'SS');
33
34insert into Studenti values( NULL , 'Adi', 'GGD' , 'Branc');
35insert into Studenti values( NULL , 'Gao', 'GPlanet' , 'Racov');
36insert into Studenti values( NULL , 'Titu', 'Saturn' , 'Big');
37insert into Studenti values( NULL , 'Marius', 'Mars' , 'Rahov');
38insert into Studenti values( NULL , 'Petre', 'TGar' , 'Berce');
39
40insert into Legatura values (1,1);
41insert into Legatura values (2,2);
42insert into Legatura values (3,3);
43insert into Legatura values (4,4);
44
45SELECT 
46    Studenti.Nume, Studenti.Prenume
47FROM
48    Studenti,
49    Profesori,
50    Legatura
51WHERE
52    Studenti.IdStudent = Legatura.IdStudent
53        AND Profesori.IdProfesor = Legatura.IdProfesor
54        AND Profesori.Nume = 'Pupezescu';
55
56SELECT 
57    Nume, Prenume
58FROM
59    Studenti
60WHERE
61    Adresa = 'Racov' 
62UNION SELECT 
63    Nume, Prenume
64FROM
65    Profesori
66WHERE
67    Materia = 'Baze';
68
69SELECT DISTINCT
70    Studenti.Nume, Studenti.Prenume
71FROM
72    Studenti
73WHERE
74    NOT EXISTS( SELECT 
75            *
76        FROM
77            Profesori
78        WHERE
79            Studenti.nume = Profesori.nume
80                AND Studenti.prenume = Profesori.prenume);
81
82
83delimiter $$
84create trigger trig
85BEFORE INSERT ON Profesori
86FOR EACH ROW
87begin
88declare mesaj varchar(20);
89if NEW.Nume='Tudorache' then
90signal sqlstate '45000' set MESSAGE_TEXT="EROARE DF";
91end if;
92end$$
93
94insert into Profesori values( NULL , 'Tudorache', 'GGD' , 'SS');
95
96
97delimiter $$
98CREATE PROCEDURE proc(p_idstudent INT, p_nume varchar(50), p_prenume varchar(50), p_adresa varchar(50))
99BEGIN
100IF p_prenume = 'Andrei' THEN
101SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "EROARE DF";
102ELSE
103INSERT INTO Studenti VALUES (NULL, p_nume, p_prenume,p_adresa);
104END IF;
105END$$
106
107CALL proc(NULL,'Marius','Andrei','Adr')