Aprenda / SQL e banco de dados

FundamentosAula 8 de 1624 min0% da trilha

Projeto guiado: modelando e consultando um schema de e-commerce

4 tabelas relacionadas, do CREATE TABLE a uma consulta de relatório com JOIN + agregação.

1. O schema — 4 tabelas relacionadas

CREATE TABLE usuarios (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE produtos (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(255) NOT NULL,
  preco DECIMAL NOT NULL,
  categoria VARCHAR(100)
);

CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  usuario_id INTEGER REFERENCES usuarios(id),
  criado_em TIMESTAMP DEFAULT NOW()
);

CREATE TABLE itens_pedido (
  id SERIAL PRIMARY KEY,
  pedido_id INTEGER REFERENCES pedidos(id) ON DELETE CASCADE,
  produto_id INTEGER REFERENCES produtos(id),
  quantidade INTEGER NOT NULL,
  preco_unitario DECIMAL NOT NULL -- preço no momento da compra, não o preço atual do produto
);

preco_unitario em itens_pedido não é redundante — é proposital: produtos.preco muda com o tempo, mas o valor pago num pedido antigo não deveria mudar retroativamente só porque o produto ficou mais caro depois.

2. Inserindo dado

INSERT INTO usuarios (nome, email) VALUES ('Ana', 'ana@exemplo.com');
INSERT INTO produtos (nome, preco, categoria) VALUES ('Teclado', 250.00, 'eletronico');
INSERT INTO pedidos (usuario_id) VALUES (1);
INSERT INTO itens_pedido (pedido_id, produto_id, quantidade, preco_unitario)
VALUES (1, 1, 1, 250.00);

3. O total de cada pedido — JOIN + agregação

SQL
SELECT
  pedidos.id AS pedido_id,
  SUM(itens_pedido.quantidade * itens_pedido.preco_unitario) AS total
FROM pedidos
JOIN itens_pedido ON itens_pedido.pedido_id = pedidos.id
GROUP BY pedidos.id;

4. Relatório: os 5 usuários que mais gastaram

SELECT
  usuarios.nome,
  SUM(itens_pedido.quantidade * itens_pedido.preco_unitario) AS total_gasto
FROM usuarios
JOIN pedidos ON pedidos.usuario_id = usuarios.id
JOIN itens_pedido ON itens_pedido.pedido_id = pedidos.id
GROUP BY usuarios.nome
ORDER BY total_gasto DESC
LIMIT 5;

5. Produtos nunca vendidos — LEFT JOIN pra achar "ausência"

SELECT produtos.nome
FROM produtos
LEFT JOIN itens_pedido ON itens_pedido.produto_id = produtos.id
WHERE itens_pedido.id IS NULL;
-- LEFT JOIN traz todo produto; WHERE ... IS NULL filtra só os que NUNCA
-- combinaram com nenhuma linha de itens_pedido — ou seja, nunca foram vendidos

Exercício

Escreva a consulta que mostra a categoria de produto e o total vendido (quantidade × preço_unitario) por categoria, só das categorias que já venderam mais de R$ 1000 no total.

Quiz rápido

Por que itens_pedido guarda preco_unitario, em vez de sempre consultar produtos.preco?
Como a consulta de 'produtos nunca vendidos' usa LEFT JOIN + WHERE ... IS NULL?