LISTA 3 Anexo II

Prévia do material em texto

Gabarito do Teste de Banco de Dados 
 
Banco Olimpíadas (Equipe, País, Atleta, Partida) 
 
1) Recuperar a quantidade de atletas com menos de 30 anos. 
 
SELECT COUNT(COD_A) FROM ATLETA WHERE IDADE < 30; 
 
 
 
2) Recuperar o nome dos atletas da equipe de código 1 com altura maior que a média da 
altura de todos os atletas. 
 
SELECT NOME_A FROM ATLETA WHERE ALTURA > (SELECT 
AVG(ALTURA) FROM ATLETA) AND COD_E = 1; 
 
 
 
3) Recuperar a média das idades dos atletas da equipe de código 1. 
 
SELECT AVG(IDADE) FROM ATLETA WHERE COD_E = 1; 
 
 
 
4) Recuperar, sem repetição, hora das partidas realizadas no dia 8. 
 
SELECT DISTINCT HORA FROM PARTIDA WHERE DIA = 8; 
 
 
 
 
5) Recuperar o nome das equipes que não ganharam sets em alguma partida. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
PARTIDA_EQUIPE WHERE NUM_SETS = 0); 
 
 
 
6) Recuperar o nome da equipe que possui o atleta mais velho desta Olimpíada. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
ATLETA WHERE IDADE = (SELECT MAX(IDADE) FROM ATLETA)); 
 
 
 
7) Recuperar nome das equipes cuja sigla do país não começa com B. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_P NOT IN (SELECT COD_P 
FROM PAIS WHERE SIGLA LIKE 'B%'); 
 
 
 
8) Recuperar a média de número de sets vencidos nas partidas da equipe cujo país tem 
sigla ‘BRA’ 
 
SELECT AVG(NUM_SETS) FROM PARTIDA_EQUIPE WHERE COD_E IN 
(SELECT COD_E FROM EQUIPE WHERE COD_P IN (SELECT COD_P FROM 
PAIS WHERE SIGLA = 'BRA')); 
 
 
 
9) Recuperar o nome da equipe que possui o atleta de menor peso desta Olimpíada. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
ATLETA WHERE PESO IN (SELECT MIN(PESO) FROM ATLETA)); 
 
 
10) Recuperar dia e hora das partidas do atleta de nome ‘Giba’ 
 
SELECT DIA, HORA FROM PARTIDA WHERE COD_PART IN (SELECT 
COD_PART FROM PARTIDA_EQUIPE WHERE COD_E IN (SELECT COD_E 
FROM ATLETA WHERE NOME_A = 'GIBA')); 
 
 
 
11) Recuperar nome dos países cujas equipes jogaram no dia 11, às 20h. 
 
SELECT NOME_P FROM PAIS WHERE COD_P IN (SELECT COD_P FROM 
EQUIPE WHERE COD_E IN (SELECT COD_E FROM PARTIDA_EQUIPE 
WHERE COD_PART IN (SELECT COD_PART FROM PARTIDA WHERE DIA 
= 11 AND HORA = 20))); 
 
 
 
12) Recuperar siglas dos países cujas equipes não jogaram as partidas de código 1 ou 3. 
 
SELECT SIGLA FROM PAIS WHERE COD_P IN (SELECT COD_P FROM 
EQUIPE WHERE COD_E NOT IN (SELECT COD_E FROM PARTIDA_EQUIPE 
WHERE COD_PART = 1 OR COD_PART = 3)); 
 
 
 
13) Recuperar o nome das equipes que perderam para a equipe de código 4 em alguma 
partida. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
PARTIDA_EQUIPE WHERE NUM_SETS < 3 AND COD_E <> 4 AND 
COD_PART IN (SELECT COD_PART FROM PARTIDA_EQUIPE WHERE 
COD_E = 4)); 
 
 
 
 
14) Recuperar quantidade de atletas que jogaram partidas após o dia 15. 
 
SELECT COUNT(DISTINCT COD_A) FROM ATLETA WHERE COD_E IN 
(SELECT COD_E FROM PARTIDA_EQUIPE WHERE COD_PART IN (SELECT 
COD_PART FROM PARTIDA WHERE DIA >15)); 
 
 
 
15) Recuperar sigla dos países cujas equipes ganharam partidas (isto é, número de sets 
vencidos é 3) 
 
