100 Exercícios SQL

Prévia do material em texto

1 
 
Lista de exercícios para o script controle de pedidos 
 
 
 
 
 
 
Com base na base de dados criada via script e no schema apresentado na figura acima, 
responda: 
 
EXERCÍCIOS DDL: 
 
1. Alterar a tabela Cliente adicionando a coluna Telefone (literal com tamanho 9) e 
DDD (literal com tamanho 3). Após a criação dos atributos, preencher com os 
valores ‘RJ’ para o DDD igual a 21 e 11 para ‘SP’. 
 
alter table cliente add telefone char(9); 
 
alter table cliente add ddd char(3); 
 
select * from cliente; 
 
2 
 
--agora vamos preencher essas colunas. O update faz parte da DML! 
 
update cliente 
set ddd = '21' 
where estadocli='RJ'; 
 
update cliente 
set ddd = '11' 
where estadocli='SP'; 
 
2. Alterar o nome da tabela cliente para cliente2 e depois retornar o nome para cliente. 
 
select * from cliente 
alter table cliente rename to cliente2; 
select * from cliente2 
alter table cliente2 rename to cliente; 
select * from cliente 
 
3. Alterar o nome da coluna nomecli tabela cliente para nomedocliente e depois 
retornar o nome para nomecli. 
 
alter table cliente rename nomecli to nomedocliente; 
 
alter table cliente rename nomedocliente to nomecli; 
 
4. Criar uma restrição para limitar a quantidade dos produtos com um valor mínimo de 
50 produtos no estoque. 
 
select * from produto 
 
alter table produto add constraint limite_quant 
check (quantprod > 50); 
 
update produto 
set quantprod = 20 –-valor não permitido 
where codprod = 1; 
 
alter table produto 
drop constraint limite_quant; 
 
alter table produto 
add constraint limite_quant check (quantprod > 20); 
 
--não desejo mais a constraint 
alter table produto drop constraint limite_quant; 
 
3 
 
5. Alterar a tabela cliente adicionando uma coluna chamada idade que deve conter 
valores acima de 18 anos. 
alter table cliente add idade integer 
check (idade > 18); 
 
ou 
 
alter table cliente add idade integer; 
 
alter table cliente add constraint idade_maior 
check (idade > 18); 
--para testar 
 
update cliente set idade = 15 where codcli = 1; 
 
6. Alterar a tabela cliente adicionando uma restrição para impedir a entrada de nomes 
repetidos. 
 
alter table cliente add constraint nome_nao_repete 
unique (nomecli); 
--cria então uma chave secundária ou candidata 
 
--para testar 
insert into cliente (codcli,nomecli) 
 values (100,'Adriana') 
 
7. Criar uma restrição para permitir clientes somente do Rio ou de São Paulo. 
 
alter table cliente add constraint limita_estado 
check (estadocli in ('RJ','SP')); 
 
alter table cliente add constraint limita_estado 
check (estadocli in ('RJ','SP','MG')); 
 
8. Criar a tabela cliente2 igual a tabela cliente. Forma rápida de fazer uma cópia de 
uma tabela 
 
create table cliente2 as 
select * from cliente; 
 
--Pode-se também, criar a tabela baseado em uma condição: 
 
create table cliente2 as 
select * from cliente 
where estadocli = 'RJ'; 
4 
 
 
 
5 
 
EXERCÍCIOS DML APLICADOS A UMA TABELA: 
 
9. Atualizar o valor do CPF na tabela Pessoa Física para 44444 para o cliente de 
código 1. 
 
update pessoa_fisica 
set cpf = 44444 
where codpessoafisica = 1 
 
10. Tente inserir um produto com os seguintes dados: 
 
insert into produto values (10,2,5,'produto10',12,4) 
 
11. Também é possível estabelecer quais campos serão inseridos na operação: 
 
insert into produto (codprod,nomeprod,codcat,codfor) 
 values (30,'produto10',1,2) 
 
12. Mostra todos os dados de todas as colunas da tabela: 
 
select * from cliente 
 
CRIAR INSERT A PARTIR DE SUBCONSULTA 
 
13. Selecionar os nomes de todos clientes. 
 
select nomecli 
from cliente 
 
