TP6

CREATE DATABASE "TP6_Grupo15";
\c "TP6_Grupo15"

CREATE TABLE Cargo(
        ID_cargo     varchar(3) PRIMARY KEY,
        Nombre_Cargo varchar(20)
);

CREATE TABLE Tipo_Cliente(
       ID_tipo     varchar(2) PRIMARY KEY,
       Nombre_Tipo varchar(30)
);

CREATE TABLE Proveedor(
       CUIT_proveedor   integer PRIMARY KEY,
       Nombre_proveedor varchar(20)
);

CREATE TABLE Empleado(
        Codigo_Empleado integer PRIMARY KEY,
        Nombre_Empleado varchar(20),
        DNI_Empleado    integer,
        ID_Cargo        varchar(3),

        FOREIGN KEY(ID_Cargo) REFERENCES Cargo(ID_Cargo)
);

CREATE TABLE Cliente (
       Codigo_Cliente integer PRIMARY KEY,
       CUIT_Cliente   integer,
       Nombre_Cliente varchar(30),
       Telef_Cliente  varchar(20),
       ID_Tipo        varchar(2),

       FOREIGN KEY(ID_Tipo) REFERENCES Tipo_Cliente(ID_Tipo)
);

CREATE TABLE Producto(
       Codigo_Producto integer PRIMARY KEY,
       Nombre_Producto varchar(20),
       Precio_Producto integer,
       CUIT_Proveedor  integer,

       FOREIGN KEY(CUIT_Proveedor) REFERENCES Proveedor(CUIT_Proveedor)
);

CREATE TABLE Provincia(
       Codigo_Provincia integer PRIMARY KEY,
       Nombre_Provincia varchar(20)
);

CREATE TABLE Compania_Envio(
       Codigo_Compania  integer PRIMARY KEY,
       Nombre_Compania  varchar(20),
       Telef_Compania   integer,
       Codigo_Provincia integer,

    FOREIGN KEY(Codigo_Provincia) REFERENCES Provincia(Codigo_Provincia)
);

CREATE TABLE Pedido(
    Codigo_Pedido          integer PRIMARY KEY,
    Fecha_Pedido           date,
    Fecha_Envio            date,
    Direccion_Destinatario varchar(20),
    Codigo_Cliente         integer,
    Codigo_Empleado        integer,
    Codigo_Compania        integer,


    FOREIGN KEY(Codigo_Cliente)  REFERENCES Cliente(Codigo_Cliente),
    FOREIGN KEY(Codigo_Empleado) REFERENCES Empleado(Codigo_Empleado),
    FOREIGN KEY(Codigo_Compania) REFERENCES Compania_Envio(Codigo_Compania)
);

CREATE TABLE Detalle_Pedido(
    Codigo_Pedido        integer references Pedido(Codigo_Pedido),
    Codigo_Producto      integer references Producto(Codigo_Producto),
    Cantidad             integer,
    Porcentaje_Descuento integer,

    primary key(Codigo_Pedido,Codigo_Producto)
);



INSERT INTO Cargo VALUES
 ('GER', 'Gerente'),
 ('ENC', 'Encargado'),
 ('VEN', 'Vendedor')
;

INSERT INTO Tipo_Cliente VALUES
 ('CF', 'ConsumidorFinal'),
 ('RI', 'ResponsableInscripto'),
 ('EX', 'Exento')
;

INSERT INTO Proveedor VALUES
 (11111111, 'Proveedor1'),
 (22222222, 'Proveedor2'),
 (33333333, 'Proveedor3')
;

INSERT INTO Empleado  VALUES
 (2810, 'Empleado1', 22222222, 'VEN'),
 (2811, 'Empleado2', 33333333, 'VEN'),
 (2812, 'Empleado3', 44444444, 'ENC'),
 (2813, 'Empleado4', 55555555, 'GER'),
 (2814, 'Empleado5', 11111111, 'VEN')
;

INSERT INTO Cliente VALUES
 (1001, 2010010, 'Cliente1', 111, 'CF'),
 (2002, 2020020, 'Cliente2', 222, 'EX'),
 (3003, 2030030, 'Cliente3', 333, 'CF'),
 (4004, 2040040, 'Cliente4', 444, 'RI'),
 (5005, 2050050, 'Cliente5', 555, 'RI')
;

INSERT INTO Producto VALUES
 (111, 'Pendrive', 250 , 11111111),
 (222, 'Memoria' , 300 , 11111111),
 (333, 'Resma A4', 180 , 22222222),
 (444, 'Caja CD' , 1200, 33333333),
 (555, 'Cartucho', 650 , 22222222)
;

INSERT INTO Provincia VALUES
 (388, 'Jujuy'),
 (381, 'Tucumán'),
 (387, 'Salta')
;

INSERT INTO Compania_Envio VALUES
 (10, 'Compañía1', 155155, 381),
 (20, 'Compañía2', 152152, 388),
 (30, 'Compañía3', 153153, 388),
 (40, 'Compañía4', 154154, 387)
;