SELECT SIGLA FROM PAIS WHERE COD_P IN (SELECT COD_P FROM 
EQUIPE WHERE COD_E IN (SELECT COD_E FROM PARTIDA_EQUIPE 
WHERE NUM_SETS = 3)); 
 
 
 
16) Recuperar quantidade de atletas com mais de 30 anos que não fazem parte das equipes 
que jogaram a partida de número 4. 
 
SELECT COUNT(COD_A) FROM ATLETA WHERE IDADE > 30 AND COD_E 
NOT IN (SELECT COD_E FROM PARTIDA_EQUIPE WHERE COD_PART = 4); 
 
 
 
17) Recuperar, sem repetição, os estádios que sediaram partidas da equipe cujo país tem 
código 4. 
 
SELECT DISTINCT ESTADIO FROM PARTIDA WHERE COD_PART IN 
(SELECT COD_PART FROM PARTIDA_EQUIPE WHERE COD_E IN (SELECT 
COD_E FROM EQUIPE WHERE COD_P = 4)); 
 
 
 
 
 
 
 
18) Calcular a média de número de sets vencidos pela equipe cujo país é “Brasil”. 
 
SELECT AVG(NUM_SETS) FROM PARTIDA_EQUIPE WHERE COD_E IN 
(SELECT COD_E FROM EQUIPE WHERE COD_P IN (SELECT COD_P FROM 
PAIS WHERE NOME_P = 'BRASIL')); 
 
 
 
19) Recuperar dia e hora das partidas cujo placar foi 3x2. 
 
SELECT DIA, HORA FROM PARTIDA WHERE COD_PART IN (SELECT 
A.COD_PART FROM PARTIDA_EQUIPE A, PARTIDA_EQUIPE B WHERE 
A.COD_PART = B.COD_PART AND ((A.NUM_SETS = 3 AND B.NUM_SETS = 
2) OR (A.NUM_SETS = 2 AND B.NUM_SETS = 3))); 
 
 
 
20) Recuperar código das partidas e siglas dos países que se enfrentaram nas partidas de 
códigos 3, 4 ou 5 ordenados crescentemente pelo código da partida. 
 
SELECT COD_PART, SIGLA FROM PARTIDA_EQUIPE, EQUIPE, PAIS 
WHERE (COD_PART = 3 OR COD_PART = 4 OR COD_PART = 5) AND 
PARTIDA_EQUIPE.COD_E = EQUIPE.COD_E AND EQUIPE.COD_P = 
PAIS.COD_P ORDER BY COD_PART; 
 
 
 
 
 
 
 
 
 
 
 
 
21) Recuperar nome e nacionalidade (nome do país) do atleta mais alto desta Olimpíada. 
 
SELECT NOME_A, NOME_P FROM ATLETA, EQUIPE, PAIS WHERE 
ATLETA.COD_E = EQUIPE.COD_E AND EQUIPE.COD_P = PAIS.COD_P AND 
ATLETA.ALTURA IN (SELECT MAX(ALTURA) FROM ATLETA); 
 
 
 
22) Recuperar nome da equipe cujos todos os jogadores têm nomes que não começam 
com a letra B. 
 
SELECT NOME_E FROM EQUIPE WHERE NOT EXISTS(SELECT COD_A 
FROM ATLETA WHERE NOME_A LIKE 'B%' AND EQUIPE.COD_E = 
ATLETA.COD_E); 
 
 
 
23) Recuperar o código da equipe com maior número de vitórias (isto é, maior número de 
partidas em que número de sets vencidos pela equipe é 3). 
 
SELECT COD_E FROM (SELECT COD_E, COUNT(*) AS RESULT FROM 
PARTIDA_EQUIPE WHERE NUM_SETS = 3 GROUP BY COD_E) AS TABELA2 
WHERE RESULT IN (SELECT MAX(RESULT) FROM (SELECT COD_E, 
COUNT(*) AS RESULT FROM PARTIDA_EQUIPE WHERE NUM_SETS = 3 
GROUP BY COD_E) AS TABELA); 
 
 
 
24) Recuperar códigos das equipes e a média de idade de seus atletas. 
 
SELECT COD_E, AVG(IDADE) FROM ATLETA GROUP BY COD_E; 
 
 
 
 
 
25) Recuperar nome das equipes que jogam pela tarde, isto é, suas partidas têm horário 
maior que 12 e menor que 18. 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
PARTIDA_EQUIPE WHERE COD_PART IN (SELECT COD_PART FROM 
PARTIDA WHERE HORA > 12 AND HORA < 18)); 
 
 
 
