Neste laboratório, você explorará relacionamentos e joins (junções) no PostgreSQL. Aprenderá a criar tabelas com restrições de chave estrangeira (foreign key) para garantir a integridade dos dados.
Começará criando duas tabelas, customers e orders, e estabelecendo um relacionamento de chave estrangeira entre elas. Em seguida, inserirá dados nessas tabelas, certificando-se de que os dados adiram ao relacionamento definido. Finalmente, aprenderá a recuperar dados usando INNER JOIN e comparará os resultados das operações LEFT, RIGHT e FULL OUTER JOIN para entender como elas lidam com diferentes relacionamentos de dados.
Este é um Lab Guiado, que fornece instruções passo a passo para ajudá-lo a aprender e praticar. Siga as instruções cuidadosamente para completar cada etapa e ganhar experiência prática. Dados históricos mostram que este é um laboratório de nível iniciante com uma taxa de conclusão de 80%. Recebeu uma taxa de avaliações positivas de 100% dos estudantes.
Criar Tabelas com Restrições de Chave Estrangeira
Nesta etapa, você criará duas tabelas, customers e orders, e estabelecerá uma restrição de chave estrangeira entre elas. Essa restrição garante que o relacionamento entre as tabelas seja mantido, impedindo a entrada de dados inválidos.
Entendendo Chaves Estrangeiras
Uma chave estrangeira (foreign key) é uma coluna em uma tabela que referencia a chave primária (primary key) de outra tabela. Ela estabelece um link entre as duas tabelas. A tabela que contém a chave estrangeira é chamada de tabela "filha" (child), e a tabela que contém a chave primária é chamada de tabela "pai" (parent).
Passo 1: Conectar ao PostgreSQL
Abra um terminal na sua VM LabEx. Conecte-se ao banco de dados PostgreSQL usando o comando psql:
sudo -u postgres psql
Você deve ver o prompt do PostgreSQL (postgres=#).
Passo 2: Criar a Tabela customers
Crie a tabela customers com as seguintes colunas:
customer_id: Um identificador único para cada cliente (chave primária).
first_name: O primeiro nome do cliente.
last_name: O sobrenome do cliente.
email: O endereço de e-mail do cliente (deve ser único).
Execute o seguinte comando SQL no shell 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 cria a tabela customers. A palavra-chave SERIAL gera automaticamente uma sequência de números para o customer_id, tornando-o auto-incrementável. PRIMARY KEY designa customer_id como a chave primária. NOT NULL garante que as colunas first_name e last_name não possam estar vazias, e UNIQUE garante que cada endereço de e-mail seja único.
Passo 3: Criar a Tabela orders com uma Chave Estrangeira
Crie a tabela orders com as seguintes colunas:
order_id: Um identificador único para cada pedido (chave primária).
customer_id: O ID do cliente que fez o pedido (chave estrangeira referenciando customers).
order_date: A data em que o pedido foi feito.
total_amount: O valor total do pedido.
Execute o seguinte comando SQL no shell 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 cria a tabela orders. A coluna customer_id é definida como uma chave estrangeira usando a palavra-chave REFERENCES. REFERENCES customers(customer_id) especifica que a coluna customer_id na tabela orders referencia a coluna customer_id na tabela customers. Isso estabelece o relacionamento de chave estrangeira.
Passo 4: Verificar a Criação da Tabela
Verifique se as tabelas foram criadas com sucesso listando as tabelas no banco de dados. Execute o seguinte comando no shell psql:
\dt
Você deve ver as tabelas customers e orders listadas.
Passo 5: Descrever as Tabelas
Para ver a estrutura das tabelas, use o comando \d seguido pelo nome da tabela. Por exemplo, para descrever a tabela customers, execute:
\d customers
Isso mostrará as colunas, tipos de dados e restrições definidas para a tabela customers. Da mesma forma, você pode descrever a tabela orders:
\d orders
Isso mostrará a restrição de chave estrangeira na coluna customer_id.
Você criou com sucesso duas tabelas com uma restrição de chave estrangeira.
Inserir Dados e Aplicar Integridade Referencial
Nesta etapa, você inserirá dados nas tabelas customers e orders, garantindo que a integridade referencial seja mantida. Isso significa que você não pode adicionar um pedido para um cliente que não existe na tabela customers.
Entendendo a Integridade Referencial
A integridade referencial garante que os relacionamentos entre as tabelas permaneçam consistentes. Em nosso caso, isso significa que o customer_id na tabela orders deve existir na tabela customers.
Passo 1: Inserir Dados na Tabela customers
Insira dados na tabela customers usando o seguinte comando SQL no shell psql:
Este comando adiciona três clientes à tabela customers. O customer_id é gerado automaticamente.
Passo 2: Verificar a Inserção de Dados na Tabela customers
Verifique se os dados foram inseridos corretamente consultando a tabela customers:
SELECT * FROM customers;
Você deve ver os três clientes que acabou de inserir, juntamente com seus valores customer_id gerados automaticamente. A saída deve ser semelhante 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)
Passo 3: Inserir Dados na Tabela orders
Insira dados na tabela orders, referenciando os valores customer_id da tabela customers:
Passo 5: Tentar Inserir Dados Inválidos (Demonstrando Integridade Referencial)
Para demonstrar a restrição de integridade referencial, tente inserir um pedido com um customer_id que não existe na tabela customers:
INSERT INTO orders (customer_id, order_date, total_amount) VALUES
(4, '2023-11-20', 50.00);
Você deve ver uma mensagem de erro semelhante a esta:
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".
Esta mensagem de erro confirma que a restrição de chave estrangeira está funcionando. O banco de dados impede a inserção do pedido porque o customer_id 4 não existe na tabela customers.
Você inseriu com sucesso dados nas tabelas customers e orders, garantindo a integridade referencial.
Consultar Dados Usando INNER JOIN
Nesta etapa, você aprenderá como recuperar dados de várias tabelas usando a cláusula INNER JOIN no PostgreSQL. INNER JOIN combina linhas de duas ou mais tabelas com base em uma coluna relacionada.
Entendendo INNER JOIN
Um INNER JOIN retorna apenas as linhas onde há uma correspondência em ambas as tabelas que estão sendo unidas. Se não houver correspondência, a linha é excluída do resultado.
Passo 1: Conectar ao PostgreSQL
Certifique-se de estar conectado ao banco de dados PostgreSQL usando o comando psql:
sudo -u postgres psql
Passo 2: Executar a Consulta INNER JOIN
Execute a seguinte consulta SQL no shell 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;
Vamos detalhar esta consulta:
SELECT orders.order_id, customers.first_name, orders.order_date, orders.total_amount: Isso especifica as colunas que você deseja recuperar das tabelas orders e customers. Usar o nome da tabela como um prefixo (por exemplo, orders.order_id) esclarece de qual tabela cada coluna vem.
FROM orders: Isso especifica a primeira tabela da qual você está consultando.
INNER JOIN customers ON orders.customer_id = customers.customer_id: Isso especifica a segunda tabela que você está unindo (customers) e a condição de junção (orders.customer_id = customers.customer_id). A cláusula ON especifica que o customer_id na tabela orders deve corresponder ao customer_id na tabela customers para que as linhas sejam incluídas.
Passo 3: Analisar os Resultados
A consulta retornará um conjunto de resultados contendo o ID do pedido, o primeiro nome do cliente, a data do pedido e o valor total de cada pedido. A saída deve ser semelhante 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)
A consulta uniu com sucesso as tabelas orders e customers com base no customer_id e recuperou as informações solicitadas. Apenas os pedidos com um cliente correspondente na tabela customers são incluídos.
Passo 4: Usando Aliases (Opcional)
Para consultas mais complexas, você pode usar aliases para tornar a consulta mais legível. A consulta anterior pode ser reescrita usando aliases:
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;
Nesta consulta, o é um alias para orders e c é um alias para customers. O resultado será o mesmo, mas a consulta é mais concisa.
Você consultou com sucesso dados de várias tabelas usando a cláusula INNER JOIN.
Comparar Resultados de LEFT, RIGHT e FULL OUTER JOIN
Nesta etapa, você explorará e comparará os resultados de LEFT OUTER JOIN, RIGHT OUTER JOIN e FULL OUTER JOIN no PostgreSQL. Esses joins recuperam todas as linhas de uma ou ambas as tabelas, mesmo que não haja valores correspondentes na outra tabela.
Entendendo OUTER JOINs
LEFT OUTER JOIN (ou LEFT JOIN): Retorna todas as linhas da tabela da esquerda e as linhas correspondentes da tabela da direita. Se não houver correspondência na tabela da direita, valores NULL são retornados para as colunas da tabela da direita.
RIGHT OUTER JOIN (ou RIGHT JOIN): Retorna todas as linhas da tabela da direita e as linhas correspondentes da tabela da esquerda. Se não houver correspondência na tabela da esquerda, valores NULL são retornados para as colunas da tabela da esquerda.
FULL OUTER JOIN (ou FULL JOIN): Retorna todas as linhas de ambas as tabelas. Se não houver correspondência em uma tabela, valores NULL são retornados para as colunas da outra tabela.
Passo 1: Conectar ao PostgreSQL
Certifique-se de estar conectado ao banco de dados PostgreSQL usando o comando psql:
sudo -u postgres psql
Passo 2: Inserir um Novo Cliente sem Pedidos
Insira um novo cliente na tabela customers que não fez nenhum pedido:
INSERT INTO customers (first_name, last_name, email) VALUES
('Alice', 'Brown', 'alice.brown@example.com');
Passo 3: Verificar o Novo Cliente
Verifique se o novo cliente foi adicionado à tabela customers:
SELECT * FROM customers;
Você deve ver Alice Brown listada nos resultados, com um novo customer_id (provavelmente 4).
Passo 4: Executar um LEFT OUTER JOIN
Execute a seguinte consulta SQL para executar um LEFT OUTER JOIN entre as tabelas customers e 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 retorna todos os clientes, juntamente com quaisquer pedidos que eles fizeram. Se um cliente não fez nenhum pedido, as colunas order_id e order_date conterão valores NULL. A saída deve ser semelhante 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)
Observe que Alice Brown está incluída, embora ela não tenha feito nenhum pedido. As colunas order_id e order_date são NULL para ela.
Passo 5: Executar um RIGHT OUTER JOIN
Execute a seguinte consulta SQL para executar um RIGHT OUTER JOIN entre as tabelas customers e 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 retorna todos os pedidos, juntamente com o primeiro nome do cliente que fez cada pedido. Como cada pedido tem um cliente correspondente, o resultado será o mesmo que um INNER JOIN neste caso. A saída deve ser semelhante 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)
Passo 6: Executar um FULL OUTER JOIN
Execute a seguinte consulta SQL para executar um FULL OUTER JOIN entre as tabelas customers e 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 retorna todos os clientes e todos os pedidos. Se um cliente não fez nenhum pedido, as colunas order_id e order_date conterão valores NULL. Se um pedido não tiver um cliente correspondente (o que não é possível em nossa configuração atual devido à restrição de chave estrangeira), a coluna first_name conteria valores NULL. A saída deve ser semelhante 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)
Observe que Alice Brown está incluída com valores NULL para order_id e order_date.
Passo 7: Entendendo as Diferenças
LEFT OUTER JOIN inclui todas as linhas da tabela customers, mesmo que não haja pedidos correspondentes.
RIGHT OUTER JOIN inclui todas as linhas da tabela orders. Em nosso caso, ele se comporta como um INNER JOIN porque todos os pedidos têm um cliente correspondente.
FULL OUTER JOIN inclui todas as linhas de ambas as tabelas.
Você explorou e comparou com sucesso os resultados de LEFT OUTER JOIN, RIGHT OUTER JOIN e FULL OUTER JOIN no PostgreSQL.
Resumo
Neste laboratório, você aprendeu como criar tabelas com restrições de chave estrangeira (foreign key constraints) no PostgreSQL para manter a integridade dos dados. Você criou as tabelas customers e orders e estabeleceu uma relação entre elas usando uma chave estrangeira. Em seguida, você inseriu dados nessas tabelas, garantindo que a restrição de chave estrangeira fosse aplicada. Por fim, você explorou diferentes tipos de operações JOIN (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN e FULL OUTER JOIN) para recuperar dados de tabelas relacionadas e entender como elas lidam com diferentes relacionamentos de dados.
