Voltar para a lista de artigos Artigos
19 minutos de leitura

11 Exercícios de expressões comuns de tabela SQL

Neste artigo, oferecemos 11 exercícios práticos que colocam em prática seus conhecimentos sobre expressões comuns de tabela (CTEs). Cada exercício de CTE é acompanhado de uma solução e de uma explicação detalhada.

Como diz o ditado: "Um pequeno progresso a cada dia resulta em grandes resultados". E isso, sem dúvida, é verdade para a proficiência em SQL. Assim como você deve ir regularmente à academia para manter seus músculos tonificados, deve fazer exercícios de expressões comuns de tabela com frequência para manter suas habilidades de consulta tonificadas para o trabalho pesado de análise de dados.

As CTEs, ou expressões comuns de tabela, são instruções poderosas no SQL. Elas permitem que você defina temporariamente uma subconsulta em uma consulta SQL e atribua um nome a ela. Isso significa que o CTE pode ser referenciado por seu nome dentro da consulta, como se fosse uma tabela.

O valor dos CTEs

Além dos nomes, há outras diferenças importantes entre os CTEs e as subconsultas. Os CTEs são úteis para estruturar consultas complexas, tornando-as mais fáceis de ler, entender e depurar. Os CTEs também podem ser usados repetidamente na mesma consulta, tornando-a mais concisa. Essas qualidades dos CTEs os tornam ideais para o trabalho de análise de dados, pois é comum que as consultas de análise de dados misturem SELECTs de dados simples com SELECTs agrupados com totais, médias e outras funções agregadas. Sem os CTEs, as consultas complexas poderiam se tornar praticamente impossíveis de ler ou exigir a criação de tabelas ou exibições temporárias que preenchem desnecessariamente o banco de dados com muitos objetos.

Os CTEs também são muito úteis para realizar análises de dados altamente complexas sem recorrer a outras linguagens de programação. O uso da recursão para encontrar caminhos críticos ou para percorrer tipos de dados abstratos (como árvores e gráficos) são exemplos que demonstram a utilidade dos CTEs. O mesmo ocorre com a possibilidade de criar consultas aninhadas, que reduzem gradualmente a complexidade de um problema até que ele se torne um simples SELECT.

A capacidade dos CTEs de tornar as consultas mais legíveis e concisas é algo que você apreciará se precisar revisar uma consulta longa anos depois de escrevê-la. Felizmente, a maioria dos sistemas modernos de gerenciamento de bancos de dados relacionais (RDBMSs) - incluindo PostgreSQL, MySQL, SQL Server e Oracle - permite o uso de CTEs.

Todos os exercícios compilados neste artigo foram retirados de nosso Consultas Recursivas curso. Trata-se de um tutorial detalhado sobre expressões de tabela comuns na análise de dados. Os 114 exercícios interativos abrangem CTEs simples, CTEs aninhados e CTEs recursivos em um tempo total estimado de 18 horas. Você também pode saber mais sobre CTEs lendo CTEs explicados com exemplos.

Outro tópico importante que todo analista de dados deve dominar são as funções de janela do SQL. Você pode conferir este conjunto de exercícios práticos de funções de janela do SQL para testar suas habilidades.

Agora, vamos começar com nossos exercícios de expressão de tabela comum. Começaremos com CTEs simples e depois passaremos para CTEs aninhados e recursivos.

Exercícios de CTE simples

Para esses exercícios de CTE, usaremos um banco de dados criado para gerenciar projetos de crowdfunding. Esse esquema é composto de três tabelas:

  1. supporter contém informações sobre os apoiadores, que são aqueles que doam dinheiro para os projetos.
  2. project contém informações sobre os projetos que recebem doações dos apoiadores.
  3. donation registra as doações dos apoiadores para os projetos.

A tabela supporter armazena os endereços id, first_name e last_name de cada apoiador no sistema. Vamos ver algumas de suas linhas:

idfirst_namelast_name
1MarleneWagner
2LonnieGoodwin
3SophiePeters
4EdwinPaul
5HughThornton

A tabela project A tabela armazena id, category, author_id e o minimal_amount necessários para iniciar cada projeto. Estas são algumas de suas linhas:

idcategoryauthor_idminimal_amount
1music11677
2music521573
3traveling24952
4traveling53135
5traveling28555

Os dados da coluna author_id vinculam cada projeto na tabela project com uma linha da tabela supporter tabela. Cada apoiador relacionado a um projeto pela coluna author_id é o autor desse projeto.

