15 perguntas complicadas de entrevista sobre SQL para usuários experientes

As perguntas da entrevista de SQL para usuários experientes geralmente contêm algumas perguntas com truques. Os entrevistadores as usam para testar sua coragem, que normalmente é resultado de uma vasta experiência e de um alto nível de conhecimento de SQL.

Neste artigo, mostrarei 15 perguntas complicadas para entrevistas sobre SQL (principalmente codificação). Lembre-se de que essas perguntas não são necessariamente complexas. Na verdade, essa é a principal característica delas: parecem muito fáceis e diretas, mas é o entrevistador que está tentando enganar você. Porém, como usuário experiente de SQL, você deve ser capaz de reconhecer as armadilhas e evitá-las.

Vou lhe mostrar como fazer isso, mas não posso me preparar para a entrevista no seu lugar. Então, como você deve abordar a preparação para a entrevista?

Preparação para a entrevista como um usuário experiente de SQL

Fundamentos sólidos em tópicos básicos e intermediários de SQL são pré-requisitos para se considerar um usuário experiente de SQL. Se você não sabe onde está no espectro de conhecimento de SQL, temos uma novidade para você: nossa Avaliação de habilidades em SQL. Você pode fazer o teste e avaliar seu nível de SQL. É um recurso gratuito; você pode fazer um teste a cada 30 dias. No final do teste, você obtém uma pontuação geral do seu conhecimento de SQL. Há resultados detalhados para seis áreas de competência: Consultas SQL básicas, Cláusulas JOIN em SQL, Funções SQL padrão, Relatórios SQL básicos, Relatórios SQL intermediários e Relatórios SQL complexos.

Após a avaliação, você pode ir para a nossa trilha SQL Avançado para praticar mais. Ele consiste em três cursos interativos principais que abrangem detalhes de funções de janela, extensões GROUP BY e consultas recursivas. Os tópicos estão distribuídos em 395 desafios de codificação, portanto, você escreverá bastante código, o que tem se mostrado a maneira mais eficiente de aprender SQL. Após o curso, você estará em casa com tópicos avançados de SQL.

A trilha de aprendizado lhe dará conhecimento, sem dúvida. No entanto, os empregadores contam com usuários experientes para aproveitar o SQL na solução de problemas reais. Mas na vida, as coisas raramente são simples; os problemas reais tendem a não ser exemplos de livros didáticos de SQL adaptados para o aprendizado. Portanto, você deve ir além dos exemplos do curso. Você precisa trabalhar a flexibilidade e a criatividade, enxergando antecipadamente as possíveis armadilhas e evitando-as em seu código SQL. É isso que os entrevistadores procuram nos usuários experientes. Por isso, não basta se preparar para a entrevista com perguntas simples sobre SQL. Você também deve se aperfeiçoar nas perguntas complicadas, pois os entrevistadores gostam de usá-las para tentar pegá-lo desprevenido.

A seguir, apresentamos algumas das perguntas mais comuns e complicadas da entrevista sobre SQL para usuários experientes.

Pergunta 1: Selecionar freelancers e suas informações de tarefa

Escreva uma consulta que selecione todos os freelancers junto com suas informações de tarefa:

• Título da tarefa
• Tipo e subtipo de tarefa
• Data de vencimento

Inclua os freelancers que não têm nenhuma tarefa atribuída.

Conjunto de dados: O conjunto de dados é de uma empresa que emprega freelancers em determinadas tarefas. Ele consiste em três tabelas. A primeira tabela é freelancer. Você pode encontrar o script aqui.

id	first_name	last_name
1	Bob	Franklin
2	Dionne	Ravanelli
3	Marek	Lewandowski
4	Francois	Cousteau
5	Emma	Biesa

A segunda tabela é um dicionário de diferentes tipos de tarefas chamado task_category. Aqui está o script.

id	task_type	task_subtype
1	Blog article	SQL
2	Blog article	Python
3	Blog article	Career
4	Social media post	LinkedIn
5	Social media post	Other social media

A terceira tabela mostra os detalhes do trabalho atribuído que os freelancers estão fazendo para nossa empresa. A tabela tem o nome taske o script está aqui.