14. Selecionar os nomes e o código das categorias, ordenados de modo descendente 
pelo código da categoria. 
 
select nomecat,codcat 
from categoria 
order by codcat desc 
 
15. Selecionar os nomes das cidades dos fornecedores ordenadas alfabeticamente. 
 
select cidadefor 
from fornecedor 
order by cidadefor 
 
ou 
select cidadefor 
from fornecedor 
order by 1 
6 
 
 
16. Selecionar os nomes dos clientes na tabela Cliente ordenados primeiramente por 
estado e depois por cidade crescentemente. 
 
select nomecli,cidadecli,estadocli 
from cliente 
order by 1, 2 
 
17. Como os nomes foram repetidos, podemos impedir esta repetição por meio da 
cláusula distinct. 
 
select distinct cidadefor 
from fornecedor 
order by cidadefor 
 
18. Selecionar os nomes dos clientes que moram no Rio de Janeiro (cidade). Para 
confirmar listar também o nome da cidade. 
 
select nomecli,cidadecli 
from cliente 
where cidadecli = 'Rio de Janeiro' 
 
19. Selecionar os nomes dos clientes que moram em Rio de Janeiro. Para confirmar 
listar também o nome da cidade. De modo a facilitar a localização, podemos usar a 
função UPPER que transforma os valores para letras maiúsculas. 
 
select nomecli,cidadecli 
from cliente 
where upper(cidadecli) = 'RIO DE JANEIRO' 
 
20. Selecionar os nomes dos clientes que moram no Estado do Rio de Janeiro e também 
na cidade do Rio. 
 
select nomecli,cidadecli 
from cliente 
where cidadecli = 'Petrópolis' 
and estadocli = 'RJ' 
 
21. Selecionar os nomes dos produtos cujas quantidades são maiores ou iguais a 900. 
Para confirmar listar também as quantidades. 
 
select nomeprod,quantprod 
from produto 
where quantprod >= 900 
 
22. Selecionar todos os dados dos produtos da categoria com código igual a 5 
7 
 
 
select * 
from produto 
where codcat=5 
 
23. Selecionar os nomes dos produtos onde o código da categoria é 1 e a quantidade é 
superior a 500. 
 
select nomeprod,codcat,quantprod 
from produto 
where quantprod > 500 and codcat = 1 
 
24. Selecionar os nomes dos produtos com uma perspectiva de aumento de 10% no 
preço. 
 
select nomeprod, precoprod, precoprod * 1.1 as 
preco_com_aumento 
from produto 
 
25. Selecionar os nomes dos produtos da tabela Produto onde o código da categoria é 
1ou 2 e a quantidade é superior a 500. 
 
select nomeprod,codcat 
from produto 
where quantprod > 500 and (codcat = 1 or codcat =2) 
 
ou podemos utilizar 
 
select nomeprod,codcat 
from produto 
where quantprod > 500 
and codcat in (1,2) 
 
26. Selecionar os códigos dos produtos cuja quantidade não é maior que 500. 
 
select codprod,quantprod 
from produto 
where not (quantprod > 500) 
 
27. Selecionar os nomes dos produtos cujas categorias não sejam 3 ou 2. 
 
select nomeprod,codcat 
from produto 
where codcat not in (3,2) 
 
8 
 
28. Podemos alterar o nome de uma coluna para facilitar o entendimento do usuário. 
Neste exemplo a coluna quantprod tem seu nome alterado para 
‘quantidade_no_estoque’ (não pode haver espaço em branco). 
 
select codprod, quantprod as quantidade_no_estoque 
from produto 
where quantprod > 200 
 
Neste exemplo a coluna quantprod tem seu nome alterado para ‘Quantidade no 
estoque’. 
 
select codprod, quantprod as "Quantidade no estoque" 
from produto 
where quantprod > 200 
 
29. Selecionar os nomes dos produtos cujas quantidades estão entre 300 e 900 itens 
(intervalo). 
 
select nomeprod,quantprod 
from produto 
where quantprod between 450 and 850 
 
30. Selecionar os nomes das categorias de produtos cujos nomes começam com a letra 
‘c’. 
 
select nomecat 
from categoria 
where nomecat like 'c%' 
 
31. Selecionar os nomes dos clientes cujas cidades começam com ‘Petró’. Falar do 
ILIKE. 
 
