Aunque compilar consultas SQL no es lo más interesante para los científicos de datos, una buena comprensión de SQL es extremadamente importante para cualquiera que quiera tener éxito en cualquier actividad relacionada con el procesamiento de datos. El punto aquí es que SQL no es solo SELECT, FROMy WHERE. Cuantas más construcciones SQL conozca un especialista, más fácil será para él crear solicitudes para obtener de las bases de datos todo lo que pueda necesitar. El autor del artículo, cuya traducción publicamos hoy, dice que está dirigido a resolver dos problemas:
- Explorando mecanismos que van más allá del conocimiento básico de SQL.
- Consideración de varias tareas prácticas para trabajar con SQL.
Este artículo cubre 5 preguntas SQL de Leetcode. Representan tareas prácticas que a menudo se encuentran en las entrevistas.Pregunta No. 1: segundo lugar en términos de salario
Escriba una consulta SQL para obtener de la tabla con información sobre el salario del empleado ( Employee) una entrada que contenga el segundo salario más alto.Por ejemplo, dicha consulta ejecutada para la tabla a continuación debería regresar 200. Si la tabla no tiene un valor inferior al salario más alto, la solicitud debe devolverse null.+
| Id | Salary |
+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+
▍ Decisión A: uso IFNULLyOFFSET
Estos son los principales mecanismos que se utilizarán en esta solución al problema:IFNULL(expression, alt): esta función devuelve su argumento expressionsi no es igual null. De lo contrario, se devuelve el argumento alt. Utilizaremos esta función para devolver nullsi la tabla no contiene el valor deseado.OFFSET: Este operador se utiliza con una expresión ORDER BYpara descartar las primeras nlíneas. Esto es útil para nosotros porque estamos interesados en la segunda fila del resultado (es decir, el segundo salario más grande, cuyos datos están en la tabla).
Aquí hay una solicitud preparada:SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1
), null) as SecondHighestSalary
FROM Employee
LIMIT 1
▍ Solución B: uso MAX
La consulta a continuación utiliza la función MAX. Aquí, se selecciona el valor salarial más alto, no igual al salario máximo recibido en toda la tabla. Como resultado, obtenemos lo que necesitamos: el segundo salario más grande.SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary != (SELECT MAX(salary) FROM Employee)
Pregunta # 2: direcciones de correo electrónico duplicadas
Escriba una consulta SQL que detecte Persontodas las direcciones de correo electrónico duplicadas en la tabla .+
| Id | Email |
+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+
▍ Decisión A: COUNTen la subconsulta
Primero, creamos una subconsulta en la que se determina la frecuencia de aparición de cada dirección en la tabla. Luego, el resultado devuelto por la subconsulta se filtra utilizando la instrucción WHERE count > 1. La consulta devolverá información sobre las direcciones encontradas en la tabla de origen más de una vez.SELECT Email
FROM (
SELECT Email, count(Email) AS count
FROM Person
GROUP BY Email
) as email_count
WHERE count > 1
▍ Solución B: expresión HAVING
HAVING: Esta es una expresión que le permite usar una instrucción WHEREcon una expresión GROUP BY.
SELECT Email
FROM Person
GROUP BY Email
HAVING count(Email) > 1
Pregunta No. 3: aumento de temperatura
Escriba una consulta SQL que encuentre en la tabla Weathertodas las fechas (identificadores de fecha) cuando la temperatura sea más alta que la temperatura en las fechas que la preceden. Es decir, nos interesan las fechas en que la temperatura "de hoy" es más alta que la de "ayer".+
| 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 |
+
▍Solución: DATEDIFF
DATEDIFF: Esta función calcula la diferencia entre dos fechas. Se utiliza para proporcionar una comparación precisa de las temperaturas "de hoy" y "de ayer".
Si formulamos la siguiente consulta en lenguaje ordinario, resulta que expresa la siguiente idea: necesitamos elegir identificadores de tal manera que la temperatura correspondiente a las fechas que representan sea mayor que la temperatura para las fechas de "ayer" con respecto a ellos.SELECT DISTINCT a.Id
FROM Weather a, Weather b
WHERE a.Temperature > b.Temperature
AND DATEDIFF(a.Recorddate, b.Recorddate) = 1
Pregunta número 4: el salario más alto de la unidad
La tabla Employeealmacena información sobre los empleados de la empresa. Cada registro en esta tabla contiene información sobre el identificador ( Id) del empleado, su nombre ( Name), salario ( Salary) y la división de la compañía donde trabaja ( 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 |
+
La tabla Departmentcontiene información sobre las divisiones de la empresa.+
| Id | Name |
+
| 1 | IT |
| 2 | Sales |
+
Escriba una consulta SQL que encuentre en cada uno de los departamentos de los empleados con el salario máximo. Por ejemplo, para las tablas anteriores, una consulta similar debería devolver los resultados representados por la siguiente tabla (el orden de las filas en la tabla no importa):+
| Department | Employee | Salary |
+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+
▍Solución: equipo IN
El comando le INpermite establecer WHEREcondiciones en la instrucción que corresponden al uso de varios comandos OR. Por ejemplo, las dos construcciones siguientes son idénticas:WHERE country = ‘Canada’ OR country = ‘USA’
WHERE country IN (‘Canada’, ’USA’).
Aquí queremos obtener una tabla que contenga el nombre del departamento ( Department), el nombre del empleado ( Employee) y su salario ( Salary). Para hacer esto, creamos una tabla que contiene información sobre el identificador de la unidad ( DepartmentID) y el salario máximo para esta unidad. Luego combinamos los dos en una tabla de condiciones según la cual la entrada en la tabla resultante cae solo si DepartmentIDy Salaryse ha formado previamente en la tabla.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
)
Pregunta No. 5: trasplante de estudiantes
Mary es maestra de secundaria. Ella tiene una mesa seatque almacena los nombres de los estudiantes e información sobre sus lugares en el aula. El valor iden esta tabla aumenta constantemente. Mary quiere intercambiar estudiantes vecinos.Aquí hay una tabla de ubicación inicial de los estudiantes:+
| id | student |
+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+
Esto es lo que debería suceder después de trasplantar a los estudiantes vecinos:+
| id | student |
+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+
Escriba una solicitud que le permita al maestro resolver el problema anterior.Tenga en cuenta que si el número de estudiantes es impar, no necesita transferir al último estudiante a ningún lado.▍Solución: uso de un operador WHEN
Una construcción SQL CASE WHEN THENpuede considerarse un operador ifen la programación.En nuestro caso, el primer operador se WHENutiliza para verificar si el identificador impar está asignado a la última fila de la tabla. Si es así, la línea no está sujeta a cambios. El segundo operador WHENes responsable de sumar 1 a cada identificador impar (por ejemplo, 1, 3, 5 se convierte en 2, 4, 6) y restar 1 de cada identificador par (2, 4, 6 se convierten en 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
Resumen
Examinamos varias tareas de SQL, discutiendo en el camino algunas herramientas avanzadas que se pueden usar para compilar consultas SQL. Esperamos que lo que aprendió hoy le sea útil durante las entrevistas en SQL y le resulte útil en el trabajo diario.PD: en nuestro mercado hay una imagen de Docker con SQL Server Express, que se instala con un solo clic. Puede verificar el funcionamiento de los contenedores en VPS. Todos los nuevos clientes tienen 3 días gratuitos para realizar las pruebas.¡Queridos lectores! ¿Qué puede aconsejar a quienes desean dominar el arte de crear consultas SQL?