La base de datos de una academia de idiomas

Ejercicio de SQLDifícilUnos 75 minutos

Nueve consultas de dificultad creciente sobre una academia con profesores, cursos, alumnos, matrículas y pagos: filtros, JOIN, LEFT JOIN, agregados, porcentajes, deudas, el mejor alumno de cada idioma y una actualización con subconsulta.

SELECT y WHERE
JOIN y LEFT JOIN
GROUP BY y HAVING
Funciones de agregado
Subconsultas
NULL
UPDATE

Enunciado

Una academia de idiomas de Madrid acaba de pasar sus listas de Excel a una base de datos y te pide los informes que hasta ahora hacían a mano. Tiene cinco profesores, siete cursos de inglés, francés, alemán e italiano, y diez alumnos que se han matriculado en septiembre.

Cada curso tiene un profesor, un número de plazas y un precio mensual. Un alumno puede estar en varios cursos; la nota de cada matrícula es NULL mientras el alumno no ha hecho el primer examen. En la tabla de pagos está lo que cada alumno ha pagado en octubre, que debería coincidir con la suma de los precios de sus cursos, pero no siempre es así.

Las tablas tienen exactamente los datos de abajo: antes de escribir cada consulta, calcula a mano qué filas deberían salir. Es la mejor forma de detectar un JOIN que duplica filas o un NULL que se cuela.

Qué hay que hacer

Escribe una consulta para cada apartado. Se corrigen por separado comparando tu resultado con el esperado: importan las filas, el número de columnas y, cuando el apartado pide un orden, el orden; los nombres de las columnas no.
Los importes y porcentajes que pidan decimales se redondean con ROUND. Ojo con la división entera: en SQLite, 3 / 12 es 0.
Los apartados van de menos a más difíciles. Si uno se te atasca, usa sus pistas antes de mirar la solución completa del final.

Las tablas y sus datos

Base de datos del ejercicio · 5 tablas

profesores
cursos
alumnos
matriculas
pagos

Subrayada, la clave primaria; con flecha, la columna a la que apunta cada clave ajena.

profesores

profesor_id	nombre	idioma	contratado
1	Laura Gómez	inglés	2019-09-01
2	Pierre Martin	francés	2021-01-15
3	Anna Schmidt	alemán	2022-09-01
4	David Ruiz	inglés	2023-02-01
5	Chiara Rossi	italiano	2024-09-01

cursos

curso_id	idioma	nivel	profesor_id	plazas	precio_mes
1	inglés	A2	1	12	55
2	inglés	B1	1	12	60
3	inglés	B2	4	10	65
4	francés	A1	2	10	50
5	francés	B1	2	8	58
6	alemán	A1	3	10	52
7	inglés	C1	4	8	75

alumnos

alumno_id	nombre	ciudad	fecha_nacimiento	email
1	Marta López	Madrid	2001-03-14	marta@correo.es
2	Javier Pérez	Toledo	1998-11-02	javier@correo.es
3	Sofía Navarro	Madrid	2004-07-21	NULL
4	Hugo Martín	Getafe	1995-01-30	hugo@correo.es
5	Lucía Ortega	Madrid	2003-05-09	lucia@correo.es
6	Daniel Serrano	Toledo	2000-12-12	NULL
7	Elena Castro	Alcalá	1999-08-25	elena@correo.es
8	Pablo Vidal	Getafe	2002-02-17	pablo@correo.es
9	Irene Molina	Madrid	1997-10-05	irene@correo.es
10	Álvaro Ramos	Alcalá	2005-04-28	alvaro@correo.es

matriculas

alumno_id	curso_id	fecha	nota
1	2	2026-09-15	8.5
1	4	2026-09-16	7
2	1	2026-09-15	5.5
3	2	2026-09-17	9
3	6	2026-09-18	NULL
4	3	2026-09-15	6
5	2	2026-09-20	4
5	5	2026-09-20	8
6	1	2026-09-22	NULL
7	3	2026-09-15	7.5
7	6	2026-09-16	6.5
8	1	2026-09-25	3.5
9	4	2026-09-15	9.5
9	5	2026-09-16	NULL
9	2	2026-09-17	6

pagos

pago_id	alumno_id	fecha	importe	metodo
1	1	2026-10-01	110	tarjeta
2	2	2026-10-02	55	bizum
3	3	2026-10-01	112	tarjeta
4	5	2026-10-03	60	efectivo
5	6	2026-10-05	55	bizum
6	7	2026-10-01	117	tarjeta
7	9	2026-10-02	168	tarjeta

Ver el script SQL que crea la base de datos

