Questão Q1078917

O esquema relacional a seguir define um banco de dados, que contém os resultados das provas de natação dos Jogos Pan-americanos de 2019. Os nomes das tabelas e dos campos são autoexplicativos, permitindo, assim, entender a organização dos dados. As únicas exceções são os campos TEMPO_INDV e TEMPO, presentes na tabela RESULTADO. Eles existem porque em provas de revezamento são registrados os tempos (TEMPO_INDV) de cada um dos 4 membros da equipe e o tempo total (TEMPO) gasto pela equipe para completar a prova. Em provas individuais, o campo TEMPO_INDV recebe o valor nulo (NULL), enquanto o tempo gasto pelo atleta para completar a prova é registrado no campo TEMPO.

CREATE TABLE ATLETA (

NUMERO NUMBER(5,0) NOT NULL,

NOME VARCHAR2(50) NOT NULL,

PAIS CHAR(3) NOT NULL,

CONSTRAINT ATLETA_PK PRIMARY KEY (NUMERO))

CREATE TABLE PROVA (

CODIGO CHAR(5) NOT NULL,

DESCRICAO VARCHAR2(30) NOT NULL,

CONSTRAINT PROVA_PK PRIMARY KEY (CODIGO))

CREATE TABLE RESULTADO (

NUM_ATL NUMBER(5,0) NOT NULL,

COD_PRV CHAR(5) NOT NULL,

COLOCACAO NUMBER(2,0) NOT NULL,

TEMPO_INDV VARCHAR2(8),

TEMPO VARCHAR2(8) NOT NULL,

CONSTRAINT RESULTADO_PK PRIMARY KEY (NUM_ATL,COD_PRV),

CONSTRAINT RES_FK1 FOREIGN KEY (NUM_ATL)

REFERENCES ATLETA (NUMERO),

CONSTRAINT RES_FK2 FOREIGN KEY (COD_PRV)

REFERENCES PROVA (CODIGO))

Deseja-se conhecer os nomes dos nadadores brasileiros (PAIS='BRA') que conquistaram medalhas de ouro (COLOCACAO=1), juntamente com a quantidade de medalhas de ouro que cada um conquistou. Os dados devem estar ordenados em ordem decrescente de número de medalhas conquistadas.

Qual consulta SQL executa o que foi pedido?

Alternativas

SELECT A.NOME, COUNT(*)

FROM ATLETA A, RESULTADO R

WHERE A.NUMERO=R.NUM_ATL AND R.COLOCACAO=1 AND A.PAIS='BRA'

GROUP BY A.NUMERO

ORDER BY COUNT(*) DESC

SELECT A.NOME, COUNT(*)

FROM RESULTADO R, ATLETA A

WHERE A.NUMERO=R.NUM_ATL AND R.COLOCACAO=1 AND A.PAIS='BRA'

ORDER BY COUNT(R.COLOCACAO) DESC

SELECT NOME, COUNT(*)

FROM RESULTADO, ATLETA

WHERE NUMERO=NUM_ATL AND COLOCACAO=1 AND PAIS='BRA'

GROUP BY NOME

ORDER BY COUNT(*) DESC

SELECT A.NOME, COUNT(*)

FROM ATLETA A, RESULTADO R

HAVING A.NUMERO=NUM_ATL AND R.COLOCACAO=1 AND A.PAIS='BRA'

GROUP BY A.NOME

ORDER BY COUNT(*) DESC

SELECT A.NOME, COUNT(*)

FROM ATLETA A, RESULTADO R

WHERE A.NUMERO=NUM_ATL AND R.COLOCACAO=1

GROUP BY A.NOME

HAVING A.PAIS='BRA'

ORDER BY COUNT(*) DESC

Comentários

Gabarito está errado, a opção correta é a letra A!
Pessoal, letra A gera erro de execução!

Deve-se sempre lembrar que ao executar um GROUP BY, TODAS colunas do SELECT devem estar listadas no agrupamento, excetuando-se funções de agregação como count, min, max, avg e etc...

Na afirmativa A, o A.nome não está presente no agrupamento, gerando erro de execução.

Gabarito passível de recurso: no gabarito C, NOME não identifica unicamente o atleta, podendo somar as medalhas de dois atletas distintos. Alternativa deveria considerar também o número do atleta!

Alguém sabe explicar o porquê de a alternativa E estar errada? Me parece fazer a mesma coisa que a letra C, há apenas a falta do alias R para NUM_ATL, mas como não há ambiguidade não seria problema.
deveria ter uma opção com JOIN para pegar o nome do atleta
A questão traz a criação de três tabelas - Atleta, Prova e Resultado. Veja que Resultado tem chaves estrangeiras que referenciam Atleta e Prova. A chave primária de Resultado é a composição das chaves estrangeiras para Atleta e Prova, o que quer dizer que uma combinação (atleta, prova) não pode se repetir na tabela de resultado. Assim, um atleta pode participar de várias provas, tendo um resultado pra cada uma, e uma prova pode ter vários atletas participantes, cada um com seu resultado. É bem lógico, não é? Vamos à consulta!

"Deseja-se conhecer os nomes dos nadadores brasileiros (PAIS='BRA') que conquistaram medalhas de ouro (COLOCACAO=1) juntamente com a quantidade de medalhas de ouro que cada um conquistou."

Para atender esse trecho, primeiro precisamos fazer a junção entre Atleta e Resultado, já que os nomes dos atletas estão na tabela Atleta, e os dados a respeito das medalhas ficam no resultado. Na representação mais antiga das junções internas na linguagem SQL, ficaria assim:

SEL ECT *
FR OM Atleta a, Resultado r
WH ERE Numero=Num_Atl

Depois adicionamos as cláusulas especificadas na questão, para limitar nosso resultado aos atletas brasileiros e as medalhas às de ouro:

SEL ECT *
FR OM Atleta a, Resultado r
WH ERE Numero=Num_Atl
A ND Colocacao=1
A ND Pais='BRA'

Essa consulta já retorna um registro para cada associação entre um atleta brasileiro e uma medalha de ouro. Se um mesmo atleta tiver mais de uma medalha, ele será retornado mais de uma vez, uma para cada medalha de ouro conquistada.

Note que queremos que sejam retornados na consulta o nome do atleta e a quantidade das medalhas de ouro conquistadas por ele. Para retornar essa quantidade, basta agruparmos pelo atleta e contar a quantidade de registros no grupo:

SEL ECT a.Nome, CO UNT(*)
FR OM Atleta a, Resultado r
WH ERE Numero=Num_Atl
A ND Colocacao=1
A ND Pais='BRA'
GR OUP BY a.Nome

Com isso, nem precisamos nos preocupar com a ordenação dos resultados, pois já é possível responder o item. Veja que a única resposta compatível é a letra C!
Opção E está errado porque utiliza HAVING A.PAIS que não utiliza função agregadora em A.PAIS nem está numa cláusula groupby

Segue erro do MYSQL ao executar algo similar:
"deve aparecer na cláusula GROUP BY ou ser utilizada em uma função de agregação"

SóProvas

Continue usando...

O que está incluso