id	task_category_id	title	freelancer_id	date_assigned	due_date	completed_date
1	2	Working With Pandas in Python	5	2023-11-30	2023-12-15	2023-12-15
2	4	Promote SQL Avançado Learning Track	4	2023-12-18	2023-12-20	2023-12-20
3	1	Working With LEFT JOIN in SQL	1	2023-12-08	2024-03-01	NULL
4	3	What Does a Data Analyst Do?	2	2023-12-20	2024-02-01	2024-02-10
5	4	Promote Working With Pandas in Python	4	2024-01-15	2024-01-18	2024-01-18
6	2	Python Libraries You Should Know	1	2024-01-15	2024-02-15	2024-02-15
7	1	Using COUNT in SQL	2	2024-01-20	2024-02-15	2024-02-15
8	1	Filtering Data in SQL	5	2024-02-20	NULL	NULL

Resposta: Esta pergunta testa suas habilidades em unir três tabelas e escolher o tipo correto de união.

Aqui está a solução:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_id = tc.id;

Explicação: Para obter todas as informações necessárias, você precisa unir as três tabelas. Primeiro, junte as tabelas freelancer e task no ID do freelancer. Para adicionar a terceira tabela, você precisa escrever novamente a palavra-chave JOIN. Em seguida, informe na cláusula ON que você está unindo tabelas com o ID da categoria da tarefa.

O tipo de união que você usa deve ser JOIN. Isso se deve à possibilidade de haver alguns freelancers que ainda não têm nenhuma tarefa. Você precisa apenas daqueles que têm.

Resultado: Aqui está o resultado da consulta:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote SQL Avançado Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL

Pergunta 2: O que são OUTER JOINs e quando usá-los?

Resposta: Esta pergunta quer ver se você realmente entende como funcionam as junções externas e como elas são diferentes de outras junções.

OUTER JOINAs junções externas são uma das categorias distintas de junção no SQL, juntamente com INNER JOINs e CROSS JOINs.

As uniões a seguir pertencem à família OUTER JOIN:

• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN
• FULL (OUTER) JOIN

A principal característica de todas as OUTER JOINs é que elas unem tabelas de uma forma em que uma tabela é dominante, portanto, todos os seus dados serão exibidos. A segunda tabela é subordinada, de modo que a consulta mostrará somente as linhas correspondentes dessa tabela. Se houver linhas não correspondentes, elas aparecerão como NULL.

Portanto, OUTER JOINs deve ser usado quando você quiser mostrar as linhas não correspondentes, bem como as correspondentes dentro das tabelas.

Cada uma das junções externas acima funciona com base nesse princípio, mas veja como elas diferem:

• LEFT JOIN mostra todos os dados da primeira tabela (esquerda) e somente as linhas correspondentes da segunda tabela (direita). Se houver linhas não correspondentes, elas serão mostradas como NULL.

• RIGHT JOIN mostra todos os dados da segunda tabela (direita) e somente as linhas correspondentes da primeira tabela (esquerda). As linhas não correspondentes são mostradas como NULL.

• FULL JOIN combina LEFT JOIN e RIGHT JOIN. Ele mostra todos os dados de ambas as tabelas. Em outras palavras, ele mostrará todas as linhas - linhas correspondentes e não correspondentes da tabela da esquerda. Em seguida, ele adicionará todas as linhas da tabela da direita que não podem ser encontradas na tabela da esquerda. Onde houver dados não correspondentes, você verá NULLs.

Pergunta 3: Selecionar informações sobre freelancer e tarefa, parte 2

Escreva uma consulta que retorne:

• O nome e o sobrenome dos freelancers.
• Os títulos de suas tarefas atribuídas.
• Tipo e subtipo de tarefa.
• Datas de vencimento da tarefa.

Inclua todos os freelancers, mesmo aqueles que não têm nenhuma tarefa.

Conjunto de dados: O mesmo da Pergunta 1.

Resposta: Mais uma pergunta de entrevista sobre SQL para o usuário experiente. Aqui, você precisa mostrar que entende as relações entre as tabelas. Você precisa usar LEFT JOIN para unir as três tabelas. Você precisa usar LEFT JOIN como uma primeira união. Mas você precisa estar ciente de que o relacionamento entre as tabelas "força" você a usar LEFT JOIN novamente como uma segunda união.

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date,
	 tc.task_type,
	 tc.task_subtype
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
LEFT JOIN task_category tc
ON t.task_category_id = tc.id;

