Práctica de Subconsultas SQL: 15 Ejercicios con Soluciones

Las subconsultas suelen ser un reto para los principiantes. La práctica hace al maestro, así que acompáñanos en estos 15 ejercicios de práctica de subconsultas SQL.

En SQL, una subconsulta es una consulta anidada dentro de otra consulta. Simplifica la creación de consultas complejas para recuperar datos que cumplen condiciones específicas de varias tablas.

En este artículo, presentamos varias formas de emplear subconsultas para crear consultas complejas. Comenzaremos presentando las subconsultas SQL junto con los casos de uso más comunes. A continuación, le guiaremos a través de 15 ejercicios prácticos de subconsultas SQL, con soluciones y explicaciones.

Si nunca ha oído hablar de las subconsultas SQL, consulte nuestra Guía gratuita para principiantes sobre la subconsulta SQL. También puede obtener experiencia práctica en la creación de subconsultas en nuestro curso SQL para principiantes curso.

Pero si conoces lo básico y estás listo para mejorar tus conocimientos, comencemos a practicar las subconsultas SQL.

Subconsultas SQL Esenciales

Para recapitular, una subconsulta SQL es una sentencia SELECT incrustada en otra sentencia SELECT. Puedes pensar en las subconsultas como bloques de construcción que componen consultas complejas: nos permiten dividir tareas complicadas en partes más pequeñas y hacen que el código sea más fácil de leer.

Imagina que pones una pregunta dentro de otra: así funcionan las subconsultas. Con la ayuda de las subconsultas, puede obtener información específica de diferentes tablas filtrada por diferentes condiciones de una sola vez.

Estos son algunos de los usos más comunes de las subconsultas SQL:

• Filtrado de datos: Utilice subconsultas en la cláusula WHERE para filtrar datos basados en condiciones específicas, haciendo sus consultas más dinámicas. Cubierto en los ejercicios prácticos de subconsultas 1, 2, 3, 4, 8 y 9.
• Agregaciones anidadas: Emplee subconsultas para realizar agregaciones dentro de agregaciones, lo que permite realizar cálculos más complejos. Cubierto en los ejercicios prácticos de subconsultas 5, 6y 7.
• Comprobación de existencia: Determinar si un valor específico existe en otra tabla utilizando subconsultas con EXISTS o IN Cubierto en los ejercicios prácticos de subconsultas 1, 2 y 14.
• Subconsultascorrelacionadas: Cree subconsultas que hagan referencia a columnas de la consulta externa, permitiendo el filtrado en función del contexto. Cubierto en los ejercicios prácticos de subconsulta 10, 11, 12 y 13.
• Subconsultaen la cláusula SELECT: Incluya una subconsulta en la cláusula SELECT para recuperar un único valor o conjunto de valores que puedan utilizarse en la consulta principal. Cubierto en los ejercicios prácticos de subconsulta 10 y 13.
• Subconsulta en la cláusula FROM: Utilice una subconsulta en la cláusula FROM para crear una tabla temporal que permita realizar uniones más complejas. Cubierto en los ejercicios de práctica de subconsultas 14 y 15.
  

Ejercicios de Subconsulta SQL

Conjunto de Datos: Orquestas

Los siguientes ejercicios utilizan el conjunto de datos orchestras que contiene tres tablas.

• La tabla orchestras almacena todas las orquestas. Las columnas son id, name, rating, city_origin, country_origin, y year en las que se fundó la orquesta.
• La tabla concerts tabla contiene todos los conciertos interpretados por las orquestas. Las columnas son id, city, country, year, rating, y orchestra_id (hace referencia a la orchestras tabla).
• La tabla members contiene los miembros (es decir, los músicos) de cada orquesta. Las columnas son id, name, position (es decir, el instrumento que tocan), wage, experience, y orchestra_id (hace referencia a la tabla). orchestras tabla).

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL. Los siguientes ejercicios proceden del Ejercicios prácticos de SQL curso.

Ejercicio 1: Seleccionar orquestas con ciudad de origen donde se celebró un concierto en 2013

Ejercicio:

Seleccionar los nombres de todas las orquestas que tengan la misma ciudad de origen que alguna ciudad en la que se haya celebrado algún concierto en 2013.

Solución:

SELECT name
FROM orchestras
WHERE city_origin IN (SELECT city FROM concerts WHERE year = 2013);

