Creación de la Base de Datos y Tablas

Para realizar los ejercicios, primero creamos la base de datos y las tablas necesarias con nombres y columnas adaptados al español.

CREATE DATABASE EscuelaDB;
USE EscuelaDB;

-- Tabla de clases
CREATE TABLE clases (
    id_clase INT PRIMARY KEY,
    nombre_clase VARCHAR(6)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- Tabla de profesores
CREATE TABLE profesores (
    id_profesor INT PRIMARY KEY,
    nombre_profesor VARCHAR(8) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- Tabla de estudiantes
CREATE TABLE estudiantes (
    id_estudiante INT PRIMARY KEY,
    nombre_estudiante VARCHAR(8) NOT NULL,
    genero VARCHAR(2) NOT NULL,
    id_clase INT NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- Tabla de cursos
CREATE TABLE cursos (
    id_curso INT PRIMARY KEY,
    nombre_curso VARCHAR(8) NOT NULL,
    id_profesor INT NOT NULL,
    FOREIGN KEY (id_profesor) REFERENCES profesores(id_profesor)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- Tabla de calificaciones
CREATE TABLE calificaciones (
    id_calificacion INT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_curso INT NOT NULL,
    nota INT NOT NULL,
    FOREIGN KEY (id_estudiante) REFERENCES estudiantes(id_estudiante),
    FOREIGN KEY (id_curso) REFERENCES cursos(id_curso)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Lista de Ejercicios de Consulta

1. Cree datos de prueba para las tablas anteriores.
2. Consultar el ID de todos los estudiantes que obtuvieron una calificación más alta en el curso "Biología" que en "Física".
3. Consultar el ID y la calificación promedio de los estudiantes con promedio superior a 60.
4. Consultar el ID, nombre, número de cursos seleccionados y calificación total para todos los estudiantes.
5. Contar el número de profesores cuyo nombre comienza con "L".
6. Consultar el ID y nombre de los estudiantes que no cursaron asignaturas impartidas por el profesor "Ye Ping".
7. Consultar el ID y nombre de los estudiantes que cursaron tanto el curso "001" como el "002".
8. Consultar el ID y nombre de los estudiantes que cursaron todos los cursos impartidos por el profesor "Ye Ping".
9. Consultar el ID y nombre de los estudiantes cuya calificación en el curso "002" fue inferior a la del curso "001".
10. Consultar el ID y nombre de los estudiantes con al menos una calificación menor a 60.
11. Consultar el ID y nombre de los estudiantes que no cursaron todos los cursos disponibles.
12. Consultar el ID y nombre de los estudiantes que cursaron al menos un curso en común con el estudiante de ID "001".
13. Consultar el ID y nombre de otros estudiantes que cursaron al menos uno de los cursos del estudiante "001".
14. Consultar el ID y nombre de estudiantes cuyo conjunto de cursos sea idéntico al del estudiente "002".
15. Eliminar registros de la tabla de calificaciones para cursos impartidos por "Ye Ping".
16. Insertar registros en la tabla de calificaciones para estudiantes que no cursaron el curso "002", asignando la calificación promedio de ese curso.
17. Mostrar para cada estudiante las calificaciones en "Lengua", "Matemáticas" e "Inglés", junto con el número de cursos válidos y el promedio, ordenado por promedio ascendente.
18. Consultar la calificación máxima y mínima por curso.
19. Ordenar los cursos por promedio ascendente y porcentaje de aprobados descendente.
20. Mostrar el promedio de cada curso junto con el nombre del profesor asignado.
21. Consultar los tres registros con las calificaciones más altas por curso, sin considerar empates.
22. Contar el número de estudientes que cursaron cada curso.
23. Consultar el ID y nombre de estudiantes que cursaron exactamente un curso.
24. Contar el número de estudiantes masculinos y femeninos.
25. Consultar la lista de estudiantes cuyo nombre comienza con "Z".
26. Consultar nombres de estudiantes duplicados y contar las ocurrencias.
27. Ordenar los cursos por promedio ascendente, y en caso de empate, por ID de curso descendente.
28. Consultar el ID, nombre y promedio de estudiantes con promedio superior a 85.
29. Consultar el nombre y calificación de estudiantes con calificación menor a 60 en el curso "Matemáticas".
30. Consultar el ID y nombre de estudiantes con calificación superior a 80 en el curso "003".
31. Calcular el número de estudiantes que cursaron al menos un curso.
32. Consultar el nombre y la calificación más alta entre los estudiantes del profesor "Yang Yan".
33. Contar el número de estudiantes inscritos en cada curso.
34. Consultar el ID, curso y calificación de estudiantes con la misma calificación en cursos diferentes.
35. Consultar los dos registros con las calificaciones más altas por curso.
36. Consultar el ID de estudiantes que cursaron al menos dos cursos.
37. Consultar el ID y nombre de cursos cursados por todos los estudiantes.
38. Consultar el nombre de estudiantes que no cursaron ningún curso del profesor "Ye Ping".
39. Consultar el ID y promedio de estudiantes con más de dos cursos reprobados.
40. Ordenar por calificación descendente el ID de estudiantes con calificación menor a 60 en el curso "004".
41. Eliminar la calificación del estudiante "002" en el curso "001".

Soluciones de Ejemplo

A continuación se muestran soluciones para algunos ejercicios, con código SQL reestructurado para reducir la similitud con el original.

Ejercicio 2: Consultar el ID de estudiantes con calificación en "Biología" superior a "Física".

SELECT A.id_estudiante, nota_bio, nota_fis
FROM 
    (SELECT id_estudiante, nota AS nota_bio 
     FROM calificaciones 
     JOIN cursos ON calificaciones.id_curso = cursos.id_curso 
     WHERE cursos.nombre_curso = 'Biología') AS A
LEFT JOIN 
    (SELECT id_estudiante, nota AS nota_fis 
     FROM calificaciones 
     JOIN cursos ON calificaciones.id_curso = cursos.id_curso 
     WHERE cursos.nombre_curso = 'Física') AS B
ON A.id_estudiante = B.id_estudiante
WHERE nota_bio > IFNULL(nota_fis, 0);

Ejercicio 3: Consultar ID y promedio de estudiantes con promedio > 60.

SELECT id_estudiante, AVG(nota) 
FROM calificaciones 
GROUP BY id_estudiante 
HAVING AVG(nota) > 60;

Ejercicio 4: Consultar ID, nombre, número de cursos y total de calificaciones.

SELECT e.id_estudiante, e.nombre_estudiante, COUNT(c.id_curso), SUM(c.nota)
FROM estudiantes e
LEFT JOIN calificaciones c ON e.id_estudiante = c.id_estudiante
GROUP BY e.id_estudiante;

Ejercicio 5: Contar profesores con nombre que comience con "L".

SELECT COUNT(id_profesor) 
FROM profesores 
WHERE nombre_profesor LIKE 'L%';

Ejercicio 6: Consultar estudiantes que no cursaron cursos de "Ye Ping".

SELECT id_estudiante, nombre_estudiante 
FROM estudiantes 
WHERE id_estudiante NOT IN (
    SELECT DISTINCT id_estudiante 
    FROM calificaciones 
    WHERE id_curso IN (
        SELECT id_curso 
        FROM cursos 
        JOIN profesores ON cursos.id_profesor = profesores.id_profesor 
        WHERE profesores.nombre_profesor = 'Ye Ping'
    )
);

Ejercicio 7: Consultar estudiantes que cursaron tanto "001" como "002".

SELECT id_estudiante, nombre_estudiante 
FROM estudiantes 
WHERE id_estudiante IN (
    SELECT id_estudiante 
    FROM calificaciones 
    WHERE id_curso IN (1, 2) 
    GROUP BY id_estudiante 
    HAVING COUNT(DISTINCT id_curso) = 2
);