1. Importe de una Factura
SELECT SUM(CANTIDAD*PRECIO) AS 'Importe' FROM LINEAS L, PRODUCTOS P WHERE NFACTURA=3 AND L.NPRODUCTO=P.NPRODUCTO;
2. Importe de las Facturas del Cliente Nº 1
SELECT SUM(CANTIDAD*PRECIO) AS 'IMPORTE DEL CLIENTE Nº1' FROM LINEAS L, PRODUCTOS P WHERE NFACTURA IN ( SELECT NFACTURA FROM FACTURAS WHERE NCLIENTE=1 ) AND L.NPRODUCTO=P.NPRODUCTO;
3. Facturación de Clientes de Madrid en Abril
A) Media de Facturación Agrupada por Clientes
SELECT AVG(SUM(CANTIDAD*PRECIO)) AS 'MEDIA MADRID' FROM LINEAS L, PRODUCTOS P WHERE NFACTURA IN ( SELECT NFACTURA FROM FACTURAS WHERE FECHA BETWEEN '2013-04-01' AND '2013-04-30' AND NCLIENTE IN( SELECT NCLIENTE FROM CLIENTES WHERE CP LIKE '28%' ) ) GROUP BY NCLIENTE;
B) Media de Facturación por Cliente
SELECT AVG(SUM(CANTIDAD*PRECIO)) AS 'MEDIA FAC POR CLIENTE' FROM LINEAS L, PRODUCTOS P, FACTURAS F WHERE FECHA BETWEEN '2013-04-01' AND '2013-04-30' AND L.NFACTURA IN ( SELECT NFACTURA FROM FACTURAS WHERE NCLIENTE IN (SELECT NCLIENTE FROM CLIENTES WHERE CP LIKE '28%')) AND L.NPRODUCTO=P.NPRODUCTO AND L.NFACTURA=F.NFACTURA GROUP BY NCLIENTE;
C) Media de Facturación Total
Primero, se crea una vista para simplificar la consulta:
CREATE VIEW DATOSFAC AS SELECT F.NFACTURA, SUM(CANTIDAD*PRECIO) AS 'IMPORTE', NCLIENTE, FECHA, COUNT(L.NPRODUCTO) AS 'NºLINEAS' FROM LINEAS L, PRODUCTOS P, FACTURAS F WHERE L.NFACTURA=F.NFACTURA AND P.NPRODUCTO=L.NPRODUCTO GROUP BY NFACTURA ORDER BY IMPORTE;
Luego, se realiza la consulta sobre la vista:
SELECT AVG(IMPORTE) AS 'MEDIA DE FACTURACION' FROM DATOSFAC WHERE NCLIENTE IN( SELECT NCLIENTE FROM CLIENTES WHERE CIUDAD= 'MADRID' ) AND FECHA BETWEEN '2013-04-01' AND '2013-04-30';
4. Clientes con Facturación Superior a 2500 en Abril
A) Clientes Preferentes
SELECT NCLIENTE, SUM(CANTIDAD*PRECIO) AS 'CLIENTES PREFERENTES' FROM LINEAS L, PRODUCTOS P, FACTURAS F WHERE FECHA BETWEEN '2013-04-01' AND '2013-04-30' AND L.NPRODUCTO=P.NPRODUCTO AND F.NFACTURA=L.NFACTURA GROUP BY NCLIENTE HAVING SUM(CANTIDAD*PRECIO)>2500;
B) Clientes con Facturación Superior a la Media
Primero, se crea una vista con los datos del cliente y su facturación:
CREATE VIEW CLIENTEFAC AS SELECT C.NCLIENTE,NOMBRE,CP,CIUDAD, SUM(CANTIDAD*PRECIO) AS 'FACTURACION' FROM CLIENTES C, LINEAS L , PRODUCTOS P, FACTURAS F WHERE FECHA BETWEEN '2013-04-01' AND '2013-04-30' AND C.NCLIENTE=F.NCLIENTE AND F.NFACTURA=L.NFACTURA AND L.NPRODUCTO=P.NPRODUCTO GROUP BY C.NCLIENTE;
Luego, se realiza la consulta sobre la vista (Nota: Falta la comparación con la media en el código original):
SELECT * FROM CLIENTEFAC;
5. Facturas con Importe Mayor a 2500€
SELECT NFACTURA , SUM(CANTIDAD*PRECIO) AS 'IMPORTE' FROM LINEAS L, PRODUCTOS P WHERE L.NPRODUCTO=P.NPRODUCTO GROUP BY NFACTURA HAVING SUM(CANTIDAD*PRECIO)>=2500;
6. Productos con Precios por Encima de la Media
SELECT COUNT(NPRODUCTO) FROM PRODUCTOS P WHERE PRECIO > ( SELECT AVG(PRECIO) FROM PRODUCTOS );
7. Consultas sobre Productos y Facturas
A) Número de Productos con Precio Mayor a 100€
SELECT COUNT(NPRODUCTO) AS 'NºDE PRODUCTOS' FROM PRODUCTOS WHERE PRECIO>100;
B) Número Medio de Facturas por Cliente
SELECT COUNT(NFACTURA) FROM FACTURAS WHERE NFACTURA IN( SELECT NCLIENTE FROM CLIENTES );
8. Clientes con Menos de 10 Facturas en 2013
SELECT NCLIENTE ,COUNT(NFACTURA) AS 'Nº DE FACTURAS' FROM FACTURAS WHERE FECHA BETWEEN '2013-01-01' AND '2013-12-30' GROUP BY NCLIENTE HAVING COUNT(NFACTURA)<10;
9. Número de Facturas por Ciudad en Octubre
SELECT CIUDAD, COUNT(NFACTURA) FROM CLIENTES C, FACTURAS F WHERE FECHA BETWEEN '2013-10-01' AND '2013-10-31' AND C.NCLIENTE=F.NCLIENTE GROUP BY CIUDAD;
10. Importe Medio de las Facturas de Pepe
SELECT AVG(IMPORTE) FROM DATOSFAC WHERE NCLIENTE IN( SELECT NCLIENTE FROM CLIENTES WHERE NOMBRE ='PP' );
11. Listado de Facturas con Importe y Número de Productos
SELECT * FROM DATOSFAC;
12. Número de Productos Adquiridos en Madrid en 2013
SELECT COUNT (NPRODUCTO) FROM PRODUCTOS WHERE NPRODUCTO IN( SELECT NPRODUCTO FROM LINEAS WHERE NFACTURA IN (SELECT NFACTURA FROM FACTURAS WHERE FECHA BETWEEN '2013-01-01' AND '2013-12-30' AND NCLIENTE IN( SELECT NCLIENTE FROM CLIENTES WHERE CIUDAD= 'MADRID')));
13. Listado de Clientes con su Número de Reserva
SELECT NCLIENTE, COUNT(*) FROM RESERVAS GROUP BY NCLIENTE;
14. Listado de Clientes con sus Habitaciones
SELECT NCLIENTE, NHAB FROM RESERVAS R, RES_HAB RE WHERE R.NRESERVA=RE.NRESERVA ORDER BY NCLIENTE;
15. Número de Reservas por Habitación
SELECT NHAB, COUNT(*) FROM RESERVAS R, RES_HAB RE WHERE R.NRESERVA=RE.NRESERVA GROUP BY NHAB;
16. Facturación de Habitaciones en 2013
SELECT NHAB, SUM(PRECIO*NOCHES) AS 'IMPORTE' FROM HABITACIONES H, RES_HAB RE, RESERVAS R WHERE FECHA BETWEEN '2013-01-01' AND '2013-12-30' AND H.NHAB=RE.NHAB AND RE.NRESERVA=R.NRESERVA GROUP BY NHAB;
17. Facturación Media de una Habitación de Hotel
Primero, se crea una vista para simplificar la consulta:
CREATE VIEW DATOSFACPORHAB AS SELECT H.NHAB, CATEGORIA, SUM(NOCHES*PRECIO) AS FACTURACION FROM HABITACIONES H, RES_HAB RE, RESERVAS R WHERE H.NHAB= RE.NHAB AND RE.NRESERVA=R.NRESERVA GROUP BY H.NHAB;
Luego, se realiza la consulta sobre la vista:
SELECT NHAB, AVG(FACTURACION) FROM DATOSFACPORHAB GROUP BY NHAB;
18. Listado de Clientes con su Facturación
SELECT NCLIENTE, SUM(PRECIO*NOCHES) AS 'FACTURACION' FROM CLIENTES C LEFT JOIN RESERVAS R ON C.NCLIENTE=R.NCLIENTE LEFT JOIN RES_HAB RE ON R.NRESERVA=RE.NRESERVA LEFT JOIN HABITACIONES H ON RE.NHAB=H.NHAB GROUP BY C.NCLIENTE;
19. Clientes con Facturación por Encima de la Media
Primero, se crea una vista para manejar los valores nulos:
CREATE VIEW FACPORCLI AS SELECT C.NCLIENTE, COALESCE(SUM(PRECIO*NOCHES),0) AS 'FACTURACION' FROM CLIENTES C LEFT JOIN RESERVAS R ON C.NCLIENTE=R.NCLIENTE LEFT JOIN RES_HAB RE ON R.NRESERVA=RE.NRESERVA LEFT JOIN HABITACIONES H ON RE.NHAB=H.NHAB GROUP BY NCLIENTE;
Luego, se realiza la consulta sobre la vista:
SELECT NCLIENTE, FACTURACION FROM FACPORCLI WHERE FACTURACION > ( SELECT AVG(FACTURACION) FROM FACPORCLI );
20. Número de Reservas por Categoría de Habitación
SELECT CATEGORIA, COUNT (NRESERVA) AS 'NumReserva' FROM HABITACIONES H LEFT JOIN RES_HAB RE ON H.NHAB = RE.NHAB LEFT JOIN RESERVAS R ON RE.NRESERVA = R.NRESERVA GROUP BY CATEGORIA;
21. Precio Medio de una Reserva en Noviembre de 2012
SELECT R.NRESERVA AS NRES, SUM(PRECIO*NOCHES) AS FACTURA FROM RESERVAS R, RES_HAB RE, HABITACIONES H WHERE R.NRESERVA=RE.NRESERVA AND RE.NHAB=H.NHAB AND R.FECHA BETWEEN '2012-11-01' AND '2012-11-30' GROUP BY NRES;
22. Facturación Total de Cada Habitación
SELECT RE.NHAB, COALESCE(SUM(NOCHES*PRECIO),0) AS FACTURACION FROM HABITACIONES H LEFT JOIN RES_HAB RE ON H.NHAB=RE.NHAB LEFT JOIN RESERVAS R ON R.NRESERVA= RE.NRESERVA GROUP BY H.NHAB;
23. Habitación con Más Reservas en la Historia
SELECT H.NHAB, COUNT(RE.NRESERVA) FROM HABITACIONES H LEFT JOIN RES_HAB RE ON H.NHAB=RE.NHAB GROUP BY H.NHAB HAVING COUNT(RE.NRESERVA)=( SELECT MAX(RESERVA_COUNT) FROM (SELECT COUNT(RE.NRESERVA) AS RESERVA_COUNT FROM HABITACIONES H LEFT JOIN RES_HAB RE ON H.NHAB = RE.NHAB GROUP BY H.NHAB) AS SUBQUERY);
24. Precio Medio de una Habitación en Noviembre de 2013
Primero se crea la vista:
CREATE VIEW FACNOV2013 AS SELECT NRESERVA, SUM(PRECIO*NOCHES) AS 'IMPORTE' FROM RESERVAS R, RES_HAB RE, HABITACIONES H WHERE FECHA BETWEEN '2013-11-01' AND '2013-11-30' AND R.NRESERVA=RE.NRESERVA AND RE.NHAB=H.NHAB GROUP BY NRESERVA;
Luego, se realiza la consulta (Nota: Falta calcular la media en el código original):
SELECT * FROM FACNOV2013;