Volver a la lista de artículos Artículos
Lectura de 34 minutos

Las 27 mejores preguntas de entrevista de SQL avanzado con respuestas

Índice

¿Dónde puede encontrar un profesional de SQL una guía completa de preguntas de entrevista de SQL avanzado? La respuesta más breve es: ¡aquí! Hemos seleccionado las 27 preguntas SQL más importantes y las hemos respondido para usted.

Prepararse para una entrevista SQL no es fácil, especialmente si tu trabajo requiere el conocimiento de SQL avanzado. Este artículo contiene las 27 preguntas avanzadas de SQL más frecuentes en las entrevistas y ofrece respuestas detalladas y recursos para seguir leyendo.

Repasaremos estos cuatro conceptos principales y algunos más:

  • JOINs
  • GROUP BY, WHERE, y HAVING
  • CTEs (Common Table Expressions) y consultas recursivas
  • Funciones de ventana

La mejor manera de refrescar sus conocimientos avanzados de SQL es seguir nuestro curso interactivo. SQL avanzado interactivo. Contiene más de 300 ejercicios prácticos sobre funciones de ventana, expresiones comunes de tabla, funciones recursivas y mucho más.

Ataquemos estas preguntas de frente, ¡sin más preámbulos!

1. ¿Qué es un JOIN en SQL?

JOIN es un comando SQL que le permite combinar dos o más tablas. Esto se hace a través de una columna común (es decir, una columna que tiene los mismos valores en ambas tablas), lo que permite utilizar datos de dos o más tablas al mismo tiempo. La unión de tablas en SQL es esencial debido a la naturaleza de las bases de datos relacionales: los datos se atomizan en tablas, y cada tabla contiene sólo una parte de los datos disponibles en la base de datos.

Utilizaremos dos tablas para mostrar cómo funciona esto. La primera tabla es football_players.

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

La segunda es national_team.

idcountry
1Italy
2Netherlands
3England
4Croatia

Esta es una consulta que une dos tablas:

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;

Selecciona columnas de ambas tablas. Para unirlas, primero hacemos referencia a una tabla en la cláusula FROM. A continuación aparece JOIN, y después la segunda tabla. Utilizamos la cláusula ON para especificar la condición con la que se unirán las tablas: el national_team_id de la tabla football_players debe ser igual a la columna id de la tabla national_team tabla.

El resultado de la consulta es

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

INNER JOIN es una de las diversas uniones distintas de SQL. Su característica es que sólo devuelve datos de las tablas unidas cuando la condición de unión es verdadera. Aquí hay más detalles sobre cómo funciona el INNER JOIN en SQL.

2. ¿Cuál es la diferencia entre INNER JOIN, LEFT JOIN, RIGHT JOIN y FULL JOIN?

Existen diferentes tipos de uniones en SQL. Las uniones más utilizadas son INNER JOIN, LEFT JOIN, RIGHT JOIN, y FULL JOIN. LEFT JOIN, RIGHT JOIN, y FULL JOIN son las llamadas uniones externas. JOIN (también conocida como INNER JOIN) es una unión interna. En este caso, "interna" significa que sólo devuelve las filas de ambas tablas que cumplen la condición de unión; las uniones externas devuelven todas las filas de una tabla, más las filas coincidentes de la(s) otra(s) tabla(s). La excepción es FULL JOIN, que devuelve todas las filas de ambas tablas.

Este es el resultado de INNER JOIN del ejemplo anterior. Volvamos a tenerlo aquí. Así será más fácil ver la diferencia entre las distintas uniones.

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

LEFT JOIN devuelve todos los datos de la tabla izquierda (es decir, la primera tabla, que aparece antes\a la izquierda de la palabra clave JOIN ) y sólo las filas coincidentes de la tabla derecha (la segunda tabla, que aparece después\a la derecha de la palabra clave JOIN ). Si no hay datos coincidentes en la tabla derecha, los valores que faltan se muestran como NULLs. Aquí se muestra la misma consulta con LEFT JOIN sustituyendo a 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;

La tabla de la izquierda es football_playersy la derecha es national_team. Como era de esperar, el resultado es diferente:

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

Todos los futbolistas de la tabla de la izquierda están aquí. Sin embargo, Kylian Mbappe y Erling Haaland no tienen un país que coincida en la tabla derecha, por lo que hay NULLs en la columna country para esos jugadores. Estas filas no estaban presentes en el resultado de INNER JOIN. Fueron añadidas por LEFT JOIN.

RIGHT JOIN hace lo contrario: devuelve todos los datos de la tabla derecha y sólo los datos coincidentes de la tabla izquierda. Cuando no hay datos coincidentes en la tabla izquierda, los valores que faltan se muestran como NULLs.

Este es el 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;

Todo sigue igual, excepto que usamos RIGHT JOIN en lugar de LEFT JOIN. Este es el resultado:

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