Explicação: A consulta é muito semelhante à da Questão 1. Portanto, sua primeira junção é LEFT JOIN, pois você precisa gerar todos os freelancers, não apenas aqueles com uma tarefa atribuída. Em outras palavras, a relação é tal que uma tarefa deve ter um freelancer atribuído, mas um freelancer não precisa ter uma tarefa atribuída.

No entanto, quando você junta a terceira tabela, precisa novamente de LEFT JOIN. Por que isso acontece? Porque uma tarefa precisa ter um tipo e um subtipo. Ao mesmo tempo, cada tipo de tarefa disponível não precisa estar entre as tarefas atribuídas. Se você usasse INNER JOIN aqui, isso "cancelaria" a primeira LEFT JOIN e distorceria seu resultado.

Saída: Aqui está a aparência de seu resultado:

first_name	last_name	title	due_date	task_type	task_subtype
Emma	Biesa	Working With Pandas in Python	2023-12-15	Blog article	Python
Francois	Cousteau	Promote SQL Avançado Learning Track	2023-12-20	Social media post	LinkedIn
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01	Blog article	SQL
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01	Blog article	Career
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18	Social media post	LinkedIn
Bob	Franklin	Python Libraries You Should Know	2024-02-15	Blog article	Python
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15	Blog article	SQL
Emma	Biesa	Filtering Data in SQL	NULL	Blog article	SQL
Marek	Lewandowski	NULL	NULL	NULL	NULL

Usar INNER JOIN como a segunda união removeria essa última linha, que mostra um freelancer sem uma tarefa atribuída. Se não há tarefa, também não há tipo de tarefa. E o site INNER JOIN não mostra linhas não correspondentes. É por isso que LEFT JOIN é necessário aqui.

Pergunta 4: Selecionar informações de freelancer para projetos com vencimento em 2024

Escreva uma consulta que selecione:

• Todos os freelancers
• Seus títulos de tarefas
• Datas de vencimento das tarefas

Inclua somente projetos com data de vencimento em 2024.

Conjunto de dados: O mesmo que na pergunta anterior.

Solução: A pergunta quer induzi-lo a escrever uma consulta que use a cláusula WHERE para filtrar os dados, conforme mostrado abaixo:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
WHERE t.due_date > '2023-12-31';

Mas essa não é a resposta correta. Para obter o resultado necessário, a condição de filtragem em WHERE deve ser movida para uma condição de união, como esta:

SELECT f.first_name,
	 f.last_name,
	 t.title,
	 t.due_date
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id AND t.due_date > '2023-12-31';

Explicação: Na primeira consulta, usar WHERE retornaria somente os dados das tarefas com data de vencimento em 2024. Isso excluiria todos os freelancers que não têm uma tarefa atribuída, mas também as tarefas que não têm - por vários motivos - uma data de vencimento.

Então, em vez disso, movemos a condição de filtragem para a cláusula ON. A primeira condição une as tabelas com o ID do freelancer. A segunda condição é adicionada usando a palavra-chave AND. Dessa forma, você inclui todos os freelancers, mas filtra os projetos que venceram em 2023.

Resultado: Aqui está o resultado correto:

first_name	last_name	title	due_date
Bob	Franklin	Working With LEFT JOIN in SQL	2024-03-01
Dionne	Ravanelli	What Does a Data Analyst Do?	2024-02-01
Francois	Cousteau	Promote Working With Pandas in Python	2024-01-18
Bob	Franklin	Python Libraries You Should Know	2024-02-15
Dionne	Ravanelli	Using COUNT in SQL	2024-02-15
Emma	Biesa	NULL	NULL
Marek	Lewandowski	NULL	NULL

Apesar de Emma Biesa ter um projeto intitulado 'Filtering Data in SQL', sua data de vencimento é NULL, portanto, o valor na coluna title também é NULL. Em outras palavras, o projeto de Emma Biesa não corresponde à condição de união.

Por outro lado, o resultado é o mesmo para Marek Lewandowski. Dessa vez, isso se deve ao fato de Marek não ter nenhum projeto atribuído.

Pergunta 5: Mostrar todos os funcionários e seus gerentes

