En este laboratorio, explorarás las relaciones y las uniones (joins) en PostgreSQL. Aprenderás cómo crear tablas con restricciones de clave externa (foreign key constraints) para garantizar la integridad de los datos.
Comenzarás creando dos tablas, customers y orders, y establecerás una relación de clave externa entre ellas. Luego, insertarás datos en estas tablas, asegurándote de que los datos se adhieran a la relación definida. Finalmente, aprenderás cómo recuperar datos utilizando INNER JOIN y comparar los resultados de las operaciones LEFT, RIGHT y FULL OUTER JOIN para comprender cómo manejan diferentes relaciones de datos.
Este es un Guided Lab, que proporciona instrucciones paso a paso para ayudarte a aprender y practicar. Sigue las instrucciones cuidadosamente para completar cada paso y obtener experiencia práctica. Los datos históricos muestran que este es un laboratorio de nivel principiante con una tasa de finalización del 80%. Ha recibido una tasa de reseñas positivas del 100% por parte de los estudiantes.
Crear Tablas con Restricciones de Clave Externa (Foreign Key Constraints)
En este paso, crearás dos tablas, customers y orders, y establecerás una restricción de clave externa entre ellas. Esta restricción asegura que la relación entre las tablas se mantenga, evitando que se ingresen datos no válidos.
Entendiendo las Claves Externas (Foreign Keys)
Una clave externa (foreign key) es una columna en una tabla que hace referencia a la clave primaria (primary key) de otra tabla. Establece un vínculo entre las dos tablas. La tabla que contiene la clave externa se llama tabla "hija" (child), y la tabla que contiene la clave primaria se llama tabla "padre" (parent).
Paso 1: Conectarse a PostgreSQL
Abre una terminal en tu VM de LabEx. Conéctate a la base de datos PostgreSQL utilizando el comando psql:
sudo -u postgres psql
Ahora deberías ver el prompt de PostgreSQL (postgres=#).
Paso 2: Crear la Tabla customers
Crea la tabla customers con las siguientes columnas:
customer_id: Un identificador único para cada cliente (clave primaria).
first_name: El nombre del cliente.
last_name: El apellido del cliente.
email: La dirección de correo electrónico del cliente (debe ser única).
Ejecuta el siguiente comando SQL en el shell de psql:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
);
Este comando crea la tabla customers. La palabra clave SERIAL genera automáticamente una secuencia de números para el customer_id, haciéndolo autoincrementable. PRIMARY KEY designa customer_id como la clave primaria. NOT NULL asegura que las columnas first_name y last_name no puedan estar vacías, y UNIQUE asegura que cada dirección de correo electrónico sea única.
Paso 3: Crear la Tabla orders con una Clave Externa (Foreign Key)
Crea la tabla orders con las siguientes columnas:
order_id: Un identificador único para cada pedido (clave primaria).
customer_id: El ID del cliente que realizó el pedido (clave externa que hace referencia a customers).
order_date: La fecha en que se realizó el pedido.
total_amount: El importe total del pedido.
Ejecuta el siguiente comando SQL en el shell de psql:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
order_date DATE NOT NULL,
total_amount NUMERIC(10, 2) NOT NULL
);
Este comando crea la tabla orders. La columna customer_id se define como una clave externa utilizando la palabra clave REFERENCES. REFERENCES customers(customer_id) especifica que la columna customer_id en la tabla orders hace referencia a la columna customer_id en la tabla customers. Esto establece la relación de clave externa.
Paso 4: Verificar la Creación de la Tabla
Verifica que las tablas se hayan creado correctamente listando las tablas en la base de datos. Ejecuta el siguiente comando en el shell de psql:
\dt
Deberías ver las tablas customers y orders listadas.
Paso 5: Describir las Tablas
Para ver la estructura de las tablas, utiliza el comando \d seguido del nombre de la tabla. Por ejemplo, para describir la tabla customers, ejecuta:
\d customers
Esto te mostrará las columnas, los tipos de datos y las restricciones definidas para la tabla customers. De manera similar, puedes describir la tabla orders:
\d orders
Esto te mostrará la restricción de clave externa en la columna customer_id.
Ahora has creado con éxito dos tablas con una restricción de clave externa.
Insertar Datos y Aplicar la Integridad Referencial
En este paso, insertarás datos en las tablas customers y orders, asegurando que se mantenga la integridad referencial. Esto significa que no puedes agregar un pedido para un cliente que no existe en la tabla customers.
Entendiendo la Integridad Referencial (Referential Integrity)
La integridad referencial asegura que las relaciones entre las tablas permanezcan consistentes. En nuestro caso, significa que el customer_id en la tabla orders debe existir en la tabla customers.
Paso 1: Insertar Datos en la Tabla customers
Inserta datos en la tabla customers utilizando el siguiente comando SQL en el shell de psql:
Este comando agrega tres clientes a la tabla customers. El customer_id se genera automáticamente.
Paso 2: Verificar la Inserción de Datos en la Tabla customers
Verifica que los datos se hayan insertado correctamente consultando la tabla customers:
SELECT * FROM customers;
Deberías ver los tres clientes que acabas de insertar, junto con sus valores de customer_id generados automáticamente. La salida debería ser similar a esta:
customer_id | first_name | last_name | email
-------------+------------+-----------+------------------------
1 | John | Doe | john.doe@example.com
2 | Jane | Smith | jane.smith@example.com
3 | David | Lee | david.lee@example.com
(3 rows)
Paso 3: Insertar Datos en la Tabla orders
Inserta datos en la tabla orders, haciendo referencia a los valores de customer_id de la tabla customers:
Paso 5: Intentar Insertar Datos No Válidos (Demostrando la Integridad Referencial)
Para demostrar la restricción de integridad referencial, intenta insertar un pedido con un customer_id que no existe en la tabla customers:
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);
Deberías ver un mensaje de error similar a este:
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(4) is not present in table "customers".
Este mensaje de error confirma que la restricción de clave externa (foreign key constraint) está funcionando. La base de datos impide la inserción del pedido porque el customer_id 4 no existe en la tabla customers.
Ahora has insertado con éxito datos en las tablas customers y orders, asegurando la integridad referencial.
Consultar Datos Usando INNER JOIN
En este paso, aprenderás cómo recuperar datos de múltiples tablas utilizando la cláusula INNER JOIN en PostgreSQL. INNER JOIN combina filas de dos o más tablas basándose en una columna relacionada.
Entendiendo INNER JOIN
Un INNER JOIN devuelve solo las filas donde hay una coincidencia en ambas tablas que se están uniendo. Si no hay coincidencia, la fila se excluye del resultado.
Paso 1: Conectarse a PostgreSQL
Asegúrate de estar conectado a la base de datos PostgreSQL utilizando el comando psql:
sudo -u postgres psql
Paso 2: Ejecutar la Consulta INNER JOIN
Ejecuta la siguiente consulta SQL en el shell de psql:
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
Analicemos esta consulta:
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: Esto especifica las columnas que deseas recuperar de las tablas orders y customers. Utilizar el nombre de la tabla como prefijo (por ejemplo, orders.order_id) aclara de qué tabla proviene cada columna.
FROM orders: Esto especifica la primera tabla desde la que estás consultando.
INNER JOIN customers ON orders.customer_id = customers.customer_id: Esto especifica la segunda tabla con la que te estás uniendo (customers) y la condición de unión (orders.customer_id = customers.customer_id). La cláusula ON especifica que el customer_id en la tabla orders debe coincidir con el customer_id en la tabla customers para que las filas se incluyan.
Paso 3: Analizar los Resultados
La consulta devolverá un conjunto de resultados que contiene el ID del pedido, el nombre del cliente, la fecha del pedido y el importe total de cada pedido. La salida debería ser similar a esta:
order_id | first_name | order_date | total_amount
----------+------------+------------+--------------
1 | John | 2023-11-01 | 100.00
3 | John | 2023-11-10 | 75.25
2 | Jane | 2023-11-05 | 250.50
4 | David | 2023-11-15 | 120.00
(4 rows)
La consulta unió con éxito las tablas orders y customers basándose en el customer_id y recuperó la información solicitada. Solo se incluyen los pedidos con un cliente correspondiente en la tabla customers.
Paso 4: Usando Alias (Opcional)
Para consultas más complejas, puedes utilizar alias para hacer que la consulta sea más legible. La consulta anterior se puede reescribir utilizando alias:
SELECT o.order_id, c.first_name, o.order_date, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
En esta consulta, o es un alias para orders y c es un alias para customers. El resultado será el mismo, pero la consulta es más concisa.
Ahora has consultado con éxito datos de múltiples tablas utilizando la cláusula INNER JOIN.
Comparar Resultados de LEFT, RIGHT y FULL OUTER JOIN
En este paso, explorarás y compararás los resultados de LEFT OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN en PostgreSQL. Estas uniones (joins) recuperan todas las filas de una o ambas tablas, incluso si no hay valores coincidentes en la otra tabla.
Entendiendo los OUTER JOINs
LEFT OUTER JOIN (o LEFT JOIN): Devuelve todas las filas de la tabla izquierda y las filas coincidentes de la tabla derecha. Si no hay coincidencia en la tabla derecha, se devuelven valores NULL para las columnas de la tabla derecha.
RIGHT OUTER JOIN (o RIGHT JOIN): Devuelve todas las filas de la tabla derecha y las filas coincidentes de la tabla izquierda. Si no hay coincidencia en la tabla izquierda, se devuelven valores NULL para las columnas de la tabla izquierda.
FULL OUTER JOIN (o FULL JOIN): Devuelve todas las filas de ambas tablas. Si no hay coincidencia en una tabla, se devuelven valores NULL para las columnas de la otra tabla.
Paso 1: Conectarse a PostgreSQL
Asegúrate de estar conectado a la base de datos PostgreSQL utilizando el comando psql:
sudo -u postgres psql
Paso 2: Insertar un Nuevo Cliente sin Pedidos
Inserta un nuevo cliente en la tabla customers que no haya realizado ningún pedido:
INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');
Paso 3: Verificar el Nuevo Cliente
Verifica que el nuevo cliente se haya agregado a la tabla customers:
SELECT * FROM customers;
Deberías ver a Alice Brown en los resultados, con un nuevo customer_id (probablemente 4).
Paso 4: Realizar un LEFT OUTER JOIN
Ejecuta la siguiente consulta SQL para realizar un LEFT OUTER JOIN entre las tablas customers y orders:
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
Esta consulta devuelve todos los clientes, junto con los pedidos que hayan realizado. Si un cliente no ha realizado ningún pedido, las columnas order_id y order_date contendrán valores NULL. La salida debería ser similar a esta:
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)
Observa que Alice Brown está incluida, aunque no haya realizado ningún pedido. Las columnas order_id y order_date son NULL para ella.
Paso 5: Realizar un RIGHT OUTER JOIN
Ejecuta la siguiente consulta SQL para realizar un RIGHT OUTER JOIN entre las tablas customers y orders:
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
RIGHT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
Esta consulta devuelve todos los pedidos, junto con el nombre del cliente que realizó cada pedido. Dado que cada pedido tiene un cliente correspondiente, el resultado será el mismo que un INNER JOIN en este caso. La salida debería ser similar a esta:
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
Jane | 2 | 2023-11-05
John | 3 | 2023-11-10
David | 4 | 2023-11-15
(4 rows)
Paso 6: Realizar un FULL OUTER JOIN
Ejecuta la siguiente consulta SQL para realizar un FULL OUTER JOIN entre las tablas customers y orders:
SELECT customers.first_name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
Esta consulta devuelve todos los clientes y todos los pedidos. Si un cliente no ha realizado ningún pedido, las columnas order_id y order_date contendrán valores NULL. Si un pedido no tiene un cliente correspondiente (lo cual no es posible en nuestra configuración actual debido a la restricción de clave externa (foreign key constraint)), la columna first_name contendría valores NULL. La salida debería ser similar a esta:
first_name | order_id | order_date
------------+----------+------------
John | 1 | 2023-11-01
John | 3 | 2023-11-10
Jane | 2 | 2023-11-05
David | 4 | 2023-11-15
Alice | |
(5 rows)
Observa que Alice Brown está incluida con valores NULL para order_id y order_date.
Paso 7: Entendiendo las Diferencias
LEFT OUTER JOIN incluye todas las filas de la tabla customers, incluso si no hay pedidos coincidentes.
RIGHT OUTER JOIN incluye todas las filas de la tabla orders. En nuestro caso, se comporta como un INNER JOIN porque todos los pedidos tienen un cliente correspondiente.
FULL OUTER JOIN incluye todas las filas de ambas tablas.
Ahora has explorado y comparado los resultados de LEFT OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN en PostgreSQL.
Resumen
En este laboratorio, aprendiste cómo crear tablas con restricciones de clave externa (foreign key constraints) en PostgreSQL para mantener la integridad de los datos. Creaste las tablas customers y orders y estableciste una relación entre ellas utilizando una clave externa. Luego, insertaste datos en estas tablas, asegurándote de que se aplicara la restricción de clave externa. Finalmente, exploraste diferentes tipos de operaciones JOIN (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN y FULL OUTER JOIN) para recuperar datos de tablas relacionadas y comprender cómo manejan diferentes relaciones de datos.
