# Laboratorio SQL

## Modelo


## Requisitos

- Crear SQL de creación de esquema de datos. Dejar todo en esquema "labotarorio_sql".
- Categories.CategoryName ← De a lo menos largo 3 caracteres
- Suppliers.CompanyName ← Valor unico
- Todas las fechas deben tener valor por defecto el dia de hoy.
- Employees.region ← debe ser solo una lista de valores posibles
- OrderDetails.unitPrice ← solo pueden ser valores positivos
- OrderDetails.discount ← solo pueden ser valores entre 0 y 1 (ambos inclusive)
- Resolver las siguientes consultas:
1. Se requiere saber el products.productName, supplier.CompanyName, products.discontinued o no de los productos asociados a una orden especifica. Solo periodo marzo-2020.
2. Se requieren filtrar todos los productos asociados al proveedor supplier.companyName = "SUPPLIER A"
3. Listar solo las ordenes que tengan productos con origen de diferente pais, solo del año 2021.
4. Contar cuantas ordenes han sido emitidas por categoria de producto, solo durante año 2021
5. Se requiere obtener el total de venta por provedor de producto y pais
6. Se requiere obtener el precio promedio por producto durante los ultimos 12 meses.
7. Se requiere obtener el total de venta por producto, categoria, pais de origen.

## RECOMENDACIONES SOBRE SCRIPT DB

- Cuando creen una constraint, usar nomenclatura. Por ejemplo:

CONSTRAINT FK_TABLA_ORIGEN_TABLA_DESTINO_ID FOREIGN KEY 
CONSTRAINT CHK_RULE_NAME CHECK 
CONSTRAINT PK_TABLE_NAME PRIMARY KEY

- Nombres de tablas en mayuscula
- Nombres de columnas con una definición clara nombreColumna o nombre_columna. En postgres segun el cliente, varia la presentación (pero la DB no es sensible a esto).

## Solucion

-- SQL

create schema IF NOT EXISTS laboratorio_sql;


-- Rodrigo Vidal
-- EMLPOYEE
CREATE TYPE regions AS ENUM ('RM', 'BIO-BIO', 'Arica');

CREATE TABLE IF NOT EXISTS laboratorio_sql.employees(
    employee_id INT primary key not null,
    last_name VARCHAR(50) not null,
    first_name VARCHAR(50) not null,
    title VARCHAR(50) null,
    title_of_courtesy VARCHAR(50) null,
    birth_date DATE not null default current_date,
    hire_date DATE not null default current_date,
    address VARCHAR(50) not null,
    city VARCHAR(50) not null,
    region regions,
    postal_code VARCHAR(20) null,
    country VARCHAR(30) not null,
    home_phone VARCHAR(15),
    extension VARCHAR(10) null,
    photo VARCHAR(100) null,
    notes VARCHAR(50) null,
    reports_to VARCHAR(50) null);
	

-- Andrea
-- PRODUCTS
create table IF NOT EXISTS laboratorio_sql.PRODUCTS(
    product_id int PRIMARY KEY not null
	,product_name varchar (50) not null
	,supplier_id int not null
	,category_id int not null
	,quantity_per_unit int 
	,unit_price int
	,units_in_stock int
	,units_on_order int
	,reorder_level int
	,discontinued int
);


-- Diego
-- CATEGORY SHIPPERS 

CREATE TABLE IF NOT EXISTS laboratorio_sql.CATEGORIES(
category_id INTEGER PRIMARY KEY,
category_name VARCHAR(50) CHECK (LENGTH(category_name)>=3) UNIQUE,
description VARCHAR(200),
picture VARCHAR(200));

CREATE TABLE laboratorio_sql.SHIPPERS(
shipper_id INTEGER PRIMARY KEY,
company_name VARCHAR(50),
phone VARCHAR(15));



-- Victoria
-- CUSTOMERS

CREATE TABLE IF NOT EXISTS laboratorio_sql.CUSTOMERS(
customer_id SERIAL NOT NULL,
company_name VARCHAR(50) NOT NULL,
contact_name VARCHAR(50) NOT NULL,
contact_title VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
region VARCHAR(50) NOT NULL,
postal_code VARCHAR(50) NULL,
country VARCHAR(50) NOT NULL,
phone INT NOT NULL,
fax INT NULL,
CONSTRAINT PK_CUSTOMERS PRIMARY KEY (customer_id));

