10 exercícios sobre subconsultas correlacionadas com soluções

As subconsultas correlacionadas são um poderoso recurso SQL essencial para a análise avançada de dados. Este artigo fornece 10 exercícios práticos para ajudá-lo a dominar as subconsultas correlacionadas.

O SQL é uma habilidade fundamental para qualquer pessoa que trabalhe com dados, seja como analista de dados, desenvolvedor de SQL, engenheiro de dados ou qualquer outra profissão relacionada. Dominar o SQL envolve mais do que apenas compreender os conceitos básicos. Também é necessário aprender os recursos avançados do SQL, como as subconsultas.

Uma subconsulta é uma consulta SQL aninhada em uma consulta maior. Há muitos tipos diferentes de subconsultas. Uma subconsulta correlacionada é um tipo de subconsulta que se refere à consulta externa e não pode ser executada de forma independente. As subconsultas, especialmente as subconsultas correlacionadas, podem ser um tópico desafiador para os alunos de SQL.

Anteriormente, fornecemos uma coleção de exercícios de subconsulta em nosso artigo: Prática de subconsulta SQL: 15 exercícios com soluções. Neste artigo, queremos oferecer a você um conjunto de exercícios práticos especificamente sobre o tópico de subconsultas correlacionadas. Os exercícios deste artigo foram extraídos de nossos cursos interativos Basic SQL Practice: A Store e Prática de SQL: Universidade. Esses dois cursos fazem parte da trilha "Trilha de Práticas em SQL", que oferece vários cursos práticos de SQL. Todos os cursos da trilha são baseados em cenários do mundo real e são organizados por tópicos de SQL para ajudá-lo a se concentrar em sua área de interesse.

Exercícios sobre um banco de dados de armazenamento

O primeiro conjunto de exercícios é baseado no banco de dados de uma loja on-line. Esses exercícios foram retirados de nosso curso Basic SQL Practice: A Store. Começaremos com pouco e trabalharemos primeiro com apenas duas tabelas: product e category.

A tabela product contém uma lista de produtos disponíveis na loja.

• product_id - o ID do produto.
• product_name - O nome do produto.
• category_id - o ID da categoria do produto. Ela ajuda você a se conectar com a tabela de categorias.
• quantity_per_unit - A quantidade de itens do produto em uma unidade.
• unit_price - O preço do produto
• discontinued - a informação sobre se o produto ainda está disponível na loja (o valor FALSE) ou se foi descontinuado (TRUE).

A tabela category contém informações sobre as categorias dos produtos:

• category_id - o ID da categoria.
• name - o nome da categoria
• description - a descrição opcional da categoria

Exercício 1: Produtos caros em cada categoria

Exercício: Localize os produtos que são mais caros do que o preço médio dos produtos em sua própria categoria. Inclua o nome da categoria, o nome do produto e o preço unitário no resultado.

Solução:

SELECT
  c.name,
  p.product_name,
  p.unit_price
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  );

Explicação da solução:

Na solução, usamos uma subconsulta correlacionada para calcular o preço unitário médio dos produtos da mesma categoria do produto que está sendo processado pela consulta externa. Essa subconsulta faz referência à categoria, denotada como c, da consulta externa. Ela identifica os produtos dessa categoria e calcula seu preço médio. Os produtos que custam mais do que a média de sua categoria são filtrados usando essa média. Em seguida, a consulta externa recupera e exibe o nome da categoria, o nome do produto e o preço unitário desses produtos.

Exercício 2: o número de produtos caros em cada categoria

Exercício: Mostre os nomes das categorias e o número de produtos dessa categoria com um preço unitário maior que o preço médio de um produto dessa categoria. Mostre somente as categorias que têm esse(s) produto(s). Exiba duas colunas: name (o nome da categoria) e expensive_products (o número de produtos que custam mais do que o produto médio nessa categoria).

Solução:

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT
    AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  )
GROUP BY
  c.category_id,
  c.name;

Explicação da solução:

Este exercício é semelhante ao anterior. Também precisamos encontrar produtos com preço unitário maior que o preço médio da categoria. Entretanto, desta vez, queremos contar os produtos em cada categoria.

