1dws4ngN

1ESERCITAZIONE BASI DI DATI 12-04-2018
2PROCEDURA 1:
3
4
5DELIMITER //
6CREATE PROCEDURE IncidenteMax(IN anno int)
7BEGIN
8	drop temporary table if exists temp;
9    create temporary table temp(
10		id integer);
11    
12    INSERT INTO temp  
13    select ID_PERSONA from PARTECIPA P JOIN INCIDENTE I 
14    ON I.NUMERO = P.NUMERO_INC
15    WHERE year(I.DATA) = anno and P.Costo = (select max(P1.COSTO)
16			from PARTECIPA P1 JOIN INCIDENTE I1 
17            ON I1.NUMERO = P1.NUMERO_INC
18            WHERE year(I.DATA) = anno);
19
20END //
21DELIMITER ;
22
23
24
25CALL IncidenteMax(2002);
26select * FROM temp;
27
28
29
30
31
32PROCEDURA 2:
33
34DELIMITER //
35CREATE PROCEDURE CostoMax(IN plc varchar(20), OUT costoTot int)
36BEGIN
37	drop temporary table if exists temp;
38    create temporary table temp(
39		ANNO integer, 
40        COSTO integer);
41    
42    INSERT INTO temp
43	SELECT year(I.DATA), SUM(P.COSTO) FROM INCIDENTE I JOIN PARTECIPA P 
44    ON I.NUMERO = P.NUMERO_INC
45    WHERE I.LUOGO = plc group by year(I.DATA);
46    
47    Select SUM(COSTO) into costoTot FROM Partecipa;
48
49
50END //
51DELIMITER ;
52
53call CostoMax("Catania", @value);
54select @value;
55select * from TEMP;
56
57
58
59
60
61
62
63
64PROCEDURA 3:
65
66DELIMITER //
67CREATE PROCEDURE COST_NUM_MEDIO(IN luogo varchar(20),
68 out costo_medio float, out numero_medio float)
69BEGIN
70	drop temporary table if exists temp;
71    create temporary table temp(
72		ANNO integer, 
73        NUMERO_MEDIO INTEGER,
74        COSTO_MEDIO integer);
75        
76	INSERT INTO TEMP
77	SELECT year(I.DATA) as anno, count(*), avg(P.COSTO) from
78    INCIDENTE I JOIN PARTECIPA P ON I.NUMERO = P.NUMERO_INC
79    WHERE I.LUOGO = luogo
80    group by anno;
81    
82    select avg(NUMERO_MEDIO), avg(COSTO_MEDIO)
83    into numero_medio, costo_medio
84    from TEMP;
85        
86        
87END //
88DELIMITER ;
89
90
91call COST_NUM_MEDIO("Catania", @Costo_Medio, @Numero_Medio);
92select * from TEMP;
93select @Costo_Medio, @Numero_Medio;
94
95
96
97
98
99
100
101
102PROCEDURA 4:
103DELIMITER //
104CREATE PROCEDURE getIncByID(IN param1 integer)
105BEGIN
106	drop temporary table if exists temp;
107    create temporary table temp(
108		NUMERO integer, 
109        DATA DATE,
110        LUOGO varchar(20));
111        
112	INSERT INTO TEMP
113    SELECT I.NUMERO, I.DATA, I.LUOGO FROM INCIDENTE I JOIN PARTECIPA P 
114    ON I.NUMERO = P.NUMERO_INC
115    WHERE P.ID_PERSONA = param1;
116
117
118END //
119DELIMITER ;
120
121call getIncByID(2);
122select * from TEMP;
123
124
125
126
127TRIGGER 1:
128
129CREATE TABLE TASSE (TARGA varchar(7), ID_INCIDENTE int, tassa int,
130PRIMARY KEY(TARGA, ID_INCIDENTE), 
131INDEX tar_ind(TARGA),
132INDEX inc_ind(ID_INCIDENTE),
133FOREIGN KEY (TARGA)  REFERENCES MACCHINA(TARGA),
134FOREIGN KEY (ID_INCIDENTE) REFERENCES INCIDENTE(NUMERO));
135
136
137
138
139
140
141
142
143
144
145
146
147
148TRIGGER 3:
149create trigger assegna_perito
150after insert on incidente
151for each row
152begin
153if not exists(select count(distinct id) as num_per from perito
154having num_per
155in (select count(distinct id_perito) from perito_incidente))     //L'if mi sta controllando se il numero di periti è uguale al numero di periti inseriti in perito_incidente... Se mancasse un perito... nn gli verrebbe mai assegnato un incidente
156THEN
157	INSERT INTO perito_incidente
158	SELECT id, NEW.Numero FROM PERITO
159	WHERE id NOT IN (SELECT DISTINC id_perito from perito_incidente)
160	LIMIT 1;
161	
162	
163ELSE
164		
165	INSERT INTO PERITO_INCIDENTE
166	select ID_PERITO, NEW.numero
167	FROM perito_incidente
168	group by id_perito
169	having count(*) <= all(select count(*) from perito_incidente group by id_perito)
170	limit 1;