Explicación de la solución:

Nuestro objetivo es seleccionar nombres de orquestas que cumplan una determinada condición, por lo que empezamos por SELECT name FROM orchestras. A continuación, vamos a imponer la condición en la columna city_origin, tal y como se menciona en las instrucciones.

Queremos seleccionar sólo las orquestas cuya ciudad de origen pertenezca al grupo de ciudades donde se realizaron conciertos en el año 2013. Para crear esta condición en la cláusula WHERE, utilizamos la subconsulta SQL.

Creemos una (sub)consulta que seleccione todas las ciudades donde se ofrecieron conciertos en 2013: SELECT city FROM concerts WHERE year = 2013. Devuelve una columna que contiene los nombres de las ciudades.

Para asegurarnos de que la ciudad de origen pertenece a las ciudades devueltas por la subconsulta, utilizamos el operador IN.

Ejercicio 2: Seleccionar miembros que pertenezcan a orquestas muy valoradas

Ejercicio:

Seleccionar los nombres y cargos (es decir, el instrumento que tocan) de todos los miembros de orquesta que tengan más de 10 años de experiencia y no pertenezcan a orquestas con una calificación inferior a 8,0.

Solución:

SELECT
  name,
  position
FROM members
WHERE experience > 10
AND orchestra_id NOT IN (SELECT id FROM orchestras WHERE rating < 8.0);

Explicación de la solución:

Queremos seleccionar los nombres y cargos de los miembros de la orquesta que cumplan las condiciones indicadas en las instrucciones, por lo que comenzamos con SELECT name, position FROM members.

A continuación, imponemos condiciones de filtrado sobre los años de experiencia de los miembros y las orquestas a las que pertenecen. Queremos seleccionar a los miembros cuyos años de experiencia superen los 10 años. Así, añadimos la primera condición de la cláusula WHERE: experience > 10.

No queremos seleccionar miembros que pertenezcan a orquestas con puntuaciones inferiores a 8,0. Creemos una (sub)consulta que seleccione todas las orquestas con una valoración inferior a 8,0: SELECT id FROM orchestras WHERE rating < 8.0.

Para asegurarnos de que las orquestas no pertenecen a las orquestas listadas por esta subconsulta, utilizamos el operador NOT IN.

Ejercicio 3: Seleccionar los miembros que ganan más que los violinistas

Ejercicio:

Mostrar el nombre y el cargo de los miembros de la orquesta que ganan más que el salario medio de todos los violinistas.

Solución:

SELECT name, position
FROM members
WHERE wage > (SELECT AVG(wage)
              FROM members
              WHERE position = 'violin');

Explicación de la solución:

Seleccionamos las columnas nombre y position de la members tabla.

Utilizamos una subconsulta para averiguar el salario medio de todos los violinistas: SELECT AVG(wage) FROM members WHERE position = 'violin'.

Para asegurarnos de que seleccionamos a los miembros de la orquesta cuyo salario es mayor que el salario medio de todos los violinistas, imponemos una condición a la columna salario para que sea mayor que el valor medio devuelto por la subconsulta.

Ejercicio 4: Seleccionar orquestas de alta calificación más recientes que la Orquesta de Cámara

Ejercicio:

Mostrar los nombres de las orquestas que se crearon después de la 'Orquesta de Cámara' y tienen una valoración superior a 7.5.

Solución:

SELECT name
FROM orchestras 
WHERE year > (SELECT year FROM orchestras 
              WHERE name = 'Chamber Orchestra') 
AND rating > 7.5;

Explicación de la solución:

Primero, seleccionamos los nombres de la orchestras tabla.

A continuación, creamos una subconsulta que devuelve el año de creación de la orquesta de cámara.

Como queremos listar orquestas creadas después de la Orquesta de Cámara, imponemos una condición a la columna año para que sea mayor que lo que nos devuelva esta subconsulta.

Por último, definimos la condición en la columna rating para que sea mayor que 7.5.

Ejercicio 5: Seleccionar intérpretes en grandes orquestas

Ejercicio:

Mostrar el nombre y número de integrantes de cada orquesta que tenga más integrantes que la media de integrantes de todas las orquestas de la tabla.

Solución:

SELECT 
  o.name,
  COUNT(m.id)
FROM orchestras o
JOIN members m
ON o.id = m.orchestra_id
GROUP BY o.name
HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d);

