O Banco da Loja Aurora

Todas as aulas deste guia usam o mesmo banco de dados: o da Loja Aurora, uma loja de departamentos fictícia com sede em Teresina, no Piauí, que vende de tudo um pouco: papelaria, eletrônicos, utensílios de cozinha, livros, brinquedos, artigos de esporte, alimentos e coisas de jardim. Ela tem clientes em várias cidades, uma pequena equipe e quase três anos de pedidos registrados. Antes de começar a escrever consultas, vale conhecer a casa: quais tabelas existem, o que cada uma guarda e como elas se ligam. É o que esta aula faz, tabela por tabela.

Os dados são inventados (nomes, emails, telefones, tudo fictício) e fixos: são os mesmos para todo mundo, sempre. No editor do guia, cada Rodar começa do banco limpo; no seu computador, é o loja.db que você criou em Instalar o SQLite (montado pelo aurora.sql ou baixado pronto como aurora.db: dá o mesmo arquivo). Por isso, todo resultado das aulas é o que você vai ver também.

A ideia: os cadernos da loja

Imagine a loja antes do computador. A dona tinha um caderno para cada assunto, e cada caderno anotava só o seu:

• o caderno dos departamentos (papelaria, eletrônicos…);
• o caderno dos fornecedores, as empresas que vendem para a loja;
• o caderno do estoque, um produto por linha, com preço e quantidade;
• o caderno dos clientes;
• o caderno dos funcionários;
• o talão de pedidos, um por venda;
• e, em cada folha do talão, as linhas do pedido: o que foi levado, quantas unidades, a que preço.

Cada caderno virou uma tabela. E quando um caderno precisa falar de outro (o pedido precisa dizer quem é o cliente), ele não copia o nome: anota o número da ficha do cliente. É o id que você conheceu em O que é um Banco de Dados.

O mapa

fornecedores (6)              categorias (8)
       ▲                             ▲
       │ fornecedor_id  categoria_id │
       └──────── produtos (45) ──────┘
                      ▲
                      │ produto_id
              itens_pedido (220)
                      │ pedido_id
                      ▼
clientes (30) ◄─── pedidos (90) ───► funcionarios (8) ◄─┐
          cliente_id       funcionario_id      │        │
                                               └────────┘
                                                chefe_id

Leia cada seta como "aponta para": um produto aponta para a sua categoria e o seu fornecedor; um pedido aponta para o cliente que comprou e para o funcionário que atendeu; cada item de pedido aponta para o pedido de que faz parte e para o produto vendido; e cada funcionário aponta para o seu chefe, que é outro funcionário. Os números entre parênteses são as linhas de cada tabela.

SELECT 'categorias', COUNT(*) FROM categorias
UNION ALL SELECT 'fornecedores', COUNT(*) FROM fornecedores
UNION ALL SELECT 'produtos', COUNT(*) FROM produtos
UNION ALL SELECT 'clientes', COUNT(*) FROM clientes
UNION ALL SELECT 'funcionarios', COUNT(*) FROM funcionarios
UNION ALL SELECT 'pedidos', COUNT(*) FROM pedidos
UNION ALL SELECT 'itens_pedido', COUNT(*) FROM itens_pedido;

Essa consulta contou as linhas das sete tabelas e empilhou as respostas. Ela usa coisas que você só vai aprender no Nível 2 (UNION ALL); por enquanto, olhe só o resultado.

1. categorias: os departamentos

A tabela mais simples: os 8 departamentos da loja.

SELECT * FROM categorias;

2. fornecedores: quem vende para a loja

As 6 empresas que abastecem o estoque. A maioria é brasileira, mas há fornecedores de fora do país.

SELECT * FROM fornecedores;

Repare em duas coisas, que vão render aulas: as empresas de Portugal e da Argentina não têm estado (aparece NULL), e uma das empresas brasileiras tem um apóstrofo no nome. Como escrever esse nome entre aspas simples sem confundir o SQLite? Você vai descobrir quando chegar a hora.

3. produtos: o estoque

O coração da loja: os 45 produtos à venda, com preço e quantidade na prateleira.

SELECT * FROM produtos LIMIT 5;

O LIMIT 5 mostra só os 5 primeiros (aula LIMIT e OFFSET). Leia a linha do lápis: categoria_id 1 (Papelaria) e fornecedor_id 6 (Norte Suprimentos, de Teresina). O descontinuado usa 0 e 1 no lugar de "não" e "sim": o SQLite não tem um tipo próprio para verdadeiro/falso, e esse é o costume (aula Os Tipos no SQLite).

4. clientes: quem compra

Os 30 clientes cadastrados, do Piauí, do Ceará, de Pernambuco e de São Paulo.

SELECT * FROM clientes LIMIT 5;

A data de nascimento do primeiro cliente está como 1985-03-12, e não "12/03/1985". É o padrão internacional (ISO), e é o que todo banco entende melhor: em ordem alfabética, as datas ficam também em ordem cronológica. As datas têm aulas próprias: Datas e Horas e Funções de Data.

5. funcionarios: a equipe

As 8 pessoas que trabalham na loja, com cargo, salário e chefe.

SELECT * FROM funcionarios;

O chefe_id é a ligação mais curiosa do banco: ele aponta para outra linha da mesma tabela. O chefe do Igor (id 6) é a funcionária de id 5, a Luana Castelo, supervisora; e o chefe da Luana é a de id 1, a gerente Marta Lustosa. E a Marta? Ela não tem chefe, e o seu chefe_id é NULL. Montar esse "organograma" com uma consulta só é a aula Self Join.

6. pedidos: o talão de vendas

