Voltar para a lista de artigos Artigos
34 minutos de leitura

Top 27 Entrevistas SQL Avançadas Perguntas com Respostas

Índice

Onde um profissional SQL pode encontrar um guia abrangente para perguntas avançadas de entrevista SQL? A resposta mais curta é: aqui! Selecionamos as 27 perguntas SQL mais importantes e as respondemos para você.

A preparação para uma entrevista SQL não é fácil, especialmente se seu trabalho requer o conhecimento de SQL avançado. Este artigo contém as 27 perguntas SQL avançadas mais comuns para entrevistas e fornece respostas detalhadas e recursos para leitura posterior.

Vamos analisar estes quatro conceitos principais e mais alguns outros:

  • JOINs
  • GROUP BY, WHERE, e HAVING
  • CTEs (Expressões comuns da tabela) e consultas recursivas
  • Funções de Janela (Window Functions) em SQL

A melhor maneira de atualizar seus conhecimentos avançados de SQL é tomando nosso SQL Avançado pista. Possui mais de 300 exercícios práticos para funções de janela, Expressões de Mesa Comum, funções recursivas e muito mais.

Vamos atacar estas questões frontalmente, sem mais delongas!

1) O que é um JOIN em SQL?

JOIN é um comando SQL que permite a combinação de duas ou mais tabelas. Isto é feito através de uma coluna comum (ou seja, uma coluna que tem os mesmos valores em ambas as tabelas), que permite utilizar dados de duas ou mais tabelas ao mesmo tempo. Unir tabelas em SQL é essencial devido à natureza dos bancos de dados relacionais: os dados são atomizados em tabelas, com cada tabela contendo apenas uma parte dos dados disponíveis no banco de dados.

Vamos usar duas tabelas para mostrar como isto funciona. A primeira tabela é football_players.

idfirst_namelast_namenational_team_idgames_played
1GianfrancoZola135
2Virgilvan Dijk253
3MarcusRashford351
4KylianMbappé566
5PhilFoden322
6Frenkiede Jong222
7MarioBalotelli136
8ErlingHaaland623

A segunda é national_team.

idcountry
1Italy
2Netherlands
3England
4Croatia

Aqui está uma consulta que une duas mesas:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Ela seleciona colunas de ambas as tabelas. Para juntá-las, primeiramente fazemos referência a uma tabela na cláusula FROM. Em seguida, JOIN, e depois disso vem a segunda tabela. Usamos a cláusula ON para especificar a condição com a qual as tabelas serão unidas: o national_team_id na football_players deve ser igual à coluna id na national_team mesa.

A saída da consulta é:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

INNER JOIN é uma das várias junções distintas em SQL. Sua característica é que só retorna dados das tabelas unidas onde a condição de união é verdadeira. Aqui estão mais detalhes sobre como funciona o SQL INNER JOIN.

Qual é a diferença entre INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN?

Existem diferentes tipos de junção em SQL. As junções mais utilizadas são INNER JOIN, LEFT JOIN, RIGHT JOIN, e FULL JOIN. LEFT JOIN, RIGHT JOIN, e FULL JOIN são as chamadas junções externas. JOIN (aka INNER JOIN) é uma união interna. Neste caso, 'interior' significa que ela retorna apenas as linhas de ambas as tabelas que satisfazem a condição de união; as uniões externas retornam todas as linhas de uma tabela, mais as linhas correspondentes na(s) outra(s) tabela(s). A exceção é FULL JOIN, que retorna todas as linhas de ambas as tabelas.

Aqui está o resultado do INNER JOIN do exemplo anterior. Vamos repetir aqui. Dessa forma, será mais fácil ver a diferença entre as diferentes uniões.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36

LEFT JOIN retorna todos os dados da tabela da esquerda (ou seja, a primeira tabela, que é listada antes\ à esquerda da palavra-chave JOIN ) e somente as linhas correspondentes da tabela da direita (a segunda tabela, listada depois\ à direita da palavra-chave JOIN ). Se não houver dados correspondentes na tabela da direita, os valores em falta são mostrados como NULLs. Aqui está a mesma consulta com LEFT JOIN substituindo por INNER JOIN:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
LEFT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

A tabela da esquerda aqui é football_playerse o direito é national_team. Como era de se esperar, a produção é diferente:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23

Todos os jogadores de futebol da mesa da esquerda estão aqui. Entretanto, Kylian Mbappe e Erling Haaland não têm um país correspondente na mesa da direita, portanto, há NULLs na coluna country para esses jogadores. Estas fileiras não estavam presentes no resultado INNER JOIN. Elas foram adicionadas pelo LEFT JOIN.

O RIGHT JOIN faz o oposto: retorna todos os dados da tabela da direita e somente os dados correspondentes da tabela da esquerda. Quando não há dados correspondentes na tabela da esquerda, os valores em falta são mostrados como NULLs.

Aqui está o código:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
RIGHT JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Tudo permanece o mesmo, exceto que estamos usando RIGHT JOIN ao invés de LEFT JOIN. Esta é a saída:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
NULLNULLNULLNULLCroatiaNULL

Agora temos todas as seleções nacionais e seus jogadores. Mas você pode ver que há um país (Croácia) que não tem jogadores na tabela da esquerda. As colunas de jogadores para a Croácia são preenchidas com NULLs.

FULL JOIN O resultado é a saída de todos os dados de todas as mesas juntas. Novamente, se não houver dados correspondentes na tabela correspondente, os valores em falta aparecerão como NULL.

Mais uma vez, mudamos o tipo de junção na consulta:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
FULL JOIN national_team nt
ON fp.national_team_id = nt.id
ORDER BY fp.id;

Ele retornará todos os dados de ambas as tabelas. Qualquer dado não coincidente é substituído por NULLs. Todos os jogadores estão no resultado, mesmo que não tenham um país correspondente na outra tabela. Todos os países estão no resultado, mesmo que não tenham jogadores na tabela de football_player mesa. O resultado FULL JOIN é a união de LEFT JOIN e RIGHT JOIN:

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3England51
4KylianMbappé5NULL66
5PhilFoden3England22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Italy36
8ErlingHaaland6NULL23
NULLNULLNULLNULLCroatiaNULL

Você pode encontrar mais informações no artigo sobre diferentes tipos de uniões. Você também pode ver nossa Cláusulas JOIN em SQL Cheat Sheet para um rápido refrescamento.

3) O que é um CROSS JOIN?