Na solução, usamos a mesma subconsulta correlacionada de antes: calculamos o preço unitário médio dos produtos da mesma categoria que o produto que está sendo processado pela consulta externa. Em seguida, essa média é usada na consulta principal para filtrar apenas os produtos cujo preço unitário é maior que a média da categoria. A consulta principal conta esses produtos usando COUNT(*) e agrupa os resultados por categoria com GROUP BY. O resultado final exibe o nome da categoria e a contagem de produtos.

Exercício 3: Produtos descontinuados, continuados e todos os produtos em uma categoria

Exercício: Para cada categoria, exiba seu endereço name, o número de produtos descontinuados nessa categoria (discontinued_products), o número de produtos continuados nessa categoria (continued_products) e o número de todos os produtos nessa categoria (all_products).

Solução:

SELECT
  c.name,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS TRUE) AS discontinued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS FALSE) AS continued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id) AS all_products
FROM category c;

Explicação da solução:

Aqui usamos três subconsultas correlacionadas na instrução SELECT para obter contagens de produtos com base em seu status contínuo/descontinuado para cada categoria. Cada subconsulta está correlacionada com a consulta principal por meio do category_id para que as contagens estejam corretas para cada categoria.

A primeira subconsulta conta o número de produtos em uma categoria em que o sinalizador discontinued é TRUE. Isso nos dá uma contagem total de todos os produtos que não estão mais disponíveis em cada categoria. A segunda subconsulta conta o número de produtos que ainda estão ativos em cada categoria, usando um método semelhante. A terceira subconsulta simplesmente conta o número de todos os produtos em uma categoria.

Como alternativa, esse exercício poderia ser resolvido sem subconsultas, usando uma combinação de CASE WHEN com SUM e GROUP BY>. Como exercício alternativo: você pode resolver esse exercício das duas maneiras?

Mais tabelas no banco de dados da loja: Tabelas Customer e Purchase

Nos exercícios a seguir, trabalharemos com tabelas adicionais no banco de dados da loja relacionadas a compras: customer, purchase, e purchase_item.

A tabela customer contém as informações sobre os clientes. Ela tem as seguintes colunas:

• customer_id - o ID do cliente.
• contact_name - O nome completo do cliente.
• contact_email - o e-mail do cliente.

A tabela purchase contém as informações sobre cada pedido:

• purchase_id - o ID da compra.
• customer_id - o ID do cliente.
• total_price - O preço total do pedido.
• purchase_date - o registro de data e hora da compra.

A tabela purchase_item conecta as compras com os produtos. A tabela contém as seguintes colunas:

• purchase_id - o ID da compra.
• product_id - O ID do produto comprado.
• unit_price - O preço de uma unidade de um produto.
• quantity - O número de unidades compradas de um produto.

Exercício 4: Produtos em categorias

Exercício: Para cada produto comprado, exiba o nome do produto, a quantidade máxima em que ele foi comprado e o número de compras desse tipo (desse produto nessa quantidade máxima). Exiba três colunas: product_name, quantity, e purchases_number.

Solução:

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
  ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) FROM purchase_item WHERE product_id = pi.product_id)
GROUP BY
  pi.product_id,
  product_name,
  quantity;

Explicação da solução:

Aqui queremos encontrar a quantidade máxima em que cada produto foi comprado e quantas vezes essas compras ocorreram. Usamos uma subconsulta correlacionada e o site GROUP BY para obter esse resultado.

Primeiro, usamos uma subconsulta correlacionada em WHERE para determinar a quantidade máxima em que cada produto foi comprado. Em seguida, na consulta externa, usamos esse valor para encontrar compras em que a quantidade é igual a essa quantidade máxima. Por fim, a consulta externa agrupa os resultados por product_id, product_name e quantity e usa a função de agregação COUNT(purchase_id) para calcular o número de compras de cada produto na quantidade máxima.

Exercício 5: Porcentagem do dinheiro gasto pelo cliente na compra

Exercício: Para cada cliente que fez alguma compra, exiba o ID de cada compra feita por esse cliente e a porcentagem do dinheiro gasto nessa compra em relação a todo o dinheiro gasto por esse cliente. Arredonde as porcentagens para números inteiros. Mostre três colunas: contact_name, purchase_id e percentage.