Ahora tenemos todas las selecciones nacionales y sus jugadores. Pero puedes ver que hay un país (Croacia) que no tiene jugadores en la tabla de la izquierda. Las columnas de jugadores de Croacia se rellenan con NULLs.

FULL JOIN saca todos los datos de todas las tablas unidas. De nuevo, si no hay datos coincidentes en la tabla correspondiente, los valores que falten aparecerán como NULL.

Una vez más, cambiamos el tipo de unión en la 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;

Devolverá todos los datos de ambas tablas. Cualquier dato no coincidente se sustituye por NULLs. Todos los jugadores están en el resultado, aunque no tengan un país correspondiente en la otra tabla. Todos los países están en el resultado, aunque no tengan jugadores en la tabla football_player tabla. El resultado FULL JOIN es la unión de LEFT JOIN y RIGHT JOIN:

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

Puedes encontrar más información en el artículo sobre los diferentes tipos de JOIN. También puede ver nuestra Tipos de JOIN en SQL Cheat Sheet para un rápido repaso.

3. ¿Qué es un CROSS JOIN?

Un CROSS JOIN es otro tipo de unión disponible en SQL. Devuelve un producto Cartesiano. Esto significa que CROSS JOIN devolverá cada fila de la primera tabla combinada con cada fila de la segunda tabla.

No se utiliza muy a menudo. Pero si está tentado de utilizarlo, piénselo dos veces. Devolver todas las combinaciones de filas puede llevar algún tiempo, ¡si es que la consulta termina!

Como ejemplo, utilicemos las tablas de las dos preguntas anteriores. Para escribir la consulta, utilice la palabra clave CROSS JOIN. Como se trata de un tipo join que devuelve todas las combinaciones de filas de todas las tablas, no hay cláusula ON. Eche un vistazo:

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

Este es el resultado. Todos los jugadores de la tabla football_players con todos los países de la tabla national_team tabla.

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

Puede aprender más sobre CROSS JOIN en esta guía ilustrada sobre el CROSS JOIN en SQL.

4. ¿Qué es un Self-Join en SQL?

Como probablemente sospeche, una auto-unión ocurre cuando la tabla se une consigo misma. Es importante notar que no es un comando distinto en SQL: cualquier tipo de JOIN puede ser usado para unir una tabla consigo misma.

La unión se realiza como cualquier otra JOIN, pero esta vez hará referencia a la misma tabla a ambos lados de la palabra clave JOIN. La autounión es especialmente útil cuando una tabla tiene una clave externa que hace referencia a su clave principal. Esto permite consultar datos jerárquicos, como árboles genealógicos o la jerarquía organizativa de una empresa. También es útil para buscar pares de valores. En el ejemplo siguiente, buscamos jugadores de la misma selección 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;

Autounión significa que, en lugar de dos tablas, está especificando la misma tabla dos veces: una en la cláusula FROM y otra después de la cláusula JOIN. Dado que está utilizando la misma tabla dos veces, debe utilizar alias para las tablas. Cada aparición de la tabla debe tener un alias distinto (fp1, fp2 en nuestra consulta) para que quede claro a qué aparición de la tabla nos estamos refiriendo.

Estamos uniendo jugadores de la misma selección nacional (sus valores national_team_id son iguales). Sin embargo, no queremos listar a un jugador consigo mismo, así que excluimos el caso en que fp1.id y fp2.id sean iguales.

El resultado de la consulta es el siguiente:

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

Puedes utilizar las columnas national_team_id de ambas tablas para confirmar que, realmente, Gianfranco Zola y Mario Balotelli jugaron en el mismo equipo.

Puede obtener más información en nuestro artículo sobre ejemplos de autounión.

5. Unir dos tablas utilizando un JOIN de dos columnas

Se le dan dos tablas. La primera es employeeque tiene los siguientes datos:

idfirst_namelast_name
1SteveBergman
2SteveJohnson
3SteveKing

La segunda tabla es customerque tiene los siguientes datos:

idfirst_namelast_name
1AnnColeman
2SteveBergman
3SteveYoung
4DonnaWinter
5SteveKing

Tu tarea es devolver todos los empleados que también son clientes de la empresa. Desafortunadamente, no puedes utilizar la columna id porque es el ID del empleado en una tabla y el ID del cliente en otra. En otras palabras, no hay ninguna columna en una tabla que haga referencia a la otra.

La solución es unir las tablas por el nombre y el apellido, es decir, utilizar una columna de dos columnas JOIN.

El código siguiente unirá primero las tablas por el nombre. Después, la palabra clave AND establece la segunda condición de unión, que es el apellido. De esta forma, obtendrá datos de ambas tablas en las que la combinación nombre/apellido sea la misma. Si utilizáramos sólo una de estas columnas, podríamos haber obtenido datos erróneos, ya que los empleados y los clientes pueden tener el mismo nombre pero apellidos diferentes (o viceversa). Esta es la consulta:

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;

Aquí está la salida de código.:

first_namelast_name
SteveBergman
SteveKing