Um CROSS JOIN é outro tipo de junção disponível em SQL. Ele retorna um produto cartesiano. Isto significa que CROSS JOIN retornará cada linha da primeira tabela combinada com cada linha da segunda tabela.

Ele não é usado com muita freqüência. Mas se você se sentir tentado a usá-lo, pense duas vezes. Retornar todas as combinações de fileiras pode levar algum tempo - se a consulta terminar de todo!

Como exemplo, vamos usar as tabelas como nas duas perguntas anteriores. Para escrever a pergunta, use a palavra-chave CROSS JOIN. Como este é um tipo de join que retorna todas as combinações de linhas de todas as tabelas, não há nenhuma cláusula ON. Dê uma olhada:

SELECT 
  fp.id,
  first_name,
  last_name,
  national_team_id,
  country,
  games_played
FROM football_players fp
CROSS JOIN national_team nt;

Aqui está a saída. Todos os jogadores no football_players tabela estão listados com todos os países no national_team mesa.

idfirst_namelast_namenational_team_idcountrygames_played
1GianfrancoZola1Italy35
2Virgilvan Dijk2Italy53
3MarcusRashford3Italy51
4KylianMbappé5Italy66
5PhilFoden3Italy22
6Frenkiede Jong2Italy22
7MarioBalotelli1Italy36
8ErlingHaaland6Italy23
1GianfrancoZola1Netherlands35
2Virgilvan Dijk2Netherlands53
3MarcusRashford3Netherlands51
4KylianMbappé5Netherlands66
5PhilFoden3Netherlands22
6Frenkiede Jong2Netherlands22
7MarioBalotelli1Netherlands36
8ErlingHaaland6Netherlands23
1GianfrancoZola1England35
2Virgilvan Dijk2England53
3MarcusRashford3England51
4KylianMbappé5England66
5PhilFoden3England22
6Frenkiede Jong2England22
7MarioBalotelli1England36
8ErlingHaaland6England23
1GianfrancoZola1Croatia35
2Virgilvan Dijk2Croatia53
3MarcusRashford3Croatia51
4KylianMbappé5Croatia66
5PhilFoden3Croatia22
6Frenkiede Jong2Croatia22
7MarioBalotelli1Croatia36
8ErlingHaaland6Croatia23

Você pode saber mais sobre o CROSS JOIN neste guia ilustrado para o SQL CROSS JOIN.

4) O que é um Auto-ajuste em SQL?

Como você provavelmente suspeita, uma auto-junção ocorre quando a mesa é unida a si mesma. É importante notar que não é um comando distinto em SQL: qualquer tipo JOIN pode ser usado para unir uma mesa consigo mesma.

A união é feita como qualquer outro JOIN, mas desta vez você referenciará a mesma tabela em ambos os lados da palavra-chave JOIN. A auto-união é especialmente útil quando uma tabela tem uma chave estrangeira referenciando sua chave primária. Isto permite que você consulte dados hierárquicos, tais como árvores genealógicas ou a hierarquia organizacional de uma empresa. Também é útil quando se deseja encontrar pares de valores. No exemplo abaixo, estamos procurando jogadores da mesma equipe nacional:

SELECT 
  fp1.id,
  fp1.first_name,
  fp1.last_name,
  fp1.national_team_id,
  fp2.id AS id_2,
  fp2.first_name AS first_name_2,
  fp2.last_name AS last_name_2,
  fp2.national_team_id as national_team_id_2
FROM football_players fp1 
JOIN football_players fp2
ON fp1.id <> fp2.id
AND fp1.national_team_id = fp2.national_team_id;

Auto-junta significa em vez de duas tabelas, você está especificando a mesma tabela duas vezes: uma na cláusula FROM e outra após a cláusula JOIN. Como você está usando a mesma tabela duas vezes, você deve usar pseudônimos para as tabelas. Cada ocorrência da tabela deve receber um apelido distinto (fp1, fp2 em nossa consulta) para que fique claro a qual ocorrência da tabela estamos nos referindo.

Estamos juntando jogadores da mesma equipe nacional (seus valores national_team_id são iguais). Entretanto, não queremos listar um jogador consigo mesmo, portanto excluímos o caso quando fp1.id e fp2.id forem iguais.

O resultado da consulta é o seguinte:

idfirst_namelast_namenational_team_idid_2first_name_2last_name_2national_team_id_2
1GianfrancoZola17MarioBalotelli1
2Virgilvan Dijk26Frenkiede Jong2
3MarcusRashford35PhilFoden3
5PhilFoden33MarcusRashford3
6Frenkiede Jong22Virgilvan Dijk2
7MarioBalotelli11GianfrancoZola1

Você pode usar as colunas national_team_id de ambas as tabelas para confirmar que, na verdade, Gianfranco Zola e Mario Balotelli jogaram pela mesma equipe.

Você pode saber mais sobre isto em nosso artigo sobre exemplos de auto-ajuda.

5. junte-se a duas mesas usando uma junção de duas colunas

Você recebe duas tabelas. A primeira é employeeque possui os seguintes dados:

idfirst_namelast_name
1SteveBergman
2SteveJohnson
3SteveKing

A segunda tabela é customerque possui os seguintes dados:

idfirst_namelast_name
1AnnColeman
2SteveBergman
3SteveYoung
4DonnaWinter
5SteveKing

Sua tarefa é devolver todos os funcionários que também são clientes da empresa. Infelizmente, você não pode usar a coluna id porque é a identificação do funcionário em uma tabela e a identificação do cliente em outra. Em outras palavras, não há uma única coluna em uma tabela que faça referência à outra.

A solução é juntar as tabelas no primeiro e último nome, ou seja, usar uma coluna de duas colunas JOIN.

O código abaixo unirá primeiro as tabelas sobre o primeiro nome. Depois disso, a palavra-chave AND estabelece a segunda condição de junção, que é o sobrenome. Dessa forma, você obterá dados de ambas as tabelas onde a combinação do primeiro nome/último nome é a mesma. Se usássemos apenas uma destas colunas, poderíamos ter obtido os dados errados porque funcionários e clientes podem ter o mesmo primeiro nome, mas sobrenomes diferentes (ou vice versa). Aqui está a pergunta:

SELECT 
  e.first_name, 
  e.last_name
FROM employee e
JOIN customer c
ON e.first_name = c.first_name
AND e.last_name = c.last_name;

Aqui está o código de saída..:

first_namelast_name
SteveBergman
SteveKing

O resultado mostra que Steve Bergman e Steve King são tanto funcionários da empresa quanto clientes.

6. junte-se a duas mesas utilizando uma non-Equi JOIN