Explicación de la solución:

Para mostrar el nombre de la orquesta, simplemente seleccionamos la columna nombre de la orchestras tabla. Y para mostrar el número de miembros de cada orquesta, debemos unir la tabla orchestras con la tabla members en su columna común (el ID de la orquesta). A continuación, utilizamos la función COUNT() para contar todos los miembros (COUNT(m.id)), agrupando por la columna nombre de la tabla orchestras tabla (GROUP BY o.name).

Queremos elegir sólo las orquestas que tengan más miembros que la media. Por lo tanto, debemos imponer una condición a COUNT(m.id) para que sea mayor que el número medio de miembros. Para imponer una condición a una función agregada, debemos utilizar la cláusula HAVING que sigue a la cláusula GROUP BY.

Podemos encontrar el número medio de miembros de la orquesta utilizando subconsultas. Para ser exactos, utilizaremos una subconsulta anidada, es decir, una subconsulta dentro de otra subconsulta.

• La subconsulta interna calcula el número de miembros de cada orquesta utilizando la función agregada COUNT():

SELECT orchestra_id, COUNT(id) FROM members GROUP BY orchestra_id

• La subconsulta externa calcula la media de todos los valores COUNT(id) devueltos por la subconsulta interna utilizando la función agregada AVG():

SELECT AVG(d.count) FROM (<inner subquery>) AS d

Finalmente, la subconsulta total es:

(SELECT AVG(d.count) 
 FROM (SELECT orchestra_id, COUNT(id) 
       FROM members GROUP BY orchestra_id) AS d)

Y devuelve el número medio de miembros por orquesta.

Ahora que tenemos el número medio de miembros de la orquesta, podemos imponer una condición en COUNT(m.id) para asegurarnos de que es mayor que el número medio de miembros de la orquesta:

HAVING COUNT(m.id) > (SELECT AVG(d.count) 
                      FROM (SELECT orchestra_id, COUNT(id) 
                            FROM members GROUP BY orchestra_id) AS d)

Conjunto de datos: Universidad

Los siguientes ejercicios utilizan el conjunto de datos de la universidad, que contiene seis tablas.

• La tabla course almacena información sobre los cursos. Las columnas son id, title, learning_path, short_description, lecture_hours, tutorial_hours, ects_points, has_exam, y has_project.
• La tabla lecturer almacena información sobre los profesores. Las columnas son id, first_name, last_name, degree, y email.
• La tabla student contiene información sobre los estudiantes. Las columnas son id, first_name, last_name, email, birth_date, y start_date.
• La tabla academic_semester contiene información sobre cada semestre de estudios. Las columnas son id, calendar_year, term, start_date, y end_date.
• La tabla course_edition tabla contiene información sobre qué profesores imparten cada curso en cada semestre. Las columnas son id, course_id (hace referencia a la course tabla), academic_semester_id (hace referencia a la academic_semester tabla), y lecturer_id (hace referencia a la lecturer tabla).
• La tabla course_enrollment contiene información sobre los estudiantes matriculados en cada curso. Las columnas son course_edition_id (hace referencia a la course_edition tabla), student_id (hace referencia a la student tabla), midterm_grade, final_grade, course_letter_grade, y passed.

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL. Los siguientes ejercicios proceden del curso universitario básico Ejercicio de SQL .

Ejercicio 6: Seleccionar cursos del trimestre de primavera

Ejercicio:

Mostrar los IDs y títulos de todos los cursos que tuvieron lugar durante cualquier trimestre de primavera.

Solución:

SELECT
  id,
  title
FROM course
WHERE id = ANY (SELECT course_id
                FROM course_edition ce
                JOIN academic_semester asem
                ON ce.academic_semester_id = asem.id
                WHERE asem.term = 'spring');

Explicación de la solución:

Empezamos seleccionando IDs y títulos de la course de la tabla. En la cláusula WHERE, debemos imponer una condición en la columna id de la tabla, utilizando subconsultas y el operador course utilizando subconsultas y el operador ANY.

Queremos seleccionar los cursos que tuvieron lugar durante el trimestre de primavera al menos una vez, así que empecemos creando una subconsulta que seleccione dichos ID de curso. Tenga en cuenta que debemos unir la tabla course_edition con la tabla academic_semester en su columna común (academic_semester_id y id, respectivamente) para poder filtrar los cursos del trimestre de primavera.

