<repo_name>GonzaloCarcamo/prueba_sql<file_sep>/consulta1.sql
--CONSULTA 1: ¿Que cliente realizó la compra más cara?

SELECT customer_name FROM customers, facs
WHERE total_value IN (SELECT MAX(total_value) FROM facs) AND  customers.id = facs.customer_id;
<file_sep>/consulta3.sql
--CONSULTA 3: ¿Cuantos clientes han comprado el producto 6?

SELECT COUNT(id_product) FROM facs_prods WHERE id_product = 6 ;

<file_sep>/prueba.sql
--CREAR BASE DE DATOS

CREATE DATABASE prueba;

--CONECTARSE A BASE DE DATOS

\c prueba

--CREAR TABLAS

CREATE TABLE customers(
id SERIAL, customer_name VARCHAR(15), rut VARCHAR(12), address VARCHAR(20), PRIMARY KEY(id));

CREATE TABLE categories(id VARCHAR(5), category_name VARCHAR(15), category_description VARCHAR(30));

CREATE TABLE products(id SERIAL, product_name VARCHAR(15), product_description VARCHAR(40), 
value INT, category_id VARCHAR(5));

CREATE TABLE facs(
fac_number SERIAL, fac_date DATE, subtotal INT, iva INT, total_value INT, customer_id INT,
PRIMARY KEY(fac_number), FOREIGN KEY(customer_id) REFERENCES customers(id));

CREATE TABLE facs_prods(
id_register INT, product_name VARCHAR(15), value INT, quantity INT, subtotal INT, fac_id INT);

--POBLAMIENTO DE TABLAS

INSERT INTO customers (customer_name, rut, address) VALUES ('ripley', '76.000.001-1', 'huerfanos 10'),
('falabella', '76.000.002-2', 'estado 20'), ('paris', '76.000.003-3', 'agustinas 30'),('tricot', '76.000.004-4', 'morande 40'),
('hites', '76.000.005-5', 'monjitas 50');


INSERT INTO categories (id, category_name, category_description) VALUES ('100', 'cuerdas', 'instrumentos_de_cuerda'),
('101', 'vientos', 'instrumentos_de_viento'), ('102', 'audio', 'articulos_grabacion');


INSERT INTO facs (fac_date, subtotal, iva, total_value, customer_id) VALUES ('2020-01-01', '200000', '38000', '238000', '1'),
('2020-01-02', '450000', '85500', '535500', '1'), ('2020-01-03', '210000', '39900', '249900', '2'),
('2020-01-04', '100000', '19000', '119000', '2'), ('2020-01-05', '360000', '68400', '428400', '2'),
('2020-01-06', '20000', '3800', '23800', '3'), ('2020-01-07', '400000', '76000', '476000', '4'),
('2020-01-08', '60000', '11400', '71400', '4'), ('2020-01-09', '400000', '76000', '476000', '4'),
('2020-01-10', '100000', '19000', '119000', '4');


INSERT INTO products (product_name, product_description, value, category_id) VALUES ('guitarra', 'instrumento_seis_cuerdas', 
'100000', '100'), ('saxo', 'instrumento_vientos', '150000', '101'), ('bajo', 'instrumento_cuatro_cuerdas', '100000', '100'),
('banjo', 'instrumento_cuerdas', '70000', '100'), ('mixer', 'mesa_mezcla', '50000', '102'), ('interfaz', 'tarjeta_sonido',
'120000', '102'), ('audifonos', 'monitoreo_mezcla', '20000', '102'), ('trompeta', 'instrumento_vientos', '200000', '101');


INSERT INTO facs_prods (id_register, product_name, value, quantity, subtotal, fac_id) VALUES (
'1', 'guitarra', '100000', '2', '200000', '1'),(
'2', 'saxo', '150000', '3', '450000', '2'),(
'4', 'banjo', '70000', '3', '210000', '3'),(
'5', 'mixer', '50000', '2', '100000', '4'),(
'6', 'interfaz', '120000', '3', '360000', '5'),(
'7', 'audifonos', '20000', '1', '20000', '6'),(
'8', 'trompeta', '200000', '2', '400000', '7'),(
'7', 'audifonos', '20000', '3', '60000', '8'),(
'3', 'bajo', '100000', '4', '400000', '9'),(
'1', 'guitarra', '100000', '1', '100000', '10');


--AGREGAR PK Y FK

ALTER TABLE categories ADD PRIMARY KEY(id);

ALTER TABLE products ADD PRIMARY KEY(id);

ALTER TABLE products ADD FOREIGN KEY(category_id) REFERENCES categories(id);

ALTER TABLE facs_prods ADD FOREIGN KEY(fac_id) REFERENCES facs(fac_number);

ALTER TABLE facs_prods ADD FOREIGN KEY(id_register) REFERENCES products(id);

ALTER TABLE facs_prods ADD FOREIGN KEY(id_register) REFERENCES products(id);

ALTER TABLE facs_prods RENAME COLUMN id_register TO id_product;
<file_sep>/consulta2.sql
--CONSULTA 2: ¿Que cliente pagó sobre 100.000 de monto?

SELECT distinct (customer_name) FROM customers, facs
WHERE customers.id = facs.customer_id AND facs.total_value > 100000;