sql
CREATE TABLE profesores (
  profesor_id INTEGER PRIMARY KEY,
  nombre TEXT NOT NULL,
  idioma TEXT NOT NULL,
  contratado DATE NOT NULL
);
CREATE TABLE cursos (
  curso_id INTEGER PRIMARY KEY,
  idioma TEXT NOT NULL,
  nivel TEXT NOT NULL,
  profesor_id INTEGER REFERENCES profesores(profesor_id),
  plazas INTEGER NOT NULL,
  precio_mes REAL NOT NULL
);
CREATE TABLE alumnos (
  alumno_id INTEGER PRIMARY KEY,
  nombre TEXT NOT NULL,
  ciudad TEXT,
  fecha_nacimiento DATE,
  email TEXT
);
CREATE TABLE matriculas (
  alumno_id INTEGER REFERENCES alumnos(alumno_id),
  curso_id INTEGER REFERENCES cursos(curso_id),
  fecha DATE NOT NULL,
  nota REAL,
  PRIMARY KEY (alumno_id, curso_id)
);
CREATE TABLE pagos (
  pago_id INTEGER PRIMARY KEY,
  alumno_id INTEGER REFERENCES alumnos(alumno_id),
  fecha DATE NOT NULL,
  importe REAL NOT NULL,
  metodo TEXT NOT NULL
);

INSERT INTO profesores (profesor_id, nombre, idioma, contratado) VALUES
  (1, 'Laura Gómez', 'inglés', '2019-09-01'),
  (2, 'Pierre Martin', 'francés', '2021-01-15'),
  (3, 'Anna Schmidt', 'alemán', '2022-09-01'),
  (4, 'David Ruiz', 'inglés', '2023-02-01'),
  (5, 'Chiara Rossi', 'italiano', '2024-09-01');

INSERT INTO cursos (curso_id, idioma, nivel, profesor_id, plazas, precio_mes) VALUES
  (1, 'inglés', 'A2', 1, 12, 55),
  (2, 'inglés', 'B1', 1, 12, 60),
  (3, 'inglés', 'B2', 4, 10, 65),
  (4, 'francés', 'A1', 2, 10, 50),
  (5, 'francés', 'B1', 2, 8, 58),
  (6, 'alemán', 'A1', 3, 10, 52),
  (7, 'inglés', 'C1', 4, 8, 75);

INSERT INTO alumnos (alumno_id, nombre, ciudad, fecha_nacimiento, email) VALUES
  (1, 'Marta López', 'Madrid', '2001-03-14', 'marta@correo.es'),
  (2, 'Javier Pérez', 'Toledo', '1998-11-02', 'javier@correo.es'),
  (3, 'Sofía Navarro', 'Madrid', '2004-07-21', NULL),
  (4, 'Hugo Martín', 'Getafe', '1995-01-30', 'hugo@correo.es'),
  (5, 'Lucía Ortega', 'Madrid', '2003-05-09', 'lucia@correo.es'),
  (6, 'Daniel Serrano', 'Toledo', '2000-12-12', NULL),
  (7, 'Elena Castro', 'Alcalá', '1999-08-25', 'elena@correo.es'),
  (8, 'Pablo Vidal', 'Getafe', '2002-02-17', 'pablo@correo.es'),
  (9, 'Irene Molina', 'Madrid', '1997-10-05', 'irene@correo.es'),
  (10, 'Álvaro Ramos', 'Alcalá', '2005-04-28', 'alvaro@correo.es');

INSERT INTO matriculas (alumno_id, curso_id, fecha, nota) VALUES
  (1, 2, '2026-09-15', 8.5),
  (1, 4, '2026-09-16', 7),
  (2, 1, '2026-09-15', 5.5),
  (3, 2, '2026-09-17', 9),
  (3, 6, '2026-09-18', NULL),
  (4, 3, '2026-09-15', 6),
  (5, 2, '2026-09-20', 4),
  (5, 5, '2026-09-20', 8),
  (6, 1, '2026-09-22', NULL),
  (7, 3, '2026-09-15', 7.5),
  (7, 6, '2026-09-16', 6.5),
  (8, 1, '2026-09-25', 3.5),
  (9, 4, '2026-09-15', 9.5),
  (9, 5, '2026-09-16', NULL),
  (9, 2, '2026-09-17', 6);