Até o momento, temos usado eqüi-juntas: junções onde há um sinal de igualdade na condição ON. Por outro lado, a adesão non-equi é uma adesão que tem uma condição de non-equidade na cláusula ON.

Desta vez, temos dados sobre usuários móveis e seu uso de dados. A primeira tabela é mobile_userque mostra os usuários móveis e seu limite mensal de dados móveis em MB:

idfirst_namelast_namemobile_data_limit
1MichaelWatson5,000
2NicoleGomez10,000
3SamStone8,000

A segunda tabela é data_usageque mostra o uso real dos dados mensais do usuário em MB:

idmobile_user_iddata_usedperiod
114,9872022_10
226,8752022_10
3312,5472022_10
415,0372022_11
5211,1112022_11
634,8972022_11

A tarefa é encontrar todos os dados onde o uso real estava acima do limite mensal. Queremos ver o primeiro e último nome do usuário, o limite mensal, os dados reais utilizados e o período de tempo.

A solução é usar a junção non-equi, como mostrado abaixo:

SELECT 
  first_name,
  last_name,
  mobile_data_limit,
  data_used,
  period
FROM mobile_user mu
JOIN data_usage du
ON mu.id = du.mobile_user_id
AND mobile_data_limit < data_used;

A consulta seleciona todas as informações necessárias a partir de duas tabelas. As tabelas são unidas usando um INNER JOIN. Primeiro unimos as tabelas onde o ID do usuário é o mesmo. Em seguida, adicionamos a segunda condição após a palavra-chave AND. Aqui temos uma condição de non-equidade que nos dará dados onde o limite está abaixo do uso mensal.

Você pode ver o resultado abaixo:

first_namelast_namemobile_data_limitdata_usedperiod
SamStone8,00012,5472022_10
MichaelWatson5,0005,0372022_11
NicoleGomez10,00011,1112022_11

Se você estiver interessado, aqui estão mais alguns exemplos de non-equi junte.

7) O que a DISTINCT faz?

DISTINCTO objetivo da DISTINCT, em geral, é remover valores duplicados. Ou, dito de outra forma, para mostrar valores únicos na saída da sua consulta.

Imagine que você está trabalhando com isto loans tabela que mostra as identificações dos empréstimos e suas durações em meses.

loan_idloan_duration
10011260
10020560
10020848
100333120
10035748
100398120

Empréstimos diferentes podem ter a mesma duração, então você quer extrair a lista de possíveis durações de empréstimo. Você pode fazer isso usando DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration; 

A saída mostra que há empréstimos com durações de 48, 60, e 120 meses:

loan_duration
48
60
120

DISTINCT pode ser usado em SELECT com uma coluna para mostrar apenas os valores únicos daquela coluna, como no exemplo acima. Se for usado em SELECT mas com várias colunas, então a saída mostrará as combinações únicas de todas estas colunas.

Você também pode usar DISTINCT com funções agregadas. Se você fizer isso, sua consulta eliminará resultados de agregação duplicados.

Você pode ver exemplos destes usos em nosso artigo falando sobre o papel do DISTINCT no SQL.

8) O que o GROUP BY SQL faz?

GROUP BY é uma cláusula SQL utilizada para organizar os dados em grupos com base em um valor ou valores comuns. Ela é mais comumente usada com funções agregadas; esta combinação retornará dados agregados para cada grupo. Entretanto, é importante saber que o uso de funções agregadas dentro da cláusula GROUP BY não é permitido.

A sintaxe geral GROUP BY é:

SELECT 
  column_1,
  column_2,
  …,
FROM table_name
WHERE …
GROUP BY column_1, column_2
HAVING …
ORDER BY column_1, column_2;

Suponha que haja a seguinte tabela salaries:

idfirst_namelast_namesalarydepartment
1NicholasPoirot4,798.44IT
2SamanthaWolf5,419.24IT
3StewartJohnsons5,419.24IT
4JackieBiden8,474.54Sales
5MarkHamilton10,574.84Sales
6MarianaCosta9,747.54Sales
7PaulStewart3,498.12Accounting
8MaryRutte4,187.23Accounting
9ThomasSchwarz3,748.55Accounting

Usaremos GROUP BY e AVG() para encontrar o salário médio por departamento:

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department;

Queremos ver os departamentos, por isso selecionamos esta coluna. Para calcular o salário médio, aplicamos a função AVG() à coluna salary.

Todas as colunas listadas em GROUP BY definem os grupos de dados. Em nosso exemplo, os grupos são definidos pela coluna do departamento: calculamos o salário médio para cada departamento.

Nosso agrupamento e agregação de dados se assemelha a isto:

departmentaverage_salary
Accounting3,811.30
Sales9,598.97
IT5,212.31

O salário médio em Contabilidade é de 3.811,30. O salário médio nos outros dois departamentos é de 9.598,97 e 5.212,31, respectivamente.

Ao escrever uma consulta, GROUP BY sempre tem que vir depois de WHERE, mas antes da cláusula HAVING. Você pode saber mais sobre isso neste artigo sobre GROUP BY em SQL.

9. como filtrar GROUP BY Groups?

Uma vez obtidos os grupos que você especificou em GROUP BY, às vezes você vai querer filtrá-los. A dica para fazer isso está na sintaxe da pergunta anterior. A cláusula que lhe permite filtrar grupos é HAVING.

Após os critérios de filtragem serem especificados em HAVING, a consulta retornará todos os dados que satisfazem os critérios. Todos os outros dados serão filtrados.

Veja como funciona nos dados da pergunta anterior se tivéssemos que mostrar apenas departamentos com um salário médio abaixo de 5.500 dólares.

SELECT 
  department,
  AVG(salary) AS average_salary
FROM salaries
GROUP BY department
HAVING AVG(salary) < 5500;

O código é muito parecido com o da pergunta anterior. A diferença é a cláusula HAVING. Utilizamo-la para filtrar os resultados e mostrar apenas os departamentos com salários abaixo de 5.500 dólares.

Aqui está o que o código retorna:

departmentaverage_salary
Accounting3,811.30
IT5,212.31

O departamento que falta na produção é Vendas porque seu salário médio é de 9.598,97.

Você pode encontrar exemplos práticos adicionais desta cláusula neste artigo explicando o HAVING em SQL.

10 Qual é a diferença entre WHERE e HAVING?

Se você sabe as respostas às duas perguntas anteriores, você provavelmente sabe a resposta a esta pergunta.

