# IIC2713 Tarea 2

## Comandos útiles

### Manejo del sistema

Para entrar a la consola de postgres:

 sudo -u postgres psql

Para crear el database (dentro de psql):

create database sistemasinfo;

Para ver los databases

\l

Para borrar un database:

drop database <nombre>;

Para conectarse (entrar) al database creado:

\c sistemasinfo; 

Para ver las tablas creadas en la database:
* `\d` (se ven todas)
* `\d <nombre_detabla>` (se ve como se compone esa tabla)

Para más comandos [sigue el siguiente link](https://stackoverflow.com/questions/769683/show-tables-in-postgresql)

### Fecha

Postgres por defecto tiene yyyy%mm%dd para cambiarlo a dd%mm%yyyy:

* Hacer pruebas de consulta de fecha antes de usar este comando, a veces no es necesario

SET datestyle = "ISO, DMY";

## Importar Tablas

#### Adquisiciones
*probado*

CREATE TABLE adquisiciones(EAN bigint, id_proveedor int, cantidad int, fecha_adquisicion date);
\COPY adquisiciones(EAN, id_proveedor, cantidad, fecha_adquisicion) FROM 'adquisiciones.csv' delimiter ',' csv header;
SELECT * FROM adquisiciones;

#### Clientes

*probado*

CREATE TABLE clientes(id_cliente int, nombre varchar(30), rut varchar(30), dir_calle varchar(50), telefono_empresa bigint,  web varchar(60), contacto_ventas varchar(60), telefono_contacto bigint, correo varchar(60), cargo varchar(20));
\COPY clientes(id_cliente, nombre, rut, dir_calle, telefono_empresa, web, contacto_ventas, telefono_contacto, correo, cargo) FROM 'clientes.csv' delimiter ',' csv header;

*probado*

CREATE TABLE clientes(id_cliente int, nombre varchar(30), rut varchar(30), dir_calle varchar(50), telefono_empresa bigint,  web varchar(60), contacto_ventas varchar(60), telefono_contacto bigint, correo varchar(60), cargo varchar(20));
\COPY clientes(id_cliente, nombre, rut, dir_calle, telefono_empresa, web, contacto_ventas, telefono_contacto, correo, cargo) FROM 'clientes.csv' delimiter ',' csv header;

#### Cobranzas Nuevas

* Será la única tabla cuyas fechas sean `varchar`, por lo que es la única tabla que necesita casteo

*probado*

CREATE TABLE cobranzas(id_factura int, fecha_pago varchar(30));
\COPY cobranzas(id_factura, fecha_pago) FROM 'cobranzas.csv' delimiter ',' csv header;
SELECT * FROM cobranzas;

* Ejemplo consulta de rango de fecha usando casteo, el orden debe respetarse

SELECT * FROM COBRANZAS
WHERE ( fecha_pago <> 'No pagado')
AND ( fecha_pago::date >= '01/01/2020' )
AND ( fecha_pago::date <= '31/01/2020' );

#### Despachos

*probado*

CREATE TABLE despachos(id_pedido int, fecha_despacho date, cantidad_despachada int);
\COPY despachos(id_pedido, fecha_despacho, cantidad_despachada) FROM 'despachos.csv' delimiter ',' csv header;
SELECT * FROM despachos;

#### Facturas

*probado*

CREATE TABLE facturas(id_pedido int, id_factura int, fecha_facturacion date, total_facturado float);
\COPY facturas(id_pedido, id_factura, fecha_facturacion, total_facturado) FROM 'facturas.csv' delimiter ',' csv header;
SELECT * FROM facturas;

#### Pedidos

*probado*

CREATE TABLE pedidos(id_pedido int, fecha_pedido date, EAN bigint, cantidad_solicitada int, id_vendedor int,id_cliente int, precio_real int);
\COPY pedidos(id_pedido, fecha_pedido, EAN, cantidad_solicitada, id_vendedor, id_cliente, precio_real) FROM 'pedidos.csv' delimiter ',' csv header;
SELECT * FROM pedidos;

#### Productos

*probado*

CREATE TABLE productos(EAN bigint, nombre varchar(100), categoria varchar(100), costo int, precio_recomendado int, saldo int, id_proveedor int);
\COPY productos(EAN, nombre, categoria, costo, precio_recomendado, saldo, id_proveedor) FROM 'productos.csv' delimiter ',' csv header;
SELECT * FROM productos;

#### Proveedores

*probado*

CREATE TABLE proveedores(id_proveedor int, nombre varchar(30));
\COPY proveedores(id_proveedor, nombre) FROM 'proveedores.csv' delimiter ',' csv header;
SELECT * FROM proveedores;

#### Vendedores

*probado*

CREATE TABLE vendedores(id_vendedor int, apellido varchar(20), nombre varchar(20));
\COPY vendedores(id_vendedor, apellido, nombre) FROM 'vendedores.csv' delimiter ',' csv header;
SELECT * FROM vendedores;

## Consultas

##### a) Determine cuántas unidades de cada categoría de productos fueron adquiridas durante el año 2019. Entregue nombre de categoría y número de unidades adquiridas ¿Cuáles fueron las categorías con mayor y menor número de unidades adquiridas?

SELECT PROD.categoria AS categoria, sum(ADQ.cantidad) AS unidades
FROM ADQUISICIONES AS ADQ, PRODUCTOS AS PROD 
WHERE (ADQ.ean = PROD.ean)
AND (ADQ.fecha_adquisicion >= '01/01/2019')
AND (ADQ.fecha_adquisicion <= '31/12/2019')
GROUP BY PROD.categoria
ORDER BY unidades DESC;

##### b) ¿Cuáles son los 5 productos con menor facturación (en pesos) del año 2019? Entregue su EAN, nombre y suma de las facturaciones.

*sin joins con where solamente*

SELECT PROD.ean, PROD.nombre, sum(FAC.total_facturado) AS facturado
FROM PRODUCTOS AS PROD, FACTURAS AS FAC, PEDIDOS AS PED
WHERE (FAC.id_pedido = PED.id_pedido)
AND (PED.ean = PROD.ean)
AND (FAC.fecha_facturacion >= '01/01/2019')
AND (FAC.fecha_facturacion <= '31/12/2019')
GROUP BY (PROD.ean, PROD.nombre)
ORDER BY facturado ASC
LIMIT 5;

*con joins*

SELECT PRODUCTOS.ean, nombre, sum(FACTURAS.total_facturado) AS facturado
FROM FACTURAS
JOIN PEDIDOS ON (PEDIDOS.id_pedido = FACTURAS.id_pedido)
JOIN PRODUCTOS ON (PEDIDOS.ean) = (PRODUCTOS.ean)
WHERE (FACTURAS.fecha_facturacion >= '01/01/2019')
AND (FAC.fecha_facturacion <= '31/12/2019')
GROUP BY (PRODUCTOS.ean, PRODUCTOS.nombre)
ORDER BY facturado ASC
LIMIT 5;

##### c) Determine la cantidad inicial de inventario de los productos al comienzo del 2020. Entregue nombre del producto, EAN y cantidad inicial. (Asuma que es el mismo saldo de inventario del 31-12-2019 )

SELECT PROD.nombre, PROD.ean, sum(PROD.saldo) + sum(ADQ.cantidad) AS cantidad_inicial
FROM PRODUCTOS AS PROD, ADQUISICIONES AS ADQ
WHERE (PROD.ean = ADQ.ean)
AND (ADQ.fecha_adquisicion <= '31/12/2019')
GROUP BY (PROD.nombre, PROD.ean)
ORDER BY cantidad_inicial DESC;

##### d) Determine los pedidos en los que la cantidad solicitada es mayor o igual a 45. Entregue No pedido, EAN, nombre producto, cantidad solicitada y cliente.

SELECT PED.id_pedido AS num_pedido, PROD.ean, PROD.nombre AS nombre_producto, PED.cantidad_solicitada, CL.nombre AS nombre_cliente
FROM PEDIDOS AS PED, CLIENTES AS CL, PRODUCTOS AS PROD
WHERE (PEd.id_cliente = CL.id_cliente)
AND (PED.ean = PROD.ean)
AND (PED.cantidad_solicitada >= 45)
ORDER BY num_pedido ASC;

##### e) Determine quiénes son los 2 vendedores que menos facturan en el año 2019. Entregue sus nombres y apellidos.

* Si no se agrupa por algo que se quiere en el select no sirve, ya que es un valor agregado.

SELECT VEN.nombre, VEN.apellido, sum(FAC.total_facturado) AS facturado_2019
FROM FACTURAS AS FAC, PEDIDOS AS PED, VENDEDORES AS VEN
WHERE (FAC.id_pedido = PED.id_pedido)
AND (PED.id_vendedor = VEN.id_vendedor)
AND (PED.fecha_pedido >= '01/01/2019')
AND (PED.fecha_pedido <= '31/12/2019')
GROUP BY (VEN.nombre, VEN.apellido)
ORDER BY facturado_2019 ASC
LIMIT 2;

##### f) LINSA desea estrechar su relación con sus clientes estrellas, es decir los que generan más utilidades para la empresa. Determine cuáles son sus 2 mejores clientes. ¿Cuánta utilidad generan?

SELECT CL.nombre, sum(FAC.total_facturado) AS utilidad
FROM FACTURAS AS FAC, PEDIDOS AS PED, CLIENTES AS CL
WHERE (FAC.id_pedido = PED.id_pedido)
AND (CL.id_cliente = PED.id_cliente)
GROUP BY (CL.nombre)
ORDER BY utilidad DESC
LIMIT 2;

##### g) LINSA desea saber cuáles son los productos de categoría Mesa menos rentables (El término rentable se refiere a los que generan menor ingreso de esa categoría). Entrega EAN y nombre.

SELECT PROD.ean, PROD.nombre, sum(FAC.total_facturado) AS ingreso
FROM FACTURAS AS FAC, PEDIDOS AS PED, PRODUCTOS AS PROD
WHERE (FAC.id_pedido = PED.id_pedido)
AND (PED.ean = PROD.ean)
AND (PROD.categoria = 'Mesa')
GROUP BY (PROD.ean, PROD.nombre)
ORDER BY ingreso ASC;

##### h) Entregue el EAN y nombre de los 3 productos Mesa más rentables, en orden descendente (El término rentable se refiere a los que generan mayor ingreso de esa categoría).

SELECT PROD.ean, PROD.nombre, sum(FAC.total_facturado) AS ingreso
FROM FACTURAS AS FAC, PEDIDOS AS PED, PRODUCTOS as PROD
WHERE (FAC.id_pedido = PED.id_pedido)
AND (PED.ean = PROD.ean)
AND (PROD.categoria = 'Mesa')
GROUP BY (PROD.ean, PROD.nombre)
ORDER BY ingreso DESC
LIMIT 3;

##### i) La empresa desea aumentar las ventas de las lámparas de la categoría Escritorio. Se desea entonces determinar cuáles son estos productos e identificar a los vendedores encargados (nombre y apellido) de al menos dos de los pedidos que tengan asociados una lámpara de tipo Escritorio.

SELECT PROD.ean, PROD.nombre, VEN.id_vendedor, VEN.nombre, VEN.apellido, COUNT(PED.id_pedido) as n_pedidos
FROM PRODUCTOS as PROD
JOIN PEDIDOS as PED ON PED.ean=PROD.ean
JOIN VENDEDORES as VEN on VEN.id_vendedor=PED.id_vendedor
WHERE PROD.categoria = 'Escritorio'
GROUP BY PROD.ean, PROD.nombre, VEN.id_vendedor, VEN.nombre, VEN.apellido
HAVING COUNT (PED.id_pedido) >= 2
ORDER BY VEN.id_vendedor ASC;

##### j) LINSA desea hacer un minucioso seguimiento de los productos que solicita el cliente al cual menos le ha facturado el 2019 (si no lo conoce, debe determinarlo primero). El seguimiento está orientado principalmente a los pedidos “no pagados”. Entregue todos los EAN y montos de los pedidos hechos, solo por este cliente, que no han sido pagados.

SELECT CL.id_cliente, CL.nombre, SUM(PED.cantidad_solicitada*PED.precio_real) as total_facturado
FROM PEDIDOS as PED
JOIN FACTURAS as FAC ON FAC.id_pedido = PED.id_pedido
JOIN CLIENTES as CL on PED.id_cliente = CL.id_cliente
WHERE (FAC.fecha_facturacion >= '01/01/2019')
AND (FAC.fecha_facturacion <= '31/12/2019')
GROUP BY CL.id_cliente, CL.nombre
ORDER BY total_facturado ASC
LIMIT 1;
-- Cliente que menos ha facturado (nombre: LamparasPY, id_cliente: 15)
SELECT CL.id_cliente, PED.ean, FAC.id_factura, COB.fecha_pago, PED.id_pedido, (PED.cantidad_solicitada*PED.precio_real) as monto
FROM COBRANZAS as COB
JOIN FACTURAS as FAC ON COB.id_factura = FAC.id_factura
JOIN PEDIDOS as PED ON FAC.id_pedido = PED.id_pedido
JOIN CLIENTES as CL ON PED.id_cliente = CL.id_cliente
WHERE COB.fecha_pago = 'No pagado'
AND CL.id_cliente = 15
GROUP BY PED.ean, CL.id_cliente, COB.fecha_pago, FAC.id_factura, PED.id_pedido, monto
ORDER BY monto DESC;

##### k) Filtrar las facturas en base a todas aquellas

* cuyo vendedor se llama “Pedro algo”
* cuyos primeros 5 dígitos sean 17234
* Escriba la consulta(s) SQL que refleje lo que el trabajador desea hacer.
* ¿A qué vendedor corresponde? ¿A cuántos pedidos podría corresponder, cuáles?

SELECT PEDIDOS.id_pedido, PEDIDOS.EAN,  PEDIDOS.fecha_pedido, VENDEDORES.nombre, VENDEDORES.apellido, FACTURAS.id_factura, FACTURAS.fecha_facturacion
FROM PEDIDOS
INNER JOIN VENDEDORES ON PEDIDOS.id_vendedor = VENDEDORES.id_vendedor
INNER JOIN FACTURAS ON PEDIDOS.id_pedido = FACTURAS.id_pedido
WHERE (VENDEDORES.nombre LIKE '%Pedro%')
AND CAST(id_factura  AS TEXT) LIKE '17234%';