-- Tomas SUPPLIERS

CREATE TABLE IF NOT EXISTS laboratorio_sql.SUPPLIERS(
	supplier_id SERIAL NOT NULL,
	company_name VARCHAR(50) NOT NULL,
	contact_name VARCHAR(50) NULL,
	contact_title VARCHAR(50) NULL,
	address VARCHAR(50) NULL,
	city VARCHAR(20) NULL,
	region VARCHAR(20) NULL,
	postal_code INTEGER,
	country VARCHAR(20) NULL,
	phone VARCHAR(20) NULL,
	fax VARCHAR(20) NULL,
	home_page VARCHAR(100) NULL,
	CONSTRAINT PK_SUPPLIERS PRIMARY KEY (supplier_id),
    CONSTRAINT unq_company_name UNIQUE (company_name)
);

-- Javiera

-- Felipe
-- ORDER

CREATE TABLE IF NOT EXISTS laboratorio_sql.ORDERS (
	order_id INT PRIMARY KEY,
	customer_id INT NOT NULL,
	employee_id INT NOT NULL,
	order_date TIMESTAMP NOT NULL DEFAULT NOW(),
	required_date TIMESTAMP NOT NULL DEFAULT NOW(),
	shipped_date TIMESTAMP NOT NULL DEFAULT NOW(),
	ship_via int NOT NULL,
	freight VARCHAR(100) NOT NULL,
	ship_name VARCHAR(100) NOT NULL,
	ship_city VARCHAR(100) NOT NULL,
	ship_region VARCHAR(100) NOT NULL,
	ship_postalCode VARCHAR(100) NOT NULL,
	ship_country VARCHAR(100) NOT NULL,
	CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(customer_id) on delete restrict on update cascade,
	CONSTRAINT fk_employees_id FOREIGN KEY (employee_id) REFERENCES EMPLOYEES(employee_id) on delete restrict on update cascade,
	CONSTRAINT fk_ship_id FOREIGN KEY (ship_via) REFERENCES SHIPPERS(shipper_id) on delete restrict on update cascade
);



-- Juan
-- ORDER_DETAIL
CREATE table IF NOT EXISTS laboratorio_sql.ORDER_DETAILS (
	order_id int not null,
	product_id int not null,
	unit_price int,
	quantity int,
	discount int,
	CONSTRAINT PK_TABLE_ORDER_DETAILS PRIMARY KEY (order_id, product_id)
);

## SQL consultas

-- Victoria
1)

SELECT 
  p.product_name,
  s.company_name,
  p.discontinued
FROM products AS p
INNER JOIN
  supplier AS s
ON 
  p.supplier_id = s.supplier_id
INNER JOIN
  order_details AS od
ON
  p.product_id = od.product_id 
INNER JOIN
  orders AS o
ON
  od.order_id = o.order_id 
WHERE
  od.order_id = '123' #ejemplo orden especifica
  AND o.order_date BETWEEN '2020-03-01' AND '2021-03-31';

2)
SELECT laboratorio_sql.products.product_name FROM laboratorio_sql.PRODUCTS
INNER JOIN laboratorio_sql.SUPPLIERS 
ON products.supplier_id = suppliers. supplier_id
WHERE supplier.company_name = SUPPLIER A’;



-- Diego

-- Andrea
1.
select products.product_name, products.discontinued,suppliers.company_name
from products 
inner join suppliers on suppliers.supplier_id = products.supplier_id
inner join order_details on order_details.product_id = products.product_id
inner join orders on orders.order_id = order_details.order_id
where date_part('year', orders.order_date) = 2020
and date_part('month', orders.order_date) = 3
group by order_details.order_id, products.product_name, products.discontinued,suppliers.company_name
order by order_details.order_id, products.product_name, products.discontinued,suppliers.company_name

2.
select products.product_name
from products
left join SUPPLIERS
on products.supplier_id = SUPPLIERS.supplier_id
where SUPPLIERS.company_name = 'SUPPLIER A';

-- Rodrigo