Por fim, a tabela donation tabela contém id, supporter_id, o valor da doação e a coluna donated, mostrando a data em que cada doação foi feita.

idproject_idsupporter_idamountdonated
144928.402016-09-07
2818384.382016-12-16
3612367.212016-01-21
4219108.622016-12-29
51020842.582016-11-30

Exercício 1: Uso de um CTE para obter dados totalizados

Exercício: Obtenha o ID do projeto, o valor mínimo e o total de doações para projetos que receberam doações acima do valor mínimo.

Solução:

WITH project_revenue AS (
  SELECT
    project_id,
    SUM(amount) AS sum_amount
  FROM donation
  GROUP BY project_id
)
SELECT project.id, minimal_amount, sum_amount
FROM project_revenue
INNER JOIN project ON
project.id = project_revenue.project_id
WHERE sum_amount >= minimal_amount;

Explicação: Para resolver esse exercício, usamos um CTE chamado project_revenue que totaliza as doações de cada projeto. Esse CTE tem duas colunas: id e sum_amount, sendo a última a soma calculada das doações para cada project_id. Após a definição do CTE, usamos uma instrução SELECT que une a tabela project com o CTE. Para cada projeto que recebeu doações, o CTE retorna seus endereços id, minimal_amount e o total de doações (sum_amount) que recebeu.

O CTE project_revenue inclui somente linhas de projetos que receberam doações porque obtém dados da tabela de doações. O SELECT abaixo da definição do CTE também mostra apenas os projetos que receberam doações por causa do INNER JOIN entre o CTE e a tabela project tabela. E a condição WHERE garante que obteremos apenas os projetos para os quais o valor doado excede o valor mínimo.

Se você precisa praticar o agrupamento de dados em SQL, confira este conjunto de 10 exercícios GROUP BY. Experimente estes exercícios práticos de SQL avançado para acelerar seu caminho rumo à proficiência em SQL.

Exercício 2: Uso de vários CTEs na mesma instrução

Exercício: Selecione os apoiadores que doaram mais de US$ 200 no total ou que doaram pelo menos duas vezes.

Solução:

WITH rich AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING SUM(amount) > 200
),
frequent AS (
  SELECT
	s.id,
	first_name,
	last_name
  FROM supporter s
  JOIN donation d
	ON d.supporter_id = s.id
  GROUP BY s.id, first_name, last_name
  HAVING COUNT(d.id) > 1
)
SELECT
  id,
  first_name,
  last_name
FROM rich
UNION ALL
SELECT
  id,
  first_name,
  last_name
FROM frequent;

Explicação: Este exercício nos pede para combinar dois resultados diferentes que devemos obter recuperando informações dos campos donation e supporter os apoiadores cujo total de doações excede US$ 200 e os apoiadores que fizeram mais de uma doação. Essa situação é ideal para ser resolvida escrevendo dois CTEs, um para obter o primeiro conjunto de dados (rich) e o outro para obter o segundo conjunto (frequent).

A sintaxe SQL permite escrever vários CTEs no mesmo comando, o que aproveitamos para resolver esse exercício. Ao colocar cada subconsulta em um CTE diferente, o SELECT final é simplesmente a união de dois SELECTs simples - cada um dos quais busca dados diretamente de um CTE.

CTEs aninhados

Embora nenhum RDBMS permita a criação de um CTE dentro de outro CTE, o que eles permitem são CTEs aninhados; isso ocorre quando um CTE se refere a um CTE definido anteriormente como se fosse uma tabela. Dessa forma, os CTEs criam diferentes níveis de abstração. Isso faz com que a consulta final seja simples e concisa SELECT.

Para nossos exercícios de CTE aninhado, usaremos um esquema de tabela de uma empresa de vendas porta a porta. Esse esquema tem três tabelas: salesman, daily_sales, e city. A tabela salesman inclui id, first_name, last_name e city_id para cada vendedor. Estas são algumas de suas linhas:

idfirst_namelast_namecity_id
1FrederickWebster1
2CaseySantiago2
3CindyFields3
4TimothyPratt4
5SusanRose5

A tabela daily_sales A tabela representa as vendas totalizadas por dia e por vendedor. Ela tem as colunas day, salesman_id, items_sold, amount_earned, distance e customers. As duas últimas colunas mostram a distância percorrida e o número de clientes atendidos por cada vendedor a cada dia. Essas são algumas de suas linhas:

daysalesman_iditems_soldamount_earneddistancecustomers
2017-01-15101673.203020
2017-01-152162288.4913613
2017-01-153171232.7812914
2017-01-15421496.882512
2017-01-155221384.1334018

Por fim, temos a tabela city tabela que armazena os endereços id, name, country e region de cada cidade:

idnamecountryregion
1ChicagoUSAAmericas
2New YorkUSAAmericas
3Mexico CityMexicoAmericas
4Rio de JaneiroBrasilAmericas
5ParisFranceEurope

Exercício 3: Uso de CTEs aninhados para avançar de forma incremental em direção a um resultado

Exercício: Obter a data, o ID da cidade, o nome da cidade e o valor total de todas as vendas diárias - agrupadas por data e cidade - que excedem a média de vendas diárias de todas as cidades e de todos os dias.

Solução:

WITH earnings_per_day_city AS (
  SELECT
	ds.day,
	c.id,
	c.name,
	SUM(amount_earned) AS total_earnings
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY ds.day, c.id, c.name
),
overall_day_city_avg AS (
  SELECT
	AVG(total_earnings) AS avg_earnings
  FROM earnings_per_day_city
)
SELECT
  day,
  id,
  name,
  total_earnings
FROM earnings_per_day_city, overall_day_city_avg
WHERE total_earnings > avg_earnings;

Explicação: Os CTEs aninhados nos permitem dividir um problema em partes e abordar a solução gradualmente. Neste exercício, primeiro precisamos totalizar as vendas por dia e por cidade. Fazemos isso com o primeiro CTE, earnings_per_day_city.

Em seguida, precisamos obter uma média de todas as vendas totalizadas por dia e por cidade. Fazemos isso com o CTE overall_day_city_avg, que, por sua vez, usa os resultados totalizados anteriormente pelo CTE earnings_per_day_city. Esse segundo CTE retornará uma única linha com a média de vendas para todos os dias e todas as cidades.

No SELECT final, simplesmente pegamos os dados dos dois CTEs (não há necessidade de combiná-los com um JOIN, já que o earnings_per_day_city retorna uma única linha) e adicionamos a condição WHERE de que o total de vendas do dia e da cidade deve ser maior que a média geral.

Exercício 4: Combinação de consultas e subconsultas aninhadas

Exercício: Obtenha a data em que o número médio de clientes atendidos por região foi o menor de todos, exibindo essa média junto com a data.

Solução:

WITH sum_region AS (
  SELECT
	day,
	region,
	SUM(customers) AS sum_customers
  FROM salesman s
  JOIN daily_sales ds
	ON s.id = ds.salesman_id
  JOIN city c
	ON s.city_id = c.id
  GROUP BY day, region
),
avg_region AS (
  SELECT
	day,
	AVG(sum_customers) AS avg_region_customers
  FROM sum_region
  GROUP BY day
)
SELECT
  day,
  avg_region_customers
FROM avg_region
WHERE avg_region_customers = (SELECT
  MIN(avg_region_customers)
  FROM avg_region);

Explicação: Para resolver essa consulta, usamos a mesma abordagem sucessiva para a solução como no exercício anterior, criando primeiro um CTE para obter o número total de clientes atendidos por dia e por região e, em seguida, outro CTE baseado no anterior para obter as médias diárias de clientes atendidos por dia. Em seguida, na página final SELECT, usamos uma subconsulta para obter o mínimo do número médio de clientes por dia e usá-lo na cláusula WHERE como valor de comparação, de modo que a consulta retorne o dia que corresponde a esse mínimo.

Se você quisesse dividir ainda mais o SELECT final, poderia adicionar um terceiro CTE em vez de uma subconsulta. Dessa forma, o SELECT final fica ainda mais simples. Aqui está o novo (terceiro) CTE e o SELECT externo:

min_avg_region as (
  SELECT
    MIN(avg_region_customers) as min_avg_region_customers
  FROM avg_region
  )
SELECT
  day,
  avg_region_customers
FROM avg_region, min_avg_region
WHERE avg_region_customers = min_avg_region_customers;

Exercício 5: Uso de CTEs aninhados para calcular estatísticas complexas

Exercício: Para cada cidade, calcule a distância total média percorrida por cada vendedor. Calcule também uma média geral de todas as médias das cidades.

Solução:

WITH distance_salesman_city AS (
  SELECT
	city_id,
	salesman_id,
	SUM(distance) AS sum_distance
  FROM daily_sales d
  JOIN salesman s
	ON d.salesman_id = s.id
  GROUP BY city_id, salesman_id
),
city_average AS (
  SELECT
	city_id,
	AVG(sum_distance) AS city_avg
  FROM distance_salesman_city
  GROUP BY city_id
)
SELECT AVG(city_avg)
FROM city_average;

Explicação: Os benefícios dos CTEs aninhados são perceptíveis quando você precisa executar cálculos estatísticos compostos de várias etapas sucessivas.

Nesse caso, o resultado final é uma média total das médias por cidade das somas das distâncias para cada cidade e vendedor. Isso é uma média de médias de somas. Os CTEs nos permitem fazer uma aproximação gradual do resultado, de forma análoga a como um cientista de dados faria com fórmulas estatísticas.

Exercício 6: Uso de CTEs aninhados para comparar conjuntos de elementos

Exercício: Compare a média de vendas de todos os vendedores nos EUA com a média de vendas de todos os vendedores no resto do mundo.

Solução:

WITH cities_categorized AS (
  SELECT
    id AS city_id,
    CASE WHEN country = 'USA' THEN country ELSE 'Rest of the World' END AS category
  FROM city
),
sales_category AS (
  SELECT
    category,
    salesman_id,
    SUM(items_sold) total_sales
  FROM daily_sales ds
  JOIN salesman s
    ON s.id = ds.salesman_id
  JOIN cities_categorized
    ON cities_categorized.city_id = s.city_id
  GROUP BY category, salesman_id
)

SELECT
  category,
  AVG(total_sales)
FROM sales_category
GROUP BY category;

Explicação: No primeiro CTE (cities_categorized), separamos as cidades em dois grupos: cidades nos EUA e cidades no resto do mundo.

No segundo CTE, combinamos as informações do CTE cities_categorized com daily_sales e vendedor para obter os totais de vendas agrupados pelas duas categorias de cidades e por vendedor.

No último SELECT, simplesmente agrupamos por categoria de cidade e obtemos a média de vendas para cada uma das duas categorias (cidades dos EUA e cidades do resto do mundo).

CTEs recursivos

Na programação SQL, os CTEs recursivos são expressões de tabela comuns que fazem referência a si mesmas. Como as funções recursivas usadas em outras linguagens de programação, os CTEs recursivos baseiam-se no princípio de pegar os dados resultantes de uma execução anterior, adicioná-los ou modificá-los e passar os resultados para a próxima execução. Continuamos a fazer o mesmo até que uma condição de parada seja atendida, que é quando o resultado final é obtido.

Os CTEs recursivos devem ter a palavra RECURSIVE após a palavra WITH. A melhor maneira de entender a operação dos CTEs recursivos é usar um exemplo simples, como no exercício a seguir.

Exercício 7: Uso de CTEs recursivos para gerar sequências

Exercício: Use a recursão para listar todos os números inteiros de 1 a 10.

Solução:

WITH RECURSIVE ten_numbers(prev_number) AS (
  SELECT 1
  UNION ALL
  SELECT ten_numbers.prev_number + 1
  FROM ten_numbers
  WHERE prev_number < 10
)

SELECT *
FROM ten_numbers;

Explicação: Essa consulta adota a notação de CTEs recursivos do PostgreSQL, que tem quatro partes:

  1. Membro de ancoragem: É aqui que definimos o ponto de partida da recursão. Essa parte da consulta deve ser capaz de ser resolvida de forma autônoma, sem a necessidade de usar resultados de iterações anteriores do mesmo CTE.
  2. Membro recursivo: Essa parte é repetida quantas vezes forem necessárias, usando os resultados da iteração anterior como base.
  3. Condição de término: Essa condição é avaliada após cada repetição do membro recursivo; quando ela for atendida, o loop recursivo será encerrado. Se essa condição não estivesse presente ou sempre produzisse um resultado verdadeiro, a recursão continuaria indefinidamente.
  4. Invocação: A principal diferença entre essa consulta SELECT e outras consultas principais de CTE é que essa SELECT atua como um gatilho para o ciclo de execuções recursivas.

Neste exercício, o membro âncora simplesmente retorna uma linha com o número 1. O membro recursivo pega a(s) linha(s) da execução anterior e anexa (por meio da cláusula UNION) uma nova linha com o valor anterior incrementado em 1. A condição de término declara que a consulta continuará iterando até que o valor obtido seja igual a 10.

Exercício 8: Uso de um CTE recursivo para percorrer uma estrutura de dados hierárquica (árvore)

