Solucion á practica DDL sobre vistas

Posible solución

 1 --Apartado 1
 2 CREATE VIEW AP1 AS
 3 SELECT NOMCE, NOMDE 
 4 FROM TCENTR C,TDEPTO D
 5 WHERE D.NUMCE=C.NUMCE
 6 ORDER BY C.NOMCE;
 7 
 8 --Apartado 2
 9 CREATE VIEW AP2 AS
10 SELECT * 
11 FROM TDEPTO 
12 WHERE DEPDE IS NULL
13 
14 --Apartado 3
15 CREATE VIEW AP3 AS
16 SELECT CONCAT_WS (' ','EN EL DEPARTAMENTO ',D.NOMDE, ' TRABAJAN 	', COUNT(*), ' EMPLEADOS') 
17 FROM TEMPLE E,TDEPTO D
18 WHERE D.NUMDE = E.NUMDE
19 GROUP BY E.NUMDE
20 
21 --Apartado 4
22 CREATE VIEW AP4 AS  
23 SELECT NUMEM, NUMDE, EXTEL, NOMEM 
24 FROM   TEMPLE 
25 WHERE  COMIS IS NOT NULL AND COMIS > 0; 
26 
27 --Apartado 5
28 -- ASÍ ES PARA LOS QUE LLEVAN 30 O MAS AÑOS EN LA EMPRESA
29 -- SI FUERA PARA LOS QUE JUSTO CUMPLEN 30 AÑOS EN LA EMPRESA HABRÍA QUE CAMBIAR < POR =
30 CREATE VIEW AP5 AS  
31 SELECT *
32 FROM TEMPLE E
33 WHERE YEAR (FECIN) < YEAR (NOW()) - 30
34 
35 --Apartado 6
36 -- 365 dias x 40 años = 14.600
37 CREATE VIEW VISTA6 AS
38 SELECT *
39 FROM TEMPLE E
40 WHERE TIMESTAMPDIFF(DAY, fecin, CURDATE()) > 14.600
41 
42 --Apartado 7
43 CREATE VIEW AP7 AS  
44 SELECT * 
45 FROM TEMPLE E
46 WHERE SALAR >= (SELECT AVG(SALAR) FROM temple e)
47 
48 --Apartado 8
49 CREATE VIEW AP8 AS
50 SELECT E.NUMDE AS Departamento, D.NOMDE AS Nombre, COUNT(*)AS 'Nº Empleados', 
51 SUM(SALAR) AS Total_Salario, SUM(COMIS) AS Total_Comision 
52 FROM TEMPLE E,TDEPTO D
53 WHERE D.NUMDE = E.NUMDE
54 GROUP BY E.NUMDE
55 
56 --Apartado 9
57 CREATE VIEW AP9 AS
58 SELECT D.NOMDE 
59 FROM TEMPLE E,TDEPTO D
60 WHERE D.NUMDE = E.NUMDE
61 GROUP BY E.NUMDE
62 HAVING COUNT(*)> 3
63 
64 --Apartado 10
65 CREATE VIEW AP10
66 SELECT * 
67 FROM TEMPLE E
68 WHERE numde in (100,110)
69 WITH CHECK OPTION