select nomecli,cidadecli 
from cliente 
where cidadecli like 'Petró%' 
 
ou 
 
select nomecli,cidadecli 
from cliente 
where cidadecli ilike 'PETRÓ%' 
 
32. Selecionar os nomes dos clientes cujas cidades não começam com ‘Petró’ 
 
 
 
9 
 
select nomecli,cidadecli 
from cliente 
where cidadecli not like 'Petró%' 
 
33. Selecionar as categorias cujos valores sejam nulos. 
 
select nomecat 
from categoria 
where nomecat is null 
 
34. Selecionar as categorias cujos valores não sejam nulos. 
 
select nomecat 
from categoria 
wherenomecat is not null 
 
35. Selecionar o maior preço e a menor quantidade dos produtos. 
 
select max(precoprod), min(quantprod) 
from produto 
 
36. Selecionar os 3 produtos mais caros. 
 
select * 
from produto 
where precoprod is not null 
order by precoprod desc 
limit 3 
 
37. Selecionar o tempo total decorrido entre duas datas informadas. 
 
select age(timestamp '2001-04-10', timestamp '1957-06-13') 
 
38. Selecionar o tempo atual. 
 
select current_date; 
 
39. Selecionar apenas o ano a partir da função now(), com o uso de CAST 
 
select substring(now()::text,1,4) 
 
40. Acrescentar uma coluna datacadastro na tabela cliente e preencher com a função 
now() 
 
alter table cliente add datacadastro timestamp; 
 
10 
 
update cliente set datacadastro=now(); 
 
select * from cliente 
 
41. Tarefa: criar 3 colunas para o dia, mês e ano. Preencher a partir do campo 
datacriacao. 
 
alter table cliente add diacadastro integer; 
 
alter table cliente add mescadastro integer; 
alter table cliente add anocadastro integer; 
 
update cliente 
set diacadastro=substring(datacadastro::text,9,2)::integer; 
 
update cliente 
set mescadastro=substring(datacadastro::text,6,2)::integer; 
 
update cliente 
set anocadastro=substring(datacadastro::text,1,4)::integer; 
 
42. Selecionar a quantidade total de produtos para a categoria 1. 
 
select sum(quantprod) as "total para a categoria 1" 
from produto 
where codcat = 1 
 
43. Calcular e exibir a média de produtos. 
 
select avg(quantprod) 
from produto 
 
Ou 
 
select sum(quantprod) / count(*) 
from produto 
 
Para arredondar: 
 
select round(avg(quantprod),2) 
from produto 
 
44. Exemplo de uso da função SUBSTR(). 
 
select substr(nomecli,2,3) 
from cliente 
11 
 
45. Exemplo de uso de concatenação. 
 
select nomecli||'-'||estadocli 
from cliente 
 
46. Selecionar o total de produtos da categoria com código igual a 5 
 
select count(*) as total 
from produto 
where codcat = 5 
 
Ou 
 
select count(1) as total 
from produto 
where codcat = 5 
 
47. Selecionar a quantidade em estoque de produtos da categoria com código igual a 5 
 
select sum(quantprod) as total 
from produto 
where codcat = 5 
 
48. Selecionar o total a ser arrecada se todos os produtos da categoria com código igual 
a 5 fossem vendidos. 
 
select sum(quantprod*precoprod) as total 
from produto 
where codcat=5 
 
Ou 
 
select 'R$ '||sum(quantprod*precoprod) as total 
from produto 
where codcat=5 
 
Se necessário, usar o CAST: 
 
select 'R$ '||sum(quantprod*precoprod)::text as total 
from produto 
where codcat=5 
 
49. Contar quantos produtos tem o preço superior a R$ 500,00. 
 
select count(*) 
from produto 
12 
 
where precoprod > 150 
 
50. Quais as categorias de produtos? 
 
select distinct codcat 
from produto 
 
51. Selecionar o código do produto com a maior quantidade na tabela Produto cujo 
código da categoria seja 1. 
 
select max(quantprod) 
from produto 
where codcat = 1 
 
52. Contar quantas cidades diferentes temos na tabela de Clientes. 
 
select count(distinct(cidadecli)) as total 
from cliente 
 
53. Selecionar a quantidade de produtos por categoria. 
 
