UFCD 10788 - Fundamentos da linguagem de programação SQL
Ficha de Trabalho 10
Objetivos
Criação de bases de dados, tabelas e chaves em SQL
Consultas utilizando SELECT
1. Crie uma nova base de dados com o nome loja_exercicios
2. Crie as tabelas abaixo e respetivas chaves primarias e estrangeiras
Efetue as consultas abaixo e respetivas Views com nome Vista3,
Vista4, Vista5 e Vista 6.
3. Mostrar todos os clientes da cidade de “Lisboa”.
SELECT * FROM clientes WHERE cidade = 'Lisboa';
4. Listar os produtos da categoria “Periféricos” com preço abaixo de
100€.
SELECT nome, preco FROM produtos WHERE categoria = 'Periféricos'
AND preco < 100;
5. Mostrar o total de pedidos por cliente.
SELECT [Link], COUNT([Link]) AS total_pedidos
FROM clientes c
LEFT JOIN pedidos p ON [Link] = p.cliente_id
GROUP BY [Link];
6. Calcular o valor total de cada pedido.
SELECT [Link] AS pedido_id,
[Link] AS cliente,
SUM([Link] * [Link]) AS total_pedido
FROM pedidos p
JOIN clientes c ON p.cliente_id = [Link]
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link], [Link];
7. Crie uma View com o nome vw_resumo_pedidos para apresentar o
resumo com o id do pedido, data do pedido e soma dos produtos de
quantidade por preço.
CREATE VIEW vw_resumo_pedidos AS
SELECT [Link] AS pedido_id,
[Link] AS cliente,
p.data_pedido,
SUM([Link] * [Link]) AS total_pedido
FROM pedidos p
JOIN clientes c ON p.cliente_id = [Link]
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link], [Link], p.data_pedido;
8. Crie uma View vw_produtos com nome dos produtos e respetiva
categoria
CREATE VIEW vw_produtos_categorias AS
SELECT nome AS produto, categoria, preco
FROM produtos;
9. Listar o nome e o email de todos os clientes.
SELECT nome, email FROM clientes;
10. Mostrar os produtos com preço superior a 100€.
SELECT nome, preco FROM produtos WHERE preco > 100;
11. Selecionar todos os pedidos realizados após 1 de outubro de 2025.
SELECT * FROM pedidos WHERE data_pedido > '2025-10-01';
12. Mostrar os clientes que são da cidade de Lisboa.
SELECT nome, cidade FROM clientes WHERE cidade = 'Lisboa';
13. Ordenar os produtos por preço decrescente.
SELECT nome, preco FROM produtos ORDER BY preco DESC;
14. Mostrar o nome do cliente e a data dos seus pedidos.
SELECT [Link], p.data_pedido FROM clientes c JOIN pedidos p ON [Link] =
p.cliente_id;
15. Mostrar o nome do produto e o total de unidades vendidas (quantidade
somada).
SELECT [Link] AS produto, SUM([Link]) AS total_vendidoFROM
itens_pedido iJOIN produtos pr ON i.produto_id = [Link] BY [Link];
16. Calcular o valor total de cada pedido (quantidade × preço).
SELECT [Link] AS pedido_id,
SUM([Link] * [Link]) AS total_pedidoFROM pedidos pJOIN
itens_pedido i ON [Link] = i.pedido_idJOIN produtos pr ON i.produto_id =
[Link] BY [Link];
1.
17. Mostrar o total gasto por cada cliente.
SELECT [Link] AS cliente,SUM([Link] * [Link]) AS total_gasto
FROM clientes c
JOIN pedidos p ON [Link] = p.cliente_id
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link];
18. Mostrar o nome dos produtos e quantas vezes foram encomendados.
SELECT [Link], COUNT([Link]) AS vezes_encomendadoFROM produtos prJOIN
itens_pedido i ON [Link] = i.produto_idGROUP BY [Link];
19. Mostrar os produtos com preço superior à média de todos os produtos.
SELECT nome, preco
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);
20. Listar os clientes que já fizeram pelo menos um pedido.
SELECT nome
FROM clientes
WHERE id IN (SELECT cliente_id FROM pedidos);
21. Listar os clientes que ainda não fizeram pedidos.
SELECT nome
FROM clientes
WHERE id NOT IN (SELECT cliente_id FROM pedidos);
22. Mostrar o cliente que mais gastou no total.
SELECT [Link], SUM([Link] * [Link]) AS total_gastoFROM clientes c
JOIN pedidos p ON [Link] = p.cliente_id
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link]
ORDER BY total_gasto DESC
LIMIT 1;
23. Mostrar o produto mais vendido (em número de unidades).
SELECT [Link], SUM([Link]) AS total_vendido
FROM produtos pr
JOIN itens_pedido i ON [Link] = i.produto_id
GROUP BY [Link]
ORDER BY total_vendido DESC
LIMIT 1;
24. Mostrar o total de vendas por categoria de produto.
SELECT [Link], SUM([Link] * [Link]) AS total_vendas
FROM produtos pr
JOIN itens_pedido i ON [Link] = i.produto_id
JOIN pedidos p ON i.pedido_id = [Link]
GROUP BY [Link];
25. Mostrar os clientes e a data do seu último pedido.
SELECT [Link], MAX(p.data_pedido) AS ultimo_pedido
FROM clientes c
JOIN pedidos p ON [Link] = p.cliente_id
GROUP BY [Link];
26. Mostrar os produtos que nunca foram vendidos.
SELECT nome
FROM produtos
WHERE id NOT IN (SELECT produto_id FROM itens_pedido);
27. Calcular a média de valor por pedido (total médio gasto por pedido).
SELECT AVG(total_pedido) AS media_valor_pedidoFROM (
SELECT SUM([Link] * [Link]) AS total_pedido
FROM pedidos p
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link]
) AS sub;
28. Mostrar o cliente e o total gasto apenas em produtos da categoria
“Periféricos”.
SELECT [Link], SUM([Link] * [Link]) AS total_perifericos
FROM clientes c
JOIN pedidos p ON [Link] = p.cliente_id
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
WHERE [Link] = 'Periféricos'GROUP BY [Link];
Criação de Procedures
29. Crie a procedure pedidos_por_cliente que mostre id de pedido, a
data e a soma do preço x quantidade
DELIMITER //
CREATE PROCEDURE pedidos_por_cliente(IN nome_cliente
VARCHAR(100))
BEGIN
SELECT [Link] AS pedido_id, p.data_pedido, SUM([Link] *
[Link]) AS total
FROM pedidos p
JOIN clientes c ON p.cliente_id = [Link]
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
WHERE [Link] = nome_cliente
GROUP BY [Link], p.data_pedido;
END //
DELIMITER ;
30. Procedure contar_produtos_por_categoria para contar quantos
produtos existem por categoria
DELIMITER //
CREATE PROCEDURE contar_produtos_por_categoria()
BEGIN
SELECT categoria, COUNT(*) AS total
FROM produtos
GROUP BY categoria;
END //
DELIMITER;
31. Escreva um SELECT que mostra o nome do cliente e o total gasto em
todos os pedidos.
SELECT [Link] AS cliente,
SUM([Link] * [Link]) AS total_gasto
FROM clientes c
JOIN pedidos p ON [Link] = p.cliente_id
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link];
Ligamos as tabelas clientes → pedidos → itens_pedido → produtos.
Multiplicamos quantidade * preço para obter o valor gasto em cada
item.
O SUM() soma todos os itens de cada cliente.
O GROUP BY agrupa por cliente.
32. Crie uma VIEW que mostre apenas os pedidos acima de 200€.
CREATE VIEW vw_pedidos_maior_200 AS
SELECT [Link] AS pedido_id,
[Link] AS cliente,
p.data_pedido,
SUM([Link] * [Link]) AS total_pedido
FROM pedidos p
JOIN clientes c ON p.cliente_id = [Link]
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
GROUP BY [Link], [Link], p.data_pedido
HAVING total_pedido > 200;
A estrutura é semelhante à da view vw_resumo_pedidos.
A diferença é o uso do HAVING total_pedido > 200 (não WHERE, pois
o filtro é sobre o resultado de uma agregação).
Depois, podemos testar: SELECT * FROM vw_pedidos_maior_200;
33. Faça uma PROCEDURE que recebe uma cidade e mostra os clientes
dessa cidade.
DELIMITER //
CREATE PROCEDURE clientes_por_cidade(IN nome_cidade
VARCHAR(50))
BEGIN
SELECT id, nome, email
FROM clientes
WHERE cidade = nome_cidade;
END //
DELIMITER ;
Para testar CALL clientes_por_cidade('Lisboa');
O parâmetro IN recebe o nome da cidade.
O SELECT mostra todos os clientes cuja cidade coincide com o
parâmetro.
Ideal para praticar filtros simples com parâmetros.
34. Crie uma PROCEDURE que calcula o valor total vendido num
determinado dia.
DELIMITER //
CREATE PROCEDURE total_vendas_dia(IN data_consulta DATE)
BEGIN
SELECT data_consulta AS data,
IFNULL(SUM([Link] * [Link]), 0) AS total_vendas
FROM pedidos p
JOIN itens_pedido i ON [Link] = i.pedido_id
JOIN produtos pr ON i.produto_id = [Link]
WHERE p.data_pedido = data_consulta;
END //
DELIMITER ;
Para testar: CALL total_vendas_dia('2025-10-03');