Conjunto de dados: A pergunta fornece a você a tabela employees. Aqui está o script.

A tabela é uma lista de funcionários.

id	first_name	last_name	manager_id
1	John	Borisov	2
2	Linda	Johnson	8
3	Frank	Ranieri	NULL
4	Nina	Bowie	1
5	Tamara	Felipe	NULL
6	Simon	Fyodorov	8
7	Lana	Hopkins	NULL
8	Tom	Bonfa	1
9	Maria	Fox	1
10	Victor	Ivanchich	2

Solução: Como há apenas uma tabela, você precisa mostrar que sabe que uma tabela pode ser unida a ela mesma. Em outras palavras, resolva a questão aplicando uma união própria.

Isso é feito da seguinte maneira:

SELECT e.first_name AS employee_first_name, 
	 e.last_name AS employee_last_name, 
	 m.first_name AS manager_first_name,
	 m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.id;

Explicação: Self-join é simplesmente uma tabela que é unida a ela mesma. Basicamente, ao atribuir aliases diferentes a uma tabela, você faz com que o SQL pense que você uniu duas tabelas diferentes.

Nossa "primeira" tabela tem o alias e. Nós a usaremos para mostrar os nomes dos funcionários.

O alias da "segunda" tabela unida é me servirá para mostrar os nomes dos gerentes.

Nesse caso, você precisa uni-las usando LEFT JOIN porque a pergunta exige que você liste todos os funcionários. Isso também inclui os funcionários que não têm gerentes. Se você usasse INNER JOIN, obteria apenas os funcionários que têm um gerente.

A tabela é autounida com a condição de que o ID do gerente seja igual ao ID do funcionário. É assim que você obtém os nomes dos gerentes de cada funcionário.

Resultado: Aqui está a lista de funcionários e seus superiores:

employee_first_name	employee_last_name	manager_first_name	manager_last_name
John	Borisov	Linda	Johnson
Linda	Johnson	Tom	Bonfa
Frank	Ranieri	NULL	NULL
Nina	Bowie	John	Borisov
Tamara	Felipe	NULL	NULL
Simon	Fyodorov	Tom	Bonfa
Lana	Hopkins	NULL	NULL
Tom	Bonfa	John	Borisov
Maria	Fox	John	Borisov
Victor	Ivanchich	Linda	Johnson

NULLs como nomes de gerente significam que o respectivo funcionário não tem um superior.

Pergunta 6: Mostrar terapeutas e seus primeiro e segundo idiomas

Escreva uma consulta que retorne todos os terapeutas com seus primeiro e segundo idiomas.

Conjunto de dados: Esse conjunto de dados é de uma prática de psicoterapia coletiva destinada a expatriados. Vários terapeutas oferecem terapia, e cada um deles faz isso em dois idiomas.

A lista de idiomas está na tabela language. Aqui está o script.

id	language_name
1	English
2	Dutch
3	Russian
4	Polish
5	Croatian

A lista de terapeutas pode ser encontrada na tabela therapist. Aqui está o script.

id	first_name	last_name	first_language_id	second_language_id
1	Maya	Hoekstra	2	1
2	Lana	Mayakovski	3	1
3	Marija	Abramović	5	2
4	Jan	Nowak	4	1
5	Francis	Gordon	1	2

Solução: Uma das muitas perguntas de entrevista sobre SQL para usuários experientes, essa tarefa exige que você mostre suas habilidades em unir três tabelas. Entretanto, aqui uma tabela é unida duas vezes. Você precisa reconhecer isso, pois a tabela therapist faz referência à tabela language em duas colunas: first_language_id e second_language_id.

A solução deve ser semelhante a esta:

SELECT t.first_name,
	 t.last_name,
	 fl.language_name AS first_language_name,
	 sl.language_name AS second_language_name
FROM therapist t
JOIN language fl
ON t.first_language_id = fl.id
JOIN language sl
ON t.second_language_id = sl.id;

Explicação: Primeiro, juntamos a tabela therapist com a tabela languagesendo que a última recebe o alias fl (como em "primeiro idioma"). Nós o usaremos para mostrar o primeiro idioma do terapeuta, ou seja, seu idioma nativo. É por isso que a condição de união procura onde o ID do primeiro idioma é o mesmo que o ID do idioma. Isso fará com que o nome do primeiro idioma seja mostrado.