Para este exercício, usaremos a tabela employee que tem as colunas id, first_name, last_name e superior_id. Suas linhas contêm os seguintes dados:

idfirst_namelast_namesuperior_id
1MadelineRaynull
2VioletGreen1
3AltonVasquez1
4GeoffreyDelgado1
5AllenGarcia2
6MarianDaniels2


Exercício: Mostre todos os dados de cada funcionário, além de um texto mostrando o caminho na hierarquia da organização que separa cada funcionário do chefe principal (identificado pelo valor literal "Chefe").

Solução:

WITH RECURSIVE hierarchy AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    'Boss' AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employee.id,
    employee.first_name,
    employee.last_name,
    employee.superior_id,
    hierarchy.path || '->' || employee.last_name
  FROM employee JOIN hierarchy
  ON employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Explicação: Os dados na tabela employee representam uma estrutura hierárquica ou de árvore, em que cada linha tem uma coluna que a relaciona ao seu superior (outra linha na mesma tabela). A linha que corresponde ao chefe da empresa (o nó raiz da árvore) é a que tem um valor nulo na coluna superior_id. Portanto, esse é o nosso membro âncora para construir esse CTE recursivo. O caminho desse membro âncora simplesmente carrega o valor literal 'Boss'.

Em seguida, o membro de consulta recursiva une a iteração anterior da hierarquia com employeedefinindo a condição de que os chefes dos funcionários da iteração atual (superior_id) já estejam na hierarquia. Isso significa que, para cada iteração, adicionamos outra camada à hierarquia. Essa camada é formada pelos subordinados dos funcionários que foram adicionados na iteração anterior. Portanto, a condição de união é employee.superior_id = hierarchy.id.

O caminho de cada funcionário é montado concatenando o caminho de seu chefe (hierarchy.path, que mostra todo o caminho até 'Boss') com o sobrenome do funcionário da iteração atual, unido por uma string que representa uma seta (hierarchy.path || '->' || employee.last_name).

Exercício 9: Mostrar o caminho de uma árvore a partir do nó raiz

Exercício: Exiba uma lista contendo o nome e o sobrenome de cada funcionário (incluindo o chefe), juntamente com um texto (o campo path) mostrando o caminho da árvore entre cada funcionário e o chefe. No caso do chefe, a coluna de caminho deve mostrar o endereço last_name do chefe.

Solução:

WITH RECURSIVE hierarchy AS (
  SELECT
	first_name,
	last_name,
	CAST(last_name AS text) AS path
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.first_name,
	employee.last_name,
	hierarchy.path || '->' || employee.last_name AS path
  FROM employee, hierarchy
  WHERE employee.superior_id = hierarchy.id
)

SELECT *
FROM hierarchy;

Explicação: A solução deste exercício é muito semelhante à do exercício anterior, com a única exceção de que o valor do caminho para o nó raiz não é um valor literal do tipo TEXT; é um valor last_name na tabela employee na tabela. Isso nos obriga a realizar uma conversão de dados para evitar a ocorrência de um erro quando executamos essa consulta.

Como o CTE faz uma UNION entre os dados retornados pelo componente âncora e os dados retornados pelo componente recursivo, é imperativo que ambos os conjuntos de resultados tenham o mesmo número de colunas e que os tipos de dados das colunas correspondam.

A coluna last_name da tabela employee (denominada path no membro âncora do CTE) é do tipo VARCHAR, enquanto a concatenação hierarchy.path || '->' || employee.last_name (denominada path no membro recursivo) gera automaticamente uma coluna TEXT. Para que UNION não cause um erro de incompatibilidade de tipos, é necessário CAST(last_name AS text) no membro âncora. Dessa forma, as colunas path de ambas as partes do CTE serão TEXT.

Exercício 10: Uso de várias colunas recursivas

Exercício: Liste todos os dados de cada funcionário, além do caminho na hierarquia até chegar ao chefe superior. Inclua uma coluna chamada distância que mostre o número de pessoas na hierarquia desde o chefe superior até o funcionário. Para o chefe, a distância é 0; para seus subordinados, é 1; para os subordinados de seus subordinados, é 2, e assim por diante.

Solução:

WITH RECURSIVE hierarchy AS (
  SELECT
	id,
	first_name,
	last_name,
	superior_id,
	'Boss' AS path,
	0 AS distance
  FROM employee
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
	employee.id,
	employee.first_name,
	employee.last_name,
	employee.superior_id,
	hierarchy.path || '->' || employee.last_name,
	hierarchy.distance + 1
 FROM employee, hierarchy
 WHERE employee.superior_id = hierarchy.id
)
SELECT *
FROM hierarchy;