El resultado muestra que Steve Bergman y Steve King son empleados y clientes de la empresa.

6. Unir dos tablas con una unión no equitativa

Hasta ahora, hemos estado utilizando uniones equitativas: uniones en las que hay un signo de igualdad en la condición ON. Por el contrario, la unión no-equi es una unión que tiene una condición de no-igualdad en la cláusula ON.

En esta ocasión, tenemos datos sobre usuarios de móviles y su consumo de datos. La primera tabla es mobile_userque muestra los usuarios de móviles y su límite mensual de datos móviles en MB:

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

La segunda tabla es data_usageque muestra el uso de datos mensual real del usuario en MB:

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

La tarea consiste en encontrar todos los datos en los que el uso real fue superior al límite mensual. Queremos ver el nombre y apellidos del usuario, el límite mensual, los datos reales utilizados y el periodo de tiempo.

La solución es utilizar la unión no equitativa, como se muestra a continuación:

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;

La consulta selecciona toda la información necesaria de dos tablas. Las tablas se unen mediante INNER JOIN. Primero las unimos cuando el ID de usuario es el mismo. A continuación, añadimos la segunda condición después de la palabra clave AND. Aquí tenemos una condición de no-igualdad que nos dará datos donde el límite está por debajo del uso mensual.

Puedes ver el resultado más abajo:

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

Si le interesa, aquí tiene más ejemplos de uniones no equitativas.

7. ¿Qué hace DISTINCT?

DISTINCTEl propósito de DISTINCT, en general, es eliminar valores duplicados. O, dicho de otro modo, mostrar valores únicos en el resultado de la consulta.

Imagine que trabaja con esta tabla loans que muestra los ID de los préstamos y su duración en meses.

loan_idloan_duration
10011260
10020560
10020848
100333120
10035748
100398120

Diferentes préstamos pueden tener la misma duración, por lo que desea extraer la lista de posibles duraciones de préstamos. Para ello, utilice DISTINCT:

SELECT DISTINCT loan_duration
FROM loans
ORDER BY loan_duration; 

La salida muestra que hay préstamos con duraciones de 48, 60 y 120 meses:

loan_duration
48
60
120

DISTINCT puede utilizarse en SELECT con una columna para mostrar sólo los valores únicos de esa columna, como en el ejemplo anterior. Si se utiliza en SELECT pero con varias columnas, la salida mostrará las combinaciones únicas de todas esas columnas.

También puede utilizar DISTINCT con funciones agregadas. Si lo hace, su consulta eliminará los resultados de agregación duplicados.

Puedes ver ejemplos de estos usos en nuestro artículo sobre el papel de DISTINCT en SQL.

8. ¿Qué hace GROUP BY en SQL?

GROUP BY GROUP BY es una cláusula SQL utilizada para organizar datos en grupos basados en un valor o valores comunes. Se utiliza más comúnmente con funciones agregadas; esta combinación devolverá datos agregados para cada grupo. Sin embargo, es importante saber que el uso de funciones de agregación dentro de la cláusula GROUP BY no está permitido.

La sintaxis general de GROUP BY es la siguiente:

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

Supongamos que existe la tabla 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 y AVG() para encontrar el salario medio por departamento:

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

Queremos ver los departamentos, así que seleccionamos esta columna. Para calcular el salario medio, aplicamos la función AVG() a la columna salary.

Todas las columnas listadas en GROUP BY definen los grupos de datos. En nuestro ejemplo, los grupos están definidos por la columna departamento: calculamos el salario medio de cada departamento.

Nuestra agrupación y agregación de datos tiene el siguiente aspecto:

departmentaverage_salary
Accounting3,811.30
Sales9,598.97
IT5,212.31

El salario medio en Contabilidad es de 3.811,30. Los salarios medios de los otros dos departamentos son 9.598,97 y 5.212,31, respectivamente.

Al escribir una consulta, GROUP BY siempre tiene que ir después de WHERE pero antes de la cláusula HAVING. Puedes aprender más sobre esto en este artículo sobre GROUP BY en SQL.

9. ¿Cómo Filtrar Grupos GROUP BY?

Una vez que obtenga los grupos que especificó en GROUP BY, a veces querrá filtrarlos. La clave para hacerlo se encuentra en la sintaxis de la pregunta anterior. La cláusula que le permite filtrar grupos es HAVING.

Una vez especificados los criterios de filtrado en HAVING, la consulta devolverá todos los datos que cumplan los criterios. Todos los demás datos se filtrarán.

A continuación se muestra cómo funciona con los datos de la pregunta anterior si tuviéramos que mostrar sólo los departamentos con un salario medio inferior a 5.500 dólares.

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

El código es muy similar al de la pregunta anterior. La diferencia es la cláusula HAVING. La utilizamos para filtrar los resultados y mostrar sólo los departamentos con salarios inferiores a 5.500.

Esto es lo que devuelve el código:

departmentaverage_salary
Accounting3,811.30
IT5,212.31

