Pular para o conteúdo principal

Questão de Banco de Dados — Consultas e Comandos em SQL — FGV 2024

Banco de DadosConsultas e Comandos em SQL
Código
fg165277
Banca
FGV
Órgão
Pref Nova Iguaçu
Ano
2024
Cargo
AFTM (Pref N Iguaçu)

Seja um banco de dados relacional especificado em SQL de uma empresa de correspondência entre clientes, instituições financeiras e empréstimos contratados por esses clientes nessas instituições, previamente implementado em um banco de dados como a seguir:

 

create table tb_cliente
(
    id_cliente     integer       primary key,
    num_cpf        char(11)      unique,
    nome           varchar(50)   not null,
    email          varchar(20),
    telefone       varchar(20),
    endereco       varchar(100),
    cidade         varchar(20),
    estado         char(2)
);

create table tb_financeira
(
    id_financeira  integer       primary key,
    razao_social   varchar(30)   not null,
    cidade         varchar(30)   not null,
    estado         char(2)       not null
);

create table tb_emprestimo
(
    id_financeira  integer       references tb_financeira,
    id_cliente     integer       references tb_cliente,
    valor          real          not null check(valor > 0),
    dia            integer       not null check(dia >= 1 and dia <= 31),
    mes            integer       not null check(mes >= 1 and mes <= 12),
    ano            integer       not null check(ano >= 1980 and ano <= 2100),
    primary key(id_financeira, id_cliente, dia, mes, ano)
);

 

OBS: Neste banco de dados, cadeias de caracteres (strings) são representadas envoltas em aspas simples.

 

Com vistas à detecção de movimentações atípicas, um auditor desenvolveu uma consulta que identificasse médias de empréstimos em meses de 2024 que fossem destoantes do comportamento da média de empréstimos desse mesmo ano.

 

Para isso, ele desenvolveu a consulta a seguir, que necessita ser complementada:

 

select f.id_financeira, c.id_cliente, e.mes, avg(e.valor) from tb_cliente c natural join tb_emprestimo e

natural join tb_financeira f

where e.ano = 2024

group by f.id_financeira, c.id_cliente, e.mes, e.ano

having avg(e.valor) >=

 

( /* COMPLEMENTAR */ )

 

A fim de substituir o trecho da consulta, marcado com o comentário /* COMPLEMENTAR */, assinale a subconsulta que corretamente atende ao objetivo almejado pelo auditor.

  1. Aselect avg(e.valor) from tb_financeira f2 where e.id_cliente = c.id_cliente and f2.id_financeira = f.id_financeira group by f2.id_financeira, e.ano
  2. Bselect avg(e2.valor) from tb_emprestimo e2 group by e2.ano
  3. Cselect avg(e2.valor) from tb_emprestimo e2 where e.id_cliente = c.id_cliente and e.id_financeira = f.id_financeira and e.ano = e.ano group by f.id_financeira, e2.ano
  4. Dselect avg(e2.valor) from tb_emprestimo e2 where e2.id_cliente = c.id_cliente and e2.id_financeira = f.id_financeira and e2.ano = e.ano group by f.id_financeira, e2.ano
  5. Eselect avg(e2.valor) from tb_emprestimo where e2.id_cliente = c.id_cliente and e2.id_financeira = f.id_financeira group by f.id_financeira, e2.ano
Revelar gabarito e comentário

GabaritoD — select avg(e2.valor) from tb_emprestimo e2 where e2.id_cliente = c.id_cliente and e2.id_financeira = f.id_financeira and e2.ano = e.ano group by f.id_financeira, e2.ano

Comentário gerado por IA. É um apoio ao estudo, ancorado em fontes, mas pode conter imprecisões — confira sempre na fonte oficial (lei, súmula, edital e gabarito da banca). Encontrou um erro? Use “Reportar”.

Subconsultas correlacionadas e agrupamento em SQL

