Gabarito disponível
contribuinte(
id_contribuinte INTEGER PRIMARY KEY,
nome VARCHAR(100));
pagamento(
id_pagamento INTEGER PRIMARY KEY,
id_contribuinte INTEGER,
valor DECIMAL(12,2),
FOREIGN KEY (id_contribuinte) REFERENCES contribuinte(id_contribuinte));
As tabelas contribuinte e pagamento apresentadas anteriormente fazem parte do banco de dados da secretaria de fazenda de certo ente federativo. A administração tributária deste ente deseja listar os nomes dos contribuintes cujo valor total de pagamentos é superior à média total dos pagamentos de todos os contribuintes.
A partir dessa situação hipotética, assinale a opção em que é corretamente apresentada a instrução adequada, de acordo com o padrão SQL/ANSI, para se obter a lista desejada pela administração tributária.
id_contribuinte INTEGER PRIMARY KEY,
nome VARCHAR(100));
pagamento(
id_pagamento INTEGER PRIMARY KEY,
id_contribuinte INTEGER,
valor DECIMAL(12,2),
FOREIGN KEY (id_contribuinte) REFERENCES contribuinte(id_contribuinte));
As tabelas contribuinte e pagamento apresentadas anteriormente fazem parte do banco de dados da secretaria de fazenda de certo ente federativo. A administração tributária deste ente deseja listar os nomes dos contribuintes cujo valor total de pagamentos é superior à média total dos pagamentos de todos os contribuintes.
A partir dessa situação hipotética, assinale a opção em que é corretamente apresentada a instrução adequada, de acordo com o padrão SQL/ANSI, para se obter a lista desejada pela administração tributária.
Resolução comentada
Use JOIN, GROUP BY nome e HAVING com subconsulta de média.
Para filtrar grupos pelo total agregado, usa-se HAVING após o GROUP BY. A comparação é SUM(p.valor) > (SELECT AVG(valor) FROM pagamento), uma subconsulta escalar com a média geral. Isso é feito na alternativa B.
Análise das alternativas
A) Não tem GROUP BY e usa ALL desnecessariamente. C) Usa HAVING sem GROUP BY e compara valor individual com a média. D) Usa função de agregação no WHERE, o que não é permitido. E) Coloca o WHERE depois do GROUP BY, o que é sintaxe inválida, e usa agregação no WHERE.
Referência: SQL ANSI – GROUP BY, HAVING e subconsultas
Resolução comentada do VaiConcursos.