Scripst Creación Tablas
CREATE TABLE PRODUCTOS (
PRODUCTO_ID INTEGER NOT NULL PRIMARY KEY,
NOMBRE VARCHAR2 (25) NOT NULL,
PRECIO INTEGER NOT NULL);
CREATE TABLE CLIENTES (
CLIENTE_ID INTEGER NOT NULL PRIMARY KEY,
NOMBRES VARCHAR2 (25) NOT NULL,
APELLIDOS VARCHAR2 (25) NOT NULL,
FECHA_NACIMIENTO DATE NOT NULL);
CREATE TABLE PEDIDOS (
PEDIDO_ID INTEGER NOT NULL PRIMARY KEY,
CLIENTE_ID INTEGER NOT NULL,
FECHA DATE DEFAULT SYSDATE NOT NULL,
FECHA_PREVISTA DATE DEFAULT NULL,
GASTOS_ENVIO NUMBER DEFAULT 0,
CONSTRAINT FK_CLIENTE_ID FOREIGN KEY (CLIENTE_ID) REFERENCES CLIENTES (CLIENTE_ID));
CREATE TABLE DETALLES (
DETALLE_ID INTEGER NOT NULL PRIMARY KEY,
PEDIDO_ID INTEGER NOT NULL,
PRODUCTO_ID INTEGER NOT NULL,
CANTIDAD INTEGER NOT NULL,
CONSTRAINT FK_PEDIDO_ID FOREIGN KEY (PEDIDO_ID) REFERENCES PEDIDOS (PEDIDO_ID),
CONSTRAINT FK_PRODUCTO_ID FOREIGN KEY (PRODUCTO_ID) REFERENCES PRODUCTOS
(PRODUCTO_ID),
CONSTRAINT UQ_PEDIDO_PRODUCTO UNIQUE (PEDIDO_ID, PRODUCTO_ID));
Estos scripts deberán crear un modelo como este:
-- Inserción de Datos en Oracle
BEGIN
INSERT INTO PRODUCTOS VALUES ( 1, 'Coca-Cola 2L', 2400);
INSERT INTO PRODUCTOS VALUES ( 2, 'Doritos', 1000);
INSERT INTO PRODUCTOS VALUES ( 3, 'Salchicha', 3600);
INSERT INTO PRODUCTOS VALUES ( 4, 'Pan', 500);
INSERT INTO PRODUCTOS VALUES ( 5, 'Queso', 1000);
INSERT INTO PRODUCTOS VALUES ( 6, 'Sandia', 8000);
INSERT INTO PRODUCTOS VALUES ( 7, 'Leche 1L', 4563);
INSERT INTO PRODUCTOS VALUES ( 8, 'Atun', 1800);
INSERT INTO PRODUCTOS VALUES ( 9, 'Pescado', 7856);
INSERT INTO PRODUCTOS VALUES ( 10, 'Cicla Estatica', 18000);
INSERT INTO PRODUCTOS VALUES ( 11, 'Camiseta', 12000);
INSERT INTO PRODUCTOS VALUES ( 12, 'Pantalon', 7800);
INSERT INTO PRODUCTOS VALUES ( 13, 'Papaya', 1400);
INSERT INTO PRODUCTOS VALUES ( 14, 'Agua en Bolsa', 1800);
INSERT INTO PRODUCTOS VALUES ( 15, 'Red Bull', 1200);
INSERT INTO PRODUCTOS VALUES ( 16, 'Caja Chiclets Pequeña', 200);
INSERT INTO CLIENTES VALUES (147, 'Tom', 'Perez', '01/01/2000 ');
INSERT INTO CLIENTES VALUES (852, 'Simon', 'Bolivar', '01/01/2000');
INSERT INTO CLIENTES VALUES (456, 'Mark', 'Zuckerberg', '01/01/2000');
INSERT INTO CLIENTES VALUES (789, 'Drew','Barrymore','01/01/2006');
INSERT INTO CLIENTES VALUES (258, 'Mark','Hoppus','01/01/2000');
INSERT INTO CLIENTES VALUES (963, 'Britney','Spears','01/01/2000');
INSERT INTO CLIENTES VALUES (369, 'John','Forbes Nash','01/01/2000');
INSERT INTO CLIENTES VALUES (159, 'Tom','Delonge','01/01/2000');
INSERT INTO CLIENTES VALUES (753, 'Sergey','Brin','01/01/2000');
INSERT INTO CLIENTES VALUES (123, 'Simon','Perez','01/01/2000');
INSERT INTO CLIENTES VALUES (741, 'Larry','Page','01/01/2000');
INSERT INTO PEDIDOS VALUES ( 1, 852, (SYSDATE-90), (SYSDATE-80), 50);
INSERT INTO PEDIDOS VALUES ( 2, 789, (SYSDATE-90), (SYSDATE-80), 50);
INSERT INTO PEDIDOS VALUES ( 3, 753, (SYSDATE-90), (SYSDATE-80), 50);
INSERT INTO PEDIDOS VALUES ( 4, 369, (SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 5, 123, (SYSDATE-90), (SYSDATE-80), 50);
INSERT INTO PEDIDOS VALUES ( 6, 456, (SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 7, 159, (SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 8, 741, (SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 9, 963, (SYSDATE-90), (SYSDATE-80), 50);
INSERT INTO PEDIDOS VALUES ( 10, 147,(SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 11, 258, '31/10/2016' , NULL, 0);
INSERT INTO PEDIDOS VALUES ( 12, 789,(SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 13, 789,(SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 14, 789,(SYSDATE-90), NULL, 0);
INSERT INTO PEDIDOS VALUES ( 15, 789,(SYSDATE), NULL, 0);
INSERT INTO DETALLES VALUES (13, 5, 1, 5);
INSERT INTO DETALLES VALUES (14, 5, 2, 10);
INSERT INTO DETALLES VALUES (11, 5, 3, 12);
INSERT INTO DETALLES VALUES (10, 5, 4, 8);
INSERT INTO DETALLES VALUES (25, 6, 5, 13);
INSERT INTO DETALLES VALUES (32, 8, 6, 4);
INSERT INTO DETALLES VALUES (28, 6, 7, 5);
INSERT INTO DETALLES VALUES (30, 8, 8, 600);
INSERT INTO DETALLES VALUES (2, 1, 9, 69);
INSERT INTO DETALLES VALUES (4, 2, 10, 15);
INSERT INTO DETALLES VALUES (41, 12, 6, 22);
INSERT INTO DETALLES VALUES (6, 3, 7, 11);
INSERT INTO DETALLES VALUES (34, 9, 12, 10);
INSERT INTO DETALLES VALUES (35, 9, 11, 65);
INSERT INTO DETALLES VALUES (3, 1, 10, 12);
INSERT INTO DETALLES VALUES (31, 8, 9, 65);
INSERT INTO DETALLES VALUES (36, 10, 8, 78);
INSERT INTO DETALLES VALUES (40, 11, 9, 92);
INSERT INTO DETALLES VALUES (38, 11, 6, 12);
INSERT INTO DETALLES VALUES (37, 10, 3, 32);
INSERT INTO DETALLES VALUES (42, 13, 1, 3);
INSERT INTO DETALLES VALUES (26, 6, 2, 45);
INSERT INTO DETALLES VALUES (43, 14, 4, 5);
INSERT INTO DETALLES VALUES (27, 6, 1, 6);
INSERT INTO DETALLES VALUES (44, 15, 12, 7);
INSERT INTO DETALLES VALUES (39, 11, 13, 8);
INSERT INTO DETALLES VALUES (1, 1, 14, 9);
INSERT INTO DETALLES VALUES (5, 3, 15, 9);
INSERT INTO DETALLES VALUES (7, 3, 10, 6);
INSERT INTO DETALLES VALUES (29, 7, 9, 7);
INSERT INTO DETALLES VALUES (33, 9, 10, 8);
INSERT INTO DETALLES VALUES (8, 4, 8, 9);
INSERT INTO DETALLES VALUES (9, 4, 7, 15);
INSERT INTO DETALLES VALUES (24, 5, 5, 5);
INSERT INTO DETALLES VALUES (23, 5, 6, 6);
INSERT INTO DETALLES VALUES (22, 5, 7, 7);
INSERT INTO DETALLES VALUES (21, 5, 8, 8);
INSERT INTO DETALLES VALUES (20, 5, 9, 5);
INSERT INTO DETALLES VALUES (19, 5, 10, 6);
INSERT INTO DETALLES VALUES (18, 5, 11, 7);
INSERT INTO DETALLES VALUES (17, 5, 12, 8);
INSERT INTO DETALLES VALUES (16, 5, 13, 5);
INSERT INTO DETALLES VALUES (15, 5, 14, 6);
INSERT INTO DETALLES VALUES (12, 5, 15, 7);
COMMIT;
END;
Una vez realizados los inserts las tablas deberían quedar así:
Productos = 16 Registros
Clientes = 11 Registros
Pedidos = 15 Registros
Detalles = 44 Registros
Esta información nos debería reflejar datos como estos:
Solución Taller
--1. Número e importe de todos los pedidos realizados en los últimos 60 días
SELECT DET.PEDIDO_ID, SUM(PRECIO * CANTIDAD) AS IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE FECHA BETWEEN (SYSDATE-60) AND SYSDATE
GROUP BY DET.PEDIDO_ID
La estructura de pedidos solo cuenta con un registro realizado en los últimos 60 días
que es el pedido 15 con fecha en ese rango
--2. Número e importe de los pedidos cuyo importe esté entre 100 y 200 E
SELECT DET.PEDIDO_ID, SUM(PRECIO * CANTIDAD) AS IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY DET.PEDIDO_ID
HAVING SUM(PRECIO * CANTIDAD) BETWEEN 100 AND 200
Este query devuelve respuesta de No rows returned, porque efectivamente no existen
pedidos cuyo importe esté entre estos valores, pues el importe más bajo almacenado
actualmente es de 2500
--3. Código y nombre de los productos ordenados ascendentemente por precio y
nombre
SELECT PRODUCTO_ID, NOMBRE
FROM PRODUCTOS
ORDER BY PRECIO, NOMBRE
Este query devuelve los 16 productos registrados actualmente, ordenados por nombre y
precio, donde se ve que el producto más barato son los chiclets y el más alto la cicla.
--4. Clientes cuyo apellido sea Perez
SELECT *
FROM CLIENTES
WHERE APELLIDOS LIKE '%Perez'
Solo existen dos clientes cuyos apellidos son los solicitados, Tom y Simon con ID 147 y
123 respectivamente
--5. Número total de productos que vende la empresa (en la columna debe aparecer "N°
de productos")
SELECT COUNT(*) AS "N° de productos"
FROM PRODUCTOS
La cantidad de 16 registros que existen actualmente
--6. Número total de productos que no han sido pedidos
SELECT COUNT(*) AS Cantidad
FROM PRODUCTOS PROD
WHERE (NOT EXISTS
(SELECT PRODUCTO_ID
FROM DETALLES DET
WHERE DET.PRODUCTO_ID = PROD.PRODUCTO_ID))
Este query nos devuelve 1 porque el producto: Caja Chiclets Pequeña aún no ha sido
pedida
--7. De cada pedido, mostrar su número, importe y datos del cliente
SELECT DET.PEDIDO_ID, NOMBRES, APELLIDOS, SUM(PRECIO * CANTIDAD) AS
IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
GROUP BY DET.PEDIDO_ID, NOMBRES, APELLIDOS
Actualmente existen estos 15 pedidos con estos importes
--8. Código, nombre del cliente y número total de pedidos que hecho cada cliente,
ordenado de más a menos pedidos
SELECT PED.CLIENTE_ID, NOMBRES, APELLIDOS, COUNT(PEDIDO_ID) AS TOTAL
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
GROUP BY PED.CLIENTE_ID, NOMBRES, APELLIDOS
ORDER BY TOTAL DESC
Solo Drew Barrymore ha realizado más de un pedido, el resto han realizado de a uno
--9. Código, nombre del cliente y número total de pedidos que ha realizado cada cliente
durante 2016
SELECT PED.CLIENTE_ID, NOMBRES, COUNT(PEDIDO_ID) AS TOTAL
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
WHERE EXTRACT(YEAR FROM [Link]) = 2016
GROUP BY PED.CLIENTE_ID, NOMBRES
La estructura de pedidos solo cuenta con un pedido efectuado durante el año 2016 y es
el que realizó el cliente: Mark Hoppus
--10. Código, nombre y número total de pedidos de los clientes que han realizado más
de un pedido
SELECT PED.CLIENTE_ID, NOMBRES, COUNT(PED.PEDIDO_ID) AS TOTAL
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
GROUP BY PED.CLIENTE_ID, NOMBRES
HAVING COUNT(*)>1
Este query solo visualiza a Drew Barrymore que es la única cliente que ha efectuado
más de un pedido, tal como se analizaba en el query 8
--11. Para cada pedido mostrar su número, código del cliente y n° total de detalles que
tiene
SELECT PED.PEDIDO_ID, PED.CLIENTE_ID, COUNT(DET.PEDIDO_ID) AS "TOTAL
DETALLES"
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
GROUP BY PED.PEDIDO_ID, PED.CLIENTE_ID
ORDER BY "TOTAL DETALLES"
Para verificar que este query es correcto, basta no más con mirar cualquiera de los
pedidos y comprobar que lo reflejado en el total detalles coincide, al consultar ese
pedido individualmente, si tomamos el Pedido_Id = 11, dice que tienes 3 detalles
--12. Código de cliente, nombre de producto y cantidad total que ha pedido cada cliente
de cada producto
SELECT PED.CLIENTE_ID, NOMBRE, CANTIDAD AS "Cantidad Total"
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
Acá se refleja la totalidad de productos con sus respectivas cantidades solicitadas por
cada cliente
--13. Para cada cliente mostrar su código, nombre, número e importe de cada uno de
sus pedidos
SELECT CLI.CLIENTE_ID, (NOMBRES||' '||APELLIDOS) AS NOMBRE,
DET.PEDIDO_ID, SUM(PRECIO * CANTIDAD) AS IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
GROUP BY CLI.CLIENTE_ID, (NOMBRES||' '||APELLIDOS), DET.PEDIDO_ID
ORDER BY CLI.CLIENTE_ID
Aquí necesariamente hay que sumarizar, porque a excepción de los pedidos de Drew
Barrymore que hizo 5 pedidos por separado, los demás se hicieron sobre un mismo
pedido; de manera que si analizamos el PEDIDO 11
Tenemos que el pedido incluye
CANTIDAD PRODUCTO PRECIO TOTAL
12 Sandia $ 8000 = 96000
92 Pescado $ 7856 = 722752
8 Papaya $ 1400 = 11200
829952 = Sumatoria
--14. Para cada cliente menor de edad mostrar su código, nombre, el importe más alto,
el más bajo de los pedidos que ha realizado
SELECT CLI.CLIENTE_ID, [Link], (PRECIO * CANTIDAD) AS IMPORTE,
'ALTO' AS "PEDIDO ALTO/BAJO"
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PROD ON PROD.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM
FECHA_NACIMIENTO)<18
GROUP BY CLI.CLIENTE_ID, [Link], PED.PEDIDO_ID,(PRECIO *
CANTIDAD)
HAVING (PRECIO * CANTIDAD) = (SELECT MAX(PRECIO * CANTIDAD)
FROM DETALLES DET
INNER JOIN PRODUCTOS PROD ON PROD.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN PEDIDOS PED ON PED.PEDIDO_ID = DET.PEDIDO_ID
INNER JOIN CLIENTES CLIE ON CLIE.CLIENTE_ID = PED.CLIENTE_ID
WHERE CLIE.CLIENTE_ID = CLI.CLIENTE_ID
GROUP BY CLIE.CLIENTE_ID)
UNION ALL
SELECT CLI.CLIENTE_ID, [Link], (PRECIO * CANTIDAD) AS IMPORTE,
'BAJO' AS "PEDIDO ALTO/BAJO"
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PROD ON PROD.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM
FECHA_NACIMIENTO)<18
GROUP BY CLI.CLIENTE_ID, [Link], PED.PEDIDO_ID,(PRECIO *
CANTIDAD)
HAVING (PRECIO * CANTIDAD) = (SELECT MIN(PRECIO * CANTIDAD)
FROM DETALLES DET
INNER JOIN PRODUCTOS PROD ON PROD.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN PEDIDOS PED ON PED.PEDIDO_ID = DET.PEDIDO_ID
INNER JOIN CLIENTES CLIE ON CLIE.CLIENTE_ID = PED.CLIENTE_ID
WHERE CLIE.CLIENTE_ID = CLI.CLIENTE_ID
GROUP BY CLIE.CLIENTE_ID)
En este caso los resultados muestran el pedido más bajo y alto para el único cliente
menor de edad Drew Barrymore quien realizó 5 pedidos discriminados así:
· Pedido: 2 donde solicitó 5 Pan para un total de: 2500 es el pedido más bajo
· Pedido: 12 donde solicitó 22 Sandia para un total de: 176000
· Pedido: 13 donde solicitó 15 Cicla Estatica para un total de: 270000 es el pedido
más alto
· Pedido: 14 donde solicitó 3 Coca-Cola 2L para un total de: 7200
· Pedido: 15 donde solicitó 7 Pantalon para un total de: 54600
--15. Mostrar el código del producto, el n° de veces que ha sido pedido y la cantidad
total de unidades que se han pedido (los que no hayan sido pedidos colocar un cero)
SELECT PRODUCTO_ID, COUNT(PRODUCTO_ID) AS "N° Veces Pedido",
SUM(CANTIDAD) AS "Unidades Pedidas"
FROM DETALLES DET
GROUP BY PRODUCTO_ID
UNION ALL
SELECT PRODUCTO_ID, 0 AS "N° Veces Pedido", 0 AS "Unidades Pedidas"
FROM PRODUCTOS PRO
WHERE (NOT EXISTS
(SELECT PRODUCTO_ID
FROM DETALLES DET
WHERE DET.PRODUCTO_ID = PRO.PRODUCTO_ID))
ORDER BY "Unidades Pedidas"
Aquí el único con producto con 0 unidades pedidas es la Caja de Chiclets Pequeña
--16. Datos del producto que más unidades se han pedido
SELECT PRO.*
FROM PRODUCTOS PRO
INNER JOIN DETALLES DET ON DET.PRODUCTO_ID = PRO.PRODUCTO_ID
GROUP BY PRO.PRODUCTO_ID, [Link], [Link]
HAVING SUM(CANTIDAD) = (SELECT MAX(SUM(CANTIDAD))
FROM PRODUCTOS PRO
INNER JOIN DETALLES DET ON DET.PRODUCTO_ID = PRO.PRODUCTO_ID
GROUP BY DET.PRODUCTO_ID)
El producto del que más unidades se han pedido es el Atún con 695 unidades
discriminadas así:
--17. Datos del producto más caro del pedido 1
SELECT PRO.*
FROM PRODUCTOS PRO
WHERE [Link] =(SELECT MAX(PRECIO) AS "Mas Caro"
FROM DETALLES DET
INNER JOIN PEDIDOS PED ON PED.PEDIDO_ID = DET.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE DET.PEDIDO_ID = 1)
El Pedido 1 involucra los productos:
· Pescado $ 7856
· Cicla Estatica $ 18000 Siendo este el precio más alto
· Agua en Bolsa $ 1800
--18. Datos del producto más caro de cada pedido (con una consulta correlacionada)
SELECT PRO.PRODUCTO_ID, [Link], [Link], [Link],
DET.PEDIDO_ID
FROM PRODUCTOS PRO
INNER JOIN DETALLES DET ON DET.PRODUCTO_ID = PRO.PRODUCTO_ID
GROUP BY PRO.PRODUCTO_ID,[Link], [Link],
[Link],DET.PEDIDO_ID
HAVING PRECIO = (SELECT MAX(PRECIO)
FROM PRODUCTOS PRO
INNER JOIN DETALLES DETA ON DETA.PRODUCTO_ID = PRO.PRODUCTO_ID
WHERE DETA.PEDIDO_ID = DET.PEDIDO_ID
GROUP BY DETA.PEDIDO_ID
Si hacemos el análisis para el pedido 4 donde dice que el producto de mayor valor es
Leche 1L a $ 4563, vemos que entre los 2 productos que pidió, efectivamente la Leche
es la más alta en precio.
--19. Código de cliente y cantidad total que se ha gastado en 2016
SELECT CLI.CLIENTE_ID, SUM(PRECIO * CANTIDAD) AS IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
WHERE EXTRACT(YEAR FROM [Link]) = 2016
GROUP BY CLI.CLIENTE_ID, DET.PEDIDO_ID
El único pedido efectuado durante el año 2016 es el 11
--20. Cantidad total gastada y código de cliente de los que menos han gastado en 2016
SELECT CLI.CLIENTE_ID, (GASTOS_ENVIO+SUM(PRECIO * CANTIDAD)) AS
IMPORTE
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
WHERE EXTRACT(YEAR FROM [Link]) = 2016
GROUP BY CLI.CLIENTE_ID, GASTOS_ENVIO
HAVING SUM(GASTOS_ENVIO+PRECIO * CANTIDAD) = (
SELECT MIN(SUM(GASTOS_ENVIO+PRECIO * CANTIDAD))
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE EXTRACT(YEAR FROM [Link]) = 2016
GROUP BY DET.PEDIDO_ID
Tenemos solo un registro, puesto que solo hay un pedido que hay sido realizado
durante el 2016, que es el pedido 11 de Mark Hoppus que solicitó
· 8 Papayas
· 12 Sandias
· 92 Pescados
--21. Para cada cliente mostrar su código y la suma total de sus pedidos y gastos de
envío
SELECT PED.CLIENTE_ID, (GASTOS_ENVIO+SUM(PRECIO * CANTIDAD)) AS Total
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.CLIENTE_ID, GASTOS_ENVIO
Si analizamos por ejemplo el pedido 9 realizado por: Britney Spears el cual incluye:
Pantalon: 7800 * 10 U = 78000
Camiseta: 12000 * 65 U = 780000
Cicla Estatica: 18000 * 8 U = 144400
= 1002000
+ 50 en gastos de envío por la bicicleta estática
Total = 1002050
--22. Número de pedido, importe y cliente de los pedidos que no tienen gastos de envío
(debe aparecer un 0 en la columna de gastos de envío nulos)
SELECT DET.PEDIDO_ID, CLI.CLIENTE_ID, [Link],
SUM(PRECIO*CANTIDAD) AS IMPORTE
FROM PEDIDOS PED
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE PED.GASTOS_ENVIO = 0
GROUP BY DET.PEDIDO_ID, CLI.CLIENTE_ID, [Link]
Con excepción de los pedidos: 1,2,3, 5 y 9 que tienen gastos de 50, estos 10 pedidos
no tienen gastos de envío.
--23. Datos del pedido más caro y más barato
SELECT PED.PEDIDO_ID, [Link], PED.FECHA_PREVISTA, PED.CLIENTE_ID,
SUM(PRECIO * CANTIDAD) AS Total, 'ALTO'
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.PEDIDO_ID, [Link], PED.FECHA_PREVISTA,
PED.CLIENTE_ID
HAVING SUM(PRECIO * CANTIDAD) = (
SELECT MAX(SUM(PRECIO * CANTIDAD))
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.PEDIDO_ID
UNION ALL
SELECT PED.PEDIDO_ID, [Link], PED.FECHA_PREVISTA,
PED.CLIENTE_ID,SUM(PRECIO * CANTIDAD) AS Total, 'BAJO'
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.PEDIDO_ID, [Link], PED.FECHA_PREVISTA,
PED.CLIENTE_ID
HAVING SUM(PRECIO * CANTIDAD) = (
SELECT MIN(SUM(PRECIO * CANTIDAD))
FROM PEDIDOS PED
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.PEDIDO_ID
)
El pedido más alto es el 8 con un total de $ 1622640 en razón de las 600 unidades de
atún que pide, y el más bajo el 2 con un total de $ 2500 que solo icluye 5 unidades de
Queso
--24. Sentencia que muestre los productos en este formato
SELECT PRODUCTO_ID||'- - - - -' AS CODIGO_PROD, NOMBRE, '- - - - -'||PRECIO AS
PRECIO
FROM PRODUCTOS
--25. Escribe los datos de los pedidos y sus clientes con el siguiente formato:
SELECT PEDIDO_ID AS "N° Pedido",
(TO_CHAR(FECHA, 'day','nls_date_language=spanish')||' : '|| TO_CHAR(FECHA,'DD/')||
TRIM(TO_CHAR(FECHA, 'month','nls_date_language=spanish'))|| TO_CHAR(FECHA,'/
YYYY')) AS "Fecha Pedido",
(APELLIDOS ||', '|| NOMBRES)AS "Nombre Completo"
FROM PEDIDOS PED
INNER JOIN CLIENTES CLI ON CLI.CLIENTE_ID = PED.CLIENTE_ID
--26. (Solo con subconsultas, sin combinar tablas) Datos de los clientes que han pedido
el producto PANTALÓN
SELECT CLIENTE_ID, (NOMBRES ||' '|| APELLIDOS) AS Nombre,
FECHA_NACIMIENTO
FROM CLIENTES
WHERE CLIENTE_ID IN (SELECT CLIENTE_ID
FROM PEDIDOS PED
WHERE PEDIDO_ID IN (SELECT PEDIDO_ID
FROM DETALLES
WHERE PRODUCTO_ID IN (SELECT PRODUCTO_ID
FROM PRODUCTOS WHERE UPPER(NOMBRE) =
UPPER('pantalon'))))
--27. (Sin subconsultas) Datos de los clientes que han pedido el producto PANTALÓN
SELECT CLI.CLIENTE_ID, ([Link] ||' '|| [Link]) AS Cliente,
FECHA_NACIMIENTO
FROM CLIENTES CLI
INNER JOIN PEDIDOS PED ON PED.CLIENTE_ID = CLI.CLIENTE_ID
INNER JOIN DETALLES DET ON DET.PEDIDO_ID = PED.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE UPPER([Link]) = UPPER('pantalon')
--28. Para cada cliente, mostrar los datos del pedido cuyo importe sea superior al medio
de sus pedidos
SELECT PED.CLIENTE_ID, (PRECIO * CANTIDAD) AS IMPORTE
FROM DETALLES DET
INNER JOIN PEDIDOS PED ON PED.PEDIDO_ID = DET.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
GROUP BY PED.CLIENTE_ID, (PRECIO * CANTIDAD)
HAVING (PRECIO * CANTIDAD)> (
SELECT AVG((PRECIO * CANTIDAD))
FROM DETALLES DET
INNER JOIN PEDIDOS PEDI ON PEDI.PEDIDO_ID = DET.PEDIDO_ID
INNER JOIN PRODUCTOS PRO ON PRO.PRODUCTO_ID = DET.PRODUCTO_ID
WHERE PEDI.CLIENTE_ID = PED.CLIENTE_ID
GROUP BY PEDI.CLIENTE_ID)
En este query hacemos el análisis para los pedidos realizados por el cliente: Drew
Barrymore, quien realizó 5 pedidos discriminados así:
Pedido: 2 donde solicitó 5 Pan para un total de: 2500
Pedido: 12 donde solicitó 22 Sandia para un total de: 176000
Pedido: 13 donde solicitó 15 Cicla Estatica para un total de: 270000
Pedido: 14 donde solicitó 3 Coca-Cola 2L para un total de: 7200
Pedido: 15 donde solicitó 7 Pantalon para un total de: 54600
Para un total de $ 510300, con un promedio o AVG de: $ 102060
Si la media para este pedido es de $ 102060, solo debe mostrar los pedidos de ciclas y
sandías, porque son los únicos mayores al AVG
--29. Lista de todos los pedidos mostrando también los días previstos de espera para el
envío
SELECT PED.*, TO_DATE(FECHA_PREVISTA) - TO_DATE(FECHA) AS DIAS
FROM PEDIDOS PED
WHERE FECHA_PREVISTA IS NOT NULL
UNION ALL
SELECT PED.*, 0 AS DIAS
FROM PEDIDOS PED
WHERE FECHA_PREVISTA IS NULL
--30. Pedidos con el mínimo N° de días previsto de espera
SELECT PEDIDO_ID, FECHA, FECHA_PREVISTA, TO_DATE(FECHA_PREVISTA) -
TO_DATE(FECHA) AS "N° días previsto"
FROM PEDIDOS
WHERE FECHA_PREVISTA = (SELECT MIN(FECHA_PREVISTA) FROM PEDIDOS)
AND FECHA_PREVISTA IS NOT NULL
Este query refleja los pedidos que involucran la cicla estatica, que toma unos días en
entregarla, donde para el caso la fecha mínima es 10 días posteriores a la fecha en que
realizaron el pedido .