El departamento que falta en la salida es Ventas porque su salario medio es de 9.598,97.

Puedes encontrar más ejemplos prácticos de esta cláusula en este artículo sobre HAVING en SQL.

10. ¿Cuál es la diferencia entre WHERE y HAVING?

Si conoces las respuestas a las dos preguntas anteriores, probablemente conozcas la respuesta a esta pregunta.

La principal diferencia es que WHERE se utiliza para filtrar datos antes de agruparlos. Su posición en la sentencia SELECT lo demuestra: viene antes que GROUP BY. Debido a su propósito, no se permiten funciones agregadas en WHERE.

HAVING, por el contrario, se utiliza para filtrar datos después de agruparlos; por eso se utiliza después de GROUP BY. Además, HAVING permite condiciones que incluyen funciones agregadas.

La mejor manera de aprender la distinción es leer este artículo sobre WHERE vs. HAVING en SQL.

11. ¿Qué devolverá la siguiente consulta que intenta filtrar NULLs?

A menudo te harán este tipo de pregunta en tu entrevista de SQL avanzado: te darán un código y tendrás que describir el resultado de la consulta. Mientras que escribir y leer código SQL van de la mano, todavía se siente diferente cuando tienes que analizar el código que alguien más escribió.

Tienes datos en la tabla 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

¿Qué devolverá este código?

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

Si respondes que devolverá todas las filas excepto ID = 3, ¡te equivocas! Se trata de una pregunta trampa. Al leer la condición WHERE, podrías leerla como: devolver todos los datos en los que la fecha de finalización sea diferente de 2022-10-01. Mirando la tabla, pensarías que son todas las filas menos una.

Lo es, ¡pero no para SQL! Como puede ver, hay tres filas con valores NULL. Para SQL, NULL no es igual a un valor; es un no-valor. Así que cuando configure la condición en WHERE de esa manera, estará excluyendo todas las fechas que no sean iguales a los valores 2022-10-01 y NULL.

Aquí está la salida como prueba:

first_namelast_namestart_datetermination_date
NicholasBertolucci2022-04-072022-11-11

Puede aprender más sobre este y otros operadores de comparación utilizados con NULL.

12. Escriba una Consulta que Encuentre el Número de Canciones por Artista. Use LEFT JOIN y COUNT().

Suponga que le dan dos tablas artist y song.

Estos son los artist datos:

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

A continuación están los song datos:

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

Necesitas usar LEFT JOIN y COUNT() para encontrar todos los artistas, sus IDs, y el número de sus canciones en la base de datos.

Podrías tener la tentación de sugerir esta solución:

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;

Veamos el resultado:

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

La salida muestra todos los artistas; eso está bien. Sin embargo, el número de canciones de Otis Redding y Lou Rawls es uno, ¡lo cual es incorrecto! Echa un vistazo a la tabla songy verás que no hay ningún ID de artista igual a 4 o 5.

¿Qué ha fallado? Al utilizar COUNT(*) con LEFT JOIN, la función de agregado contará todos los valores no coincidentes (NULLs). Por eso el resultado mostraba una canción para Otis Redding y Lou Rawls, aunque no tuvieran canciones en la tabla.

COUNT(*) se utiliza para contar todas las filas. Para dar una respuesta correcta, deberías usar COUNT(song_title) en su 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 le dará la salida correcta:

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

El número de canciones de Prince, Jimi Hendrix y Santana permanece igual que en la salida anterior. Sin embargo, el número de canciones de los otros dos artistas es ahora cero, y esa es la cuenta correcta.

13. ¿Cuál es la diferencia entre JOIN y UNION?

JOIN es una cláusula SQL que se utiliza para unir dos o más tablas. Permite utilizar datos de todas las tablas unidas. En otras palabras, las columnas de todas las tablas se muestran unas junto a otras, lo que significa que los datos se apilan horizontalmente.

UNION es un operador de conjunto que se utiliza para combinar los resultados de dos o más sentencias SELECT. Los datos se apilan verticalmente. Uno de los requisitos al utilizar UNION es que tiene que haber el mismo número de columnas en todas las sentencias SELECT unidas. Además, todas las columnas seleccionadas deben ser del mismo tipo de datos.

14. ¿Cuál es la diferencia entre UNION y UNION ALL?

Lo que tienen en común es que ambos son operadores de conjunto. Además, ambos operadores se utilizan con el mismo fin: fusionar datos de dos o más sentencias SELECT.

Los requisitos relativos al número de columnas y su tipo de datos también son los mismos.

Ahora bien, la diferencia es que UNION sólo devuelve registros únicos. En cambio, UNION ALL devuelve todos los registros, incluidos los duplicados.

Normalmente, UNION ALL es más rápido porque no ordena el resultado para eliminar los duplicados. La regla general es utilizar UNION ALL por defecto. Utilice UNION sólo si necesita resultados únicos o si está absolutamente seguro de que su consulta no devolverá datos duplicados.