Na próxima etapa, unimos novamente a tabela language. Dessa vez, ela tem o alias sl para "segundo idioma". A união pega o ID do segundo idioma e o procura em language. É assim que obtemos o nome do segundo idioma.

Para mostrar o primeiro e o segundo idioma, selecionamos a coluna language_name - uma vez na fl 'table' e a segunda vez na sl 'table' - e damos nomes apropriados às colunas.

Saída: Aqui está o resultado:

first_name	last_name	first_language_name	second_language_name
Jan	Nowak	Polish	English
Lana	Mayakovski	Russian	English
Maya	Hoekstra	Dutch	English
Francis	Gordon	English	Dutch
Marija	Abramović	Croatian	Dutch

Pergunta 7: Mostrar o número de freelancers com tarefas atribuídas

Conjunto de dados: O conjunto de dados de freelancers usado nas Questões 1, 3 e 4.

Solução: Essa pergunta complicada de entrevista leva você a usar a função agregada COUNT(). Parece muito fácil, com uma consulta simples que usa apenas uma tabela. Porém, a pergunta quer que você seja apressado e escreva a seguinte consulta:

SELECT COUNT(freelancer_id) AS number_of_working_freelancers
FROM task;

No entanto, você precisa mostrar que é mais esperto do que isso e escrever uma consulta que use COUNT(DISTINCT freelancer_id) em vez de COUNT(freelancer_id).

SELECT COUNT(DISTINCT freelancer_id) AS number_of_working_freelancers
FROM task;

Explicação: Por que a primeira consulta está errada? Bem, o COUNT(freelancer_id) contará todas as instâncias do ID de um freelancer. Isso significa que ele também contará as duplicatas como outro freelancer. (Lembre-se de que cada freelancer pode ter várias tarefas).

Para evitar isso, basta adicionar DISTINCT nessa expressão. Isso eliminará as duplicatas, ou seja, cada freelancer será contado apenas uma vez.

Resultado: A primeira consulta retornará isso:

number_of_working_freelancers
8

Você sabe que isso está errado porque conhece seus dados. A tabela freelancer tem apenas cinco freelancers listados, portanto, não pode ser verdade que há mais freelancers trabalhando do que o número de freelancers.

Portanto, o resultado correto é o que está abaixo. Há quatro freelancers porque sabemos que um não está atribuído, ou seja, não está trabalhando.

number_of_working_freelancers
4

Pergunta 8: Mostrar o número de tarefas por tipo e subtipo de tarefa

Conjunto de dados: O mesmo que o anterior.

Solução: Aqui, você deve reconhecer que precisa usar uma função de agregação e agrupar a saída por duas colunas.

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype;

Explicação: Para obter o resultado, você precisa unir as tabelas task_category e task no ID da categoria da tarefa.

Em seguida, selecione o tipo e o subtipo de tarefa e use COUNT(*), que simplesmente contará o número de linhas, que é igual ao número de tarefas. Cada linha é uma tarefa.

Depois disso, use GROUP BY para agrupar os dados por tipo de tarefa. No entanto, a pergunta pede que você agregue dados também no nível do subtipo de tarefa, portanto, é necessário adicioná-lo em GROUP BY. Todas as colunas em GROUP BY devem ser separadas por vírgula.

Saída: O tipo de tarefa "Social media post" aparece apenas uma vez, pois não há outros subtipos nas tarefas ativas.

Por outro lado, o tipo de tarefa "Blog article" aparece três vezes, cada uma com um subtipo de tarefa diferente. A coluna number_of_tasks representa o número de tarefas por subtipo.

task_type	task_subtype	number_of_tasks
Social media post	LinkedIn	2
Blog article	SQL	3
Blog article	Python	2
Blog article	Career	1

Pergunta 9: Mostrar o número de tarefas ativas por tipo e subtipo de tarefa

Escreva uma consulta que mostre o número de tarefas ativas por tipo e subtipo de tarefa.

Inclua apenas as categorias com mais de duas tarefas.

Conjunto de dados: O mesmo que o anterior.

Solução: Essa pergunta comum de entrevista sobre SQL testará se você reconhece que precisa usar HAVING em vez de WHERE para filtrar a saída. Talvez você queira resolver a pergunta da seguinte forma:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
WHERE COUNT(*) > 2
GROUP BY task_type, task_subtype;

