Dominando las Consultas SQL para el Análisis de Cursos de Estudiantes

Introducción

En este proyecto, aprenderás a realizar diversas consultas SQL en una base de datos de cursos de estudiantes. La base de datos consta de tres tablas: Tabla de Estudiantes (S), Tabla de Cursos (C) y Tabla de Estudiantes-Cursos (SC). Aprenderás a recuperar y manipular datos de estas tablas para realizar diferentes tareas.

👀 Vista previa

Imagen de vista previa de la base de datos SQL

🎯 Tareas

En este proyecto, aprenderás:

Cómo listar los nombres de todos los estudiantes que no han tomado cursos impartidos por el profesor "Daniel"
Cómo listar los números de estudiante y los nombres de los estudiantes que han reprobado (calificación < 60) en dos o más cursos
Cómo listar los nombres de todos los estudiantes que han tomado tanto el curso "11" como el curso "12"
Cómo listar los números de estudiante de todos los estudiantes cuya calificación en el curso "11" es mayor que la de en el curso "12", y ordenarlos en orden ascendente
Cómo listar los nombres de todos los estudiantes que son mayores que "John Davis"
Cómo listar el número de estudiante del estudiante con la calificación más alta entre todos los estudiantes que han tomado el curso "12"
Cómo listar los nombres de todos los estudiantes, junto con sus identificadores de cursos seleccionados y calificaciones
Cómo listar los números de estudiante y las calificaciones totales (alias: SG) de los estudiantes que han tomado cuatro o más cursos, ordenados en orden descendente de calificaciones totales

🏆 Logros

Después de completar este proyecto, podrás:

Comprender cómo escribir consultas SQL complejas para recuperar y manipular datos de una base de datos relacional
Aplicar diversas técnicas SQL, como subconsultas, uniones, agregaciones y ordenamiento, para resolver problemas de análisis de datos del mundo real
Desarrollar habilidades SQL que son esenciales para la toma de decisiones y la resolución de problemas basados en datos

Iniciar MySQL y Importar Datos

En este paso, aprenderás a iniciar el servicio de MySQL e importar los datos necesarios en la base de datos.

Inicie el servicio de MySQL utilizando el comando sudo sin ninguna contraseña:

sudo service mysql start

Acceda al cliente de MySQL utilizando el comando sudo sin ninguna contraseña:

sudo mysql

Importe los datos del script personnel.sql proporcionado en la base de datos de MySQL:

SOURCE /home/labex/project/initdb.sql

Agregar Código de Consulta

En este paso, aprenderás a listar los nombres de todos los estudiantes que no han tomado cursos impartidos por el profesor "Daniel". Siga los pasos siguientes para completar este paso:

Abra el archivo answer.sql en el directorio /home/labex/project.
Agregue la instrucción SQL debajo del comentario:
Encuentre el comentario -- List the names of all students who have not taken courses taught by the "Daniel" teacher.
```
SELECT s.sname
FROM S s
WHERE NOT EXISTS (
  SELECT 1
  FROM SC sc
  JOIN C c ON sc.cno = c.cno
  WHERE c.tname = 'Daniel' AND sc.sno = s.sno
);
```
Esta instrucción SQL primero selecciona la columna sname de la tabla S. Luego utiliza una cláusula NOT EXISTS para comprobar si hay registros en la tabla SC que tengan un identificador de curso (cno) que coincida con un curso impartido por el profesor "Daniel", y el identificador de estudiante (sno) coincida con el estudiante actual. Si no existen tales registros, el nombre del estudiante se incluirá en el resultado.
Encuentre el comentario -- List the student numbers and names of students who have failed (grade < 60) in two or more courses.
```
SELECT s.sno, s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.grade < 60
GROUP BY s.sno, s.sname
HAVING COUNT(sc.sno) >= 2;
```
Esta instrucción SQL primero une las tablas S y SC para obtener la información de los estudiantes y sus calificaciones de cursos. Luego filtra los registros donde la calificación es menor que 60, agrupa los resultados por número de estudiante y nombre, y finalmente selecciona solo los estudiantes que han reprobado en dos o más cursos utilizando la cláusula HAVING.
Encuentre el comentario -- List the names of all students who have taken both course "11" and course "12".
```
 SELECT s.sname
 FROM S s
 JOIN SC sc ON s.sno = sc.sno
 WHERE sc.cno IN (11, 12)
 GROUP BY s.sno
 HAVING COUNT(DISTINCT sc.cno) = 2
 ORDER BY s.sno;
```
Esta instrucción SQL primero une las tablas S y SC para obtener la información de los estudiantes y sus identificadores de cursos. Luego filtra los registros donde el identificador de curso es 11 o 12, agrupa los resultados por número de estudiante, y selecciona solo los estudiantes que han tomado ambos cursos utilizando la cláusula HAVING. Finalmente, ordena los resultados por número de estudiante.
Encuentre el comentario -- List the student numbers of all students whose grades in course "11" are higher than those in course "12", and sort them in ascending order.
```
SELECT sc11.sno
FROM SC sc11
JOIN SC sc12 ON sc11.sno = sc12.sno AND sc12.cno = 12
WHERE sc11.cno = 11 AND sc11.grade > sc12.grade
ORDER BY sc11.sno;
```
Esta instrucción SQL primero une la tabla SC consigo misma para obtener la información de calificación para el curso 11 y el curso 12. Luego filtra los registros donde el identificador de curso es 11 y la calificación es mayor que la calificación del curso 12, y selecciona el número de estudiante. Finalmente, ordena los resultados por número de estudiante.
Encuentre el comentario -- List the names of all students who are older than "John Davis".
```
SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;
```
Esta instrucción SQL primero selecciona el valor de sage para el estudiante con el nombre "John Davis" y lo almacena en una subconsulta zs. Luego selecciona el sname de la tabla S donde la edad del estudiante (sage) es mayor que la edad de "John Davis".
Encuentre el comentario -- List the student number of the student with the highest grade among all students who have taken course "12".
```
SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
  SELECT MAX(grade)
  FROM SC
  WHERE cno = 12
);
```
Esta instrucción SQL primero selecciona el sno de la tabla SC donde el identificador de curso es 12 y la calificación es igual a la calificación máxima entre todos los estudiantes que han tomado el curso 12. Esto nos dará el número de estudiante del estudiante con la calificación más alta en el curso 12.
Encuentre el comentario -- List the names of all students, along with their selected course IDs and grades.
```
SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;
```
Esta instrucción SQL realiza un left join entre las tablas S y SC, lo que incluirá a todos los estudiantes de la tabla S, incluso si no tienen registros en la tabla SC. Esto garantizará que todos los nombres de estudiantes estén incluidos en el resultado, junto con sus identificadores de cursos y calificaciones (si los tienen).
Encuentre el comentario -- List the student numbers and total grades (alias as: SG) of students who have taken four or more courses, sorted in descending order of total grades.
```
SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;
```
Esta instrucción SQL primero agrupa la tabla SC por número de estudiante (sno) y calcula la suma de las calificaciones para cada estudiante (alias como SG). Luego filtra los resultados para incluir solo a los estudiantes que han tomado cuatro o más cursos utilizando la cláusula HAVING. Finalmente, ordena los resultados por las calificaciones totales en orden descendente.

Ejecutar el Script SQL

En este último paso, ejecutarás el script SQL que creaste en los pasos anteriores.

En el cliente de MySQL, ejecuta el siguiente comando para ejecutar el script answer.sql:

SOURCE /home/labex/project/answer.sql

Esto ejecutará las consultas SQL en el archivo answer.sql y mostrará los resultados.

¡Felicitaciones! Has completado el proyecto. Deberías ver la siguiente salida:

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4 filas en el conjunto (0.013 segundos)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2 filas en el conjunto (0.000 segundos)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7 filas en el conjunto (0.000 segundos)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4 filas en el conjunto (0.001 segundos)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2 filas en el conjunto (0.013 segundos)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2 filas en el conjunto (0.000 segundos)

+-----------------+------+-------+
| sname           | cno  | grade |
+-----------------+------+-------+
| James Johnson   |   11 |    50 |
| James Johnson   |   12 |    80 |
| James Johnson   |   13 |    50 |
| James Johnson   |   14 |    50 |
| James Johnson   |   15 |    50 |
| Michael Brown   |   11 |    70 |
| Michael Brown   |   12 |    65 |
| Michael Brown   |   15 |    70 |
| John Davis      |   11 |    90 |
| John Davis      |   12 |    70 |
| John Davis      |   14 |    80 |
| John Davis      |   15 |    90 |
| Robert Miller   |   11 |    80 |
| Robert Miller   |   12 |    50 |
| Robert Miller   |   13 |    70 |
| Robert Miller   |   14 |    62 |
| Robert Miller   |   15 |    80 |
| William Wilson  |   11 |    40 |
| William Wilson  |   12 |    90 |
| William Wilson  |   13 |    60 |
| William Wilson  |   15 |    61 |
| David Moore     |   11 |    30 |
| David Moore     |   12 |    50 |
| David Moore     |   13 |    40 |
| David Moore     |   14 |    30 |
| David Moore     |   15 |    35 |
| Richard Taylor  |   11 |    90 |
| Richard Taylor  |   12 |    80 |
| Richard Taylor  |   13 |    80 |
| Richard Taylor  |   15 |    80 |
| Joseph Anderson |   12 |    90 |
| Joseph Anderson |   14 |    70 |
| Joseph Anderson |   15 |    95 |
| Charles Thomas  | NULL |  NULL |
+-----------------+------+-------+
34 filas en el conjunto (0.001 segundos)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6 filas en el conjunto (0.000 segundos)

Resumen

¡Felicitaciones! Has completado este proyecto. Puedes practicar más laboratorios en LabEx para mejorar tus habilidades.