Solução:

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 / (SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer 
  ON p.customer_id = customer.customer_id;

Explicação da solução:

Na consulta externa, listamos cada compra feita por cada cliente. Unimos as tabelas purchase e customer para exibir o nome de contato do cliente e o ID da compra. Usamos uma subconsulta correlacionada para descobrir o valor total gasto pelo cliente atual. Usamos o valor encontrado pela subconsulta para calcular a porcentagem do gasto total que a compra atual representa.

Exercício 6: Clientes com compras acima de seu valor médio de compra

Exercício: Encontre clientes cujo total da última compra foi maior que o valor médio de compra. Exiba o nome do cliente e o total da última compra.

Solução:

SELECT 
  c.contact_name, 
  p.total_price AS last_purchase_total
FROM purchase p
JOIN customer c 
ON p.customer_id = c.customer_id
WHERE p.purchase_date = (
    SELECT MAX(lp.purchase_date)
    FROM purchase lp
    WHERE lp.customer_id = p.customer_id
  )
AND p.total_price > (
    SELECT AVG(ap.total_price)
    FROM purchase ap
    WHERE ap.customer_id = p.customer_id
);

Explicação da solução:

Este exercício precisa de duas subconsultas. A primeira subconsulta é usada para encontrar a data da compra mais recente de cada cliente, semelhante à subconsulta do Exercício 4, mas com foco na data da transação e não nas quantidades. A segunda subconsulta calcula o preço total médio das compras do cliente e é usada para filtrar as compras cujo preço é maior que a média. As duas subconsultas são combinadas na cláusula WHERE usando um operador AND.

Exercícios sobre o modelo de dados da universidade

O segundo conjunto de exercícios deste artigo é baseado no banco de dados de uma universidade. Esses exercícios foram extraídos de nosso curso Prática de SQL: Universidade. O banco de dados da universidade tem 4 tabelas.

A tabela course contém informações sobre os cursos oferecidos na universidade e os caminhos de aprendizagem aos quais eles estão associados:

• id - Um ID exclusivo para cada curso.
• title - O nome do curso.
• lecture_hours - Número total de horas de aula no curso.
• tutorial_hours - Número total de horas de tutorial no curso.

A tabela student contém todas as informações sobre cada aluno que frequenta a universidade:

• id - Um ID exclusivo para cada aluno.
• first_name - O primeiro nome do aluno.
• last_name - O sobrenome do aluno.

A tabela course_edition contém informações sobre quais professores ministram cada curso em cada semestre:

• id - O ID da edição do curso.
• course_id - O ID do curso.
• academic_semester - O ano civil e o período (outono ou primavera) do semestre
• lecturer - O nome do professor que ministra o curso.

A tabela course_enrollment contém as informações sobre o aluno matriculado em uma edição do curso:

• course_edition_id - O ID da edição do curso.
• student_id - O ID do aluno.
• midterm_grade - A nota do meio do curso que o aluno recebeu.
• final_grade - A nota final que o aluno recebeu.
• course_letter_grade - A nota do curso, na forma de uma letra (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D- ou F).
• passed - Se o aluno foi aprovado (TRUE) ou reprovado (FALSE) no curso.

Se um aluno ainda não tiver feito o exame, você poderá ver alguns campos NULL na tabela.

Exercício 7: Alunos com nota acima da média

Exercício: Encontre os alunos que obtiveram uma nota final acima da média em qualquer uma das edições do curso que frequentaram. Mostrar: nome e sobrenome do aluno, título do curso e final_grade. Mostre apenas os alunos cuja nota final nessa edição do curso foi maior do que a nota final média nessa edição do curso.

Solução:

SELECT
  first_name,
  last_name,
  title,
  final_grade
FROM course_enrollment AS c_en
JOIN student AS s
  ON c_en.student_id = s.id
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_e
  WHERE c_e.course_edition_id = c_en.course_edition_id
);

Explicação da solução:

A solução usa uma única consulta correlacionada. Na subconsulta correlacionada, encontramos a nota final média para a edição do curso processada na consulta principal. Em seguida, usamos a média encontrada na subconsulta para filtrar as matrículas do curso em que a nota final do aluno estava acima da média. Na consulta principal, juntamos as tabelas course_enrollment, student, course_edition e course para combinar o nome e o sobrenome do aluno com o título do curso e a nota que ele recebeu.

