10 Ejercicios de Subconsultas Correlacionadas con Soluciones

Las subconsultas correlacionadas son una potente función de SQL esencial para el análisis avanzado de datos. Este artículo proporciona 10 ejercicios prácticos que le ayudarán a dominar las subconsultas correlacionadas.

SQL es una habilidad fundamental para cualquiera que trabaje con datos, ya sea como analista de datos, desarrollador de SQL, ingeniero de datos o cualquier otra profesión relacionada. Dominar SQL implica algo más que comprender los conceptos básicos. También requiere aprender funciones avanzadas de SQL, como las subconsultas.

Una subconsulta es una consulta SQL anidada dentro de una consulta mayor. Hay muchos tipos diferentes de subconsultas. Una subconsulta correlacionada es un tipo de subconsulta que hace referencia a la consulta externa y no puede ejecutarse de forma independiente. Las subconsultas, y en particular las subconsultas correlacionadas, pueden ser un reto para los estudiantes de SQL.

Anteriormente, proporcionamos una colección de ejercicios de subconsultas en nuestro artículo: Práctica de Subconsultas SQL: 15 Ejercicios con Soluciones. En este artículo, queremos darte un conjunto de ejercicios de práctica específicamente sobre el tema de las subconsultas correlacionadas. Los ejercicios de este artículo han sido tomados de nuestros cursos interactivos Basic SQL Practice: A Store y SQL Practice: University. Ambos cursos forman parte del track "Ejercicio de SQL", que ofrece múltiples cursos de práctica SQL. Todos los cursos del track se basan en escenarios reales y están organizados por temas SQL para ayudarte a centrarte en tu área de interés.

Ejercicios sobre una base de datos de almacén

El primer conjunto de ejercicios se basa en la base de datos de una tienda en línea. Estos ejercicios están tomados de nuestro curso Basic SQL Practice: A Store: Tienda. Empezaremos con poco y trabajaremos con sólo dos tablas: product y category.

La tabla product contiene una lista de los productos disponibles en la tienda.

• product_id - el ID del producto.
• product_name - el nombre del producto.
• category_id - el ID de la categoría del producto. Le ayuda a conectarse con la tabla de categorías.
• quantity_per_unit - la cantidad de artículos del producto en una unidad.
• unit_price - el precio del producto
• discontinued - la información sobre si el producto aún está disponible en la tienda (el valor FALSE), o si ha sido descatalogado (TRUE).

La tabla category contiene información sobre las categorías de los productos:

• category_id - el ID de la categoría
• name - el nombre de la categoría
• description - la descripción opcional de la categoría

Ejercicio 1: Productos caros en cada categoría

Ejercicio: Encuentre los productos que son más caros que el precio medio de los productos de su propia categoría. Incluya en el resultado el nombre de la categoría, el nombre del producto y el precio unitario.

Solución:

SELECT
  c.name,
  p.product_name,
  p.unit_price
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  );

Explicación de la solución:

En la solución, utilizamos una subconsulta correlacionada para calcular el precio unitario medio de los productos de la misma categoría que el producto procesado por la consulta externa. Esta subconsulta hace referencia a la categoría, denominada c, de la consulta externa. Identifica los productos de esta categoría y calcula su precio medio. Los productos que cuestan más que la media de su categoría se filtran utilizando esta media. A continuación, la consulta externa recupera y muestra el nombre de la categoría, el nombre del producto y el precio unitario de estos productos.

Ejercicio 2: Número de productos caros en cada categoría

Ejercicio: Mostrar los nombres de las categorías y el número de productos de esta categoría con un precio unitario superior al precio medio de un producto de esta categoría. Muestre sólo las categorías que tengan dicho(s) producto(s). Muestre dos columnas: name (el nombre de la categoría), y expensive_products (el número de productos que cuestan más que el producto medio de esta categoría).

Solución:

SELECT
  c.name,
  COUNT(*) AS expensive_products
FROM category AS c
JOIN product AS p
  ON c.category_id = p.category_id
WHERE p.unit_price > (
  SELECT
    AVG(unit_price)
  FROM product
  JOIN category
    ON product.category_id = category.category_id
  WHERE category.category_id = c.category_id
  )
GROUP BY
  c.category_id,
  c.name;

Explicación de la solución:

Este ejercicio es similar al anterior. También necesitamos encontrar productos con un precio unitario superior al precio medio de su categoría. Sin embargo, esta vez queremos contar los productos de cada categoría.

