QUERY 2
DROP PROCEDURE IF EXISTS Q2;
DELIMITER //
CREATE PROCEDURE Q2(in val char(3))
BEGIN
SELECT * FROM PERSONA WHERE CF like Concat(val,'%');
END //
DELIMITER ;


QUERY 3
DROP PROCEDURE IF EXISTS Q3;
DELIMITER //
CREATE PROCEDURE Q3(in val int)
BEGIN
SELECT * FROM QUALIFICA WHERE AnnoIntroduzione=val;
END//
DELIMITER ;

QUERY 4
DROP PROCEDURE IF EXISTS Q4;
DELIMITER //
CREATE PROCEDURE Q4(in val varchar(255))
BEGIN
SELECT p.CF,p.Nome,p.Cognome FROM PERSONA p,QUALIFICA q,ATTESTATO a WHERE p.CF=a.Persona AND a.QUALIFICA=q.CODICE AND q.Nome=val;
END //
DELIMITER ;
CALL Q4("Consulente");

QUERY 5
DROP VIEW IF EXISTS V1;
CREATE VIEW V1 AS
SELECT COUNT(*) AS NumeroC, year(Data) as Anno
FROM ATTESTATO GROUP BY Anno;


DROP PROCEDURE IF EXISTS Q5;
DELIMITER //
CREATE PROCEDURE Q5(in anno_in int)
BEGIN
SELECT * from v1 where Anno>=anno_in;
END//
DELIMITER ;


PUNTO 6;
DROP VIEW IF EXISTS V2;
CREATE VIEW V2 AS
SELECT COUNT(*) as Contatore,Year(data) as Anno FROM ATTESTATO GROUP BY Anno;
Select min(Anno) FROM V2;

DROP TABLE IF EXISTS t1;
CREATE TABLE t1(
id int Auto_increment primary key,
Anno int,
Contatore int);

DROP PROCEDURE IF EXISTS Q6;
DELIMITER //
CREATE PROCEDURE Q6()
BEGIN
DECLARE min_anno int DEFAULT 0;
DECLARE max_anno int DEFAULT 0;
SELECT Year(CURDATE()) into max_anno;
SELECT min(Anno) into min_anno
FROM V2;

while(min_anno<=max_anno) DO
INSERT INTO t1 (Anno,Contatore) VALUES (min_anno,'0');
SET min_anno=min_anno+1;
END WHILE;

DROP VIEW IF EXISTS V3;
CREATE VIEW V3 AS SELECT t1.Anno,IFNULL(v2.Contatore,0) as Cont FROM t1 LEFT JOIN v2 ON t1.Anno=v2.anno;
SELECT AVG(Cont) FROM v3;
END //

DELIMITER ;
CALL Q6();


PUNTO 7;
DROP PROCEDURE IF EXISTS Q7;
DELIMITER //
CREATE PROCEDURE Q7(in anno_in int)
BEGIN
SELECT t1.Anno,IFNULL(v1.NumeroC,0) as NumeroQualifiche FROM t1 left join v1 on t1.Anno=v1.Anno where t1.Anno>=anno_in;
END//
DELIMITER ;
CALL Q7();


PUNTO 8;
DROP VIEW IF EXISTS prova1;
Create view prova1 as SELECT COUNT(*) as Cont,Q.Importanza as Importanza FROM ATTESTATO A JOIN QUALIFICA Q ON A.QUALIFICA=Q.CODICE GROUP BY Importanza;
Select Cont as ContaPersone,max(Importanza) FROM prova1;


PUNTO 9;
DROP VIEW IF EXISTS Q9;
CREATE VIEW Q9 AS SELECT A.PERSONA FROM ATTESTATO A LEFT JOIN USO U ON A.PERSONA=U.PERSONA AND A.QUALIFICA=U.QUALIFICA WHERE U.Persona is NULL GROUP BY PERSONA;

PUNTO 10;
DROP VIEW IF EXISTS Q10;
CREATE VIEW Q10 AS
SELECT A.Persona, A.Qualifica, Year(A.Data) AS Attestato, Year(U.Data) As Utilizzo, (Year(U.Data)-Year(A.Data)) AS Anni_inattivita FROM ATTESTATO A JOIN USO U ON A.PERSONA=U.PERSONA AND A.QUALIFICA=U.QUALIFICA;
SELECT AVG(Anni_inattivita) FROM Q10;