Isso está errado, então você precisa substituir WHERE por HAVING:

SELECT task_type,
	 task_subtype,
	 COUNT(*) AS number_of_tasks
FROM task_category tc
JOIN task t
ON tc.id = t.task_category_id
GROUP BY task_type, task_subtype
HAVING COUNT(*) > 2;

Explicação: Essa consulta é basicamente a mesma que a da pergunta anterior. O requisito adicional é mostrar somente os tipos e subtipos de tarefas com mais de duas tarefas ativas.

A primeira consulta não retornará nada, exceto um erro dizendo que as funções de agregação não podem ser usadas em WHERE. Isso ocorre, é claro, porque o site WHERE filtra os dados antes da agregação.

Portanto, primeiro você precisa agregar dados usando COUNT(*) para encontrar o número de tarefas ativas por tipo e subtipo. Somente depois disso você poderá procurar as categorias com mais de duas tarefas.

Em outras palavras, você deve usar HAVING, pois ele filtra os dados após a agregação. Você simplesmente usa a agregação da coluna number_of_tasks e estabelece uma condição de que a contagem deve ser maior que dois.

Saída:

task_type	task_subtype	number_of_tasks
Blog article	SQL	3

Pergunta 10: O que há de errado com esta consulta?

Conjunto de dados: O mesmo que o anterior.

Solução: A pergunta lhe dá uma consulta:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type
ORDER BY last_name;

Sua resposta deve ser que essa consulta não funcionará porque a coluna task_subtype não está listada na cláusula GROUP BY. A consulta corrigida deve ser semelhante a esta:

SELECT first_name,
	 last_name,
	 task_type,
	 task_subtype,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
JOIN task t
ON f.id = t.freelancer_id
JOIN task_category tc
ON t.task_category_ID = tc.id
GROUP BY first_name, last_name, task_type, task_subtype
ORDER BY last_name;

Explicação: Por que a coluna task_subtype deve aparecer em GROUP BY? A regra do SQL é que todas as colunas (exceto as que contêm funções agregadas) devem aparecer em GROUP BY. Isso é algo que você deve saber e ser capaz de reconhecer na consulta imediatamente.

Saída: A saída corrigida agora funcionará e retornará o seguinte resultado. Ele mostra os freelancers e o número de suas tarefas por tipo e subtipo.

first_name	last_name	task_type	task_subtype	task_count
Emma	Biesa	Blog article	Python	1
Emma	Biesa	Blog article	SQL	1
Francois	Cousteau	Social media post	LinkedIn	2
Bob	Franklin	Blog article	Python	1
Bob	Franklin	Blog article	SQL	1
Dionne	Ravanelli	Blog article	Career	1
Dionne	Ravanelli	Blog article	SQL	1

Pergunta 11: Mostrar todos os freelancers e o número de suas tarefas

Conjunto de dados: O mesmo que o anterior.

Solução: Nesta pergunta, você poderia facilmente ser levado a escrever uma consulta que usasse o site COUNT(*) para encontrar o número de tarefas, da seguinte forma:

SELECT first_name,
	 last_name,
	 COUNT(*) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

Sim, você usou sabiamente o site LEFT JOIN para retornar freelancers sem uma tarefa. Entretanto, você deveria usar COUNT(task_category_id) em vez de COUNT(*)...

SELECT first_name,
	 last_name,
	 COUNT(task_category_id) AS task_count
FROM freelancer f
LEFT JOIN task t
ON f.id = t.freelancer_id
GROUP BY first_name, last_name;

... certo?

Explicação: Não caia nesse truque! Tenho certeza de que você sabe que COUNT(*) não deve ser usado em combinação com LEFT JOIN.

Você usa LEFT JOIN para incluir freelancers sem a tarefa. Esses freelancers não terão valores correspondentes na tabela correta, portanto, serão mostrados como NULL. Infelizmente, COUNT(*) não ignora NULLs, portanto, eles serão contados como valores regulares.

Em vez disso, você precisa usar COUNT(task_category_id). Dessa forma, você contará apenas os valores não correspondentes aNULL.

