SQL I - Practica Clase I
# install
!a
pt install postgresql postgresql-contrib &>log# INSTALAR POSTGRESQL Y CONTRIB
!s
ervice postgresql start# INICIA EL EL SERVICIO POSTGRESQL EN ENTORNO COOLAB
!s
udo -u postgres psql -c"CREATE USER root WITH SUPERUSER"# CREA UN USUARIO LLAMADO
"root"
# CON PRIVILEGIO DE SUPERUSUARIO
# set connection
"""Carga la extensión SQL en Colab, permitiendo ejecutar consultas
SQL directamente en las celdas de codigo. Luego, se importan las
bibliotecas necesarias, incluidas SQLAlchemy para la conexión y
Pandas para el manejo de datos."""
%reload_extsql
importsqlalchemy
importpandasaspd
"""Establecimiento de la conexión: Se crea un motor SQLAlchemy para establecer
la conexión con la base de datos PostgreSQL. La cadena de conexión especifica
el tipo de base de datos (postgresql+psycopg2) y el nombre de usuario y contraseña
(en este caso, no se proporcionan, por lo que se utilizarán los valores predeterminados).
La conexión se realiza con la base de datos "postgres"."""
engine = sqlalchemy.create_engine('postgresql+psycopg2://@/postgres')
""" Creación de la función exec_sql: Esta función se utiliza para ejecutar consultas
SQL en la base de datos. Toma una consulta como argumento, la ejecuta y devuelve
los resultados si la consulta es un SELECT, o bien, confirma la transacción si
es una consulta de modificación de datos."""
defexec_sql(q
uery):
text_query = sqlalchemy.sql.text(query)
withengine.connect()asconn:
res = conn.execute(text_query)
ifquery.strip().lower().startswith('select'):
returnpd.DataFrame(res)
else:
conn.commit()
Crear base de datos
create_schema = 'CREATE SCHEMA IF NOT EXISTS humai'
exec_sql(create_schema)
DDL - Crear tablas
Tabla Customers
CREATE TABLE IF NOT EXISTS humai.Customers (
customer_id INT NOT NULL,
customer_name VARCHAR(50) NOT
NULL, fecha_inicio DATE NOT NULL,
fecha_fin DATE,
PRIMARY KEY (customer_id)
);
create_customers = """
REATE TABLE IF NOT EXISTS humai.Customers (
C
customer_id INT NOT NULL,
customer_name VARCHAR(50) NOT
NULL, fecha_inicio DATE NOT NULL,
fecha_fin DATE,
RIMARY KEY (customer_id));
P
"""
exec_sql(create_customers)
Tabla Orders
CREATE TABLE IF NOT EXISTS humai.Orders (
order_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
order_price DECIMAL(8,2),
PRIMARY KEY (order_id),
OREIGN KEY (customer_id) REFERENCES humai.Customers(customer_id)
F
);
reate_orders = """
c
CREATE TABLE IF NOT EXISTS humai.Orders (
order_id INT NOT NULL,
customer_id INT NOT NULL, order_date DATE NOT NULL,
order_price DECIMAL(8,2), PRIMARY KEY (order_id),
FOREIGN KEY (customer_id) REFERENCES humai.Customers(customer_id)
) ;
"""
exec_sql(create_orders)
Tabla Shipments
CREATE TABLE IF NOT EXISTS humai.Shipments (
shipment_id INT NOT NULL,
order_id INT NOT NULL,
shipment_date DATE NOT NULL,
shipment_city VARCHAR(50),
PRIMARY KEY (shipment_id),
FOREIGN KEY (order_id) REFERENCES humai.Orders(order_id)
);
reate_shipments = """
c
CREATE TABLE IF NOT EXISTS humai.Shipments (
shipment_id INT NOT NULL,
order_id INT NOT NULL,
hipment_date DATE NOT
s
NULL, shipment_city
VARCHAR(50),
PRIMARY KEY (shipment_id),
FOREIGN KEY (order_id) REFERENCES humai.Orders(order_id)
) ;
"""
exec_sql(create_shipments)
Check que se hayan creado las tablas
xec_sql("SELECT * FROM information_schema.tables
e
WHERE table_schema = 'humai';")
DML - Insertar datos ficticios
INSERT INTO humai.Customers
VALUES
(1, 'Eugenio', '08/21/1998', Null),
(2, 'Mario', '05/05/2005', Null),
(3, 'Pedro', '03/08/2020', '02/05/2022')
;
insert_customers = """
INSERT INTO humai.Customers VALUES
(1, 'Eugenio', '08/21/1998', Null),
(2, 'Mario', '05/05/2005', Null),
(3, 'Pedro', '03/08/2020', '02/05/2022') """
exec_sql(insert_customers)
INSERT INTO humai.Orders
VALUES
(1, 1, '06/05/2022', 45),
(2, 1, '06/05/2021', 60),
(3, 1, '06/06/2022', 70),
(4, 2, '01/05/2022', 5),
( 5, 3, '06/10/2022', 145),
(6, 3, '03/02/2022', 2)
;
insert_orders = """
INSERT INTO humai.Orders VALUES
(1, 1, '06/05/2022', 45),
(2, 1, '06/05/2021', 60),
(3, 1, '06/06/2022', 70),
(4, 2, '01/05/2022', 5),
(5, 3, '06/10/2022', 145),
(6, 3, '03/02/2022', 2);
"""
exec_sql(insert_orders)
INSERT INTO humai.Shipments
VALUES
(1, 1, '06/06/2022', 'Belgrano'),
(2, 2, '06/06/2021', 'Mar del Plata'),
(3, 3, '06/10/2022', 'Belgrano'),
(4, 4, '02/05/2022', 'San Isidro'),
(5, 5, '06/15/2022', 'Belgrano'),
(6, 6, '03/05/2022', 'Mar del Plata')
;
insert_shipments = """
INSERT INTO humai.Shipments VALUES
(1, 1, '06/06/2022', 'Belgrano'),
(2, 2, '06/06/2021', 'Mar del Plata'),
(3, 3, '06/10/2022', 'Belgrano'),
( 4, 4, '02/05/2022', 'San Isidro'),
(5, 5, '06/15/2022', 'Belgrano'),
(6, 6, '03/05/2022', 'Mar del Plata');
"""
exec_sql(insert_shipments)
select_orders = 'SELECT * FROM humai.orders;'
table_catalog table_schema table_name table_type self_referencing_c
customers ASE
B
0 postgres humai
TABL
E
orders ASE
B
1 postgres humai
TABL
E
BASE
2 postgres humai shipments
exec_sql(select_orders)# Este comando me permite ver la tabla Customers
order_id customer_id order_date order_price
0 1 1 2022-06-05 45.00
1 2 1 2021-06-05 60.00
2 3 1 2022-06-06 70.00
3 4 2 2022-01-05 5.00
4 5 3 2022-06-10 145.00
5 6 3 2022-03-02 2.00
DDL - Consulas SELECT
1.Retorna todos los registros de la tabla Customers.
SELECT *
FROM humai.Customers;
select_customers = 'SELECT * FROM humai.Customers;'
exec_sql(select_customers)
customer_id customer_nam fecha_inicio fecha_fin
e
0 1 Eugenio 1998-08-21 None
1 2 Mario 2005-05-05 None
2 3 Pedro 2020-03-08 2022-02-05
2.Retorna la cantidad de envíos por shipment_city
SELECT shipment_city as "Barrio", count(*) as "Envios" FROM
humai.Shipments
GROUP BY shipment_city
elect_shipment = 'SELECT shipment_city as "Barrio", count(*) as "Envios" FROM
s
humai.Shipments
GROUP BY shipment_city'
exec_sql(select_shipment)
Barrio Envios
0 Belgrano 3
1 Mar del Plata 2
2 San Isidro 1
3.Retorna la cantidad de envios a Belgrano
SELECT count(*) as "Envios a Belgrano"
FROM humai.Shipments
WHERE shipment_city = 'Belgrano'
xec_sql("SELECT count(*) as \"Envios a Belgrano\"
e
FROM humai.Shipments
WHERE shipment_city = 'Belgrano';")
Envios a Belgrano
0 3
4.Retorna todas las ordenes mayores a $50
SELECT *
FROM humai.Orders
WHERE order_price > 50;
exec_sql("SELECT * FROM humai.Orders WHERE order_price > 50;")
order_id customer_id order_date order_price
0 2 1 2021-06-05 60.00
1 3 1 2022-06-06 70.00
2 5 3 2022-06-10 145.00
5.Retorna el cliente que más dinero gasto
SELECT customer_id, sum(order_price) as "Total gastado"
FROM humai.Orders
GROUP BY customer_id
ORDER BY sum(order_price) DESC
LIMIT 1
;
xec_sql("SELECT customer_id, sum(order_price) as \"Total gastado\"
e
FROM humai.Orders GROUP BY customer_id ORDER BY sum(order_price) DESC LIMIT 1;
customer_id Total gastado
0 1 175.00
6.Y que si queremos ver el nombre del cliente? Dato que se
encuentra en otra tabla.
SELECT c.customer_name, sum(order_price) as "Total gastado" FROM
humai.Orders o
FULL JOIN humai.Customers c
ON o.customer_id = c.customer_id
GROUP BY c.customer_name
ORDER BY sum(order_price) DESC
LIMIT 1;
elect = """
s
SELECT c.customer_name, sum(order_price) as \"Total gastado\"
FROM humai.Orders o
FULL JOIN humai.Customers c
N o.customer_id = c.customer_id
O
GROUP BY c.customer_name
ORDER BY sum(order_price)
DESC LIMIT 1;
"""
exec_sql(select)
customer_name Total gastado
0 Eugenio 175.00
7.Retornar la duración promedio de los clientes que se dieron de baja.
SELECT CAST(AVG(fecha_fin-fecha_inicio) AS INT) AS "Promedio Total dias" FROM
humai.Customers
WHERE fecha_fin IS NOT NULL
xec_sql("SELECT CAST(AVG(fecha_fin-fecha_inicio) AS INT) AS \"Promedio Total
e
FROM humai.Customers WHERE fecha_fin IS NOT NULL")
Promedio Total dias
0 699
8.Retornar los clientes que tienen una 'e' en el nombre.
SELECT
customer_name FROM
humai.Customers
WHERE customer_name LIKE '%e%';
xec_sql("SELECT customer_name FROM humai.Customers WHERE customer_name
e
LIKE '
customer_name
0 Eugenio
1 Pedro
Ejercicios Prácticos DML DDL
Ejercicio 1 DML
Ingresar dos nuevos clientes en la tabla Customers.
insert_customers = """
INSERT INTO humai.Customers VALUES
(4, 'Gustavo', '2024-04-20', NULL),
(5, 'Florencia', '2024-04-20', NULL); """
exec_sql(insert_customers)
insert_customers = """
INSERT INTO humai.Customers VALUES
(6, 'Santiago', '2004-01-11', NULL) """
exec_sql(insert_customers)
insert_customers = """
INSERT INTO humai.Customers VALUES
(7, 'Lautaro', '2004-01-13', '2004-03-12'),
(8, 'Ines', '2004-01-15', '2004.03.18'); """
exec_sql(insert_customers)# Conjunto de comandos con la función INSERT para
# incorporar Datos a una Tabla
select_customers = 'SELECT * FROM humai.Customers;'# Este comando me permite ver
# la tabla Customers
exec_sql(select_customers)
customer_id customer_nam fecha_inicio fecha_fin
e
0 1 Eugenio 1998-08-21 None
1 2 Mario 2005-05-05 None
2 3 Pedro 2020-03-08 2022-02-05
3 4 Gustavo 2024-04-20 None
4 5 Florencia 2024-04-20 None
5 6 Santiago 2004-01-11 None
6 7 Lautaro 2004-01-13 2004-03-12
7 8 Ines 2004-01-15 2004-03-18
Ejercicios 2 DML
Actualizar el valor de la orden con order_id 5 a $200.
elect_orders = 'SELECT * FROM humai.Orders;'
s
exec_sql(select_orders)
order_id customer_id order_date order_price
0 1 1 2022-06-05 45.00
1 2 1 2021-06-05 60.00
2 3 1 2022-06-06 70.00
3 4 2 2022-01-05 5.00
4 5 3 2022-06-10 145.00
5 6 3 2022-03-02 2.00
pdat_order = "UPDATE humai.Orders SET order_price = 200 WHERE order_id = 5"
u
exec_sql(updat_order)
Ejercicio 3 DML
Eliminar uno de los clientes agregados a la tabla Customers.
elete_customers = "DELETE FROM humai.Customers WHERE customer_id =
d
8" exec_sql(delete_customers)
Ejercicio 4 DML
Insertar una orden y su envio.
insert_orders = """
INSERT INTO humai.Orders
VALUES
(12, 1, '2024-01-02', '135'); """
exec_sql(insert_orders)
insert_shipments = """
INSERT INTO
humai.Shipments VALUES
(10, 6, '2024-01-03', 'La Plata'); """
exec_sql(insert_shipments)
Ejercicio 1 DDL
Calcular la cantidad de ventas realizadas en el 2022
elect_orders = """
s
SELECT COUNT (*) as "Ventas 2022"
FROM humai.Orders
WHERE EXTRACT (YEAR FROM order_date) =
2022; """
exec_sql(select_orders)
Ventas 2022
0 5
Ejercicio 2 DDL
Retornar la fecha de la primera venta registrada
elect_order = """
s
SELECT MIN (order_date) as "Primera Venta"
FROM humai.Orders;
"""
exec_sql(select_order)
Primera Venta
0 2021-06-05
Ejercicio 3 DLL
Retornar la venta de mayor dinero de cada cliente que tenga ventas
elect_order = """
s
SELECT MAX (order_price) as "Mayor Venta x Cliente"
FROM humai.Orders
GROUP BY (order_id);
"""
exec_sql(select_order)
MayorVenta x Cliente
0 60.00
1 70.00
2 200.00
3 5.00
4 2.00
5 45.00
Ejercicio 4 DDL
Retornar la cantidad de envios a cada ciudad que no sea Belgrano
elect_shipment = """
s
SELECT shipment_city as "Ciudad", count (*) as "Cant Envios"
FROM humai.Shipments
WHERE shipment_city <> 'Belgrano'
ROUP BY
G
shipment_city """
exec_sql(select_shipment)
Ciudad Cant Envios
0 Mar del Plata 2
1 San Isidro 1
Ejercicio 5 DDL
Calcular la cantidad de dias activos que tiene cada cliente que aún no se
han dado de baja
elect_customer = """
s
SELECT (fecha_fin-fecha_inicio) as "Dias Activos", customer_name as "Cliente" FROM
humai.Customers
WHERE fecha_fin IS NOT
NULL """
exec_sql(select_customer)
Dias Activos Cliente
0 699 Pedro
1 59 Lautaro
elect_customers = 'SELECT * FROM humai.Customers;'
s
exec_sql(select_customers)
customer_id customer_nam fecha_inicio fecha_fin
e
0 1 Eugenio 1998-08-21 None
1 2 Mario 2005-05-05 None
2 3 Pedro 2020-03-08 2022-02-05
3 4 Gustavo 2024-04-20 None
4 5 Florencia 2024-04-20 None
5 6 Santiago 2004-01-11 None
6 7 Lautaro 2004-01-13 2004-03-12
Ejercicio 6 DDL
alcular el promedio de cantidad de dias activos que tienen los clientes que
C
aún no se han dado de baja
elect_customer = """
s
SELECT AVG(fecha_fin-fecha_inicio) as "Promedio Total dias"
FROM humai.Customers
WHERE fecha_inicio IS NOT NULL
"""
exec_sql(select_customer)
Promedio Total dias
0 379.0000000000000000
xec_sql("SELECT CAST(AVG(fecha_fin-fecha_inicio) AS INT) AS \"Promedio Total
e
FROM humai.Customers WHERE fecha_fin IS NOT NULL")
Promedio Total dias
0 699
Ejercicio 7 DDL
Retornar la cantidad de envios realizados entre enero y junio del 2022 para
Mar del Plata
elect_shipment = """
s
SELECT count(*)
FROM humai.Shipments
HERE shipment_date >= '01-01-2022'
W
AND shipment_date <= '06-01-2022'
AND shipment_city = 'Mar del Plata' """
exec_sql(select_shipment)
count
0 1
Ejercicio 8 DDL
antidad gastada por cliente con sus nombres para aquellos clientes que
C
hayan gastado mas de $20
uery = """
q
SELECT customer_name, SUM(order_price) AS total_order_price
FROM Humai.Customers
INNER JOIN Humai.Orders
N Humai.Customers.customer_id = Humai.Orders.customer_id
O
WHERE order_price > 20
GROUP BY Humai.Customers.customer_id
;
"""
exec_sql(query)
customer_name total_order_price
0 Pedro 200.00
1 Maria 1000.00
2 Eugenio 175.00