A principal diferença é que WHERE é usado para filtrar os dados antes de serem agrupados. Sua posição na declaração SELECT mostra o seguinte: ela vem antes de GROUP BY. Devido a sua finalidade, nenhuma função agregada é permitida em WHERE.

HAVINGpelo contrário, é utilizada para filtrar dados após o agrupamento; é por isso que é utilizada após GROUP BY. Além disso, HAVING permite condições que incluem funções agregadas.

A melhor maneira de aprender a distinção é ler este artigo sobre ONDE vs. HAVING em SQL.

11. o que a seguinte tentativa de filtrar NULLs retornará?

Você receberá com freqüência este tipo de pergunta em sua entrevista SQL avançada: você receberá um código e terá que descrever o retorno da consulta. Enquanto escreve e lê o código SQL anda de mãos dadas, ainda se sente diferente quando você tem que analisar o código que outra pessoa escreveu.

Você tem dados na tabela contributors:

idfirst_namelast_namestart_datetermination_date
1ValeriaBogdanov2022-10-11NULL
2NicholasBertolucci2022-04-072022-11-11
3MathildeBauman2022-05-252022-10-01
4TrevorTrucks2022-01-28NULL
5MariaSzabo2022-03-15NULL

O que este código retornará?

SELECT 
  first_name,
  last_name,
  start_date,
  termination_date
FROM contributors
WHERE termination_date != '2022-10-01';

Se você responder que ele retornará todas as linhas exceto ID = 3, você está errado! Esta é uma espécie de pergunta com rasteira. Ao ler a condição WHERE, você poderia lê-la como: devolver todos os dados onde a data de terminação é diferente de 2022-10-01. Ao olhar para a tabela, você pensaria que são todas as linhas, exceto uma.

E é, mas não para SQL! Como você pode ver, há três filas com valores NULL. Para SQL, NULL não é igual a um valor; é um valor que não tem valor. Portanto, quando você configura a condição em ONDE assim, estará excluindo todas as datas que não são iguais aos valores de 2022-10-01 e NULL.

Aqui está a saída como prova:

first_namelast_namestart_datetermination_date
NicholasBertolucci2022-04-072022-11-11

Você pode saber mais sobre este e outros operadores de comparação utilizados com o NULL.

12. escreva uma pergunta que encontre o número de canções por artista. Use LEFT JOIN e COUNT().

Suponha que lhe sejam dadas duas tabelas: artist e song.

Aqui está o artist dados:

idartist_name
1Prince
2Jimi Hendrix
3Santana
4Otis Redding
5Lou Rawls

Abaixo estão os song dados:

idartist_idsong_title
11Purple Rain
22Purple Haze
33Europa
41Cream
51Bambi
61Why You Wanna Treat Me So Bad?
72Spanish Castle Magic
83Taboo
93Incident at Neshabur
103Flor D' Luna

Você precisa usar LEFT JOIN e COUNT() para encontrar todos os artistas, suas identificações e o número de suas músicas no banco de dados.

Você pode ser tentado a sugerir esta solução:

SELECT 
  a.id,
  artist_name,
  COUNT(*) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Vamos dar uma olhada na saída:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding1
5Lou Rawls1

A saída mostra todos os artistas; tudo bem. Entretanto, o número de músicas para Otis Redding e Lou Rawls é um, o que é errado! Dê uma olhada na tabela songe você verá que não há identificações de artistas iguais a 4 ou 5.

O que deu errado? Ao utilizar COUNT(*) com LEFT JOIN, a função agregada contará todos os valores não correspondentes (NULLs). É por isso que o resultado mostrou uma canção cada para Otis Redding e Lou Rawls, embora eles não tenham nenhuma canção na tabela.

COUNT(*) é usada para contar todas as fileiras. Para dar uma resposta correta, deve-se usar COUNT(song_title) em seu lugar.

SELECT 
  a.id,
  artist_name,
  COUNT(song_title) AS number_of_songs
FROM artist a
LEFT JOIN song s
ON a.id = s.artist_id
GROUP BY a.id, artist_name
ORDER BY a.id;

Este código lhe dará a saída correta:

idartist_namenumber_of_songs
1Prince4
2Jimi Hendrix2
3Santana4
4Otis Redding0
5Lou Rawls0

O número de músicas do Príncipe, Jimi Hendrix e Santana permaneceu o mesmo que na saída anterior. Entretanto, o número de canções dos outros dois artistas é agora zero, e essa é a contagem correta.

13 Qual é a diferença entre JOIN e UNION?

JOIN é uma cláusula SQL utilizada para unir duas ou mais tabelas. Ela permite o uso de dados de todas as tabelas unidas. Em outras palavras, colunas de todas as tabelas são mostradas uma ao lado da outra, o que significa que os dados são empilhados horizontalmente.

UNION é um operador conjunto usado para combinar os resultados de duas ou mais declarações SELECT. Os dados são empilhados verticalmente. Um dos requisitos ao usar UNION é que deve haver um número igual de colunas em todas as declarações do SELECT sindicalizadas. Além disso, todas as colunas selecionadas têm que ser do mesmo tipo de dados.

14 Qual é a diferença entre UNION e UNION ALL?

O que eles têm em comum é que ambos são operadores definidos. Além disso, ambos os operadores são usados para o mesmo propósito: fundir dados de duas ou mais declarações SELECT.

Os requisitos relativos ao número de colunas e seu tipo de dados também são os mesmos.

Agora, a diferença é que a UNION retorna apenas registros únicos. Por outro lado, UNION ALL retorna todos os registros, o que inclui duplicatas.

Normalmente, o UNION ALL é mais rápido porque não classifica o resultado para remover as duplicatas. A regra geral é usar o UNION ALL por padrão. Use UNION somente se você precisar de resultados únicos ou se tiver certeza absoluta de que sua consulta não retornará dados duplicados.

Você pode saber mais sobre sua sintaxe e uso neste artigo sobre as diferenças entre UNION e UNION ALL.

15 O que é uma Subquery em SQL?

Uma subconsulta é uma consulta escrita dentro de outra consulta SQL. A consulta 'outra' é chamada de consulta principal, enquanto uma subconsulta às vezes é também chamada de consulta aninhada.

As subconsultas podem ser usadas nas instruções SELECT, INSERT, UPDATE, e DELETE. Elas também podem ser usadas em cláusulas como FROM ou WHERE, que é o uso mais comum.

Aqui está um exemplo. A tabela é productse armazena informações sobre nomes, quantidades e categorias de produtos:

idproduct_namequantityproduct_category
1Apple MacBook Air (2020) MGN63N/A Space Gray319Laptop
2Fairphone 4 128GB Green 5G208Mobile phone
3Apple iMac 24" (2021) 16GB/512GB Apple M1 with 8 core GPU Silver157Desktop
4HP 17-cp0971nd487Laptop
5Huawei P30 Pro - 128GB - Blue148Mobile phone
6Lenovo Legion T5 - AMD Ryzen 9 - 32 GB - 2TB HDD+SSD - Windows 10 Home PC514Desktop
7Toshiba Dynabook Satellite Pro E10-S-101 Notebook207Laptop
8Samsung Galaxy S23 5G - 256GB - Phantom Black56Mobile phone
9Intel Compleet PC | Intel Core i7-10700459Desktop

Usaremos uma subquisição e mostraremos a quantidade total por categoria de produto, mas somente para os produtos individuais cuja quantidade está acima da quantidade média para todos os produtos.

Aqui está a solução:

SELECT 
  product_category,
  SUM(quantity) AS product_quantity
FROM products
WHERE quantity > (SELECT AVG(quantity)
			FROM products)
GROUP BY product_category;

A consulta seleciona a categoria do produto e soma a quantidade usando a função agregada SUM(). Há uma condição em WHERE que diz que somente os produtos individuais cuja quantidade esteja acima da média serão incluídos na soma. Usamos a subconsulta e a função AVG() para obter esta média.

A consulta retorna duas filas:

product_categoryproduct_quantity
Laptop806
Desktop973

Uma categoria está faltando porque não satisfaz os critérios de filtragem - telefones celulares.

Há diferentes tipos de subconsultas, como escalar, múltiplas linhas e consultas correlatas. Você pode saber mais sobre eles em nosso artigo sobre tipos de subconsultas.

16. escreva uma consulta para devolver os vendedores e seus dados de vendas mensais acima da média de suas vendas pessoais. Use uma subconsulta relacionada à Cor.

Uma subconsulta correlata é um tipo de subconsulta que utiliza valores da consulta externa. É verificado uma vez para cada linha que a consulta externa retorna, o que pode retardar o desempenho.

No entanto, a pergunta insiste em usá-la, portanto, vamos ver os dados.

A primeira tabela é salesperson:

idfirst_namelast_name
1NinaLee
2CarolinaGreen
3MickJohnson

A outra tabela é sales:

idsalesperson_idmonthly_salesperiod
111,200.472021_10
225,487.222021_10
33700.472021_10
4115,747.542021_11
5216,700.872021_11
5314,322.872021_11
619,745.552021_12
729,600.972021_12
836,749.582021_12

Sua tarefa é usar uma subconsulta correlata e devolver o nome completo do vendedor, suas vendas mensais e os períodos em que suas vendas estão acima de sua média pessoal.

Aqui está a solução:

SELECT 
  first_name,
  last_name,
  monthly_sales,
  period
FROM salesperson sp
JOIN sales s
ON sp.id = s.salesperson_id
WHERE monthly_sales > 
   (SELECT AVG(monthly_sales)
    FROM sales
    WHERE salesperson_id = sp.id);

A consulta seleciona todas as colunas necessárias. Estes dados são de ambas as tabelas, por isso nos unimos a elas.

Agora vem a parte crucial. Para filtrar os dados, usamos a cláusula WHERE. A condição diz que a consulta deve retornar todos os dados onde as vendas mensais são mais altas do que a média de vendas de cada vendedor. Como calculamos essas vendas médias individuais? Usando a função AVG() na subconsulta que escrevemos na cláusula WHERE.

Aqui está a saída:

first_namelast_namemonthly_salesperiod
NinaLee15,747.542021_11
CarolinaGreen16,700.872021_11
MickJohnson14,322.872021_11
NinaLee9,745.552021_12

Há mais alguns exemplos neste artigo sobre subconsultas correlatas.

17. O que é Funções de Janela (Window Functions) em SQL em SQL?

As funções da janela SQL recebem seu nome pelo fato de serem aplicadas a uma janela de dados. Esta janela é simplesmente um conjunto de linhas relacionadas com a linha atual.

As funções de janela são iniciadas pela cláusula OVER(). Outra cláusula importante é PARTITION BY, que define partições de dados dentro de uma estrutura de janela. Quando esta cláusula é omitida, a partição é a tabela de resultados completa. Quando PARTITION BY é usado, você pode definir uma ou mais colunas pelas quais os dados serão particionados. Você pode olhar para ela como GROUP BY para funções de janela.

Outra cláusula importante é ORDER BY. Ela ordena os dados dentro da janela. No contexto das funções de janela, esta cláusula dá instruções sobre a ordem na qual a função será executada.

Para saber mais, consulte este artigo sobre funções de janela.

18 Qual é a diferença entre Funções de Janela (Window Functions) em SQL e GROUP BY?

A única semelhança que eles compartilham é que tanto GROUP BY quanto as funções de janela podem ser - e muito freqüentemente são - usadas com as funções agregadas, e ambas trabalham em um conjunto de linhas.

Entretanto, quando se usa GROUP BY, a saída é mostrada como grupos, e não se pode ver as linhas individuais que formam o grupo.

As funções de janela não têm tais problemas. Uma de suas características é que elas não colapsam as linhas individuais ao mostrar os dados agregados. Isto significa que é possível mostrar os dados agregados e não agregados simultaneamente.

As funções de janela são usadas para mais do que a agregação de dados, como você verá na pergunta a seguir. Mas se você quiser saber mais sobre o tópico atual, temos um artigo que explica as funções de janela vs. GROUP BY.

19. O que Funções de Janela (Window Functions) em SQL Você Sabe?

As funções de janela SQL podem ser geralmente divididas em quatro categorias:

  • Funções de Ranking
  • Funções de distribuição
  • Funções analíticas
  • Funções agregadas

As funções de classificação são:

  • ROW_NUMBER() - Retorna um número único para cada linha dentro de uma partição; os valores empatados têm números de linhas diferentes.
  • RANK() - Classifica os dados dentro de uma partição; os valores empatados têm a mesma classificação, e há uma lacuna após os empates (por exemplo, 1, 2, 3, 3, 5).
  • DENSE_RANK() - Classifica os dados dentro de uma partição; os valores empatados têm a mesma classificação e não há uma lacuna na classificação (por exemplo, 1, 2, 3, 3, 4).

As funções de distribuição são:

  • PERCENT_RANK() - Retorna a classificação relativa dentro de uma partição.
  • CUME_DIST() - Retorna a distribuição cumulativa dentro de uma partição.