26) Recuperar dia e hora das partidas em que a equipe do técnico “Bernardinho” 
participou. 
 
SELECT DIA, HORA FROM PARTIDA WHERE COD_PART IN (SELECT 
COD_PART FROM PARTIDA_EQUIPE WHERE COD_E IN (SELECT COD_E 
FROM EQUIPE WHERE TECNICO = 'BERNARDINHO')); 
 
 
 
27) Recuperar quantidade de atletas com altura acima da média (de altura de todos os 
atletas da competição). 
 
SELECT COUNT(COD_A) FROM ATLETA WHERE ALTURA > (SELECT 
AVG(ALTURA) FROM ATLETA); 
 
 
 
28) Recuperar horário e quantidade de partidas que ocorrem em cada hora do dia 8. 
 
SELECT HORA, COUNT(COD_PART) FROM PARTIDA WHERE DIA = 8 
GROUP BY HORA; 
 
 
 
29) Recuperar código (sem repetição) dos atletas de maior idade, maior peso ou maior 
altura do campeonato. 
 
SELECT DISTINCT(COD_A) FROM ATLETA WHERE IDADE IN(SELECT 
MAX(IDADE) FROM ATLETA) OR PESO IN(SELECT MAX(PESO) FROM 
ATLETA) OR ALTURA IN(SELECT MAX(ALTURA) FROM ATLETA); 
 
 
 
30) Recuperar média de idade dos jogadores da partida de código 3. 
 
SELECT AVG(IDADE) FROM ATLETA WHERE COD_E IN (SELECT COD_E 
FROM PARTIDA_EQUIPE WHERE COD_PART = 3); 
 
 
 
31) Recuperar quantidade de partidas em que o atleta de código 1 não disputou. 
 
SELECT COUNT(COD_PART) FROM PARTIDA WHERE COD_PART NOT IN 
(SELECT COD_PART FROM PARTIDA_EQUIPE WHERE COD_E IN (SELECT 
COD_E FROM ATLETA WHERE COD_A= 1)); 
 
 
 
32) Recuperar nomes dos países cuja equipe tem jogadores maiores de 35 anos. 
 
SELECT NOME_P FROM PAIS WHERE COD_P IN (SELECT COD_P FROM 
EQUIPE, ATLETA WHERE EQUIPE.COD_E = ATLETA.COD_E AND IDADE > 
35); 
 
 
 
 
 
 
 
33) Recuperar nome do técnico da equipe cujo país tem sigla ‘EUA’ 
 
SELECT TECNICO FROM EQUIPE WHERE COD_P IN (SELECT COD_P FROM 
PAIS WHERE SIGLA = 'EUA'); 
 
 
 
34) Recuperar, sem repetição, código da partida e placar (número de sets vencidos por 
cada equipe) das partidas de código 1, 2 e 3. 
 
SELECT A.COD_PART, A.NUM_SETS, B.NUM_SETS FROM 
PARTIDA_EQUIPE A, PARTIDA_EQUIPE B WHERE A.COD_PART = 
B.COD_PART AND A.COD_E > B.COD_E AND (A.COD_PART = 1 OR 
A.COD_PART = 2 OR A.COD_PART = 3); 
 
 
 
35) Recuperar nome do estádio da última partida que ocorreu na competição. 
 
SELECT ESTADIO FROM PARTIDA WHERE DIA IN (SELECT MAX(DIA) 
FROM PARTIDA) AND HORA IN (SELECT MAX(HORA) FROM PARTIDA 
WHERE DIA IN (SELECT MAX(DIA) FROM PARTIDA)); 
 
 
 
36) Recuperar dia e hora das partidas da equipe cujo país é “Brasil”. 
 
SELECT DIA, HORA FROM PARTIDA WHERE COD_PART IN (SELECT 
COD_PART FROM PARTIDA_EQUIPE WHERE COD_E IN (SELECT COD_E 
FROM EQUIPE WHERE COD_P IN (SELECT COD_P FROM PAIS WHERE 
NOME_P = 'BRASIL'))); 
 
 
 
37) Recuperar nome do jogador de maior altura da equipe cujo país tem código 2. 
 