Exercício 8: Número de alunos com notas iguais ou superiores

Exercício:Para cada nota final na tabela course_enrollment, conte o número de alunos que obtiveram exatamente essa nota ou acima dela. Mostre duas colunas: final_grade e students_number.

Solução:

SELECT DISTINCT
  final_grade,
  (SELECT COUNT (student_id)
   FROM course_enrollment
   WHERE final_grade >= c.final_grade) AS students_number
FROM course_enrollment c;

Explicação da solução:

A consulta principal seleciona cada nota final única (distinta) da tabela course_enrollment. Para cada uma dessas notas, a subconsulta correlacionada conta o número de alunos cujas notas finais são iguais ou superiores à nota que está sendo processada pela consulta externa. O resultado é exibido em duas colunas: final_grade, que mostra a nota em consideração, e students_number, que indica o número de alunos que alcançaram essa nota ou uma nota mais alta.

Exercício 9: Alunos que foram aprovados e os que não foram

Exercício: Divida os alunos de cada edição do curso em dois grupos: os que foram aprovados e os que não foram. Para cada grupo, exiba as colunas a seguir:

• course_edition_id
• passed
• average_final_grade - A média dos alunos desse grupo (aprovados ou não aprovados), arredondada para duas casas decimais.
• average_edition_grade - A média geral dos alunos dessa edição do curso, arredondada para duas casas decimais.

Solução:

SELECT
  course_edition_id,
  passed,
  ROUND(AVG(final_grade), 2) AS average_final_grade,
  (SELECT ROUND(AVG(final_grade), 2)
         FROM course_enrollment
         WHERE course_edition_id = c_e.course_edition_id) AS average_edition_grade
FROM course_enrollment c_e
GROUP BY 
  course_edition_id, 
  passed;

Explicação da solução:

A consulta externa seleciona o ID da edição do curso, o valor passed e a nota final média para esse grupo de alunos (aprovado ou não aprovado) usando a cláusula regular GROUP BY. Ela também usa uma subconsulta correlacionada para encontrar a nota final média geral da edição do curso processada na consulta externa e adiciona essa média ao resultado da consulta. Dessa forma, você pode comparar a nota final de cada grupo com a média geral.

Exercício 10: Médias novamente

Exercício: Para cada edição do curso, mostre os seguintes dados:

• title
• average_result - A nota final média dos alunos com uma nota final maior que a média da edição do curso. Arredonde o resultado para 0 casas decimais (ou seja, um número inteiro).
• results_better_than_average - O número de alunos cuja nota final é maior do que a nota final média para essa edição do curso.

Solução:

SELECT
  c.title,
  ROUND(AVG(final_grade)) AS average_result,
  COUNT (student_id) AS results_better_than_average
FROM course_enrollment AS c_en
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_en2
  WHERE c_en2.course_edition_id = c_en.course_edition_id
)
GROUP BY 
  c.id, 
  c.title;

Explicação da solução:

Na subconsulta correlacionada, calculamos a nota final média para a edição do curso processada pela consulta externa. Em seguida, usamos esse valor para filtrar os alunos cuja nota final nessa edição do curso estava acima da média. Na consulta externa, exibimos o título do curso, o número de alunos com a nota final superior à média e a nota final média desse grupo de alunos.

Conclusão e próximas etapas

Neste artigo, exploramos vários exercícios de subconsulta correlacionada para melhorar sua compreensão desse importante recurso de SQL. Para aqueles que desejam desenvolver ainda mais suas habilidades em SQL, recomendamos nossa trilha "Trilha de Práticas em SQL". Ele inclui 10 cursos práticos de SQL diferentes. Cada curso é repleto de exercícios práticos baseados em cenários do mundo real e é organizado por tópicos de SQL para ajudá-lo a se concentrar em sua área de interesse.

Além disso, recomendamos que você dê uma olhada em nosso Ilimitado Vitalício SQL Package - o melhor negócio para quem está aprendendo SQL. Essa oferta de pagamento único fornece acesso vitalício a todos os cursos atuais e futuros em nosso catálogo, incluindo tudo o que está na trilha "Trilha de Práticas em SQL". É um recurso inestimável para qualquer pessoa que queira se tornar um especialista em SQL.