As funções analíticas são: Retorna a distribuição relativa dentro de uma partição:

  • LEAD() - Permite acessar valores de uma linha subseqüente em relação à linha atual.
  • LAG() - Permite acessar os valores de uma linha anterior em relação à linha atual.
  • NTILE() - Divide as linhas dentro de uma partição em grupos aproximadamente iguais.
  • FIRST_VALUE() - Permite acessar os valores da primeira linha dentro de uma partição.
  • LAST_VALUE() - Permite acessar os valores da última linha dentro de uma partição.
  • NTH_VALUE() - Permite acessar a n-ésima fileira dentro de uma partição.

Finalmente, as funções agregadas são:

  • AVG() - Retorna um valor médio para as linhas dentro de uma partição.
  • COUNT() - Retorna o número de valores nas linhas de uma partição.
  • MAX() - Retorna o valor máximo para as linhas em uma partição.
  • MIN() - Retorna o valor mínimo para as linhas em uma partição.
  • SUM() - Devolve o valor da soma das linhas em uma partição.

Nossa Funções de Janela (Window Functions) em SQL Cheat Sheet lhe fornecerá mais informações sobre todas estas funções.

20. Como você cria um ranking em SQL?

A maneira mais fácil de classificar os dados em SQL é usar uma das três funções da janela de classificação:

  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()

Você recebe este conjunto de dados chamado album_sales com os seguintes dados:

idartistalbumcopies_sold
1EaglesHotel California42,000,000
2Led ZeppelinLed Zeppelin IV37,000,000
3Shania TwainCome On Over40,000,000
4Fleetwood MacRumours40,000,000
5AC/DCBack in Black50,000,000
6Bee GeesSaturday Night Fever40,000,000
7Michael JacksonThriller70,000,000
8Pink FloydThe Dark Side of the Moon45,000,000
9Whitney HoustonThe Bodyguard45,000,000
10EaglesTheir Greatest Hits (1971-1975)44,000,000

Estas são as vendas dos dez álbuns mais vendidos da história. Como você pode ver, os álbuns não estão classificados. Isso é o que faremos aqui: classificá-los do melhor para o pior, usando funções de janela.

Se você usar ROW_NUMBER(), a consulta será parecida com esta:

SELECT 
  ROW_NUMBER() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

A sintaxe é simples. Primeiro, você escolhe a função de janela. Em seguida, você usa a cláusula obrigatória OVER() que sinaliza que é uma função de janela. Em ORDER BY, você ordena os dados de forma decrescente. Isto agora significa que os números das linhas serão atribuídos de acordo com as cópias vendidas da alta para a baixa.

É claro, liste todas as outras colunas necessárias e faça referência à tabela para obter a mesma saída:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
4Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
8Fleetwood MacRumours40,000,000
9Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,000,000

Como você pode ver, os álbuns são classificados de um a dez. Dois álbuns venderam 45 milhões de cópias. No entanto, eles são classificados de forma diferente (terceiro e quarto) de acordo com critérios aleatórios. O mesmo acontece com três álbuns que venderam 40 milhões de cópias.

Se você usar RANK(), a sintaxe é a mesma, exceto que você usa uma função de janela diferente:

SELECT 
  RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

No entanto, a saída é diferente:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
5EaglesTheir Greatest Hits (1971-1975)44,000,000
6EaglesHotel California42,000,000
7Shania TwainCome On Over40,000,000
7Fleetwood MacRumours40,000,000
7Bee GeesSaturday Night Fever40,000,000
10Led ZeppelinLed Zeppelin IV37,000,000

Você pode ver que os álbuns empatados são classificados como terceiros (duas vezes). O próximo álbum não empatado é classificado como quinto. O mesmo acontece com os álbuns classificados em sétimo lugar.

Vamos ver o que acontece se usarmos DENSE_RANK():

SELECT 
  DENSE_RANK() OVER (ORDER BY copies_sold DESC) AS rank,
  artist,
  album,
  copies_sold
FROM album_sales;

Aqui está o resultado:

rankartistalbumcopies_sold
1Michael JacksonThriller70,000,000
2AC/DCBack in Black50,000,000
3Whitney HoustonThe Bodyguard45,000,000
3Pink FloydThe Dark Side of the Moon45,000,000
4EaglesTheir Greatest Hits (1971-1975)44,000,000
5EaglesHotel California42,000,000
6Shania TwainCome On Over40,000,000
6Fleetwood MacRumours40,000,000
6Bee GeesSaturday Night Fever40,000,000
7Led ZeppelinLed Zeppelin IV37,000,000

Os primeiros álbuns empatados são classificados como terceiros, o que é o mesmo que no resultado anterior. Mas a diferença é que a próxima classificação sem vínculos é a quarta - o que significa que a classificação não é ignorada.

Os outros três álbuns empatados são agora classificados como sexto, e não sétimo como antes. Além disso, a posição mais alta é a sétima, e não a décima.

Como você pode ver, cada método retorna resultados diferentes. Você deve usar o que melhor se adequa aos seus dados e ao que você deseja alcançar com a classificação. Para saber mais, leia o artigo sobre linhas de ranking em SQL.

21 Qual é a diferença entre RANK() e DENSE_RANK()?

Já abordamos a diferença na pergunta anterior. Você a viu em um exemplo prático, e agora vamos formulá-la para responder a esta pergunta.

RANK() Atribui a mesma classificação a linhas com os mesmos valores. Quando chega à seguinte fileira não amarrada, ela salta a classificação pelo número de fileiras empatadas.

DENSE_RANK() também dá a mesma classificação aos valores empatados. Entretanto, a classificação não é ignorada quando alcança a seguinte linha não amarrada. Em outras palavras, DENSE_RANK() classifica os dados seqüencialmente.

Mais detalhes são explicados neste artigo sobre as diferenças RANK() e DENSE_RANK().

22. encontrar as linhas Top n em SQL usando uma função de janela e um CTE.

Esta é uma questão comum e pode ser resolvida de várias maneiras. Usaremos a função de janela em um CTE para retornar o resultado desejado.

Os dados disponíveis são armazenados na função salary mesa:

idfirst_namelast_namesalarydepartment
1TimThompson10,524.74Sales
2MartinaHrabal7,895.14Accounting
3SusanTruman15,478.69Sales
4CiroConte8,794.41Accounting
5JorgeDe Lucia7,489.15Sales
6CarmenLopez10,479.15Accounting
7CatherineMolnar8,794.89Sales
8RichardBuchanan12,487.69Accounting
9MarkWong9,784.19Sales
10SilviaKarelias9,748.64Accounting