El operador ANY devuelve verdadero si al menos un valor devuelto por la subconsulta cumple la condición.

Ilustrémoslo:

La línea en verde devuelve verdadero porque 9 es igual a uno de los números devueltos por la subconsulta.

La línea en rojo devuelve falso porque 3 no es igual a ninguno de los números devueltos por la subconsulta.

Ejercicio 7: Seleccionar todos los alumnos que aprobaron al menos un curso

Ejercicio:

Seleccionar los IDs y nombres de los estudiantes que aprobaron al menos un curso.

Solución:

SELECT
  id,
  first_name,
  last_name
FROM student
WHERE id = ANY (SELECT student_id
                FROM course_enrollment
                WHERE passed = 't');

Explicación de la solución:

Empezamos seleccionando IDs y nombres de la student tabla. En la cláusula WHERE, debemos imponer una condición sobre la columna id de la student tabla utilizando subconsultas y el operador ANY.

Queremos seleccionar los estudiantes que aprobaron al menos un curso, así que comencemos creando una subconsulta que seleccione los ID de todos los estudiantes que aprobaron uno o más cursos: SELECT student_id FROM course_enrollment WHERE passed = 't'

El operador ANY devuelve verdadero si al menos un valor devuelto por la subconsulta cumple la condición.

Ilustrémoslo:

Las líneas en verde devuelven verdadero porque tanto 5 como 8 pertenecen a los IDs devueltos por la subconsulta.

La línea en rojo devuelve falso porque 3 no pertenece a los IDs devueltos por la subconsulta.

Ejercicio 8: Seleccionar el Profesor o Profesores que Imparten Menos Cursos

Ejercicio:

Encuentre el/los profesor/es con el menor número de cursos impartidos. Muestre el nombre y apellidos del profesor y el número de cursos que imparte (como no_of_courses).

Solución:

SELECT
  l.first_name,
  l.last_name,
  COUNT(ce.id) AS no_of_courses
FROM lecturer l
JOIN course_edition ce
ON l.id = ce.lecturer_id
GROUP BY l.first_name, l.last_name
HAVING COUNT(ce.id) 
            <= ALL (SELECT COUNT(id)
                    FROM course_edition
                    GROUP BY lecturer_id);

Explicación de la solución:

Seleccionamos los nombres de la tabla lecturer uniéndola con la tabla course_edition por el ID del profesor. Contamos las filas de la tabla course_edition tabla para cada profesor: COUNT(ce.id) AS no_of_courses. Así, agrupamos por nombre de profesor.

Para asegurarnos de que seleccionamos sólo al profesor o profesores con menos cursos impartidos, debemos imponer una condición en COUNT(ce.id) para que sea menor o igual que el número de cursos impartidos por cada profesor.

Creemos una subconsulta que seleccione el número de cursos impartidos por cada profesor: SELECT COUNT(id) FROM course_edition GROUP BY lecturer_id.

El operador ALL devuelve verdadero si la condición se cumple en todas las filas devueltas por la subconsulta. En este caso, queremos asegurarnos de que sólo seleccionamos al profesor o profesores cuyo no_of_courses es menor que el de todos los demás profesores (y sólo igual al menor número de cursos impartidos, de ahí el signo menor/igual).

Ilustrémoslo:

La línea en verde devuelve verdadero porque 4 es menor que cada número devuelto por la subconsulta e igual sólo al número más pequeño devuelto por la subconsulta.

La línea en rojo devuelve falso porque 8 no es menor que todos los números devueltos por la subconsulta (es decir, 8 > 4, 8 > 5, 8 > 6).

Ejercicio 9: Seleccionar los alumnos matriculados en el mayor número de cursos

Ejercicio:

Encuentre el/los estudiante/s matriculado/s en el mayor número de ediciones de cursos. Muestre el ID del estudiante, su nombre y apellidos, y el número de ediciones de curso en las que ha estado matriculado (como no_of_course_ed).

Solución:

SELECT
  student_id,
  first_name,
  last_name,
  COUNT(course_edition_id) AS no_of_course_ed
FROM course_enrollment
JOIN student
ON course_enrollment.student_id = student.id
GROUP BY student_id, first_name, last_name
HAVING COUNT(course_edition_id)
            >= ALL (SELECT COUNT(course_edition_id)
                    FROM course_enrollment
                    GROUP BY student_id);