En la solución, utilizamos la misma subconsulta correlacionada que antes: calculamos el precio unitario medio de los productos de la misma categoría que el producto procesado por la consulta externa. Esta media se utiliza en la consulta principal para filtrar únicamente los productos cuyo precio unitario es superior a la media de la categoría. La consulta principal cuenta estos productos mediante COUNT(*) y agrupa los resultados por categoría con GROUP BY. El resultado final muestra el nombre de la categoría y el recuento de productos.

Ejercicio 3: Productos descatalogados, continuados y todos los productos de una categoría

Ejercicio: Para cada categoría, muestre su name, el número de productos discontinuados en esta categoría (discontinued_products), el número de productos continuados en esta categoría (continued_products), y el número de todos los productos en esta categoría (all_products).

Solución:

SELECT
  c.name,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS TRUE) AS discontinued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id AND discontinued IS FALSE) AS continued_products,
  (SELECT COUNT(*) FROM product WHERE category_id = c.category_id) AS all_products
FROM category c;

Explicación de la solución:

Aquí utilizamos tres subconsultas correlacionadas en la sentencia SELECT para obtener recuentos de productos basados en su estado continuado/descontinuado para cada categoría. Cada subconsulta se correlaciona con la consulta principal a través de category_id para que los recuentos sean correctos para cada categoría.

La primera subconsulta cuenta el número de productos de una categoría cuyo indicador discontinued es TRUE. Esto nos da un recuento total de todos los productos que ya no están disponibles en cada categoría. La segunda subconsulta cuenta el número de productos que siguen activos en cada categoría, utilizando un método similar. La tercera subconsulta simplemente cuenta el número de todos los productos de una categoría.

Este ejercicio podría resolverse alternativamente sin subconsultas, utilizando una combinación de CASE WHEN con SUM y GROUP BY. Como ejercicio alternativo: ¿puede resolver este ejercicio de ambas maneras?

Más Tablas en la Base de Datos de la Tienda: Tablas de Clientes y Compras

En los siguientes ejercicios, trabajaremos con tablas adicionales de la base de datos de la tienda relacionadas con las compras: customer, purchase, y purchase_item.

La tabla customer contiene la información de los clientes. Tiene las siguientes columnas

• customer_id - el ID del cliente.
• contact_name - el nombre completo del cliente.
• contact_email - el correo electrónico del cliente.

La tabla purchase contiene la información sobre cada pedido:

• purchase_id - el ID de la compra.
• customer_id - el ID del cliente.
• total_price - el precio total del pedido.
• purchase_date - la fecha y hora de la compra.

La tabla purchase_item relaciona las compras con los productos. La tabla contiene las siguientes columnas

• purchase_id - ID de la compra.
• product_id - el ID del producto comprado.
• unit_price - el precio de una unidad de un producto.
• quantity - el número de unidades compradas de un producto.

Ejercicio 4: Productos en categorías

Ejercicio: Para cada producto comprado, muestre el nombre del producto, la cantidad máxima en la que se compró y el número de compras de este tipo (de este producto en esta cantidad máxima). Muestre tres columnas: product_name, quantity, y purchases_number.

Solución:

SELECT 
  product_name,
  quantity,
  COUNT(purchase_id) AS purchases_number
FROM purchase_item pi
JOIN product p
  ON pi.product_id = p.product_id 
WHERE quantity = (SELECT MAX(quantity) FROM purchase_item WHERE product_id = pi.product_id)
GROUP BY
  pi.product_id,
  product_name,
  quantity;

Explicación de la solución:

Aquí queremos encontrar la cantidad máxima en la que se compró cada producto y cuántas veces se produjeron dichas compras. Utilizamos una subconsulta correlacionada y GROUP BY para lograr este resultado.

En primer lugar, utilizamos una subconsulta correlacionada en WHERE para determinar la cantidad máxima en que se ha comprado cada producto. A continuación, en la consulta externa utilizamos este valor para encontrar las compras en las que la cantidad es igual a esta cantidad máxima. Por último, la consulta externa agrupa los resultados por product_id, product_name, y quantity y utiliza la función agregada COUNT(purchase_id) para calcular el número de compras de cada producto en la cantidad máxima.

Ejercicio 5: Porcentaje de dinero gastado por el cliente en la compra

Ejercicio: Para cada cliente que haya realizado alguna compra, muestra el ID de cada compra realizada por este cliente, el porcentaje del dinero gastado en esa compra en relación con todo el dinero gastado por ese cliente. Redondee los porcentajes a números enteros. Muestre tres columnas: contact_name, purchase_id y percentage.

Solución:

SELECT
  contact_name,
  purchase_id,
  ROUND(total_price * 100.0 / (SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id)) AS percentage