select codcat,count(*) as total 
from produto 
group by codcat 
 
ou 
 
select codcat,count(quantprod) as total 
from produto 
group by codcat 
 
54. Selecionar as quantidades de produtos armazenados no estoque por categoria na 
tabela Produto. 
 
select codcat,sum(quantprod) as total 
from produto 
group by codcat 
 
55. Qual o total a ser arrecadado, por categoria, no caso da venda de todo o estoque. 
 
select codcat,sum(quantprod * precoprod) 
from produto 
group by codcat 
 
Para formatar: 
 
13 
 
select codcat,'R$ '||round(sum(quantprod*precoprod ),2)::text 
from produto 
group by codcat 
 
56. Listar o total a ser arrecadado por produto em caso de venda de todo o estoque. 
Considerar uma margem de lucro de 30%, por produto. 
 
select sum(quantprod * (precoprod*1.3)) 
from produto 
 
57. Listar os totais de produtos por código das categorias. 
 
select codcat,count(*) as total 
from produto 
group by codcat 
order by 1 
 
58. Quais os códigos das categorias com mais de 3 produtos. 
 
select codcat,count(*) 
from produto 
group by codcat 
having count(*)>3 
 
59. Selecionar o nome do produto, o código da categoria e o nome da categoria dos 
produtos sabendo que os códigos indicam os seguintes nomes de categorias: 1) 
padrão; 2) higiene; 3) alimento e, que ainda existem outras categorias. 
 
select nomeprod,codcat,case 
 when codcat=1 then 'padrão' 
 when codcat=2 then 'higiene' 
 when codcat=3 then 'alimento' 
 else 'outras' 
 end nome_categoria 
from produto 
 
 
 
 
14 
 
EXERCÍCIOS DML APLICADOS A MAIS DE UMA TABELA: 
 
60. Listar os nomes dos produtos e o nome de suas categorias. 
 
select nomeprod,nomecat 
from produto, 
categoria 
where produto.codcat = categoria.codcat 
 
select nomeprod,nomecat -–alias para as tabelas 
from produto p, 
categoria c 
where c.codcat = p.codcat 
 
explain select nomeprod,nomecat 
from produto p, 
categoria c 
where c.codcat = p.codcat 
 
61. Listar os números dos pedidos feitos por ‘Ana’. 
 
select nrped 
from cliente, 
pedido 
where cliente.codcli = pedido.codcli 
and upper(nomecli)='ANA' 
 
Ou 
 
select nrped 
from cliente c, 
pedido p 
where c.codcli = p.codcli 
and lower(nomecli)= 'ana' 
 
Ou 
 
SELECT c.codcli,p.codcli,nrped AS "Nr Pedido" 
FROM cliente c INNER JOIN pedido p 
ON c.codcli = p.codcli 
WHERE upper(nomecli) = 'ANA' 
 
 
 
 
 
15 
 
62. Listar todos os dados dos clientes que fizeram pedidos. 
 
 
select * 
from cliente c, 
pedido o 
where c.codcli = o.codcli 
 
Ou 
 
select * 
from cliente c inner join pedido p 
on c.codcli = p.codcli 
 
63. Listar os nomes dos clientes que compraram o produto de código 1. Ordenados pelo 
nome do cliente. 
 
select nomecli 
from cliente c, 
pedido p, 
pedido_produto pp 
where c.codcli=p.codcli 
and p.nrped = pp.nrped 
and codprod = 1 
order by 1 
 
Ou 
 
select distinct nomecli,nrped 
from cliente c inner join pedido p 
on c.codcli=p.codcli 
order by 1,2 
 
64. Listar os nomes dos clientes e os nomes dos produtos comprados por eles. 
 
select nomecli as nome_cliente, nomeprod as nome_produto 
from cliente c, 
pedido p, 
pedido_produto pp, 
produto o 
where c.codcli = p.codcli 
and p.nrped = pp.nrped 
and pp.codprod = o.codprod 
 
 
 
16 
 
65. Listar os nomes dos clientes que compraram o produto ‘TV 21’. 
 
select nomecli 
from cliente c, 
pedido p, 
pedido_produto pp, 
produto o 
where c.codcli = p.codcli 
and p.nrped = pp.nrped 
and pp.codprod = o.codprod 
and nomeprod = 'TV 21' 
 