Explicación de la solución:

Seleccionamos los ID y los nombres de la tabla student y unimos student con la tabla course_edition en su columna común (ID de alumno). Contamos las filas de la course_edition de cada alumno (COUNT(course_edition_id) AS no_of_course_ed). Así, agrupamos por ID de estudiante y por nombre.

Para asegurarnos de que seleccionamos sólo a los alumnos con el mayor número de cursos matriculados, debemos imponer una condición en COUNT(course_edition_id) para que sea mayor o igual que el número de cursos matriculados para cada alumno.

Creemos una subconsulta que seleccione el número de cursos matriculados de cada alumno: SELECT COUNT(course_edition_id) FROM course_enrollment GROUP BY student_id

El operador ALL devuelve verdadero si la condición se cumple en todas las filas devueltas por la subconsulta. Aquí queremos asegurarnos de que seleccionamos sólo a los estudiantes cuyo no_of_course_ed es mayor que el de cualquier otro estudiante (y sólo igual al mayor número de cursos matriculados - de ahí el signo mayor/igual).

Ilustrémoslo:

La línea en verde devuelve verdadero porque 9 es mayor que cada número devuelto por la subconsulta e igual sólo al mayor número devuelto por la subconsulta.

La línea en rojo devuelve falso porque 6 no es mayor que todos los números devueltos por la subconsulta (es decir, 6 < 8 y 6 < 9).

Conjunto de datos: Tienda

Los siguientes ejercicios utilizan la base de datos Store que contiene seis tablas:

• La tabla customer contiene información sobre Las columnas son customer_id, contact_name, company_name, contact_email, address, city, y country.
• La tabla product tabla almacena información sobre los productos. Las columnas son product_id, product_name, category_id (hace referencia a la category tabla), quantity_per_unit, unit_price, units_in_stock, y discontinued.
• La tabla category almacena información sobre las categorías de productos. Las columnas son category_id, name, description, y parent_category_id (hace referencia a sí misma).
• La tabla purchase almacena información sobre las compras realizadas por los clientes. Las columnas son purchase_id, customer_id (hace referencia a la customer tabla), employee_id (hace referencia a la employee tabla), total_price, purchase_date, shipped_date, ship_address, ship_city, y ship_country.
• La tabla purchase_item asocia todas las compras a los productos. Las columnas son purchase_id (hace referencia a la purchase tabla), product_id (hace referencia a la product tabla), unit_price, y quantity.
• La tabla employee almacena información sobre los empleados. Las columnas son employee_id, last_name, first_name, birth_date, address, city, country, y reports_to.

Ahora que ya estamos familiarizados con el conjunto de datos, pasemos a los ejercicios prácticos de SQL. Los siguientes ejercicios provienen del curso Almacén Básico Ejercicio de SQL .

Ejercicio 10: Calcular el Porcentaje Gastado por el Cliente en Cada Compra

Ejercicio:

Para cada cliente que realizó al menos una compra, muestre el ID de cada compra realizada por este cliente y 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 c
ON p.customer_id = c.customer_id;

Explicación de la solución:

Para identificar a los clientes que han realizado al menos una compra, debemos unir la tabla purchase con la tabla customer en su columna común (ID de cliente).

Para calcular el porcentaje de dinero gastado en una compra en relación con todo el dinero gastado por ese cliente, necesitamos subconsultas. La subconsulta calcula cuánto dinero gastó un cliente en todas las compras: SELECT SUM(total_price) FROM purchase WHERE customer_id = p.customer_id

Obsérvese que la subconsulta hace referencia a la tabla purchase (con el alias p) para obtener el ID de cliente correcto. Esto se denomina subconsulta correlacionada.

Por último, calculamos el valor porcentual dividiendo total_price por el valor devuelto por la subconsulta. Además, debemos multiplicar este valor por 100 para obtener el porcentaje y ROUND() a un número entero.

Si desea aprender más sobre subconsultas correlacionadas, lea Aprenda a Escribir una Subconsulta Correlacionada en 5 Minutos.

Ejercicio 11: Encontrar el Número de Productos Caros en Cada Categoría

Ejercicio:

Muestre los nombres de las categorías y el número de productos de esta categoría que tienen un precio unitario mayor que el precio medio de un producto de esta categoría. Muestre sólo las categorías que tengan dicho(s) producto(s). Muestre dos columnas: nombre (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.name;

Explicación de la solución:

Queremos mostrar los nombres de las categorías y el número de productos; por lo tanto, necesitamos unir la tabla category con la tabla product en su columna común (ID de categoría).

Para mostrar el número de productos por categoría, utilizamos la función COUNT(). Como seleccionamos el nombre de la categoría (c.name) y el recuento de productos por nombre de categoría (COUNT(*)), debemos agrupar por la columna de nombre de categoría (GROUP BY c.name).

En la función COUNT(), queremos incluir sólo los productos con un precio unitario superior al precio medio de un producto de esta categoría. Para ello, utilizamos una subconsulta correlacionada.

En la subconsulta, unimos las columnas product y category y seleccionamos el valor medio de los precios unitarios. Para asegurarnos de que tomamos la media de los valores del ID de categoría específico, imponemos una condición en la cláusula WHERE que establece que category_id de la subconsulta debe ser igual a category_id de la consulta externa.

La condición de la cláusula WHERE de la consulta principal dice que unit_price debe ser mayor que la media de unit_price para esta categoría, devuelta por la subconsulta.

Ejercicio 12: Mostrar Productos Comprados con su Cantidad Máxima Comprada

Ejercicio:

Para cada producto comprado, muestre su nombre, la mayor cantidad en la que ha sido comprado y el número de compras de cantidad máxima para este producto. 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:

Para obtener información sobre los productos y las cantidades en las que se han comprado, debemos unir la tabla purchase_item con la tabla product en su columna común (ID de producto).

Utilizamos la función agregada COUNT() para obtener el número de compras (COUNT(purchase_id)).

Para asegurarnos de que seleccionamos sólo la mayor cantidad en la que se compró un producto en particular, necesitamos construir una subconsulta. La subconsulta hace referencia al valor product_id de la consulta externa para asegurarnos de que elegimos la cantidad máxima para el producto correcto - por lo tanto, es una subconsulta correlacionada.

En la cláusula WHERE de la consulta principal, imponemos la condición de que el valor de la cantidad debe ser igual al valor devuelto por la subconsulta.

Ejercicio 13: Listar Productos Discontinuados, Continuados y Totales en Cada Categoría

Ejercicio:

Para cada categoría, muestre

• Su nombre.
• El número de productos discontinuados (es decir, que ya no están disponibles) en esta categoría (nombre esta columna discontinued_products).
• El número de productos continuados (es decir, actualmente disponibles) en esta categoría (nombre de esta columna continued_products).
• El número de todos los productos de esta categoría (nombre de esta columna 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:

En este ejercicio, no utilizamos una ni dos, sino tres subconsultas correlacionadas.

Seleccionamos el nombre de la categoría de la tabla category tabla.

La primera subconsulta correlacionada cuenta todos los productos que han sido discontinued. Esta subconsulta hace referencia al valor category_id de la consulta externa para garantizar que los productos descatalogados se cuentan por categoría.

La segunda subconsulta correlacionada cuenta todos los productos que no se han descatalogado. Esta subconsulta hace referencia al valor category_id de la consulta externa para garantizar que los productos que continúan se cuentan por categoría.

La tercera subconsulta correlacionada cuenta todos los productos por categoría. Esta subconsulta hace referencia al valor category_id de la consulta externa para garantizar que todos los productos se cuenten por categoría.

Ejercicio 14: Conteo de Compras Manejadas por Cada Empleado en Houston

Ejercicio:

Muestre el ID del empleado y el número total de compras que este empleado manejó. Utilice una subconsulta para obtener información acerca del número de pedidos que cada empleado manejó por cliente y haga que la consulta principal seleccione DESDE esta subconsulta. Considere sólo los empleados que viven en Houston.

Solución:

SELECT
  employee_per_customer.employee_id,
  SUM(employee_per_customer.no_of_purchases) AS total_no_of_purchases
FROM (SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id
     ) AS employee_per_customer
GROUP BY employee_per_customer.employee_id;

Explicación de la solución:

Empecemos por asegurarnos de que sólo tenemos en cuenta a los empleados que viven en Houston. Para ello, utilizamos la palabra clave EXISTS. Devuelve verdadero si la subconsulta devuelve al menos una fila.

Observe que la subconsulta pasada a la palabra clave EXISTS es una subconsulta correlacionada, ya que hace referencia al valor employee_id de su consulta externa (que es una subconsulta de la consulta principal).

Analicemos la subconsulta que se pasa a la consulta principal en la cláusula FROM. Selecciona los ID de empleado y cliente y cuenta cuántas compras se han realizado por empleado y por cliente (de ahí la agrupación por valores de ID de empleado y cliente).

      SELECT
        e.employee_id,
        p.customer_id,
        COUNT(p.purchase_id) AS no_of_purchases
      FROM employee e
      JOIN purchase p
      ON e.employee_id = p.employee_id
      WHERE EXISTS (SELECT * FROM employee 
                    WHERE employee.employee_id = e.employee_id 
                    AND city = 'Houston')
      GROUP BY e.employee_id, p.customer_id

La parte en rojo garantiza que sólo tenemos en cuenta a los empleados que viven en Houston.

Esta subconsulta tiene el alias AS employee_per_customer y la consulta principal selecciona a partir de ella.

La consulta principal selecciona lo siguiente

• Los ID de los empleados de la subconsulta (de employee_per_customer),
• El número total de compras realizadas por cada empleado. Para ello es necesario agrupar por ID de empleado (GROUP BY employee_per_customer.employee_id).

Observe que la subconsulta correlacionada utiliza COUNT() para contar las compras (o filas) por empleado y por cliente. Pero la consulta principal utiliza la función SUM() para sumar todos los valores devueltos por COUNT() en la subconsulta.

Puede obtener más información sobre las funciones de agregación aquí. Y consulta este artículo sobre cómo utilizar SUM() con OVER(PARTITION BY ) para conocer más detalles sobre las funciones ventana.

Este ejercicio presentó la idea de usar subconsultas como bloques de construcción - aquí, usamos tres bloques de construcción para obtener los datos deseados.

Ejercicio 15: Encontrar el Mayor Número de Categorías de Productos en una Compra

Ejercicio:

Utilice una subconsulta para seleccionar el ID de la compra y el número de categorías distintas contenidas en esta compra. En la consulta principal, seleccione el número máximo de categorías de esta subconsulta.

Solución:

SELECT MAX(categories_per_purchase.category_count) 
         AS max_categories_per_purchase
FROM (SELECT 
        purchase_id, 
        COUNT(DISTINCT category_id) AS category_count
      FROM purchase_item pi
      JOIN product p
      ON pi.product_id = p.product_id
      GROUP BY purchase_id) AS categories_per_purchase;

Explicación de la solución:

Partamos de la subconsulta que se pasa en la cláusula FROM de la consulta principal.

Unimos la tabla purchase_item con la tabla product en su columna común (ID de producto). Seleccionamos el ID de compra y los ID de categoría COUNT DISTINCT por compra. Por lo tanto, agrupamos por la columna purchase_id.

La consulta principal utiliza la función MAX() para seleccionar (a partir de la subconsulta alias AS categories_per_purchase) el número máximo de categorías de productos contenidas en una compra.

Más prácticas de subconsultas SQL

Este artículo presentó varios casos de uso de subconsultas, incluyendo el filtrado avanzado de datos o el anidamiento de consultas una dentro de otra. Una idea básica de cómo beneficiarse del uso de subconsultas es dividir una pregunta en (sub)preguntas más pequeñas - cada (sub)pregunta pequeña debería poder responderse utilizando una subconsulta.

Anímate y practica por tu cuenta - es la mejor manera de aprender más técnicas de empleo de subconsultas para simplificar tareas complejas de extracción de datos. Consulta más ejercicios de subconsultas aquí y aquí.

Te animamos a que te sumerjas y practiques con nuestros cursos de SQL. Cada uno de estos cursos tiene una sección separada sobre subconsultas y secciones separadas sobre otros temas SQL desafiantes: consultas de tabla única, JOINs, agrupación y más.

1. Ejercicios prácticos de SQL
2. Básico Ejercicio de SQL: Una tienda
3. Básico Ejercicio de SQL: Universidad
4. Básico Ejercicio de SQL: Datos de Tráfico del Blog
5. Básico Ejercicio de SQL: Realizar un seguimiento de las consultas

Regístrate ahora y empieza gratis. ¡Mucha suerte!