FROM purchase p
JOIN customer 
  ON p.customer_id = customer.customer_id;

Explicación de la solución:

En la consulta externa listamos cada compra realizada por cada cliente. Unimos las tablas purchase y customer para mostrar el nombre de contacto del cliente y el ID de la compra. Utilizamos una subconsulta correlacionada para averiguar la cantidad total de dinero gastada por el cliente actual. Usamos la cantidad encontrada por la subconsulta para calcular qué porcentaje del gasto total representa la compra actual.

Ejercicio 6: Clientes con compras superiores a su importe medio de compra

Ejercicio: Buscar clientes cuya última compra total haya sido superior a su importe medio de compra. Mostrar el nombre del cliente y el total de la última compra.

Solución:

SELECT 
  c.contact_name, 
  p.total_price AS last_purchase_total
FROM purchase p
JOIN customer c 
ON p.customer_id = c.customer_id
WHERE p.purchase_date = (
    SELECT MAX(lp.purchase_date)
    FROM purchase lp
    WHERE lp.customer_id = p.customer_id
  )
AND p.total_price > (
    SELECT AVG(ap.total_price)
    FROM purchase ap
    WHERE ap.customer_id = p.customer_id
);

Explicación de la solución:

Este ejercicio necesita dos subconsultas. La primera subconsulta se utiliza para encontrar la fecha de la compra más reciente de cada cliente, de forma similar a la subconsulta del Ejercicio 4, pero centrándose en la fecha de la transacción en lugar de en las cantidades. La segunda subconsulta calcula el precio medio total de las compras del cliente y se utiliza para filtrar las compras cuyo precio es superior a la media. Ambas subconsultas se combinan en la cláusula WHERE utilizando un operador AND.

Ejercicios sobre el Modelo de Datos de la Universidad

El segundo conjunto de ejercicios de este artículo se basa en la base de datos de una universidad. Estos ejercicios están tomados de nuestro curso SQL Practice: University. La base de datos de la universidad tiene 4 tablas.

La tabla course contiene información sobre los cursos ofrecidos en la universidad y los itinerarios de aprendizaje a los que están asociados:

• id - Un ID único para cada curso.
• title - El nombre del curso.
• lecture_hours - Número total de horas lectivas del curso.
• tutorial_hours - Número total de horas de tutoría del curso.

La tabla student contiene toda la información sobre cada estudiante que asiste a la universidad:

• id - Un identificador único para cada estudiante.
• first_name - Nombre del alumno.
• last_name - El apellido del alumno.

La tabla course_edition contiene información sobre qué profesores imparten cada curso en cada semestre:

• id - El ID de la edición del curso.
• course_id - El ID del curso.
• academic_semester - El año natural y el trimestre (otoño o primavera) del semestre.
• lecturer - El nombre del profesor que imparte el curso.

La tabla course_enrollment contiene la información sobre el alumno matriculado en una edición del curso:

• course_edition_id - El ID de la edición del curso.
• student_id - El ID del alumno.
• midterm_grade - La calificación parcial que recibió el alumno.
• final_grade - La calificación final que recibió el alumno.
• course_letter_grade - La calificación del curso, en forma de letra (A+, A, A-, B+, B, B-, C+, C, C-, D+, D, D-, o F).
• passed - Si el alumno ha aprobado (TRUE) o suspendido (FALSE) el curso.

Si un estudiante aún no ha realizado su examen, es posible que vea algunos campos NULL en la tabla.

Ejercicio 7: Alumnos con nota superior a la media

Ejercicio: Encuentre los alumnos que obtuvieron una nota final superior a la media en cualquiera de las ediciones del curso al que asistieron. Mostrar: nombre y apellidos del alumno, título del curso y su final_grade. Mostrar sólo los alumnos cuya nota final en esta edición del curso haya sido superior a la nota final media de esta edición del curso.

Solución:

SELECT
  first_name,
  last_name,
  title,
  final_grade
FROM course_enrollment AS c_en
JOIN student AS s
  ON c_en.student_id = s.id
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_e
  WHERE c_e.course_edition_id = c_en.course_edition_id
);

Explicación de la solución:

La solución utiliza una única consulta correlacionada. En la subconsulta correlacionada encontramos la nota final media de la edición del curso procesada en la consulta principal. A continuación, utilizamos la media encontrada en la subconsulta para filtrar las inscripciones en cursos en los que la nota final del alumno fue superior a la media. En la consulta principal unimos las tablas course_enrollment, student, course_edition, y course para combinar el nombre y apellidos del alumno con el título del curso y la calificación que obtuvo.