Explicação: Como há duas colunas recursivas no CTE, é necessário indicar um valor inicial para cada uma delas no membro de ancoragem. Nesse caso, a coluna de caminho tem o valor inicial "Chefe" (como no exercício 8) e a coluna de distância tem o valor 0. Em seguida, no membro recursivo, o valor da distância resulta da adição de 1 à distância da iteração anterior.

Exercício 11: Uso de CTEs recursivos para percorrer estruturas de dados do tipo rede

Para este exercício, usamos duas tabelas: uma tabela destination (que consiste nas colunas id e name) e uma tabela ticket (que consiste nas colunas city_from, city_to e cost ). A tabela destination A tabela contém os IDs e os nomes de um grupo de cidades, enquanto a tabela ticket tabela indica o custo das passagens entre os pares de cidades na destination tabela (onde essas conexões existem).

Este é um exemplo de dados de ambas as tabelas (primeiro destination, depois ticket):

idname
1Warsaw
2Berlin
3Bucharest
4Prague
city_fromcity_tocost
12350
1380
14220
23410
24230
32160
34110
42140
4375

Exercício: Encontre a rota mais barata para viajar entre todas as cidades da tabela destination a partir de Varsóvia. A consulta deve mostrar as seguintes colunas:

  • path - Os nomes das cidades no caminho, separados por '->'.
  • last_id - O ID da cidade final dessa viagem.
  • total_cost - A soma dos custos das passagens.
  • count_places - O número de cidades visitadas. Esse número deve ser igual ao número total de cidades em destinationou seja, 4.

Solução:

WITH RECURSIVE travel(path, last_id,
	total_cost, count_places) AS (
  SELECT
	CAST(name as text),
	Id,
	0,
	1
  FROM destination
  WHERE name = 'Warsaw'
  UNION ALL
  SELECT
	travel.path || '->' || c2.name,
	c2.id,
	travel.total_cost + t.cost,
	travel.count_places + 1
  FROM travel
  JOIN ticket t
	ON travel.last_id = t.city_from
  JOIN destination c1
	ON c1.id = t.city_from
  JOIN destination c2
	ON c2.id = t.city_to
  WHERE position(c2.name IN travel.path) = 0
)
SELECT *
FROM travel
WHERE count_places = 4
ORDER BY total_cost ASC;

Explicação: O método para resolver este exercício é semelhante ao do exercício anterior. Nesse caso, entretanto, não há ordem direta para a relação entre os elementos na mesma tabela. Em vez disso, as relações entre os elementos da tabela destination são expressas na tabela ticket tabela, ligando cada par de cidades conectadas.

A primeira linha dos resultados da consulta acima mostra a rota de menor custo. Isso é possível porque o SELECT externo da consulta classifica os resultados em ordem crescente por total_cost. Por sua vez, o SELECT externo garante que o número de cidades percorridas seja 4, definindo a condição de que count_places seja igual a 4.

O membro recursivo garante que cada iteração adicione uma nova cidade à rota, definindo a condição de que o nome da cidade ainda não esteja no caminho (position(c2.name IN travel.path) = 0).

Como o ponto de partida da viagem é a cidade de Varsóvia, o membro âncora do CTE recursivo é a linha da tabela destination onde o nome é igual a "Warsaw". Observe que convertemos a coluna name para o tipo de dados TEXT (como no Exercício 9) para que o tipo de dados corresponda à coluna correspondente no membro CTE recursivo.

Precisa de mais exercícios de expressão de tabela comum?

Se você já fez os exercícios de SQL deste artigo, agora sabe como usar expressões de tabela comuns. E sabe como os CTEs podem ser úteis. Esses exercícios foram retirados de nosso Consultas Recursivas e há ainda mais exercícios como esses no curso completo.

Para se tornar realmente bom em análise de dados com SQL, pense em juntar-se a LearnSQL.com.br. Recomendamos que você dê uma olhada no Ilimitado Vitalício SQL Package. Ele lhe dá acesso vitalício a todos os cursos de SQL que oferecemos atualmente, além de todos os novos cursos que adicionarmos posteriormente. Dessa forma, você pode continuar aprendendo novas habilidades para sempre. Associe-se hoje mesmo ao site LearnSQL.com.br e comece a construir um grande futuro na análise de dados!