Prática de subconsulta SQL: 15 exercícios com soluções

As subconsultas costumam ser um desafio para os iniciantes. A prática leva à perfeição, portanto, junte-se a nós nestes 15 exercícios práticos de subconsulta SQL!

Em SQL, uma subconsulta é uma consulta aninhada em outra consulta. Ela simplifica a criação de consultas complexas para recuperar dados que atendam a condições específicas de várias tabelas.

Neste artigo, apresentamos várias maneiras de empregar subconsultas para criar consultas complexas. Começamos apresentando as subconsultas SQL e os casos de uso comuns. Em seguida, vamos orientá-lo em 15 exercícios práticos de subconsulta SQL, com soluções e explicações completas.

Se você nunca ouviu falar de subconsultas SQL, confira nosso Guia gratuito para iniciantes sobre subconsultas SQL. Você também pode obter experiência prática na criação de subconsultas em nosso SQL para Iniciantes curso.

Mas se você conhece o básico e está pronto para aprimorar seu conhecimento, vamos começar a praticar as subconsultas SQL.

Fundamentos da subconsulta SQL

Apenas para recapitular, uma subconsulta SQL é uma instrução SELECT incorporada em outra instrução SELECT. Você pode pensar nas subconsultas como blocos de construção que compõem consultas complexas: elas nos permitem dividir tarefas complicadas em partes menores e facilitam a leitura do código.

Imagine colocar uma pergunta dentro de outra - é assim que as subconsultas funcionam. Com a ajuda das subconsultas, você pode obter informações específicas de diferentes tabelas filtradas por diferentes condições, tudo de uma só vez.

Aqui estão alguns casos de uso comuns para subconsultas SQL:

• Filtragem de dados: Use subconsultas na cláusula WHERE para filtrar dados com base em condições específicas, tornando suas consultas mais dinâmicas. Abordado nos exercícios práticos de subconsulta 1, 2, 3, 4, 8 e 9.
• Agregações aninhadas: Empregue subconsultas para realizar agregações dentro de agregações, permitindo cálculos mais complexos. Abordado nos exercícios práticos de subconsulta 5, 6e 7.
• Verificação de existência: Determinar se um valor específico existe em outra tabela usando subconsultas com EXISTS ou IN. Abordado nos exercícios práticos de subconsultas 1, 2 e 14.
• Subconsultas correlacionadas: Crie subconsultas que façam referência a colunas da consulta externa, permitindo a filtragem com reconhecimento de contexto. Abordado nos exercícios práticos de subconsulta 10, 11, 12 e 13.
• Subconsultana cláusula SELECT: Inclua uma subconsulta na cláusula SELECT para recuperar um único valor ou conjunto de valores que possam ser usados na consulta principal. Abordado nos exercícios práticos de subconsulta 10 e 13.
• Subconsultana cláusula FROM: Use uma subconsulta na cláusula FROM para criar uma tabela temporária, permitindo uniões mais complexas. Abordado nos exercícios práticos de subconsulta 14 e 15.
  

Exercícios de subconsulta SQL

Conjunto de dados: Orquestras

Os exercícios a seguir usam o conjunto de dados orchestras que contém três tabelas.

• A tabela orchestras armazena todas as orquestras. As colunas são id, name, rating, city_origin, country_origin, e year em que a orquestra foi fundada.
• A tabela concerts tabela contém todos os concertos realizados pelas orquestras. As colunas são id, city, country, year, rating, e orchestra_id (faz referência à tabela). orchestras tabela).
• A tabela members tabela armazena os membros (ou seja, os músicos que tocam) de cada orquestra. As colunas são id, name, position (ou seja, o instrumento tocado), wage, experience, e orchestra_id (faz referência à tabela). orchestras tabela).

Agora que já estamos familiarizados com o conjunto de dados, vamos prosseguir com os exercícios práticos de SQL. Os exercícios a seguir foram retirados do Curso de Práticas em SQL curso.

Exercício 1: Selecionar orquestras com uma cidade de origem onde foi realizado um concerto em 2013

Exercício:

Selecione os nomes de todas as orquestras que tenham a mesma cidade de origem de qualquer cidade em que alguma orquestra tenha se apresentado em 2013.

Solução:

SELECT name
FROM orchestras
WHERE city_origin IN (SELECT city FROM concerts WHERE year = 2013);

Explicação da solução:

Nosso objetivo é selecionar nomes de orquestras que atendam a uma determinada condição, portanto, começamos com SELECT name FROM orchestras. Em seguida, a condição será imposta na coluna city_origin, conforme mencionado nas instruções.

Queremos selecionar somente as orquestras cuja cidade de origem pertença ao grupo de cidades onde foram realizados concertos no ano de 2013. Para criar essa condição na cláusula WHERE, usamos a subconsulta SQL.

Vamos criar uma (sub)consulta que seleciona todas as cidades onde foram realizados concertos em 2013: SELECT city FROM concerts WHERE year = 2013. Ela retorna uma coluna contendo nomes de cidades.

Para garantir que a cidade de origem pertença às cidades retornadas pela subconsulta, usamos o operador IN.

Exercício 2: Selecionar membros que pertencem a orquestras de alta classificação

Exercício:

Selecione os nomes e as posições (ou seja, o instrumento tocado) de todos os membros da orquestra que tenham mais de 10 anos de experiência e não pertençam a orquestras com classificação inferior a 8,0.

Solução:

SELECT
  name,
  position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);

Explicação da solução:

Queremos selecionar nomes e posições de membros da orquestra que atendam às condições indicadas nas instruções, portanto, começamos com SELECT name, position FROM members.

Em seguida, impomos condições de filtragem aos anos de experiência dos membros e às orquestras às quais eles pertencem. Queremos selecionar membros com mais de 10 anos de experiência. Assim, adicionamos a primeira condição da cláusula WHERE: experience > 10.

Não queremos selecionar membros que pertençam a orquestras com classificações abaixo de 8,0. Vamos criar uma (sub)consulta que seleciona todas as orquestras com classificação abaixo de 8,0: SELECT id FROM orchestras WHERE rating < 8.0.

Para garantir que as orquestras não pertençam às orquestras listadas por essa subconsulta, usamos o operador NOT IN.

Exercício 3: Selecionar membros que ganham mais do que violinistas

Exercício:

Mostre o nome e a posição dos membros da orquestra que ganham mais do que o salário médio de todos os violinistas.

Solução:

SELECT name, position
FROM members
WHERE wage > (SELECT AVG(wage)
              FROM members
              WHERE position = 'violin');

Explicação da solução:

Selecionamos as colunas name e position da tabela. members tabela.

Usamos uma subconsulta para descobrir o salário médio de todos os violinistas: SELECT AVG(wage) FROM members WHERE position = 'violin'.

Para garantir que selecionemos os membros da orquestra cujo salário seja maior que o salário médio de todos os violinistas, impomos uma condição à coluna wage para que seja maior que o valor médio retornado pela subconsulta.

Exercício 4: Selecionar orquestras de alta classificação mais novas que a orquestra de câmara

Exercício:

Mostre os nomes das orquestras que foram criadas depois da "Chamber Orchestra" e que têm uma classificação maior que 7,5.

Solução:

SELECT name
FROM orchestras 
WHERE year > (SELECT year FROM orchestras 
              WHERE name = 'Chamber Orchestra') 
AND rating > 7.5;

Explicação da solução:

Primeiro, selecionamos os nomes na tabela orchestras da tabela.

Em seguida, criamos uma subconsulta que retorna o ano em que a Chamber Orchestra foi criada.

Como queremos listar as orquestras criadas depois da Chamber Orchestra, impomos uma condição à coluna year para que seja maior do que o valor retornado por essa subconsulta.

Por fim, definimos a condição na coluna rating para ser maior que 7,5.

Exercício 5: Selecionar músicos em grandes orquestras

Exercício:

Mostre o nome e o número de membros de cada orquestra que tenha mais membros do que a média de membros de todas as orquestras da tabela.

Solução:

SELECT 
  o.name,
  COUNT(m.id)