INSERT INTO pagos (pago_id, alumno_id, fecha, importe, metodo) VALUES
  (1, 1, '2026-10-01', 110, 'tarjeta'),
  (2, 2, '2026-10-02', 55, 'bizum'),
  (3, 3, '2026-10-01', 112, 'tarjeta'),
  (4, 5, '2026-10-03', 60, 'efectivo'),
  (5, 6, '2026-10-05', 55, 'bizum'),
  (6, 7, '2026-10-01', 117, 'tarjeta'),
  (7, 9, '2026-10-02', 168, 'tarjeta');

Guía paso a paso

Intenta resolverlo por tu cuenta y abre un paso solo cuando te atasques: cada uno te acerca a la solución sin dártela entera.

1. Conoce los datos antes de consultar

Lee las cinco tablas y fíjate en los casos especiales: el curso de inglés C1 no tiene alumnos, Chiara no da ningún curso, Álvaro no está matriculado, Sofía y Daniel no tienen email o nota, y Hugo y Pablo no han pagado. Casi todos los errores de las consultas aparecen con esas filas.

2. Una tabla, luego dos, luego agregados

Los apartados 1 y 2 son de calentamiento: un filtro y un JOIN. Antes de seguir, comprueba que entiendes por qué el JOIN del apartado 2 devuelve 7 filas (una por curso) y no 5 (una por profesor).

3. Cuando la cuenta tiene que incluir los ceros

Para contar cosas que pueden no existir (cursos sin alumnos, profesores sin cursos) la tabla principal va a la izquierda de un LEFT JOIN y se cuenta una columna de la tabla de la derecha.

sql
SELECT c.curso_id, COUNT(m.alumno_id)
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.curso_id
GROUP BY c.curso_id;

4. Cuidado con sumar a dos niveles

La deuda del apartado 6 mezcla dos cosas que se cuentan distinto: los cursos de cada alumno y sus pagos. Si las unes en un solo JOIN, cada pago se repite por cada curso. Calcula cada suma por separado (subconsultas) y opera con los resultados.

5. Comprueba el UPDATE antes de lanzarlo

Antes de escribir el UPDATE, escribe un SELECT con el mismo WHERE para ver qué cursos va a tocar. Si salen los cursos 1 y 2, el WHERE está bien; entonces cambia el SELECT por el UPDATE.

Resuélvelo aquí

Cada apartado se corrige por separado contra la base de datos de arriba: escribe la consulta y pulsa «Comprobar».

🗄️SQLApartado 1Difícil

Nombre y email de los alumnos de Madrid que tienen email, por orden alfabético del nombre.

🗄️SQLApartado 2Difícil

Todos los cursos con el nombre de su profesor: idioma, nivel, nombre del profesor y precio al mes, ordenados por idioma y, dentro de cada idioma, por nivel.

🗄️SQLApartado 3Difícil

Cuántos alumnos hay en cada curso, incluidos los cursos que no tienen ninguno: idioma, nivel y número de matriculados, ordenados por idioma y nivel.

🗄️SQLApartado 4Difícil

Ocupación de cada curso: idioma, nivel, matriculados, plazas y porcentaje de ocupación redondeado a un decimal (3 de 12 plazas es 25.0). De mayor a menor ocupación y, a igualdad, por idioma y nivel.

🗄️SQLApartado 5Difícil

Nota media por idioma, contando solo las matrículas que ya tienen nota: idioma, número de alumnos evaluados y nota media redondeada a dos decimales, de mayor a menor nota media.

🗄️SQLApartado 6Difícil

Lo que debe cada alumno de octubre: nombre, total de sus cursos, lo que ha pagado y la deuda (total menos pagado). Solo los que deben algo, de mayor a menor deuda. Quien no tiene ningún pago ha pagado 0.

🗄️SQLApartado 7Difícil

Para cada profesor, incluidos los que no dan clase, cuántos alumnos distintos tiene en total entre todos sus cursos: nombre, idioma y número de alumnos, de más a menos alumnos y, a igualdad, por nombre.

🗄️SQLApartado 8Difícil

El alumno con la nota más alta de cada idioma: idioma, nombre del alumno y nota, ordenado por idioma.

🗄️SQLApartado 9Difícil

La academia sube un 5 % el precio de los cursos de inglés que tienen más de 2 alumnos matriculados. Escribe el UPDATE (se comprobarán los precios de todos los cursos después de ejecutarlo).

Solución explicada

Ver las soluciones de todos los apartados

Apartado 1

sql
SELECT nombre, email
FROM alumnos
WHERE ciudad = 'Madrid' AND email IS NOT NULL
ORDER BY nombre;

Apartado 2

sql
SELECT c.idioma, c.nivel, p.nombre, c.precio_mes
FROM cursos c
JOIN profesores p ON p.profesor_id = c.profesor_id
ORDER BY c.idioma, c.nivel;