66. Listar o total de produtos por categoria (nome da categoria). 
 
select codcat,count(*) 
from produto o, 
categoria c 
where o.codcat = c.codcat 
group by 1 
 
67. Listar o total de produtos comprados por categoria (código da categoria). 
 
select codcat,count(*) 
from pedido_produto pp, 
produto o 
where pp.codprod=o.codprod 
group by codcat 
 
68. Listar quantas unidades foram vendidas para cada produto (nome do produto). 
 
select nomeprod 
from produto p, 
pedido_produto pp, 
where pp.codprod = p.codprod 
group by nomeprod 
 
69. Listar o total de produtos comprados por categoria (nome da categoria). 
 
select nomecat,count(*) 
from pedido_produto pp, 
produto o, 
categoria c 
where pp.codprod=o.codprod 
and c.codcat=o.codcat 
group by 1 
 
17 
 
70. Listar todos os dados dos clientes do tipo pessoa jurídica. 
 
select * 
from cliente, 
pessoa_juridica 
where cliente.codcli = pessoa_juridica.codpessoajuridica 
 
71. Listar os nomes das categorias cujo total de produtos seja superior a 4 itens. 
select nomecat,count(*) as total 
from categoria c inner join produto p 
on c.codcat = p.codcat 
group by nomecat 
having count(*) > 4 
 
72. Listar o total de produtosque tiveram mais de 5 pedidos. 
select count(*) as total 
from pedido_produto 
group by codprod 
having count(*) > 5 
 
73. Que compraram apenas 3 produtos 
SELECT nomecli,count(*) 
FROM cliente c, 
pedido p, 
pedido_produto pp 
WHERE c.codcli = p.codcli 
AND p.nrped = pp.nrped 
GROUP BY nomecli 
HAVING count(*) = 3 
ORDER BY nomecli 
 
74. Listar os nomes dos clientes que compraram produtos com preço acima de R$ 
1000,00. 
 
select distinct nomecli 
from cliente c, 
pedido p, 
pedido_produto e, 
produto r 
where c.codcli = p.codcli 
and p.nrped = e.nrped 
and e.codprod = r.codprod 
and precoprod > 1000 
18 
 
75. Quantos clientes fizeram pedidos? 
select count(distinct(codcli)) as total 
from pedido p 
 
76. Listar o total de pedidos feitos por cliente (nome). 
 
select nomecli, count(*) 
from cliente c, 
pedido p 
where c.codcli = p.codcli 
group by nomecli 
 
77. Listar o total de pedidos feitos por categoria (nome da categoria). 
 
select nomecat, count(*) 
from pedido_produto pp, 
produto r, 
categoria c 
where pp.codprod = r.codprod 
and c.codcat = p.codcat 
group by nomecat 
 
78. Listar quantas unidades foram vendidas para cada produto. Primeiro pelo próprio 
código do produto, depois alterar para agrupar pelo nome do produto. 
 
alter table pedido_produto add quant_vendida integer 
update pedido_produto set quant_vendida = 5 
 
select codprod, sum(quant_vendida) as total 
from pedido_produto 
group by codprod 
 
select nomeprod, sum(quant_vendida) as total 
from pedido_produto pp inner join produto p 
on pp.codprod = p.codprod 
group by nomeprod 
 
79. Exibir os nomes dos clientes que compraram produtos dos fornecedores que estão 
localizados no mesmo estado dos clientes. 
 
 
 
 
 
 
19 
 
select distinct nomecli,estadocli,estadofor 
from cliente c, 
pedido p, 
pedido_produto e, 
produto r, 
fornecedor f 
where c.codcli = p.codcli 
and p.nrped = e.nrped 
and e.codprod = r.codprod 
and r.codfor = f.codfor 
and c.estadocli = f.estadofor 
 
80. Listar os CPFs dos clientes que compraram produtos da categoria ‘higiene, com 
preço superior a R$ 500,00 e cujo fornecedor está localizado no ‘RJ’. 
 