Puedes aprender más sobre su sintaxis y uso en este artículo sobre las diferencias entre UNION y UNION ALL.

15. ¿Qué es una Subconsulta en SQL?

Una subconsulta es una consulta escrita dentro de otra consulta SQL. La 'otra' consulta se llama consulta principal, mientras que una subconsulta a veces también se llama consulta anidada.

Las subconsultas pueden utilizarse en las sentencias SELECT, INSERT, UPDATE, y DELETE. También pueden utilizarse en cláusulas como FROM o WHERE, que es el uso más común.

He aquí un ejemplo. La tabla es productsy almacena información sobre nombres de productos, cantidades y categorías:

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

Utilizaremos una subconsulta y mostraremos la cantidad total por categoría de producto, pero sólo para los productos individuales cuya cantidad sea superior a la cantidad media de todos los productos.

Esta es la solución:

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

La consulta selecciona la categoría de producto y suma la cantidad utilizando la función de agregado SUM(). Hay una condición en WHERE que dice que sólo los productos individuales con una cantidad superior a la media se incluirán en la suma. Utilizamos la subconsulta y la función AVG() para obtener esta media.

La consulta devuelve dos filas:

product_categoryproduct_quantity
Laptop806
Desktop973

Falta una categoría porque no cumple los criterios de filtrado: teléfonos móviles.

Existen diferentes tipos de subconsultas, como las escalares, las de varias filas y las correlacionadas. Puede aprender más sobre ellos en nuestro artículo sobre tipos de subconsultas.

16. Escriba una Consulta para Devolver a los Vendedores y sus Datos de Ventas Mensuales por Encima de su Promedio Personal de Ventas. Utilice una Subconsulta Correlacionada.

Una subconsulta correlacionada es un tipo de subconsulta que utiliza valores de la consulta externa. Se comprueba una vez por cada fila que devuelve la consulta externa, lo que puede ralentizar el rendimiento.

Sin embargo, la pregunta insiste en utilizarla, así que veamos los datos.

La primera tabla es salesperson:

idfirst_namelast_name
1NinaLee
2CarolinaGreen
3MickJohnson

La otra tabla es 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

Su tarea consiste en utilizar una subconsulta correlacionada y devolver el nombre completo del vendedor, sus ventas mensuales y los periodos en los que sus ventas superan su media personal.

He aquí la solución:

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);

La consulta selecciona todas las columnas necesarias. Estos datos proceden de ambas tablas, así que las hemos unido.

Ahora viene la parte crucial. Para filtrar los datos, utilizamos la cláusula WHERE. La condición dice que la consulta debe devolver todos los datos en los que las ventas mensuales sean superiores a las ventas medias de cada vendedor. ¿Cómo calculamos estas ventas medias individuales? Utilizando la función AVG() en la subconsulta que escribimos en la cláusula WHERE.

Este es el resultado:

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

Hay más ejemplos en este artículo sobre subconsultas correlacionadas.

17. ¿Qué es Funciones de ventana en SQL?

Las funciones de ventana de SQL reciben su nombre del hecho de que se aplican a una ventana de datos. Esta ventana es simplemente un conjunto de filas relacionadas con la fila actual.

Las funciones de ventana son iniciadas por la cláusula OVER(). Otra cláusula importante es PARTITION BY, que define particiones de datos dentro de un marco de ventana. Cuando se omite esta cláusula, la partición es toda la tabla de resultados. Cuando se utiliza PARTITION BY, se pueden definir una o más columnas por las que se particionarán los datos. Se puede considerar como GROUP BY para las funciones de ventana.

Otra cláusula importante es ORDER BY. Ordena los datos dentro de la ventana. En el contexto de las funciones de ventana, esta cláusula da instrucciones sobre el orden en que se ejecutará la función.

Para saber más, consulta este artículo sobre funciones de ventana.

18. ¿Cuál es la diferencia entre Funciones de ventana y GROUP BY?

La única similitud que comparten es que tanto GROUP BY como las funciones ventana pueden utilizarse -y muy a menudo se utilizan- con las funciones de agregado, y ambas trabajan sobre un conjunto de filas.

Sin embargo, cuando se utiliza GROUP BY, la salida se muestra como grupos, y no se pueden ver las filas individuales que forman el grupo.

Las funciones de ventana no tienen estos problemas. Una de sus características es que no contraen las filas individuales cuando muestran datos agregados. Esto significa que es posible mostrar datos agregados y no agregados simultáneamente.

Las funciones de ventana se utilizan para algo más que para agregar datos, como verás en la siguiente pregunta. Pero si quieres saber más sobre el tema actual, tenemos un artículo que explica las funciones ventana frente a GROUP BY.

19. ¿Qué Funciones de ventana conoces?

Las funciones ventana de SQL se pueden dividir generalmente en cuatro categorías:

  • Funciones de Clasificación
  • Funciones de Distribución
  • Funciones Analíticas
  • Funciones Agregadas