Os 90 pedidos feitos de janeiro de 2024 a outubro de 2026. Cada linha é uma venda: quem comprou, quem atendeu, quando, quando chegou e quanto custou o frete.

SELECT * FROM pedidos LIMIT 5;

Repare: o pedido não guarda o que foi comprado, nem o valor total. Um pedido pode ter vários produtos, e uma linha não tem lugar para "vários". Para isso existe a última tabela.

7. itens_pedido: o que foi comprado

Cada linha é um produto dentro de um pedido: "no pedido 1, foram 2 unidades do produto 1, a R$ 12,35 cada". São 220 itens no total.

Esta tabela não tem uma coluna id própria. Quem identifica cada linha é o par pedido_id + produto_id: o mesmo produto não aparece duas vezes no mesmo pedido (se a cliente levou três lápis, é uma linha com quantidade 3). Isso se chama chave primária composta, e você a verá em PRIMARY KEY. Uma tabela assim, que liga duas outras (pedidos e produtos), é o assunto de Relação Muitos-para-Muitos.

E por que guardar o preço de novo, se o produto já tem preco? Porque preços mudam. O preco da tabela de produtos é o de hoje; o preco_unitario é o que o cliente pagou naquele dia. Uma nota fiscal antiga não pode mudar quando a loja reajusta a tabela de preços.

8. Seguindo um pedido de ponta a ponta

Agora junte tudo. Vamos descobrir, só com o que você já viu, a história completa do pedido 1. É um trabalho de detetive: cada resposta dá a pista para a próxima pergunta.

Passo 1: o pedido.

SELECT * FROM pedidos WHERE id = 1;

Foi feito em 5 de janeiro de 2024 e entregue no dia 10, com frete de R$ 9,90. Quem comprou foi o cliente 1, e quem atendeu foi o funcionário 2.

Passo 2: quem comprou e quem atendeu.

SELECT id, nome FROM clientes WHERE id = 1;
SELECT id, nome, cargo FROM funcionarios WHERE id = 2;

O José Ribamar Silva foi atendido pelo vendedor Caio Teles.

Passo 3: o que foi comprado.

SELECT * FROM itens_pedido WHERE pedido_id = 1;

Três itens: produtos 1, 2 e 3. Que produtos são esses?

SELECT id, nome, preco FROM produtos WHERE id IN (1, 2, 3);

2 cadernos, 1 caneta azul e 3 lápis: material escolar, bem a tempo da volta às aulas. (O IN (1, 2, 3) quer dizer "o id é 1, 2 ou 3"; aula IN.)

Passo 4: quanto custou. Cada item vale quantidade × preco_unitario: 2 × 12,35 + 1 × 2,79 + 3 × 1,15. O banco faz a conta:

SELECT SUM(quantidade * preco_unitario)
FROM itens_pedido
WHERE pedido_id = 1;

Os produtos somam R$ 30,94. Com o frete de R$ 9,90, o José pagou R$ 40,84. O SUM ("soma") tem aula própria: SUM().

Foram cinco consultas para uma história. No Nível 2 você vai aprender o JOIN, que junta as tabelas e conta a mesma história numa consulta só. Mas a ideia é exatamente esta: seguir os ids de uma tabela para outra.

SELECT * FROM pedidos WHERE id = 2;

SELECT * FROM pedidos WHERE id = 2;
SELECT id, nome, cargo FROM funcionarios WHERE id = 3;

9. Os casos especiais (de propósito)

Um banco de verdade nunca é perfeitinho: falta o email de um cliente, um produto encalha, um pedido atrasa. A Loja Aurora foi montada com esses "defeitos" de propósito, para cada aula ter um caso real a resolver. Sem estragar a surpresa, aqui está o que espera por você:

Quem são esses clientes, quais são esses produtos e quantos são os pedidos atrasados? Essas são exatamente as perguntas que você vai aprender a fazer ao banco. Não vale espiar.

10. Formas de explorar o banco

Quando dá errado

SELECT nome, email FROM pedidos;

SELECT * FROM itens_pedidos;

SELECT id, data FROM pedidos WHERE data = '05/01/2024';

SELECT id, data FROM pedidos WHERE data = '2024-01-05';

SELECT nome FROM produtos WHERE preco = 2,79;

Referência rápida

Exercícios

Missão final: o chefe da Sofia

SELECT id, nome, cargo, chefe_id
FROM funcionarios
WHERE nome = 'Sofia Rocha';

SELECT id, nome, cargo, chefe_id
FROM funcionarios
WHERE nome = 'Sofia Rocha';

SELECT id, nome, cargo
FROM funcionarios
WHERE id = 5;

Agora você conhece a casa. A partir da próxima aula, SELECT, você começa a fazer as suas próprias perguntas a ela.

Resumo

• Todas as aulas usam o banco da Loja Aurora: dados fictícios e fixos, iguais no editor e no seu loja.db.
• São 7 tabelas: categorias (8), fornecedores (6), produtos (45), clientes (30), funcionarios (8), pedidos (90) e itens_pedido (220).
• Elas se ligam pelos ids: o produto aponta para a categoria e o fornecedor; o pedido, para o cliente e o funcionário; o item, para o pedido e o produto; o funcionário, para o chefe.
• O pedido não guarda o que foi comprado: isso fica em itens_pedido, com a quantidade e o preço pago. Total = soma de quantidade * preco_unitario + frete.
• Datas em 'ano-mês-dia', dinheiro com ponto decimal, sim/não como 1 e 0.
• O banco tem casos especiais de propósito (dados faltando, clientes sem pedidos, produtos encalhados, pedidos a caminho…), que as próximas aulas vão encontrar.