SELECT NOME_A FROM ATLETA WHERE COD_E IN (SELECT COD_E 
FROM EQUIPE WHERE COD_P = 2) AND ALTURA IN (SELECT 
MAX(ALTURA) FROM ATLETA WHERE COD_E IN (SELECT COD_E FROM 
EQUIPE WHERE COD_P = 2)); 
 
 
 
38) Recuperar código de partidas e número de sets vencidos em cada uma pela equipe do 
técnico “Bernardinho”. 
 
SELECT COD_PART, NUM_SETS FROM PARTIDA_EQUIPE WHERE COD_E 
IN (SELECT COD_E FROM EQUIPE WHERE TECNICO = 'BERNARDINHO'); 
 
 
 
39) Recuperar nome dos estádios, sem repetição, que sediaram partidas a partir das 21h 
(inclusive). 
 
SELECT DISTINCT ESTADIO FROM PARTIDA WHERE HORA >= 21; 
 
 
 
40) Recuperar peso do atleta mais velho da competição. 
 
SELECT PESO FROM ATLETA WHERE IDADE IN (SELECT MAX(IDADE) 
FROM ATLETA); 
 
 
 
 
 
 
 
 
41) Recuperar códigos das equipes que não venceram partida alguma (isto é, número de 
sets é menor que 3 em todas as partidas da equipe). 
 
SELECT COD_E FROM EQUIPE A WHERE NOT EXISTS(SELECT * FROM 
PARTIDA_EQUIPE B WHERE NUM_SETS = 3 AND A.COD_E = B.COD_E); 
 
 
 
42) Recuperar código da equipe e nome dos atletas mais velhos de cada equipe. 
 
SELECT ATLETA.COD_E, NOME_A FROM ATLETA, (SELECT COD_E, 
MAX(IDADE) AS MAIOR FROM ATLETA GROUP BY COD_E) AS TABELA 
WHERE ATLETA.COD_E = TABELA.COD_E AND ATLETA.IDADE = 
TABELA.MAIOR; 
 
 
 
43) Recuperar nome e sigla dos países cujas siglas não terminam com “A”. 
 
SELECT NOME_P, SIGLA FROM PAIS WHERE SIGLA NOT LIKE '%A'; 
 
 
 
44) Recuperar sigla dos países cujas equipes jogam no dia 20, após as 18h. 
 
SELECT SIGLA FROM PAIS WHERE COD_P IN (SELECT COD_P FROM 
EQUIPE WHERE COD_E IN (SELECT COD_E FROM PARTIDA_EQUIPE 
WHERE COD_PART IN (SELECT COD_PART FROM PARTIDA WHERE DIA 
= 20 AND HORA > 18))); 
 
 
 
 
 
 
45) Recuperar quantidade de partidas cujo placar foi 3x2. 
 
SELECT COUNT(DISTINCT A.COD_PART) FROM PARTIDA_EQUIPE A, 
PARTIDA_EQUIPE B WHERE A.COD_PART = B.COD_PART AND 
((A.NUM_SETS = 3 AND B.NUM_SETS = 2) OR (A.NUM_SETS = 2 AND 
B.NUM_SETS = 3)); 
 
 
 
46) Recuperar número total de sets vencidos por cada equipe. 
 
SELECT COD_E, SUM(NUM_SETS) FROM PARTIDA_EQUIPE GROUP BY 
COD_E; 
 
 
 
47) Recuperar média de idade dos atletas da equipe cujo país é “Estados Unidos”. 
 
SELECT AVG(IDADE) FROM ATLETA WHERE COD_E IN (SELECT COD_E 
FROM EQUIPE WHERE COD_P IN (SELECT COD_P FROM PAIS WHERE 
NOME_P = 'ESTADOS UNIDOS')); 
 
 
 
48) Recuperar nome das equipes que venceram ao menos uma partida nesta Olimpíada 
(isto é, tem número de sets igual a 3 em alguma partida). 
 
SELECT NOME_E FROM EQUIPE WHERE COD_E IN (SELECT COD_E FROM 
PARTIDA_EQUIPE WHERE NUM_SETS = 3); 
 
 
 
 
 
 
49) Recuperar o IMC (10000 * peso/altura2) do atleta de código 3. 
 
SELECT 10000*PESO/(ALTURA*ALTURA) AS IMC FROM ATLETA WHERE 
COD_A = 3; 
 
 
 
50) Recuperar a média do IMC (10000*peso/altura2) de todos os atletas da competição. 
 
SELECT AVG(10000*PESO/(ALTURA*ALTURA)) FROM ATLETA;