Las funciones de clasificación son:

  • ROW_NUMBER() - Devuelve un número único para cada fila dentro de una partición; los valores empatados tienen números de fila diferentes.
  • RANK() - Ordena los datos dentro de una partición; los valores empatados tienen el mismo rango y hay un espacio después de los empates (por ejemplo, 1, 2, 3, 3, 5).
  • DENSE_RANK() - Clasifica los datos dentro de una partición; los valores empatados tienen el mismo rango y no hay diferencia en la clasificación (por ejemplo, 1, 2, 3, 3, 4).

Las funciones de distribución son:

  • PERCENT_RANK() - Devuelve el rango relativo dentro de una partición.
  • CUME_DIST() - Devuelve la distribución acumulativa dentro de una partición.

Las funciones analíticas son:

  • LEAD() - Permite acceder a valores de una fila posterior en relación con la fila actual.
  • LAG() - Permite acceder a valores de una fila anterior en relación con la fila actual.
  • NTILE() - Divide las filas de una partición en grupos aproximadamente iguales.
  • FIRST_VALUE() - Permite acceder a los valores de la primera fila de una partición.
  • LAST_VALUE() - Permite acceder a los valores de la última fila de una partición.
  • NTH_VALUE() - Permite acceder a la n-ésima fila de una partición.

Por último, las funciones de agregación son:

  • AVG() - Devuelve un valor medio de las filas de una partición.
  • COUNT() - Devuelve el número de valores de las filas de una partición.
  • MAX() - Devuelve el valor máximo de las filas de una partición.
  • MIN() - Devuelve el valor mínimo de las filas de una partición.
  • SUM() - Devuelve el valor de la suma de las filas de una partición.

Nuestra Funciones de ventana Cheat Sheet le proporcionará más información sobre todas estas funciones.

20. ¿Cómo Crear un Ranking en SQL?

La forma más sencilla de clasificar datos en SQL es utilizar una de las tres funciones de ventana de clasificación:

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

Se le da un conjunto de datos llamado album_sales con los siguientes datos:

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 son las ventas de los diez álbumes más vendidos de la historia. Como puede ver, los álbumes no están clasificados. Eso es lo que haremos aquí: ordenarlos del más vendido al menos vendido utilizando funciones de ventana.

Si utiliza ROW_NUMBER(), la consulta tendrá este aspecto:

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

La sintaxis es sencilla. Primero se elige la función ventana. A continuación, utilice la cláusula obligatoria OVER() que indica que se trata de una función ventana. En ORDER BY, se ordenan los datos de forma descendente. Esto significa ahora que los números de fila se asignarán según las copias vendidas de mayor a menor.

Por supuesto, enumere todas las demás columnas que necesite y haga referencia a la tabla para obtener el mismo resultado:

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 puedes ver, los álbumes están clasificados del uno al diez. Dos álbumes vendieron 45 millones de copias. Sin embargo, están clasificados de forma diferente (tercero y cuarto) según criterios aleatorios. Lo mismo ocurre con tres álbumes que vendieron 40 millones de copias.

Si utiliza RANK(), la sintaxis es la misma, salvo que utiliza una función de ventana diferente:

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

Sin embargo, la salida es 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

Puede ver que los álbumes empatados se clasifican en tercer lugar (dos veces). El siguiente álbum no empatado ocupa el quinto lugar. Lo mismo ocurre con los álbumes que ocupan el séptimo lugar.

Veamos qué ocurre si utilizamos DENSE_RANK():

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

Éste es el 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

Los primeros álbumes empatados se clasifican como terceros, que es lo mismo que en el resultado anterior. Pero la diferencia es que el siguiente álbum no empatado es el cuarto, lo que significa que no se salta la clasificación.

Los otros tres álbumes empatados ocupan ahora el sexto lugar, no el séptimo como antes. Además, el puesto más alto es el séptimo, no el décimo.

Como puede ver, cada método da resultados diferentes. Debes utilizar el que mejor se adapte a tus datos y a lo que quieras conseguir con la clasificación. Para saber más, lee el artículo sobre clasificación de filas en SQL.

21. ¿Cuál es la diferencia entre RANK() y DENSE_RANK()?

Ya hemos tocado la diferencia en la pregunta anterior. Allí la viste en un ejemplo práctico, y ahora vamos a formularla para responder a esta pregunta.

RANK() asigna el mismo rango a las filas con los mismos valores. Cuando llega a la siguiente fila no empatada, se salta el rango por el número de rangos empatados.

DENSE_RANK() también asigna el mismo rango a los valores empatados. Sin embargo, no se salta el rango cuando llega a la siguiente fila no vinculada. En otras palabras, DENSE_RANK() ordena los datos secuencialmente.

En este artículo se explican con más detalle las diferencias entre RANK() y DENSE_RANK().

22. Encontrar las N Filas Superiores en SQL Utilizando una Función de Ventana y una CTE.