FROM orchestras o
JOIN members m
ON o.id = m.orchestra_id
GROUP BY o.name
HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d);

Explicação da solução:

Para mostrar o nome da orquestra, basta selecionar a coluna name na tabela orchestras tabela. E para mostrar o número de membros em cada orquestra, devemos unir a tabela orchestras com a tabela members em sua coluna comum (o ID da orquestra). Em seguida, usamos a função COUNT() para contar todos os membros (COUNT(m.id)), agrupando pela coluna de nome da tabela orchestras tabela (GROUP BY o.name).

Queremos escolher somente as orquestras que têm mais do que o número médio de membros. Portanto, devemos impor uma condição em COUNT(m.id) para que seja maior que o número médio de membros. Para impor uma condição em uma função agregada, devemos usar a cláusula HAVING que segue a cláusula GROUP BY.

Podemos encontrar o número médio de membros da orquestra usando subconsultas. Para ser exato, usamos uma subconsulta aninhada - uma subconsulta dentro de uma subconsulta.

• A subconsulta interna encontra o número de membros da orquestra para cada orquestra usando a função agregada COUNT():

SELECT orchestra_id, COUNT(id) FROM members GROUP BY orchestra_id

• A subconsulta externa calcula a média de todos os valores COUNT(id) retornados pela subconsulta interna usando a função de agregação AVG():

SELECT AVG(d.count) FROM (<inner subquery>) AS d

Finalmente, a subconsulta total é:

(SELECT AVG(d.count) 
 FROM (SELECT orchestra_id, COUNT(id) 
       FROM members GROUP BY orchestra_id) AS d)

E ela retorna o número médio de membros por orquestra.

Agora que temos o número médio de membros da orquestra, podemos impor uma condição em COUNT(m.id) para garantir que seja maior que o número médio de membros da orquestra:

HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d)

Conjunto de dados: Universidade

Os exercícios a seguir usam o conjunto de dados da universidade, que contém seis tabelas.

• A tabela course armazena informações sobre os cursos. As colunas são id, title, learning_path, short_description, lecture_hours, tutorial_hours, ects_points, has_exam, e has_project.
• A tabela lecturer armazena informações sobre os professores. As colunas são id, first_name, last_name, degree, e email.
• A tabela student tabela contém informações sobre os alunos. As colunas são id, first_name, last_name, email, birth_date, e start_date.
• A tabela academic_semester contém informações sobre cada semestre de estudo. As colunas são id, calendar_year, term, start_date, e end_date.
• A tabela course_edition tabela contém informações sobre quais professores ministram cada curso em cada semestre. As colunas são id, course_id (faz referência à course tabela), academic_semester_id (faz referência à academic_semester tabela) e lecturer_id (faz referência à lecturer tabela).
• A tabela course_enrollment contém informações sobre os alunos matriculados em cada curso. As colunas são course_edition_id (faz referência à tabela), (faz referência à tabela) e (faz referência à tabela). course_edition tabela), student_id (faz referência à student table), midterm_grade, final_grade, course_letter_grade, e passed.

Agora que estamos familiarizados com o conjunto de dados, vamos prosseguir com os exercícios práticos de SQL. Os exercícios a seguir são do curso básico da Universidade Trilha de Práticas em SQL .

Exercício 6: Selecionar cursos do período da primavera

Exercício:

Exibir os IDs e os títulos de todos os cursos que ocorreram em um período de primavera.

Solução:

SELECT
  id,
  title
FROM course
WHERE id = ANY (SELECT course_id
                FROM course_edition ce
                JOIN academic_semester asem
                ON ce.academic_semester_id = asem.id
                WHERE asem.term = 'spring');

Explicação da solução:

Começamos selecionando IDs e títulos da tabela course tabela. Na cláusula WHERE, devemos impor uma condição na coluna id da tabela, utilizando subconsultas e o operador . course utilizando subconsultas e o operador ANY.

Queremos selecionar cursos que ocorreram durante o período de primavera pelo menos uma vez, portanto, vamos começar criando uma subconsulta que selecione esses IDs de curso. Observe que precisamos unir a tabela course_edition com a tabela academic_semester em sua coluna comum (academic_semester_id e id, respectivamente) para poder filtrar os cursos do período de primavera.