Apartado 3

sql
SELECT c.idioma, c.nivel, COUNT(m.alumno_id) AS matriculados
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.curso_id
GROUP BY c.curso_id
ORDER BY c.idioma, c.nivel;

Apartado 4

sql
SELECT c.idioma, c.nivel, COUNT(m.alumno_id) AS matriculados, c.plazas,
       ROUND(100.0 * COUNT(m.alumno_id) / c.plazas, 1) AS ocupacion
FROM cursos c
LEFT JOIN matriculas m ON m.curso_id = c.curso_id
GROUP BY c.curso_id
ORDER BY ocupacion DESC, c.idioma, c.nivel;

Apartado 5

sql
SELECT c.idioma, COUNT(m.nota) AS evaluados, ROUND(AVG(m.nota), 2) AS nota_media
FROM matriculas m
JOIN cursos c ON c.curso_id = m.curso_id
WHERE m.nota IS NOT NULL
GROUP BY c.idioma
ORDER BY nota_media DESC;

Apartado 6

sql
SELECT nombre, total, pagado, total - pagado AS deuda
FROM (
  SELECT a.nombre,
         (SELECT SUM(c.precio_mes) FROM matriculas m JOIN cursos c ON c.curso_id = m.curso_id
          WHERE m.alumno_id = a.alumno_id) AS total,
         COALESCE((SELECT SUM(p.importe) FROM pagos p WHERE p.alumno_id = a.alumno_id), 0) AS pagado
  FROM alumnos a
)
WHERE total - pagado > 0
ORDER BY deuda DESC;

Apartado 7

sql
SELECT p.nombre, p.idioma, COUNT(DISTINCT m.alumno_id) AS alumnos
FROM profesores p
LEFT JOIN cursos c ON c.profesor_id = p.profesor_id
LEFT JOIN matriculas m ON m.curso_id = c.curso_id
GROUP BY p.profesor_id
ORDER BY alumnos DESC, p.nombre;

Apartado 8

sql
SELECT c.idioma, a.nombre, m.nota
FROM matriculas m
JOIN cursos c ON c.curso_id = m.curso_id
JOIN alumnos a ON a.alumno_id = m.alumno_id
WHERE m.nota = (
  SELECT MAX(m2.nota)
  FROM matriculas m2
  JOIN cursos c2 ON c2.curso_id = m2.curso_id
  WHERE c2.idioma = c.idioma
)
ORDER BY c.idioma;

Apartado 9

sql
UPDATE cursos
SET precio_mes = ROUND(precio_mes * 1.05, 2)
WHERE idioma = 'inglés'
  AND (SELECT COUNT(*) FROM matriculas m WHERE m.curso_id = cursos.curso_id) > 2;

Los apartados 3, 4 y 7 tienen el mismo esqueleto: la tabla de la que queremos todas las filas a la izquierda, LEFT JOIN hacia la tabla que se cuenta y COUNT de una columna de esa tabla, que vale 0 cuando no hay coincidencias. En el 7 además hace falta DISTINCT, porque un alumno puede estar en dos cursos del mismo profesor.

En el 4 la trampa es la división entera: COUNT(...) y plazas son enteros, y 3 / 12 vale 0. Multiplicar por 100.0 antes de dividir convierte la operación en decimal. El orden por ocupacion DESC, idioma, nivel deshace los empates de forma predecible.

El 6 es el clásico error de multiplicar filas al unir dos relaciones uno a muchos con la misma tabla: cada alumno tiene varios cursos y puede tener varios pagos. Calculando cada total en su propia subconsulta no hay repeticiones; COALESCE convierte en 0 la suma de quien no ha pagado nada, y el filtro total - pagado > 0 descarta también a Álvaro, cuyo total es NULL.

El 8 resuelve el problema de «el máximo y de quién es» con una subconsulta correlacionada: para cada matrícula se calcula el máximo de su propio idioma. Si hubiera un empate en la nota máxima, saldrían los dos alumnos, que es lo correcto.

En el UPDATE, la subconsulta cuenta los matriculados del curso que se está modificando (cursos.curso_id). Solo cumplen la condición los cursos 1 (3 alumnos) y 2 (4 alumnos): 55 pasa a 57.75 y 60 a 63.

Para ir más allá

Muestra, para cada ciudad, cuántos alumnos hay y cuánto se ha cobrado en total.
Crea una vista deudores con la consulta del apartado 6 y úsala para enviar un aviso solo a los que tienen email.
Añade una restricción para que una matrícula no se pueda insertar si el curso ya está lleno (pista: un disparador BEFORE INSERT).