Esta es una pregunta común y puede ser resuelta de varias maneras. Utilizaremos la función ventana en una CTE para devolver el resultado deseado.

Los datos disponibles se almacenan en la tabla salary tabla:

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

La tarea aquí es devolver los tres empleados mejor pagados de cada departamento, junto con su salario y departamento.

El planteamiento es el siguiente:

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;

La primera parte del código es una Expresión de Tabla Común, o CTE. Se inicia utilizando la palabra clave WITH. La CTE se denomina clasificación. Después de la palabra clave AS, escribimos la definición de la CTE como una sentencia SELECT entre paréntesis.

Después de seleccionar todas las columnas requeridas viene ranking, usamos la función DENSE_RANK(). Si lo desea, puede utilizar cualquier otra función de ventana de clasificación.

La sintaxis es familiar. Para obtener los rankings por departamento, necesitamos particionar los datos por esa columna. Además, queremos ordenar los salarios de mayor a menor. En otras palabras, los datos de la partición deben ordenarse por salario en orden descendente.

La segunda sentencia SELECT (es decir, la consulta externa) selecciona todas las columnas del CTE y establece la condición en la cláusula WHERE para filtrar sólo los tres salarios más altos por departamento. Por último, la salida se ordena por departamento y rango salarial.

Este es el resultado:

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

23. Calcular la Diferencia entre Dos Filas (Delta) Utilizando Funciones de ventana

Este problema se resuelve más elegantemente utilizando la función de ventana LAG(). Recuerde, esta es una función que accede al valor de la fila anterior.

Los datos del ejemplo se encuentran en la tabla 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

Necesita mostrar los ingresos reales, el periodo de tiempo y la diferencia mensual (delta) entre el mes real y el anterior.

He aquí cómo hacerlo.

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

Un delta se calcula restando el mes anterior del mes real. Eso es exactamente lo que hace esta consulta. Para obtener los ingresos del mes anterior, la función LAG() resulta muy útil. La columna actual_revenue es el argumento de la función, ya que queremos acceder a los datos de ingresos de la fila anterior. Como en toda función de ventana, existe una cláusula OVER(). En ella, ordenamos los datos por periodo de forma ascendente porque es lógico calcular el delta cronológicamente.

Esta es la salida de la 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

La primera muestra el cambio de ingresos como NULL. Esto es lo esperado porque no hay un mes anterior del que deducir. En 2022_08, hubo un aumento de ingresos de 1.739.003,37 = ingresos del mes actual - ingresos del mes anterior = 10.487.444,59 - 8.748.441,22. La misma lógica se aplica a todos los demás resultados.

Puede encontrar ejemplos similares en el artículo sobre el cálculo de la diferencia entre dos filas en SQL.

24. Utilice Funciones de ventana para calcular un total acumulado

Un total actual o acumulado es la suma de una secuencia de números. El total acumulado se actualiza cada vez que se añade un nuevo valor a la secuencia. Piense en los ingresos mensuales: los ingresos totales del mes en curso incluirán la suma de los ingresos del mes en curso y de todos los meses anteriores.

La función de ventana perfecta para calcular un total corrido (suma acumulada) es SUM().

Veamos el método con los mismos datos que en la pregunta anterior. El objetivo es calcular los ingresos acumulados de todos los meses disponibles en 2022.

He aquí la solución:

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

La suma acumulada es la suma de los ingresos del mes actual y la suma de los ingresos de todos los meses anteriores. La función de ventana SUM() aplica esta lógica. El argumento de la función son los ingresos reales, porque eso es lo que estamos sumando. Para que sume todos los ingresos anteriores y los ingresos actuales, ordene los datos de forma ascendente por periodo. De nuevo, es lógico calcular un total acumulado desde el mes más antiguo hasta el más reciente.

Esto es lo que devuelve el código:

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

Los ingresos acumulados en la primera fila son los mismos que los ingresos reales. Para la segunda fila, el acumulado es de 19.235.885,81 = 8.748.441,22 + 10.487.444,59. En septiembre, el acumulado es de 26.717.342,96 = 8.748.441,22 + 10.487.444,59 + 7.481.457,15.

La misma lógica se aplica al resto de la tabla.

Puede obtener más información sobre el total móvil y cómo calcularlo aquí.

25. Encontrar una media móvil utilizando Funciones de ventana

Una media móvil se utiliza cuando se analiza una serie. Puede encontrarla con otros nombres, como media móvil, media móvil o media corrida. Es un promedio del valor actual y el número definido de valores precedentes. Por ejemplo, una media móvil de 7 días es la media del día actual y los seis días anteriores.

Para mostrarle cómo calcularla, utilizaremos la tabla eur_usd_rate tabla:

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

Calcularemos la media móvil de 3 días de la siguiente manera:

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;

Utilizamos la función de ventana AVG() en la columna exchange_rate. En la cláusula OVER(), los datos se ordenan por fecha en orden ascendente. ¡Ahora viene la parte importante! Recuerda que necesitamos una media móvil de 3 días que incluya la fila actual y las dos anteriores. Lo especificamos en la cláusula BETWEEN: le decimos a la función que incluya dos filas anteriores y la fila actual.