O operador ANY retorna verdadeiro se pelo menos um valor retornado pela subconsulta atender à condição.

Vamos ilustrar isso:

A linha em verde retorna verdadeiro porque 9 é igual a um dos números retornados pela subconsulta.

A linha em vermelho retorna falso porque 3 não é igual a nenhum dos números retornados pela subconsulta.

Exercício 7: Selecionar todos os alunos que foram aprovados em pelo menos uma disciplina

Exercício:

Selecione os IDs e os nomes dos alunos que foram aprovados em pelo menos um curso.

Solução:

SELECT
  id,
  first_name,
  last_name
FROM student
WHERE id = ANY (SELECT student_id
                FROM course_enrollment
                WHERE passed = 't');

Explicação da solução:

Começamos selecionando os IDs e os nomes da tabela student tabela. Na cláusula WHERE, devemos impor uma condição na coluna id da tabela student usando subconsultas e o operador ANY.

Queremos selecionar os alunos que foram aprovados em pelo menos um curso, portanto, vamos começar criando uma subconsulta que seleciona os IDs de todos os alunos que foram aprovados em um ou mais cursos: SELECT student_id FROM course_enrollment WHERE passed = 't'

O operador ANY retorna true se pelo menos um valor retornado pela subconsulta atender à condição.

Vamos ilustrar isso:

As linhas em verde retornam verdadeiro porque tanto 5 quanto 8 pertencem aos IDs retornados pela subconsulta.

A linha em vermelho retorna falso porque 3 não pertence aos IDs retornados pela subconsulta.

Exercício 8: Selecionar o(s) professor(es) que leciona(m) o menor número de cursos

Exercício:

Encontre o(s) professor(es) com o menor número de cursos ministrados. Exiba o nome e o sobrenome do professor e o número de cursos que ele ministra (como no_of_courses).

Solução:

SELECT
  l.first_name,
  l.last_name,
  COUNT(ce.id) AS no_of_courses
FROM lecturer l
JOIN course_edition ce
ON l.id = ce.lecturer_id
GROUP BY l.first_name, l.last_name
HAVING COUNT(ce.id) 
            <= ALL (SELECT COUNT(id)
                    FROM course_edition
                    GROUP BY lecturer_id);

Explicação da solução:

Selecionamos os nomes da tabela lecturer juntando-os com a tabela course_edition na ID do professor. Contamos as linhas na tabela course_edition tabela para cada palestrante: COUNT(ce.id) AS no_of_courses. Assim, agrupamos por nomes de professores.

Para garantir que selecionemos somente o(s) palestrante(s) com o menor número de cursos ministrados, devemos impor uma condição em COUNT(ce.id) para que seja menor ou igual ao número de cursos ministrados para cada palestrante.

Vamos criar uma subconsulta que seleciona o número de cursos ministrados para cada professor: SELECT COUNT(id) FROM course_edition GROUP BY lecturer_id.

O operador ALL retorna verdadeiro se a condição for atendida por todas as linhas retornadas pela subconsulta. Aqui, queremos garantir que selecionaremos somente o(s) professor(es) cujo no_of_courses seja menor que todos os outros professores (e somente igual ao menor número de cursos ministrados, daí o sinal de menor/igual).

Vamos ilustrar isso:

A linha em verde retorna verdadeiro porque 4 é menor do que cada número retornado pela subconsulta e igual apenas ao menor número retornado pela subconsulta.

A linha em vermelho retorna falso porque 8 não é menor do que todos os números retornados pela subconsulta (ou seja, 8 > 4, 8 > 5, 8 > 6).

Exercício 9: Selecionar alunos matriculados no maior número de cursos

Exercício:

Encontre o(s) aluno(s) matriculado(s) no maior número de edições do curso. Exiba o ID, o nome e o sobrenome do aluno e o número de edições do curso em que ele está matriculado (como no_of_course_ed).

Solução:

SELECT
  student_id,
  first_name,
  last_name,
  COUNT(course_edition_id) AS no_of_course_ed
FROM course_enrollment
JOIN student
ON course_enrollment.student_id = student.id
GROUP BY student_id, first_name, last_name
HAVING COUNT(course_edition_id)
            >= ALL (SELECT COUNT(course_edition_id)
                    FROM course_enrollment
                    GROUP BY student_id);

Explicação da solução:

Selecionamos os IDs e os nomes da tabela student e juntamos student com a tabela course_edition tabela em sua coluna comum (ID do aluno). Contamos as linhas na tabela course_edition tabela para cada aluno (COUNT(course_edition_id) AS no_of_course_ed). Assim, agrupamos por IDs e nomes de alunos.

Para garantir que selecionemos somente os alunos com o maior número de cursos matriculados, devemos impor uma condição para que COUNT(course_edition_id) seja maior ou igual ao número de cursos matriculados de cada aluno.

Vamos criar uma subconsulta que seleciona o número de cursos matriculados de cada aluno: SELECT COUNT(course_edition_id) FROM course_enrollment GROUP BY student_id

O operador ALL retorna true se a condição for atendida por todas as linhas retornadas pela subconsulta. Aqui, queremos garantir que selecionemos somente os alunos cujo no_of_course_ed seja maior do que o de qualquer outro aluno (e somente igual ao maior número de cursos matriculados - daí o sinal de maior/igual).

Vamos ilustrar isso:

A linha em verde retorna verdadeiro porque 9 é maior do que cada número retornado pela subconsulta e igual apenas ao maior número retornado pela subconsulta.

A linha em vermelho retorna falso porque 6 não é maior do que todos os números retornados pela subconsulta (ou seja, 6 < 8 e 6 < 9).

Conjunto de dados: Loja

Os exercícios a seguir usam o banco de dados store que contém seis tabelas:

• A tabela customer contém informações sobre As colunas são customer_id, contact_name, company_name, contact_email, address, city, e country.
• A tabela product armazena informações sobre os produtos. As colunas são product_id, product_name, category_id (faz referência à category tabela), quantity_per_unit, unit_price, units_in_stock, e discontinued.
• A tabela category armazena informações sobre as categorias de produtos. As colunas são category_id, name, description, e parent_category_id (faz referência a ela mesma).
• A tabela purchase armazena informações sobre as compras feitas pelos clientes. As colunas são purchase_id, customer_id (faz referência à customer tabela), employee_id (faz referência à employee tabela), total_price, purchase_date, shipped_date, ship_address, ship_city, e ship_country.
• A tabela purchase_item associa todas as compras a produtos. As colunas são purchase_id (faz referência à purchase tabela), product_id (faz referência à product tabela), unit_price, e quantity.
• A tabela employee armazena informações sobre os funcionários. As colunas são employee_id, last_name, first_name, birth_date, address, city, country, e reports_to.

Agora que estamos familiarizados com o conjunto de dados, vamos prosseguir com os exercícios práticos de SQL. Os exercícios a seguir foram retirados do curso Basic Trilha de Práticas em SQL Store.

Exercício 10: Calcular a porcentagem gasta pelo cliente em cada compra

Exercício:

Para cada cliente que fez pelo menos uma 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 c
ON p.customer_id = c.customer_id;

Explicação da solução:

Para identificar os clientes que fizeram pelo menos uma compra, devemos unir a tabela purchase com a tabela customer em sua coluna comum (ID do cliente).

Para calcular a porcentagem de dinheiro gasto em uma compra em relação a todo o dinheiro gasto por esse cliente, precisamos de subconsultas. A subconsulta calcula quanto dinheiro um cliente gastou em todas as compras: SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id

Observe que a subconsulta faz referência à tabela purchase da consulta externa (com o alias de p) para obter o ID correto do cliente. Isso é chamado de subconsulta correlacionada.

Por fim, calculamos o valor percentual dividindo o total_price pelo valor retornado pela subconsulta. Além disso, devemos multiplicar esse valor por 100 para obter a porcentagem e ROUND() transformá-la em um número inteiro.

