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 PED.FECHA) = 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, CLI.NOMBRES, (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, CLI.NOMBRES, 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, CLI.NOMBRES, (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, CLI.NOMBRES, 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, PRO.NOMBRE, PRO.PRECIO

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 PRO.PRECIO =(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, PRO.NOMBRE, PRO.PRECIO, DET.CANTIDAD,

DET.PEDIDO_ID

FROM PRODUCTOS PRO

INNER JOIN DETALLES DET ON DET.PRODUCTO_ID = PRO.PRODUCTO_ID

GROUP BY PRO.PRODUCTO_ID,PRO.NOMBRE, PRO.PRECIO,
DET.CANTIDAD,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 PED.FECHA) = 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 PED.FECHA) = 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 PED.FECHA) = 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, CLI.NOMBRES,

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, CLI.NOMBRES

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, PED.FECHA, 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, PED.FECHA, 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, PED.FECHA, 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, PED.FECHA, 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, (CLI.NOMBRES ||' '|| CLI.APELLIDOS) 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(PRO.NOMBRE) = 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 .