Veamos el 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

La media móvil de la primera fecha es igual al tipo de cambio porque 1.0666/1 = 1.0666. Para 2023-01-02, se calcula así: (1,0666 + 1,0683)/2 = 1,0675.

En 2023-01-03, por fin tendremos tres fechas: (1,0666 + 1,0683 + 1,0545)/3 = 1,0631. Esta lógica se aplica al resto de las fechas.

Puedes encontrar más ejemplos en este artículo sobre el cálculo de medias móviles en SQL.

26. ¿Cuál es la diferencia entre ROWS y RANGE?

Tanto ROWS como RANGE son cláusulas utilizadas para definir un marco de ventana. Limitan el rango de datos utilizado en una función de ventana dentro de una partición.

La cláusula ROWS limita las filas. Se utiliza para especificar un número fijo de filas que preceden y siguen a la fila actual. El valor de las filas no se tiene en cuenta.

La cláusula RANGE limita el rango de datos de forma lógica. En otras palabras, limita los datos teniendo en cuenta los valores de las filas precedentes y siguientes en relación con la fila actual. No tiene en cuenta el número de filas.

¿Cómo se utilizan en la práctica? Lea nuestro artículo sobre FILAS y RANGO para más detalles.

27. Utilice una Consulta Recursiva para Encontrar a Todos los Empleados Bajo un Gerente Dado.

Una consulta recursiva es un tipo especial de CTE que se referencia a sí misma hasta llegar al final de la recursión. Es ideal para consultar datos gráficos o estructuras jerárquicas.

Un ejemplo de esto último es la estructura organizativa de la empresa, que se muestra en la tabla company_organization tabla:

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

Esta tabla muestra todos los empleados y el ID de su responsable directo.

La tarea aquí es utilizar la recursividad y devolver todos los subordinados directos e indirectos de Jack Simmons. Además, vamos a añadir una columna que se puede utilizar para distinguir los diferentes niveles de organización. Aquí está el 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 la recursión utilizando WITH RECURSIVE. (Si está trabajando en MS SQL Server, utilice sólo WITH.)

El primer SELECT de un CTE se denomina miembro de anclaje. En él, hacemos referencia al conjunto de datos y seleccionamos todas las columnas necesarias. Además, creamos una nueva columna con el valor cero y filtramos los datos en la cláusula WHERE. ¿Por qué utilizar esta condición exacta en WHERE? Porque el ID de empleado de Jack Simmons es 5529, y queremos mostrarle a él y a sus subordinados.

A continuación viene UNION ALL, que combina los resultados de la consulta ancla y la consulta recursiva, es decir, la segunda sentencia SELECT.

Queremos que la recursividad llegue hasta el final de la estructura organizativa. En la consulta recursiva, unimos el CTE con la tabla company_organization tabla. Volvemos a enumerar todas las columnas necesarias de esta última tabla. Además, queremos añadir un nivel organizativo con cada recursión.

Por último, llegamos a la consulta que utiliza el CTE. Esta consulta sirve para obtener datos tanto del CTE como de la tabla. company_organization tabla. Utilizamos el CTE para mostrar los datos de los empleados. La otra tabla se utiliza para mostrar la información del responsable directo.

Ejecutando el código obtendremos este resultado:

employee_idemployee_first_nameemployee_last_namedirect_manager_iddirect_manager_first_namedirect_manager_last_namelevel
5529JackSimmons5125AlbertKoch0
5952HarryRaitt5529JackSimmons1
5500JackieCarlin5529JackSimmons1
5012BonniePresley5952HarryRaitt2
5118SteveNicks5952HarryRaitt2

La tabla de arriba muestra que el jefe directo de Jack Simmons es Albert Koch. Los subordinados directos de Simmons son Harry Raitt y Jackie Carlin. Los subordinados indirectos son Bonnie Presley y Steve Nicks. Su jefe directo es Harry Raitt.

Puede encontrar otras variaciones de esta tarea en el artículo sobre CTE recursivas.

Puedes aprender más sobre las funciones ventana en este artículo dedicado a las preguntas de entrevista sobre funciones ventana en SQL.

¿Estás preparado para superar las preguntas de la entrevista de trabajo de SQL?

Escribir esta guía no ha sido fácil. Pero vale la pena cuando pensamos en hacer más fácil tu entrevista de trabajo de SQL avanzado.

Por supuesto, estas no son todas las preguntas que te podrían hacer en la entrevista. Sin embargo, creemos que esta selección te proporcionará una base firme de los conceptos de SQL avanzado más importantes. Además, esta guía es lo suficientemente breve como para que puedas repasarla rápidamente antes de la entrevista y refrescar la memoria.

Para más refrescos sobre temas avanzados de SQL, prueba nuestro Funciones de ventana curso o SQL avanzado curso.