Gabarito: letra D. A subconsulta correta é a que calcula a média de empréstimos por financeira e por ano, correlacionando as colunas id_cliente e id_financeira da subconsulta com as da consulta externa, e filtrando pelo mesmo ano (e2.ano = e.ano). Isso permite comparar a média de cada grupo (financeira, cliente, mês) com a média anual daquela financeira, identificando meses destoantes.

A consulta principal agrupa os empréstimos de 2024 por id_financeira, id_cliente e mes, calculando a média de valor para cada grupo. O objetivo é comparar essa média mensal com a média anual da mesma financeira. Para isso, a subconsulta precisa:

  1. Correlacionar com a consulta externa: usar e2.id_cliente = c.id_cliente e e2.id_financeira = f.id_financeira para que a subconsulta seja avaliada para cada linha do grupo externo.

  2. Filtrar pelo mesmo ano: e2.ano = e.ano garante que a média anual seja calculada apenas para o ano de 2024 (já que e.ano = 2024 na consulta externa).

  3. Agrupar por financeira e ano: GROUP BY f.id_financeira, e2.ano para obter a média anual por financeira.

A alternativa D é a única que atende a todos esses requisitos. As demais ou não correlacionam corretamente, ou não filtram o ano, ou agrupam de forma incorreta.

  1. 1Correlacionar com a externa
  2. 2Filtrar pelo mesmo ano
  3. 3Agrupar por financeira e ano
LEVEL · soulevel.com.br

Alternativa A — ❌ Incorreta

Esta alternativa referencia e.valor dentro da subconsulta, mas e é um alias da consulta externa. Dentro da subconsulta, o alias e não está definido — a subconsulta usa tb_financeira f2 e não tem acesso direto ao alias e da consulta externa. Além disso, a subconsulta não filtra pelo ano (e.ano não é comparado com e2.ano), então ela calcularia a média de todos os anos, não apenas de 2024. O GROUP BY f2.id_financeira, e.ano também é inválido, pois e.ano não é uma coluna da subconsulta.

Alternativa B — ❌ Incorreta

Esta subconsulta calcula a média de e2.valor agrupada apenas por e2.ano, sem correlacionar com a consulta externa. Isso significa que ela retornaria a média global de todos os empréstimos de cada ano, não a média por financeira. Como o HAVING compara a média de cada grupo (financeira, cliente, mês) com esse valor global, a comparação não faria sentido para detectar meses destoantes por financeira.

Alternativa C — ❌ Incorreta

Esta alternativa tem um erro de correlação: usa e.id_cliente = c.id_cliente e e.id_financeira = f.id_financeira, mas e é o alias da consulta externa, não da subconsulta. Dentro da subconsulta, o alias e não está definido — deveria ser e2. Além disso, a condição e.ano = e.ano é tautológica (sempre verdadeira), não filtrando o ano corretamente. O GROUP BY f.id_financeira, e2.ano também referencia f que não está na subconsulta.

Alternativa D — ✅ Correta ⟵ GABARITO

Esta subconsulta correlaciona corretamente com a consulta externa usando e2.id_cliente = c.id_cliente e e2.id_financeira = f.id_financeira, e filtra pelo mesmo ano com e2.ano = e.ano. O GROUP BY f.id_financeira, e2.ano agrupa por financeira e ano, calculando a média anual de cada financeira. Como a consulta externa filtra e.ano = 2024, a subconsulta também considera apenas 2024. Assim, o HAVING avg(e.valor) >= (subconsulta) compara a média mensal de cada grupo com a média anual da mesma financeira, identificando meses com média maior ou igual à média anual.

Alternativa E — ❌ Incorreta

Esta alternativa não filtra pelo ano (e2.ano = e.ano está ausente). Isso faria a subconsulta calcular a média de todos os anos, não apenas de 2024. Além disso, o GROUP BY f.id_financeira, e2.ano referencia f que não está na subconsulta, causando erro de sintaxe. A correlação e2.id_cliente = c.id_cliente e e2.id_financeira = f.id_financeira está correta, mas a falta do filtro de ano invalida a comparação.

Gabarito: letra D

Link permanente: /questoes/fg165277