· 8 years ago · Apr 20, 2018, 11:00 AM
1DROP DATABASE IF EXISTS notes_programacio;
2
3CREATE DATABASE notes_programacio;
4USE notes_programacio;
5
6CREATE TABLE notes
7(dni VARCHAR(9) NOT NULL,
8codi VARCHAR(3) NOT NULL,
9nota INT CHECK (nota BETWEEN 0 AND 10),
10PRIMARY KEY (dni,codi)
11);
12
13CREATE TABLE notes_alumne
14(dni VARCHAR(9),
15mitjana DECIMAL(3,1),
16codi_nota VARCHAR(1),
17PRIMARY KEY (dni)
18);
19
20CREATE TABLE notes_prova
21(codi VARCHAR(3) NOT NULL,
22nota INT
23);
24
25DELIMITER //
26 CREATE TRIGGER NAI AFTER INSERT ON notes FOR EACH ROW
27 BEGIN
28 IF (select count(*) from notes_alumne where dni = new.dni)=0 then
29 IF (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25)/2)>4.5 then
30 INSERT INTO notes_alumne VALUES (new.dni, (((select sum(nota) from notes where codi like 'E%' and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2, 'A');
31 else
32 INSERT INTO notes_alumne VALUES (new.dni, (((select sum(nota) from notes where codi like 'E%' and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2, 'S');
33 end if;
34 else
35 IF (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25)/2)>4.5 then
36 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2 where dni=new.dni;
37 update notes_alumne set codi_nota = 'A' where dni=new.dni;
38 else
39 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2 where dni=new.dni;
40 update notes_alumne set codi_nota = 'S' where dni=new.dni;
41 end if;
42 end if ;
43 END //
44
45 CREATE TRIGGER NAU AFTER UPDATE ON notes FOR EACH ROW
46 BEGIN
47 IF (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25)/2)>4.5 then
48 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2 where dni=new.dni;
49 update notes_alumne set codi_nota = 'A' where dni=new.dni;
50 else
51 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=new.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=new.dni)*0.25))/2 where dni=new.dni;
52 update notes_alumne set codi_nota = 'S' where dni=new.dni;
53 end if;
54 END //
55
56 CREATE TRIGGER NAD AFTER DELETE ON notes FOR EACH ROW
57 BEGIN
58 IF (((select sum(nota) from notes where codi like 'E%'and dni=old.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=old.dni)*0.25)/2)>4.5 then
59 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=old.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=old.dni)*0.25))/2 where dni=old.dni;
60 update notes_alumne set codi_nota = 'A' where dni=old.dni;
61 else
62 update notes_alumne set mitjana = (((select sum(nota) from notes where codi like 'E%'and dni=old.dni)*0.75) + ((select sum(nota) from notes where codi like 'P%' and dni=old.dni)*0.25))/2 where dni=old.dni;
63 update notes_alumne set codi_nota = 'S' where dni=old.dni;
64 end if ;
65 END //
66
67DELIMITER ;
68
69INSERT INTO notes VALUES ('45992947P', 'E01', 4);
70INSERT INTO notes VALUES ('45992947P', 'P01', 7);
71INSERT INTO notes VALUES ('45992947P', 'P02', 7);
72INSERT INTO notes VALUES ('45992947P', 'P03', 8);
73INSERT INTO notes VALUES ('45992947P', 'P04', 6);
74INSERT INTO notes VALUES ('45079996W', 'E01', 6);
75INSERT INTO notes VALUES ('45079996W', 'P01', 6);
76INSERT INTO notes VALUES ('45079996W', 'P02', 8);
77INSERT INTO notes VALUES ('45079996W', 'P03', 4);
78INSERT INTO notes VALUES ('45079996W', 'P04', 7);
79INSERT INTO notes VALUES ('45999996F', 'E01', 7);
80INSERT INTO notes VALUES ('45999996F', 'P01', 4);
81INSERT INTO notes VALUES ('45999996F', 'P02', 8);
82INSERT INTO notes VALUES ('45999996F', 'P03', 6);
83INSERT INTO notes VALUES ('45999996F', 'P04', 7);
84
85UPDATE notes SET nota = 9 WHERE codi='P01' and dni='45992947P';
86UPDATE notes SET nota = 9 WHERE codi='P02' and dni='45992947P';
87
88DELETE FROM notes WHERE dni = '45992947P' and codi = 'P02';
89
90select * from notes;
91select * from notes_alumne;