Saída: Dê uma olhada no resultado da primeira consulta (incorreta):

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	1
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Marek Lewandowski tem uma tarefa. Mas sabemos que isso não pode estar correto, pois ele não tem nenhuma tarefa atribuída. A saída mostra a contagem de um porque COUNT(*) contou o valor NULL (linha não correspondente).

A saída da consulta de solução mostra corretamente que a contagem de tarefas de Marek é zero:

first_name	last_name	task_count
Dionne	Ravanelli	2
Marek	Lewandowski	0
Emma	Biesa	2
Bob	Franklin	2
Francois	Cousteau	2

Pergunta 12: Mostrar o número de tarefas concluídas por data de conclusão

Escreva uma consulta que mostre o número de tarefas concluídas por data de conclusão. Inclua NULLs como uma categoria de data separada.

Conjunto de dados: O mesmo que o anterior.

Solução: Essa pergunta tenta induzi-lo a pensar que, de alguma forma, você precisa declarar explicitamente uma condição em que todas as tarefas sem a data de conclusão serão contadas juntas na categoria NULL como uma data.

Mas a solução é mais simples do que você imagina:

SELECT completed_date,
	 COUNT(id) AS completed_task_count
FROM task
GROUP BY completed_date
ORDER BY completed_date ASC;

Explicação: Como você pode ver, a consulta acima não se refere a NULLs de forma alguma. Ela simplesmente seleciona a data de conclusão e usa COUNT() na coluna de ID da tarefa para contar o número de tarefas concluídas.

Obviamente, o resultado precisa ser agrupado pela data de conclusão. Ela também é classificada da data mais antiga para a mais recente, o que não é necessário, mas tem uma aparência mais agradável.

Ao escrever essa consulta, você mostra que os valores de NULL não são contados separadamente. Todos os valores NULL serão mostrados como uma categoria - NULL.

Resultado: Como você pode ver, todas as tarefas sem a data de conclusão são mostradas em uma linha:

completed_date	completed_task_count
2023-12-15	1
2023-12-20	1
2024-01-18	1
2024-02-10	1
2024-02-15	2
NULL	2

Pergunta 13: Mostrar funcionários com seus departamentos e salários

Escreva uma consulta que mostre os funcionários, seus departamentos e seus salários.

Inclua apenas os funcionários com um salário inferior à média de seu departamento.

Conjunto de dados: Essa pergunta da entrevista SQL usa a tabela salaries. Você pode encontrar o script aqui.

id	first_name	last_name	department	salary
1	Benny	Gilhespy	Sales	5,293.47
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
5	Genna	Beche	Accounting	7,451.65
6	Kirsten	Fernandez	Engineering	7,533.13
7	Pen	Fredy	Sales	7,867.54
8	Tish	Calderbank	Sales	4,103.19
9	Gallard	Philipet	Accounting	7,220.06
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
12	Tami	Langrish	Sales	5,588.34
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Solução: A parte complicada aqui é reconhecer que a consulta pode ser muito curta se você souber como usar subconsultas correlacionadas.

Ela deve ser feita assim:

SELECT id, 
	 first_name,
	 last_name,
	 department,
	 salary
FROM salaries s1
WHERE salary < (SELECT AVG(salary)
				FROM salaries s2
				WHERE s1.department = s2.department);

Explicação: Portanto, a consulta primeiro lista todas as colunas necessárias da tabela salários. Eu dei à tabela um alias, s1.

Em seguida, uso a cláusula WHERE para comparar o salário de cada funcionário com a média do departamento. A média departamental é calculada em um tipo especial de subconsulta - uma subconsulta correlacionada.

O que há de tão especial nisso? Bem, essa subconsulta é correlacionada porque faz referência aos dados da consulta principal. Isso acontece na cláusula WHERE de uma subconsulta: o departamento da tabela s1 (que aparece na consulta principal) tem de ser o mesmo que o departamento da tabela s2 que aparece na subconsulta. Essa condição permitirá que a função agregada AVG() calcule a média departamental do departamento em que esse funcionário específico trabalha.

Resultado: A tabela abaixo mostra apenas os funcionários cujos salários estão abaixo da média dos salários de seus respectivos departamentos:

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

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

Resposta: Para responder a essa pergunta, você obviamente precisa saber a diferença entre os dois operadores de conjunto mais comuns no SQL.