INSERT INTO Pedido VALUES
 (1, '30/09/2019', '02/10/2019', 'Direccion1', 1001, 2814, 10),
 (2, '30/09/2019', '03/10/2019', 'Direccion2', 4004, 2814, 20),
 (3, '30/09/2019', '10/10/2019', 'Direccion3', 1001, 2810, 40),
 (4, '01/10/2019', '02/10/2019', 'Direccion4', 2002, 2814, 30),
 (5, '01/10/2019', '03/10/2019', 'Direccion5', 3003, 2811, 10),
 (6, '02/10/2019', '05/10/2019', 'Direccion6', 5005, 2810, 20)
;

INSERT INTO Detalle_Pedido VALUES
 (1, 111,  2, 0),
 (1, 222,  5, 0),
 (1, 444, 20, 10),
 (2, 333, 10, 5),
 (2, 555,  1, 0),
 (3, 111,  1, 0),
 (4, 555,  2, 0),
 (5, 111,  1, 0),
 (5, 222,  1, 15),
 (5, 333,  1, 0),
 (1, 555,  5, 10)
;
--4. Utilice subcONsultas y muestre el nombre de los empleados que vendieron el Producto
--   "Pendrive"

SELECT nombre_empleado
FROM   empleado
WHERE  codigo_empleado IN(

       SELECT codigo_empleado
       FROM   pedido
       WHERE  codigo_pedido IN(

              SELECT codigo_pedido
              FROM   detalle_pedido
              WHERE  codigo_producto IN(

                     SELECT codigo_producto
                     FROM   producto
                     WHERE  nombre_producto='Pendrive')))
;


--5. Utilice subcONsultas y muestre los pedidos cON fecha de pedido del 30/09/2019 que
--   correspONden al tipo de cliente “CONsumidor FINal” y realizados en la compañía ubicada en
--   “Tucumán”.

SELECT *
FROM   pedido
WHERE  fecha_pedido = '30/09/2019'

       AND codigo_cliente IN(
           SELECT codigo_cliente
           FROM   cliente
           WHERE  id_tipo IN(

                  SELECT id_tipo
                  FROM   tipo_cliente
                  WHERE  nombre_tipo = 'Consumidor Final'))

       AND codigo_compania IN(
           SELECT codigo_compania
           FROM   compania_envio
           WHERE  codigo_provINcia IN(

                  SELECT codigo_provINcia
                  FROM   provINcia
                  WHERE  nombre_provINcia = 'Tucumán'))
;

--6. Utilice INNER JOIN y muestre.codigo_producto.codigo_producto cada producto cON su correspONdiente cantidad vendida,
--   agrupado por “Nombre producto”.

SELECT     nombre_producto AS "Nombre Producto",
           sum(cantidad)   AS "Cantidad Vendida"

FROM       producto p
INNER JOIN detalle_pedido dp
on         p.codigo_producto = dp.codigo_producto
GROUP BY   nombre_producto;

--7. Utilice inner join y muestre los empleados que vendieron “Resma A4”.

SELECT     nombre_empleado
FROM       empleado e

INNER JOIN pedido pe
           INNER JOIN detalle_pedido dp
                      INNER JOIN producto pr
                      ON pr.codigo_producto = dp.codigo_producto
           ON pe.codigo_pedido = dp.codigo_pedido
ON e.codigo_empleado  = pe.codigo_empleado

WHERE      pr.nombre_producto = 'Resma A4';

--8. Muestre los códigos de pedidos de la tabla “DetallePedido” con su correspondiente total.
--   Considere el precio, la cantidad y el descuento para el cálculo del total.

SELECT     dp.codigo_pedido,
           sum(dp.cantidad*pr.precio_producto*(1-dp.porcentaje_descuento/100)) AS "Total"

FROM       detalle_pedido dp
INNER JOIN producto pr
ON         pr.codigo_producto = dp.codigo_producto
GROUP BY   dp.codigo_pedido
ORDER BY   dp.codigo_pedido;

--9. Muestre en una consulta relacionada con inner join el cuit y nombre de cliente, y el
--   “nombre tipo” agrupadas por “nombre tipo”.

SELECT prov.cuit_proveedor AS "CUIT",
       cli.nombre_cliente  AS "Nombre de cliente",
       tc.nombre_tipo      AS "Nombre Tipo"

FROM   detalle_pedido dp
INNER JOIN producto prod
           INNER JOIN proveedor prov
           ON prov.cuit_proveedor = prod.cuit_proveedor
ON dp.codigo_producto = prod.codigo_producto

INNER JOIN pedido ped
           INNER JOIN cliente cli
                      INNER JOIN tipo_cliente tc
                      ON tc.id_tipo = cli.id_tipo
           ON ped.codigo_cliente = cli.codigo_cliente
ON ped.codigo_pedido = dp.codigo_pedido

GROUP BY "Nombre Tipo", "CUIT", "Nombre de cliente";



--10. Utilizando inner join muestre el porcentaje descuento, el nombre del producto y la fecha de
--    envío, cuya fecha pedido es 01/10/2019.

SELECT prod.nombre_producto    AS "Nombre producto",
       ped.fecha_envio         AS "Fecha envio",
       dp.porcentaje_descuento AS "Porcentaje de descuento"

FROM   detalle_pedido dp

INNER JOIN producto prod
ON dp.codigo_producto = prod.codigo_producto

INNER JOIN pedido ped
ON ped.codigo_pedido = dp.codigo_pedido

WHERE  fecha_pedido = '01/10/2019'
ORDER BY nombre_producto asc;