Embora a compilação de consultas SQL não seja a coisa mais interessante para os cientistas de dados, um bom entendimento do SQL é extremamente importante para quem deseja ter sucesso em qualquer atividade relacionada ao processamento de dados. O ponto aqui é que o SQL não é apenas SELECT, FROMe WHERE. Quanto mais construções SQL um especialista conhecer, mais fácil será para ele criar solicitações para obter dos bancos de dados tudo o que ele possa precisar. O autor do artigo, cuja tradução estamos publicando hoje, diz que ele visa solucionar dois problemas:
- Explorar mecanismos que vão além do conhecimento básico de SQL.
- Consideração de várias tarefas práticas para trabalhar com SQL.
Este artigo aborda 5 questões SQL do Leetcode. Eles representam tarefas práticas que são frequentemente encontradas em entrevistas.Pergunta nº 1: segundo lugar em termos de salário
Escreva uma consulta SQL para obter da tabela com informações sobre o salário dos funcionários ( Employee) uma entrada contendo o segundo salário mais alto.Por exemplo, uma consulta executada para a tabela abaixo deve retornar 200. Se a tabela não tiver um valor menor que o salário mais alto, a solicitação deverá retornar null.+
| Id | Salary |
+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+
▍ Decisão A: uso IFNULLeOFFSET
Aqui estão os principais mecanismos que serão usados nesta solução para o problema:IFNULL(expression, alt): esta função retorna seu argumento expressionse não for igual null. Caso contrário, o argumento será retornado alt. Usaremos essa função para retornar nullse a tabela não contiver o valor desejado.OFFSET: Este operador é usado com uma expressão ORDER BYpara descartar as primeiras nlinhas. Isso é útil para nós pelo motivo de estarmos interessados na segunda linha do resultado (ou seja, o segundo maior salário, cujos dados estão na tabela).
Aqui está uma solicitação pronta:SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), null) as SecondHighestSalary
FROM Employee
LIMIT 1
▍ Solução B: use MAX
A consulta a seguir usa a função MAX. Aqui, o valor mais alto do salário é selecionado, diferente do salário máximo recebido em toda a tabela. Como resultado, obtemos o que precisamos - o segundo maior salário.SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
Pergunta # 2: endereços de email duplicados
Escreva uma consulta SQL que detecte Persontodos os endereços de email duplicados na tabela .+
| Id | Email |
+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+
▍ Decisão A: COUNTna subconsulta
Primeiro, criamos uma subconsulta na qual é determinada a frequência de ocorrência de cada endereço na tabela. Em seguida, o resultado retornado pela subconsulta é filtrada usando a instrução WHERE count > 1. A consulta retornará informações sobre endereços encontrados na tabela de origem mais de uma vez.SELECT Email
FROM (
SELECT Email, count(Email) AS count
FROM Person
GROUP BY Email
) as email_count
WHERE count > 1
▍ Solução B: expressão HAVING
HAVING: Esta é uma expressão que permite que você use uma instrução WHEREcom uma expressão GROUP BY.
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1
Pergunta nº 3: aumento da temperatura
Escreva uma consulta SQL que encontre na tabela Weathertodas as datas (identificadores de data) quando a temperatura for maior que a temperatura nas datas anteriores. Ou seja, estamos interessados em datas em que a temperatura "de hoje" é mais alta que a de "ontem".+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+
▍Solução: DATEDIFF
DATEDIFF: Esta função calcula a diferença entre duas datas. É usado para fornecer uma comparação exata das temperaturas "de hoje" e "de ontem".
Se formularmos a seguinte consulta em linguagem comum, verifica-se que ela expressa a seguinte idéia: precisamos escolher identificadores para que a temperatura correspondente às datas que eles representam seja maior que a temperatura das datas de ontem em relação a eles.SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
Pergunta número 4: o salário mais alto da unidade
A tabela Employeearmazena informações sobre os funcionários da empresa. Cada entrada nesta tabela contém informações sobre o identificador ( Id) do funcionário, seu nome ( Name), salário ( Salary) e o departamento da empresa em que ele trabalha ( Department).+
| Id | Name | Salary | DepartmentId |
+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+
A tabela Departmentcontém informações sobre as divisões da empresa.+
| Id | Name |
+
| 1 | IT |
| 2 | Sales |
+
Escreva uma consulta SQL que encontre em cada um dos departamentos dos funcionários com o salário máximo. Por exemplo, para as tabelas acima, uma consulta semelhante deve retornar os resultados representados pela tabela a seguir (a ordem das linhas na tabela não importa):+
| Department | Employee | Salary |
+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+
▍Solução: equipe IN
O comando INpermite definir WHEREcondições na instrução que correspondem ao uso de vários comandos OR. Por exemplo, as duas construções a seguir são idênticas:WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).
Aqui queremos obter uma tabela contendo o nome do departamento ( Department), o nome do funcionário ( Employee) e seu salário ( Salary). Para isso, criamos uma tabela que contém informações sobre o identificador da unidade ( DepartmentID) e o salário máximo dessa unidade. Em seguida, combinamos os dois em uma tabela de condições, de acordo com a qual a entrada na tabela resultante cai apenas se DepartmentIDe Salaryjá foi formada anteriormente na tabela.SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.Id
WHERE (DepartmentId , Salary)
IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
Pergunta nº 5: transplantando estudantes
Mary é professora do ensino médio. Ela tem uma tabela seatque armazena os nomes dos alunos e informações sobre seus lugares na sala de aula. O valor idnesta tabela está aumentando constantemente. Mary quer trocar estudantes vizinhos.Aqui está uma tabela da colocação inicial dos alunos:+
| id | student |
+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+
Eis o que deve acontecer após o transplante de alunos vizinhos:+
| id | student |
+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+
Escreva uma solicitação que permita ao professor resolver o problema acima.Observe que, se o número de alunos for ímpar, você não precisará transferir o último aluno para qualquer lugar.:Solução: usando um operador WHEN
Uma construção SQL CASE WHEN THENpode ser considerada um operador ifna programação.No nosso caso, o primeiro operador é WHENusado para verificar se o identificador ímpar está atribuído à última linha da tabela. Nesse caso, a linha não está sujeita a alterações. O segundo operador WHENé responsável por adicionar 1 a cada identificador ímpar (por exemplo, 1, 3, 5 se transforma em 2, 4, 6) e subtrair 1 de cada identificador par (2, 4, 6 se transforma em 1, 3, 5).SELECT
CASE
WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND id = (SELECT MAX(id) FROM seat) THEN id
WHEN id%2 = 1 THEN id + 1
ELSE id - 1
END AS id, student
FROM seat
ORDER BY id
Sumário
Examinamos várias tarefas SQL, discutindo ao longo do caminho algumas ferramentas avançadas que podem ser usadas para compilar consultas SQL. Esperamos que o que você aprendeu hoje seja útil para você durante as entrevistas em SQL e se prove útil no trabalho diário.PS No nosso mercado, há uma imagem do Docker com o SQL Server Express, instalada em um clique. Você pode verificar a operação de contêineres no VPS. Todos os novos clientes recebem 3 dias gratuitamente para testes.Queridos leitores! O que você pode aconselhar para quem deseja dominar a arte de criar consultas SQL?