Ficha 02 · SELECT, JOIN, agregações
- Escrever SELECT complexo
- Usar JOIN
- Agregar dados
- Combinar com subqueries
Contexto comum
Para todos os exercícios, usa este esquema:
Cliente (id, nome, email, cidade)
Produto (id, nome, preco, categoria_id, stock)
Categoria (id, nome)
Encomenda (id, cliente_id, data, total, estado)
EncomendaItem (encomenda_id, produto_id, qty, preco_unit)
Parte I · SELECT básico
Exercício 1 · Filtros (15 pts)
Escreve queries para:
a) Todos os clientes de Lisboa.
b) Produtos com stock <= 5.
c) Encomendas de Maio de 2026 com total > 100€.
d) Produtos cujo nome começa por "Caneta".
e) Encomendas com estado em ('pago', 'enviado').
-- a)
SELECT * FROM Cliente WHERE cidade = 'Lisboa';
-- b)
SELECT * FROM Produto WHERE stock <= 5;
-- c)
SELECT * FROM Encomenda
WHERE data >= '2026-05-01' AND data < '2026-06-01'
AND total > 100;
-- d)
SELECT * FROM Produto WHERE nome LIKE 'Caneta%';
-- e)
SELECT * FROM Encomenda WHERE estado IN ('pago', 'enviado');
Exercício 2 · ORDER BY e LIMIT (10 pts)
a) Top 5 produtos mais caros.
b) Top 3 cidades com mais clientes.
c) Última encomenda registada.
-- a)
SELECT * FROM Produto ORDER BY preco DESC LIMIT 5;
-- b)
SELECT cidade, COUNT(*) AS n
FROM Cliente
GROUP BY cidade
ORDER BY n DESC
LIMIT 3;
-- c)
SELECT * FROM Encomenda ORDER BY data DESC LIMIT 1;
Parte II · Agregações
Exercício 3 · Funções (15 pts)
a) Quantos clientes existem?
b) Preço médio dos produtos?
c) Total facturado em encomendas pagas?
d) Por cidade, quantos clientes?
e) Mês com mais encomendas em 2026?
-- a)
SELECT COUNT(*) FROM Cliente;
-- b)
SELECT AVG(preco) FROM Produto;
-- c)
SELECT SUM(total) FROM Encomenda WHERE estado = 'pago';
-- d)
SELECT cidade, COUNT(*) AS n
FROM Cliente
GROUP BY cidade
ORDER BY n DESC;
-- e)
SELECT MONTH(data) AS mes, COUNT(*) AS n
FROM Encomenda
WHERE YEAR(data) = 2026
GROUP BY MONTH(data)
ORDER BY n DESC
LIMIT 1;
Exercício 4 · GROUP BY + HAVING (15 pts)
a) Categorias com mais de 10 produtos.
b) Clientes com 5+ encomendas e total gasto > 500€.
c) Produtos que apareceram em mais de 100 encomendas.
-- a)
SELECT categoria_id, COUNT(*) AS n
FROM Produto
GROUP BY categoria_id
HAVING COUNT(*) > 10;
-- b)
SELECT cliente_id, COUNT(*) AS n_enc, SUM(total) AS gasto
FROM Encomenda
GROUP BY cliente_id
HAVING COUNT(*) >= 5 AND SUM(total) > 500;
-- c)
SELECT produto_id, COUNT(*) AS n
FROM EncomendaItem
GROUP BY produto_id
HAVING COUNT(*) > 100;
Parte III · JOINs
Exercício 5 · INNER JOIN (15 pts)
a) Nome do cliente e total de cada encomenda.
b) Produto e nome da categoria para cada produto.
c) Detalhes da encomenda 42: cliente, produtos, qty, preço.
-- a)
SELECT c.nome, e.id, e.total
FROM Cliente c
JOIN Encomenda e ON c.id = e.cliente_id;
-- b)
SELECT p.nome, c.nome AS categoria
FROM Produto p
JOIN Categoria c ON p.categoria_id = c.id;
-- c)
SELECT c.nome AS cliente, p.nome AS produto, ei.qty, ei.preco_unit
FROM Encomenda e
JOIN Cliente c ON e.cliente_id = c.id
JOIN EncomendaItem ei ON e.id = ei.encomenda_id
JOIN Produto p ON ei.produto_id = p.id
WHERE e.id = 42;
Exercício 6 · LEFT JOIN (15 pts)
a) Todos os clientes, com nº de encomendas (0 incluído).
b) Clientes que nunca compraram.
c) Categorias e nº de produtos em cada (incluindo categorias com 0 produtos).
-- a)
SELECT c.nome, COUNT(e.id) AS n_enc
FROM Cliente c
LEFT JOIN Encomenda e ON c.id = e.cliente_id
GROUP BY c.id, c.nome
ORDER BY n_enc DESC;
-- b)
SELECT c.nome
FROM Cliente c
LEFT JOIN Encomenda e ON c.id = e.cliente_id
WHERE e.id IS NULL;
-- c)
SELECT cat.nome, COUNT(p.id) AS n
FROM Categoria cat
LEFT JOIN Produto p ON cat.id = p.categoria_id
GROUP BY cat.id, cat.nome
ORDER BY n DESC;
Parte IV · Subqueries
Exercício 7 · Subqueries (15 pts)
a) Produtos com preço acima da média.
b) Clientes que gastaram mais que a média dos clientes.
c) Produto mais caro de cada categoria.
-- a)
SELECT * FROM Produto
WHERE preco > (SELECT AVG(preco) FROM Produto);
-- b)
WITH gastos AS (
SELECT cliente_id, SUM(total) AS total
FROM Encomenda
GROUP BY cliente_id
)
SELECT c.nome, g.total
FROM Cliente c
JOIN gastos g ON c.id = g.cliente_id
WHERE g.total > (SELECT AVG(total) FROM gastos);
-- c)
SELECT p.*
FROM Produto p
WHERE p.preco = (
SELECT MAX(preco) FROM Produto WHERE categoria_id = p.categoria_id
);