A tarefa aqui é devolver os três funcionários mais bem pagos de cada departamento, juntamente com seu salário e departamento.

A abordagem é a seguinte:

WITH ranking AS (
  SELECT 
    first_name,
    last_name,
    salary,
    department,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
  FROM salary
)

SELECT *
FROM ranking
WHERE salary_rank <= 3
ORDER BY department, salary_rank;

A primeira parte do código é uma Expressão de Tabela Comum, ou CTE. Ela é iniciada usando a palavra-chave WITH. O CTE é nomeado ranking. Após a palavra-chave AS, escrevemos a definição do CTE como uma declaração SELECT entre parênteses.

Depois de selecionar todas as colunas necessárias vem a classificação, usamos a função DENSE_RANK(). Você pode usar qualquer outra função de janela de ranking, se desejar.

A sintaxe é familiar. Para obter os rankings por departamento, precisamos dividir os dados por essa coluna. Além disso, queremos classificar os salários de alto para baixo. Em outras palavras, os dados na partição precisam ser organizados por salário em ordem decrescente.

A segunda declaração SELECT (ou seja, a consulta externa) seleciona todas as colunas do CTE e estabelece a condição na cláusula WHERE para filtrar somente os três salários mais altos pelo departamento. Finalmente, a saída é ordenada por departamento e por ordem de salário.

O resultado é o seguinte:

first_namelast_namesalarydepartmentsalary_rank
RichardBuchanan12,487.69Accounting1
CarmenLopez10,479.15Accounting2
SilviaKarelias9,748.64Accounting3
SusanTruman15,478.69Sales1
TimThompson10,524.74Sales2
MarkWong9,784.19Sales3

23. calcular a diferença entre duas fileiras (Delta) usando Funções de Janela (Window Functions) em SQL

Este problema é resolvido da forma mais elegante usando a função de janela LAG(). Lembre-se, esta é uma função que acessa o valor da linha anterior.

Os dados do exemplo podem ser encontrados na tabela revenue:

idactual_revenueperiod
18,748,441.222022_07
210,487,444.592022_08
37,481,457.152022_09
47,497,441.892022_10
58,697,415.362022_11
612,497,441.562022_12

Você precisa mostrar a receita real, período de tempo e diferença mensal (delta) entre o mês real e o mês anterior.

Veja como fazer isso.

SELECT 
  actual_revenue,
  actual_revenue - LAG(actual_revenue) OVER (ORDER BY period ASC) AS monthly_revenue_change,	 
 period
FROM revenue
ORDER BY period;

Um delta é calculado pela subtração do mês anterior do mês real. Isso é exatamente o que esta consulta faz! Para obter a receita do mês anterior, a função LAG() vem a calhar. A coluna actual_revenue é o argumento da função, já que queremos acessar os dados da receita da linha anterior. Como em toda função de janela, há uma cláusula OVER(). Nela, ordenamos os dados por período de forma ascendente porque é lógico calcular o delta cronologicamente.

Esta é a saída da consulta:

actual_revenuemonthly_revenue_changeperiod
8,748,441.22NULL2022_07
10,487,444.591,739,003.372022_08
7,481,457.15-3,005,987.442022_09
7,497,441.8915,984.742022_10
8,697,415.361,199,973.472022_11
12,497,441.563,800,026.202022_12

A primeira mostra a mudança de receita como NULL. Isto é esperado porque não há um mês anterior para deduzir. Em 2022_08, houve um aumento da receita de 1.739.003,37 = receita mensal real - a receita do mês anterior = 10.487.444,59 - 8.748.441,22. A mesma lógica se aplica a todos os outros resultados.

Você pode encontrar exemplos similares no artigo sobre o cálculo da diferença entre duas linhas em SQL.

24. Use Funções de Janela (Window Functions) em SQL para calcular um total em execução

Um total em execução ou cumulativo é uma soma de uma seqüência numérica. O total em execução é atualizado cada vez que um novo valor é adicionado à seqüência. Pense na receita mensal: a receita total do mês atual incluirá a soma da receita do mês atual e de todos os meses anteriores.

A função de janela que é perfeita para calcular um total em execução (soma acumulada) é SUM().

Vamos mostrar a abordagem sobre os mesmos dados que na pergunta anterior. O objetivo é calcular a receita acumulada para todos os meses disponíveis em 2022.

Aqui está a solução:

SELECT 
  actual_revenue,
  SUM(actual_revenue) OVER (ORDER BY period ASC) AS cumulative_revenue,
  period
FROM revenue;

A soma cumulativa é a soma da receita real do mês e a soma de toda a receita dos meses anteriores. A função da janela SUM() aplica esta lógica. O argumento da função é a receita real, porque é isso que estamos somando. Para que ela totalize toda a receita anterior e a receita atual, ordenar os dados de forma ascendente por período. Novamente, é lógico calcular um total cumulativo do primeiro ao último mês.

Isto é o que o código retorna:

actual_revenuecumulative_revenueperiod
8,748,441.228,748,441.222022_07
10,487,444.5919,235,885.812022_08
7,481,457.1526,717,342.962022_09
7,497,441.8934,214,784.852022_10
8,697,415.3642,912,200.212022_11
12,497,441.5655,409,641.772022_12

A receita acumulada na primeira linha é a mesma que a receita real. Para a segunda linha, o acumulado é 19.235.885,81 = 8.748.441,22 + 10.487.444,59. Em setembro, o cumulativo é 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

A mesma lógica se aplica ao restante da tabela.

Você pode aprender mais sobre o total em execução e como calculá-lo aqui.

25. encontrar uma média móvel usando Funções de Janela (Window Functions) em SQL

Uma média móvel é usada quando se analisa uma série. Você pode encontrá-la sob outros nomes, tais como média móvel, média móvel ou média em execução. É uma média do valor atual e do número definido de valores anteriores. Por exemplo, uma média móvel de 7 dias é a média do dia atual e dos seis dias anteriores.

Para mostrar como calculá-la, usaremos o eur_usd_rate mesa:

idexchange_ratedate
11.06662022-12-30
21.06832023-01-02
31.05452023-01-03
41.05992023-01-04
51.06012023-01-05
61.05002023-01-06
61.06962023-01-09
71.07232023-01-10
81.07472023-01-11
91.07722023-01-12
101.08142023-01-13

Vamos calcular a média móvel de 3 dias da seguinte forma:

SELECT 
  exchange_rate,
  AVG(exchange_rate) OVER (ORDER BY date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS eur_usd_moving_average,
  date
FROM eur_usd_rate;

Usamos a função da janela AVG() na coluna exchange_rate. Na cláusula OVER(), os dados são ordenados por data, em ordem ascendente. Agora vem a parte importante! Lembre-se, precisamos de uma média móvel de 3 dias envolvendo a linha atual e duas linhas anteriores. Especificamos isso na cláusula BETWEEN: dizemos à função para incluir duas linhas anteriores e a linha atual.

Vamos dar uma olhada no resultado:

exchange_rateeur_usd_moving_averagedate
1.06661.06662022-12-30
1.06831.06752023-01-02
1.05451.06312023-01-03
1.05991.06092023-01-04
1.06011.05822023-01-05
1.05001.05672023-01-06
1.06961.05992023-01-09
1.07231.06402023-01-10
1.07471.07222023-01-11
1.07721.07472023-01-12
1.08141.07782023-01-13

A média móvel da primeira data é a mesma que a taxa de câmbio porque: 1.0666/1 = 1.0666. Para 2023-01-02, é calculado assim: (1,0666 + 1,0683)/2 = 1,0675.

Em 2023-01-03, finalmente teremos três datas: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Esta lógica se aplica a todas as outras datas.

Mais exemplos podem ser encontrados neste artigo sobre o cálculo de médias móveis em SQL.

26 Qual é a diferença entre ROWS e RANGE?

Tanto ROWS quanto RANGE são cláusulas utilizadas para definir um quadro de janela. Elas limitam o intervalo de dados utilizados em uma função de janela dentro de uma partição.

A cláusula ROWS limita as linhas. É usada para especificar um número fixo de linhas que precedem e seguem a linha atual. O valor das linhas não é levado em conta.

A cláusula RANGE limita o intervalo de dados de forma lógica. Em outras palavras, ela limita os dados analisando os valores das linhas anterior e seguinte em relação à linha atual. Ela não leva em conta o número de linhas.

Como utilizá-las na prática? Leia nosso artigo sobre ROWS e RANGE para mais detalhes.

27 Use uma consulta recursiva para encontrar todos os funcionários sob um determinado gerente.

Uma consulta recursiva é um tipo especial de CTE que se refere a si mesmo até chegar ao final da recursividade. É ideal para consulta de dados gráficos ou estrutura hierárquica.

Um exemplo desta última é a estrutura organizacional da empresa, mostrada na seção company_organization mesa:

employee_idfirst_namelast_namemanager_id
5529JackSimmons5125
5238MariaPopovich5329
5329DanJacobsson5125
5009SimoneGudbois5329
5125AlbertKochNULL
5500JackieCarlin5529
5118SteveNicks5952
5012BonniePresley5952
5952HarryRaitt5529
5444SeanElsam5329

Esta tabela mostra todos os funcionários e a identificação de seu gerente direto.

A tarefa aqui é utilizar a recorrência e devolver todos os subordinados diretos e indiretos de Jack Simmons. Além disso, vamos acrescentar uma coluna que pode ser usada para distinguir os diferentes níveis organizacionais. Aqui está o código:

WITH RECURSIVE subordinates AS (
	SELECT
  employee_id,
	  first_name,
	  last_name,
	  manager_id,
	  0 AS level
	FROM company_organization
	WHERE employee_id= 5529

	UNION ALL

	SELECT
 	  co.employee_id, 
	  co.first_name,
	  co.last_name,
	  co.manager_id,
	  level + 1
	FROM company_organization co 
JOIN subordinates s 
ON co.manager_id = s.employee_id
)

SELECT
  s.employee_id,
  s.first_name AS employee_first_name,
  s.last_name AS employee_last_name,
  co.employee_id AS direct_manager_id,
  co.first_name AS direct_manager_first_name,
  co.last_name AS direct_manager_last_name,
  s.level
FROM subordinates s 
JOIN company_organization co 
ON s.manager_id = co.employee_id
ORDER BY level;

Iniciamos a recursividade usando WITH RECURSIVE. (Se você estiver trabalhando no MS SQL Server, use apenas WITH.)

O primeiro SELECT em um CTE é chamado de membro âncora. Nele, referenciamos o conjunto de dados e selecionamos todas as colunas necessárias. Além disso, criamos uma nova coluna com o valor zero e filtramos os dados na cláusula WHERE. Por que usar esta condição exata em WHERE? Porque o ID de funcionário de Jack Simmons é 5529, e queremos mostrar a ele e seus subordinados.

Depois vem o UNION ALL, que combina os resultados da consulta de âncora e da consulta recursiva, ou seja, a segunda declaração SELECT.

Queremos que a recursividade percorra todo o caminho através da estrutura organizacional. Na consulta recursiva, juntamos o CTE com o company_organization mesa. Listamos novamente todas as colunas necessárias a partir desta última tabela. Além disso, queremos acrescentar um nível organizacional a cada repetição.

Finalmente, chegamos à consulta que utiliza o CTE. Esta consulta serve para obter dados tanto do CTE quanto do company_organization mesa. Utilizamos o CTE para mostrar os dados dos funcionários. A outra tabela é usada para mostrar as informações do gerente direto.

Executando o código, você obterá este resultado:

employee_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel
5529JackSimmons5125AlbertKoch0
5952HarryRaitt5529JackSimmons1
5500JackieCarlin5529JackSimmons1
5012BonniePresley5952HarryRaitt2
5118SteveNicks5952HarryRaitt2

A tabela acima mostra que o gerente direto de Jack Simmons é Albert Koch. Diretamente sob Simmons, há Harry Raitt e Jackie Carlin. Os subordinados indiretos são Bonnie Presley e Steve Nicks. O gerente direto deles é Harry Raitt.

Algumas outras variações desta tarefa podem ser encontradas no artigo sobre CTEs recursivas.

Você pode aprender mais sobre funções de janela neste artigo dedicado a perguntas sobre funções de janela SQL.

Você está pronto para Ace SQL Job Interview Questions?

Escrever este guia não foi fácil. Mas compensa quando pensamos em facilitar sua entrevista de trabalho SQL avançada.

É claro, estas não são todas as perguntas que você poderia obter na entrevista. Entretanto, acreditamos que esta seleção lhe proporcionará uma base firme dos mais importantes conceitos avançados de SQL. Este guia também é suficientemente curto para que você possa analisá-lo rapidamente antes da entrevista e refrescar sua memória.

Para mais atualizadores sobre tópicos avançados de SQL, tente nosso Funções de Janela (Window Functions) em SQL curso ou SQL Avançado pista.