Ejercicio 8: Número de alumnos con la misma nota o superior

Ejercicio:Para cada nota final de la tabla course_enrollment, cuente el número de estudiantes que obtuvieron exactamente esa nota o por encima de ella. Muestra dos columnas: final_grade y students_number.

Solución:

SELECT DISTINCT
  final_grade,
  (SELECT COUNT (student_id)
   FROM course_enrollment
   WHERE final_grade >= c.final_grade) AS students_number
FROM course_enrollment c;

Explicación de la solución:

La consulta principal selecciona cada nota final única (distinta) de la tabla course_enrollment. Para cada una de estas calificaciones, la subconsulta correlacionada cuenta el número de estudiantes cuyas calificaciones finales son iguales o superiores a la calificación procesada por la consulta externa. El resultado se muestra en dos columnas: final_grade, que muestra la calificación considerada, y students_number, que indica el número de alumnos que obtuvieron esa calificación o una superior.

Ejercicio 9: Alumnos aprobados y no aprobados

Ejercicio: Divida a los alumnos de cada edición del curso en dos grupos: los que aprobaron el curso y los que no. Para cada grupo, muestre las siguientes columnas:

• course_edition_id
• passed
• average_final_grade - La media de los alumnos de este grupo (aprobados o no aprobados), redondeada a 2 decimales.
• average_edition_grade - La media global de los alumnos de esta edición del curso, redondeada a 2 decimales.

Solución:

SELECT
  course_edition_id,
  passed,
  ROUND(AVG(final_grade), 2) AS average_final_grade,
  (SELECT ROUND(AVG(final_grade), 2)
         FROM course_enrollment
         WHERE course_edition_id = c_e.course_edition_id) AS average_edition_grade
FROM course_enrollment c_e
GROUP BY 
  course_edition_id, 
  passed;

Explicación de la solución:

La consulta externa selecciona el ID de edición del curso, el valor passed y la nota media final de este grupo de estudiantes (ya sea "aprobado" o "no aprobado") utilizando la cláusula GROUP BY. También utiliza una subconsulta correlacionada para hallar la nota final media global de la edición del curso procesada en la consulta externa y añade esta media al resultado de la consulta. De este modo, puede comparar la calificación final de cada grupo con la media global.

Ejercicio 10: Promedios de nuevo

Ejercicio: Para cada edición del curso, muestre los siguientes datos:

• title
• average_result - La nota final media de los alumnos con una nota final superior a la media de la edición del curso. Redondee el resultado a 0 decimales (es decir, un número entero).
• results_better_than_average - El número de alumnos cuya nota final es superior a la nota final media de esta edición del curso.

Solución:

SELECT
  c.title,
  ROUND(AVG(final_grade)) AS average_result,
  COUNT (student_id) AS results_better_than_average
FROM course_enrollment AS c_en
JOIN course_edition AS c_ed
  ON c_en.course_edition_id = c_ed.id
JOIN course AS c
  ON c_ed.course_id = c.id
WHERE final_grade > (
  SELECT AVG(final_grade)
  FROM course_enrollment AS c_en2
  WHERE c_en2.course_edition_id = c_en.course_edition_id
)
GROUP BY 
  c.id, 
  c.title;

Explicación de la solución:

En la subconsulta correlacionada calculamos la nota final media de la edición del curso procesada por la consulta externa. A continuación, utilizamos este valor para filtrar los alumnos cuya calificación final en esta edición del curso fue superior a la media. En la consulta externa mostramos el título del curso, el número de estudiantes con la nota final superior a la media y la nota final media de este grupo de estudiantes.

Conclusión y próximos pasos

En este artículo, hemos explorado varios ejercicios de subconsultas correlacionadas para mejorar su comprensión de esta importante función de SQL. Para aquellos que buscan desarrollar aún más sus habilidades SQL, recomendamos nuestra pista "Ejercicio de SQL". Incluye 10 cursos prácticos de SQL diferentes. Cada curso está lleno de ejercicios prácticos basados en escenarios reales y está organizado por temas SQL para ayudarle a centrarse en su área de interés.

Además, le recomendamos que eche un vistazo a nuestro paquete SQL Todo, para siempre , la mejor oferta para los estudiantes de SQL. Esta oferta de pago único proporciona acceso de por vida a todos los cursos actuales y futuros de nuestro catálogo, incluido todo lo que hay en la pista 'Ejercicio de SQL'. Se trata de un recurso inestimable para cualquiera que desee convertirse en un experto en SQL.