1.
SELECT laboratorio_sql.products.product_name, laboratorio_sql.suppliers.company_name, laboratorio_sql.products.discontinued
FROM laboratorio_sql.orders
INNER JOIN laboratorio_sql.order_details on orders.order_id = order_details.order_id
INNER JOIN laboratorio_sql.products on order_details.product_id = products.product_id
INNER JOIN laboratorio_sql.suppliers on products.supplier_id = suppliers.supplier_id
WHERE order_date between '2020-03-01' and '2020-03-31';

2.
SELECT laboratorio_sql.products.product_name
FROM laboratorio_sql.products
INNER JOIN laboratorio_sql.suppliers on products.supplier_id = suppliers.supplier_id
AND laboratorio_sql.suppliers.company_name='SUPPLIER A';

3.
SELECT laboratorio_sql.orders.order_id
FROM laboratorio_sql.orders
INNER JOIN laboratorio_sql.order_details on orders.order_id = order_details.order_id
INNER JOIN laboratorio_sql.products on order_details.product_id = products.product_id
INNER JOIN laboratorio_sql.suppliers on products.supplier_id = suppliers.supplier_id


-- Felipe
--1
select t0.product_name,t1.supplier_name,t0.discontinued
from laboratorio_sql.products as t0
join laboratorio_sql.suppliers as t1 on t1.supplier_id = t0.supplier_id
join laboratorio_sql.order_details as t2 on t2.product_id = t0.product_id
join laboratorio_sql.orders as t3 on t3.order_id = t2.order_id
where date_part('year', t3.order_date) = 2020
and date_part('month', t3.order_date) = 3

--2
select t0.*
from laboratorio_sql.products as t0
JOIN laboratorio_sql.suppliers as t1 on t1.supplier_id = t0.supplier_id
where t1.company_name = 'SUPPLIER A'

--3
select DISTINCT t1.country,t0.product_name
from laboratorio_sql.products as t0
join laboratorio_sql.suppliers as t1 on t1.supplier_id = t0.supplier_id
join laboratorio_sql.order_details as t2 on t2.product_id = t0.product_id
join laboratorio_sql.orders as t3 on t3.order_id = t2.order_id
join laboratorio_sql.categories as t4 on t4.category_id = t0.category_id
where date_part('year', t3.order_date) = 2021

--4
select t4.category_name,sum(n) as cantidad
from (
select t4.category_name,t3.*, 1 AS n
from laboratorio_sql.products as t0
join laboratorio_sql.suppliers as t1 on t1.supplier_id = t0.supplier_id
join laboratorio_sql.order_details as t2 on t2.product_id = t0.product_id
join laboratorio_sql.orders as t3 on t3.order_id = t2.order_id
join laboratorio_sql.categories as t4 on t4.category_id = t0.category_id
where date_part('year', t3.order_date) = 2021
) as a
group by t4.category_name

--7
select t0.product_name,t4.category_name,t1.country,sum(n) as cantidad
from (
select t1.country,t3.*,t0.product_name, t2.unit_price AS n
from laboratorio_sql.products as t0
join laboratorio_sql.suppliers as t1 on t1.supplier_id = t0.supplier_id
join laboratorio_sql.order_details as t2 on t2.product_id = t0.product_id
join laboratorio_sql.orders as t3 on t3.order_id = t2.order_id
join laboratorio_sql.categories as t4 on t4.category_id = t0.category_id
) as a
group by t0.product_name,t4.category_name,t1.country


-- Tomas

SELECT p.product_name, s.company_name, p.discontinued FROM laboratorio_sql.products p 
JOIN laboratorio_sql.order_details od on p.product_id = od.product_id
JOIN laboratorio_sql.orders o on od.order_id = od.order_id
JOIN laboratorio_sql.suppliers s on p.supplier_id = s.supplier_id
where o.order_date >= '2020-03-01'
and o.order_date <= '2020-03-31';

SELECT P.* FROM laboratorio_sql.products p 
JOIN laboratorio_sql.suppliers s on p.supplier_id = s.supplier_id
WHERE s.company_name = 'SUPPLIER A';

SELECT DISTINCT sp.country, od.order_id FROM laboratorio_sql.orders o
JOIN laboratorio_sql.order_details od on o.order_id = od.order_id
JOIN laboratorio_sql.products p on od.product_id = p.product_id
JOIN laboratorio_sql.suppliers sp on p.supplier_id = sp.supplier_id
WHERE (select to_char(o.order_date,'YYYY')) = '2021';

-- Juan