Se você quiser saber mais sobre subconsultas correlacionadas, leia Aprenda a escrever uma subconsulta correlacionada em 5 minutos.

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

Exercício:

Mostre os nomes das categorias e o número de produtos dessa categoria que têm 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: nome (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.name;

Explicação da solução:

Queremos exibir os nomes da categoria e a contagem de produtos; portanto, precisamos unir a tabela category com a tabela product em sua coluna comum (ID da categoria).

Para mostrar o número de produtos por categoria, usamos a função COUNT(). Como selecionamos o nome da categoria (c.name) e a contagem de produtos por nome de categoria (COUNT(*)), precisamos agrupar pela coluna do nome da categoria (GROUP BY c.name).

Na função COUNT(), queremos incluir somente produtos com preço unitário maior que o preço médio de um produto nessa categoria. Para fazer isso, usamos uma subconsulta correlacionada.

Na subconsulta, juntamos as colunas product e category novamente e selecionamos o valor médio dos preços unitários. Para garantir que obtenhamos a média dos valores do ID da categoria específica, impomos uma condição na cláusula WHERE afirmando que o category_id da subconsulta deve ser igual ao category_id da consulta externa.

A condição da cláusula WHERE da consulta principal diz que o unit_price deve ser maior que a média do unit_price para essa categoria, conforme retornado pela subconsulta.

Exercício 12: Exibir produtos comprados com sua quantidade máxima comprada

Exercício:

Para cada produto comprado, exiba seu nome, a maior quantidade em que foi comprado e o número de compras de quantidade máxima para esse produto. 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:

Para obter informações sobre os produtos e as quantidades em que foram comprados, é necessário unir a tabela purchase_item com a tabela product em sua coluna comum (ID do produto).

Usamos a função de agregação COUNT() para obter o número de compras (COUNT(purchase_id)).

Para garantir que selecionemos somente a maior quantidade em que um determinado produto foi comprado, precisamos criar uma subconsulta. A subconsulta faz referência ao valor product_id da consulta externa para garantir que escolhamos a quantidade máxima do produto correto - portanto, é uma subconsulta correlacionada.

Na cláusula WHERE da consulta principal, impomos a condição de que o valor da quantidade deve ser igual ao valor retornado pela subconsulta.

Exercício 13: Listar produtos descontinuados, continuados e totais em cada categoria

Exercício:

Para cada categoria, exiba:

• Seu nome.
• O número de produtos descontinuados (ou seja, não mais disponíveis) nessa categoria (nomeie essa coluna como discontinued_products).
• O número de produtos contínuos (ou seja, atualmente disponíveis) nessa categoria (dê o nome dessa coluna continued_products).
• O número de todos os produtos nessa categoria (nomear essa coluna 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:

Neste exercício, não usamos uma ou duas, mas três subconsultas correlacionadas.

Selecionamos o nome da categoria na tabela category da tabela.

A primeira subconsulta correlacionada conta todos os produtos que foram discontinued. Essa subconsulta faz referência ao valor category_id da consulta externa para garantir que os produtos descontinuados sejam contados por categoria.

A segunda subconsulta correlacionada conta todos os produtos que não foram descontinuados. Essa subconsulta faz referência ao valor category_id da consulta externa para garantir que os produtos continuados sejam contados por categoria.

A terceira subconsulta correlacionada conta todos os produtos por categoria. Essa subconsulta se refere ao valor category_id da consulta externa para garantir que todos os produtos sejam contados por categoria.

Exercício 14: Contar as compras feitas por cada funcionário em Houston

Exercício:

Exiba o ID do funcionário e o número total de compras que esse funcionário processou. Use uma subconsulta para obter informações sobre o número de pedidos que cada funcionário processou por cliente e faça com que a consulta principal selecione FROM essa subconsulta. Considere apenas os funcionários que moram em Houston.

Solução:

SELECT
  employee_per_customer.employee_id,
  SUM(employee_per_customer.no_of_purchases) AS total_no_of_purchases
FROM (SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id
     ) AS employee_per_customer
GROUP BY employee_per_customer.employee_id;

Explicação da solução:

Vamos começar garantindo que consideremos somente os funcionários que moram em Houston. Para isso, usamos a palavra-chave EXISTS. Ela retornará true se a subconsulta retornar pelo menos uma linha.

Observe que a subconsulta passada para a palavra-chave EXISTS é uma subconsulta correlacionada, pois se refere ao valor employee_id de sua consulta externa (que é uma subconsulta para a consulta principal).

Vamos analisar a subconsulta que é passada para a consulta principal na cláusula FROM. Ela seleciona IDs de funcionários e clientes e conta quantas compras foram feitas por funcionário e por cliente (por isso, o agrupamento por valores de ID de funcionário e cliente).

      SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id

A parte em vermelho garante que consideremos apenas os funcionários que moram em Houston.

Essa subconsulta tem o alias AS employee_per_customer e a consulta principal seleciona a partir dela.

A consulta principal seleciona o seguinte:

• IDs de funcionários da subconsulta (de employee_per_customer),
• O número total de compras feitas por cada funcionário. Isso requer o agrupamento por ID de funcionário (GROUP BY employee_per_customer.employee_id).

Observe que a subconsulta correlacionada usa COUNT() para contar as compras (ou linhas) por funcionário e por cliente. Mas a consulta principal usa a função SUM() para somar todos os valores retornados por COUNT() na subconsulta.

Você pode saber mais sobre as funções de agregação aqui. E confira este artigo sobre como usar SUM() com OVER(PARTITION BY) para saber mais detalhes sobre funções de janela.

Este exercício apresentou a ideia de usar subconsultas como blocos de construção - aqui, usamos três blocos de construção para buscar os dados desejados.

Exercício 15: Encontre o maior número de categorias de produtos em uma compra

Exercício:

Use uma subconsulta para selecionar o ID da compra e o número de categorias distintas contidas nessa compra. Na consulta principal, selecione o número máximo de categorias dessa subconsulta.

Solução:

SELECT MAX(categories_per_purchase.category_count) 
         AS max_categories_per_purchase
FROM (SELECT 
        purchase_id, 
        COUNT(DISTINCT category_id) AS category_count
      FROM purchase_item pi
      JOIN product p
      ON pi.product_id = p.product_id
      GROUP BY purchase_id) AS categories_per_purchase;

Explicação da solução:

Vamos começar com a subconsulta que é passada na cláusula FROM da consulta principal.

Juntamos a tabela purchase_item com a tabela product em sua coluna comum (ID do produto). Selecionamos o ID da compra e os IDs da categoria COUNT DISTINCT por compra. Portanto, agrupamos pela coluna purchase_id

A consulta principal usa a função MAX() para selecionar (na subconsulta com o alias AS categories_per_purchase) o número máximo de categorias de produtos contidas em uma compra.

Mais prática de subconsulta SQL

Este artigo apresentou vários casos de uso de subconsultas, incluindo filtragem avançada de dados ou aninhamento de consultas entre si. Uma ideia básica de como se beneficiar do uso de subconsultas é dividir uma pergunta em (sub)perguntas menores - cada pequena (sub)pergunta deve poder ser respondida usando uma subconsulta.

Vá em frente e pratique por conta própria - essa é a melhor maneira de aprender mais técnicas de emprego de subconsultas para simplificar tarefas complexas de extração de dados. Confira mais exercícios de subconsulta aqui e aqui.

Incentivamos você a mergulhar de cabeça e praticar com nossos cursos de SQL. Cada um desses cursos tem uma seção separada sobre subconsultas e seções separadas sobre outros tópicos desafiadores de SQL: consultas de tabela única, JOINs, agrupamento e muito mais.

1. Curso de Práticas em SQL
2. Básico Trilha de Práticas em SQL: A Store
3. Básico Trilha de Práticas em SQL: Universidade
4. Básico Trilha de Práticas em SQL: Dados de tráfego de blog
5. Básico Trilha de Práticas em SQL: Rastreamento de consultas

Inscreva-se agora e comece gratuitamente. Boa sorte!