Joselv: Página creada con «= Posible solución = --Apartado 1 CREATE VIEW AP1 AS SELECT NOMCE, NOMDE FROM TCENTR C,TDEPTO D WHERE D.NUM…»

2021-12-15T11:11:02Z

Página creada con «= Posible solución = <syntaxhighlight lang="sql" line enclose="div" highlight="" > --Apartado 1 CREATE VIEW AP1 AS SELECT NOMCE, NOMDE FROM TCENTR C,TDEPTO D WHERE D.NUM…»

Página nueva

= Posible solución =

<syntaxhighlight lang="sql" line enclose="div" highlight="" >
--Apartado 1
CREATE VIEW AP1 AS
SELECT NOMCE, NOMDE
FROM TCENTR C,TDEPTO D
WHERE D.NUMCE=C.NUMCE
ORDER BY C.NOMCE;

--Apartado 2
CREATE VIEW AP2 AS
SELECT *
FROM TDEPTO
WHERE DEPDE IS NULL

--Apartado 3
CREATE VIEW AP3 AS
SELECT CONCAT_WS (' ','EN EL DEPARTAMENTO ',D.NOMDE, ' TRABAJAN ', COUNT(*), ' EMPLEADOS')
FROM TEMPLE E,TDEPTO D
WHERE D.NUMDE = E.NUMDE
GROUP BY E.NUMDE

--Apartado 4
CREATE VIEW AP4 AS
SELECT NUMEM, NUMDE, EXTEL, NOMEM
FROM TEMPLE
WHERE COMIS IS NOT NULL AND COMIS > 0;

--Apartado 5
-- ASÍ ES PARA LOS QUE LLEVAN 30 O MAS AÑOS EN LA EMPRESA
-- SI FUERA PARA LOS QUE JUSTO CUMPLEN 30 AÑOS EN LA EMPRESA HABRÍA QUE CAMBIAR < POR =
CREATE VIEW AP5 AS
SELECT *
FROM TEMPLE E
WHERE YEAR (FECIN) < YEAR (NOW()) - 30

--Apartado 6
-- 365 dias x 40 años = 14.600
CREATE VIEW VISTA6 AS
SELECT *
FROM TEMPLE E
WHERE TIMESTAMPDIFF(DAY, fecin, CURDATE()) > 14.600

--Apartado 7
CREATE VIEW AP7 AS
SELECT *
FROM TEMPLE E
WHERE SALAR >= (SELECT AVG(SALAR) FROM temple e)

--Apartado 8
CREATE VIEW AP8 AS
SELECT E.NUMDE AS Departamento, D.NOMDE AS Nombre, COUNT(*)AS 'Nº Empleados',
SUM(SALAR) AS Total_Salario, SUM(COMIS) AS Total_Comision
FROM TEMPLE E,TDEPTO D
WHERE D.NUMDE = E.NUMDE
GROUP BY E.NUMDE

--Apartado 9
CREATE VIEW AP9 AS
SELECT D.NOMDE
FROM TEMPLE E,TDEPTO D
WHERE D.NUMDE = E.NUMDE
GROUP BY E.NUMDE
HAVING COUNT(*)> 3

--Apartado 10
CREATE VIEW AP10
SELECT *
FROM TEMPLE E
WHERE numde in (100,110)
WITH CHECK OPTION

</syntaxhighlight>

Solucion á practica DDL sobre vistas - Historial de revisiones

Joselv: Página creada con «= Posible solución = --Apartado 1 CREATE VIEW AP1 AS SELECT NOMCE, NOMDE FROM TCENTR C,TDEPTO D WHERE D.NUM…»