select cpf 
from cliente c, 
pedido p, 
pedido_produto e, 
produto r, 
fornecedor f, 
categoria a, 
pessoa_fisica pf 
where c.codcli = p.codcli 
and p.nrped = e.nrped 
and e.codprod = r.codprod 
and r.codfor = f.codfor 
and a.codcat = r.codcat 
and c.codcli = pf.codpessoafisica 
and precoprod >= 500 
and f.estadofor = 'RJ' 
and nomecat = 'higiene' 
 
 
CRIAR AUTO JUNÇÃO 
select * from categoria 
alter table categoria add nivel integer 
select * from categoria 
update categoria set nivel = 1 
select * from categoria 
alter table categoria add categoria_acima integer 
select * from categoria 
alter table categoria add constraint fkcategoria foreign 
key(categoria_acima) references categoria 
20 
 
insert into categoria values (7,'som',2,40) 
insert into categoria values (7,'som',2,4) 
select * from categoria 
 
 
21 
 
QUESTÕES COM OUTROS TIPOS DE JUNÇÕES (EXTERNAS): 
 
-- Verificando os clientes cadastrados 
 
select codcli 
from cliente order by 1 
 
81. Listar os códigos dos clientes que fizeram pedidos. 
 
select distinct codcli 
from pedido 
order by 1 
 
-- Incluindo um cliente que ainda não fez pedido 
 
insert into cliente (codcli,nomecli) values (50,'Eli') 
 
-- Vamos verificar agora as diferenças entre os “joins”: 
 
82. INNER JOIN / listar nome e número de pedidos dos clientes que fizeram pedidos 
 
select nomecli,nrped 
from cliente c join pedido p 
on c.codcli = p.codcli 
order by 1,2 
 
83. LEFT OUTER JOIN / listar nome e número de pedidos dos clientes que fizeram 
pedidos e o nome dos que não fizeram 
 
select nomecli,nrped AS numero_do_pedido 
from cliente c left outer join pedido p 
on c.codcli = p.codcli 
order by 2,1 
 
84. LEFT OUTER JOIN / listar o nome dos clientes que não fizeram 
 
select nomecli,nrped AS numero_do_pedido 
from cliente c left outer join pedido p 
on c.codcli = p.codcli 
where p.codcli is null 
order by 2,1 
 
85. Listar todos os dados dos clientes que NÃO fizeram pedidos. 
 
 
 
22 
 
select nomecli 
from cliente c left join pedido p 
on c.codcli = p.codcli 
where p.codcli is null 
 
86. RIGHT OUTER JOIN / a mesma consulta anterior usando o RIGHT. Observe que o 
cliente Eli não será recuperado. Por quê? 
 
 
select nomecli, p.nrped 
from cliente c right outer join pedido p 
on c.codcli = p.codcli 
order by 2,1 
 
87. RIGHT OUTER JOIN - correto 
 
select nomecli, p.nrped 
from pedido p right outer join cliente c 
on p.codcli = c.codcli --aqui não influencia 
order by 2,1 
 
-- Para testar o uso do FULL OUTER JOIN vamos efetuar os comandos a seguir: 
 
create table pedido2 as select * from pedido 
 
insert into pedido2(nrped) values (100) 
 
insert into pedido(nrped,codcli) values (101,1) 
 
select * from pedido2 
 
88. FULL OUTER JOIN / a consulta vai exibir registros sem correspondência em 
ambos os lados 
 
select nomecli, nrped 
from cliente c full join pedido2 p 
on c.codcli=p.codcli 
 
89. FULL OUTER JOIN / exemplo de uma junção através do campo “nrped” 
 
select p1.nrped, p2.nrped 
from pedido p1 full join pedido2 p2 
on p1.nrped = p2.nrped 
 
90. Listar os clientes que moram no mesmo estado 
 
23 
 
select distinct a.nomecli,b.nomecli 
from cliente a inner join cliente b 
on a.estadocli = b.estadocli 
where a.nomecli <> b.nomecli 
and a.nomecli||b.nomecli<>b.nomecli||a.nomecli 
order by 1 
 
 
 
 
24 
 
QUESTÕES BASEADAS NAS OPERAÇÕES DE CONJUNTOS: 
 
91. UNION 
 
select * from pedido 
union 
select * from pedido2 
 
92. INTERSECT 
 
select * from pedido 
intersect 
select * from pedido2 
 
93. EXCEPT 
 
select * from pedido 
except 
select * from pedido2