Ambos os operadores mesclam verticalmente os resultados de duas ou mais consultas. UNION faz isso excluindo as linhas duplicadas. Em outras palavras, se as mesmas linhas aparecerem em ambas as consultas, elas serão mostradas apenas uma vez. Você pode pensar nisso como DISTINCT no mundo dos operadores de conjunto.

Por outro lado, o UNION ALL mostra todas as linhas de ambas as consultas, inclusive as duplicadas. Você pode ler mais sobre a diferença entre UNION e UNION ALL em nosso guia.

Pergunta 15: Mostrar livros selecionados com seu autor e subtítulo

Escreva uma consulta que selecione o autor, o título e o subtítulo de um livro, mas somente para os livros em que o subtítulo inclua a palavra "woman" (mulher). Inclua livros sem subtítulos.

Conjunto de dados: A tabela usada neste exemplo é bookse o script está aqui.

id	first_name	last_name	department	salary
2	Benetta	Featherstonhaugh	Engineering	2,214.55
3	Karla	Stiell	Sales	2,070.45
4	Sissie	Seabon	Accounting	5,077.42
8	Tish	Calderbank	Sales	4,103.19
10	Walsh	Kleinholz	Accounting	4,000.18
11	Carce	Wilkenson	Accounting	3,991.00
13	Shayne	Dearn	Engineering	2,785.92
14	Merla	Ingilson	Engineering	2,980.36
15	Keely	Patifield	Sales	2,877.92

Solução: A parte simples aqui é que você precisa procurar a palavra "woman" (mulher) no subtítulo. Entretanto, como você também inclui livros sem legendas, ou seja, com valores NULL?

A resposta é que você precisa lidar explicitamente com NULLs para incluí-los na saída, assim:

SELECT 
  author,
  title,
  subtitle
FROM books
WHERE subtitle ILIKE '%woman%' OR subtitle IS NULL;

Explicação: Sua resposta inclui duas condições em WHERE. A primeira condição procura a palavra "woman" no subtítulo. Você faz isso usando LIKE (se seu banco de dados não diferencia maiúsculas de minúsculas) ou ILIKE (se seu banco de dados diferencia minúsculas de minúsculas, como o PostgreSQL). Para procurar a palavra em qualquer lugar em uma cadeia de caracteres, você precisa cercá-la com '%'. Como você está procurando uma string, tudo isso deve ser escrito entre aspas simples.

Agora, você pode adicionar outra condição de filtragem em que declara que o subtítulo deve ser NULL usando o operador IS NULL. As duas condições são unidas usando a palavra-chave OR, pois não podem ser satisfeitas ao mesmo tempo: se não houver legenda, ela não poderá conter a palavra 'woman'.

Saída: Aqui está o resultado mostrando todos os dados que satisfazem uma das condições:

author	title	subtitle
Miljenko Jergović	Sarajevo Marlboro	NULL
Tilar J. Mazzeo	Irena's Children	The Extraordinary Story of the Woman Who Saved 2,500 Children from the Warsaw Ghetto
Olga Tokarczuk	Primeval and Other Times	NULL
Thomas Hardy	Tess of the d' Urbevilles	A Pure Woman Faithfully Presented
Sylvia Plath	Ariel	NULL
Toni Morrison	Jazz	NULL
Haben Girma	Haben	The Deafblind Woman Who Conquered Harvard Law

Mais recursos de entrevista para usuários experientes de SQL

Esses 15 exercícios abrangem algumas das perguntas mais comuns de "truques" de entrevistas sobre SQL para usuários experientes. Ter todas essas soluções deve deixá-lo ciente das armadilhas preparadas para você e de como evitá-las.

Mas você não deve parar agora! Não existe excesso de preparação para uma entrevista de emprego. Portanto, recomendo que você dê uma olhada em mais 25 exemplos de consultas SQL avançadas ou em outras 27 perguntas de entrevista sobre SQL avançado.

Você também deve praticar o que aprendeu aqui. Aqui estão algumas ideias sobre como praticar SQL avançado com nossos cursos e alguns exercícios práticos de SQL avançado para você começar.

Use isso em conjunto com a nossa trilha SQL Avançado e a trilha SQL Avançado Practice, e você estará bem preparado para a sua próxima entrevista de emprego!