SHARE
TWEET

Untitled

a guest Feb 16th, 2020 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. QUERY 2
  2. DROP PROCEDURE IF EXISTS Q2;
  3. DELIMITER //
  4. CREATE PROCEDURE Q2(in val char(3))
  5. BEGIN
  6. SELECT * FROM PERSONA WHERE CF like Concat(val,'%');
  7. END //
  8. DELIMITER ;
  9.  
  10.  
  11. QUERY 3
  12. DROP PROCEDURE IF EXISTS Q3;
  13. DELIMITER //
  14. CREATE PROCEDURE Q3(in val int)
  15. BEGIN
  16. SELECT * FROM QUALIFICA WHERE AnnoIntroduzione=val;
  17. END//
  18. DELIMITER ;
  19.  
  20. QUERY 4
  21. DROP PROCEDURE IF EXISTS Q4;
  22. DELIMITER //
  23. CREATE PROCEDURE Q4(in val varchar(255))
  24. BEGIN
  25. 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;
  26. END //
  27. DELIMITER ;
  28. CALL Q4("Consulente");
  29.  
  30. QUERY 5
  31. DROP VIEW IF EXISTS V1;
  32. CREATE VIEW V1 AS
  33. SELECT COUNT(*) AS NumeroC, year(Data) as Anno
  34. FROM ATTESTATO GROUP BY Anno;
  35.  
  36.  
  37. DROP PROCEDURE IF EXISTS Q5;
  38. DELIMITER //
  39. CREATE PROCEDURE Q5(in anno_in int)
  40. BEGIN
  41. SELECT * from v1 where Anno>=anno_in;
  42. END//
  43. DELIMITER ;
  44.  
  45.  
  46. PUNTO 6;
  47. DROP VIEW IF EXISTS V2;
  48. CREATE VIEW V2 AS
  49. SELECT COUNT(*) as Contatore,Year(data) as Anno FROM ATTESTATO GROUP BY Anno;
  50. Select min(Anno) FROM V2;
  51.  
  52. DROP TABLE IF EXISTS t1;
  53. CREATE TABLE t1(
  54. id int Auto_increment primary key,
  55. Anno int,
  56. Contatore int);
  57.  
  58. DROP PROCEDURE IF EXISTS Q6;
  59. DELIMITER //
  60. CREATE PROCEDURE Q6()
  61. BEGIN
  62. DECLARE min_anno int DEFAULT 0;
  63. DECLARE max_anno int DEFAULT 0;
  64. SELECT Year(CURDATE()) into max_anno;
  65. SELECT min(Anno) into min_anno
  66. FROM V2;
  67.  
  68. while(min_anno<=max_anno) DO
  69. INSERT INTO t1 (Anno,Contatore) VALUES (min_anno,'0');
  70. SET min_anno=min_anno+1;
  71. END WHILE;
  72.  
  73. DROP VIEW IF EXISTS V3;
  74. CREATE VIEW V3 AS SELECT t1.Anno,IFNULL(v2.Contatore,0) as Cont FROM t1 LEFT JOIN v2 ON t1.Anno=v2.anno;
  75. SELECT AVG(Cont) FROM v3;
  76. END //
  77.  
  78. DELIMITER ;
  79. CALL Q6();
  80.  
  81.  
  82. PUNTO 7;
  83. DROP PROCEDURE IF EXISTS Q7;
  84. DELIMITER //
  85. CREATE PROCEDURE Q7(in anno_in int)
  86. BEGIN
  87. SELECT t1.Anno,IFNULL(v1.NumeroC,0) as NumeroQualifiche FROM t1 left join v1 on t1.Anno=v1.Anno where t1.Anno>=anno_in;
  88. END//
  89. DELIMITER ;
  90. CALL Q7();
  91.  
  92.  
  93. PUNTO 8;
  94. DROP VIEW IF EXISTS prova1;
  95. 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;
  96. Select Cont as ContaPersone,max(Importanza) FROM prova1;
  97.  
  98.  
  99. PUNTO 9;
  100. DROP VIEW IF EXISTS Q9;
  101. 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;
  102.  
  103. PUNTO 10;
  104. DROP VIEW IF EXISTS Q10;
  105. CREATE VIEW Q10 AS
  106. 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;
  107. SELECT AVG(Anni_inattivita) FROM Q10;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top