Мастерство в SQL-запросах для анализа учебного процесса студентов

Введение

В этом проекте вы научитесь выполнять различные SQL-запросы к базе данных студентов и курсов. База данных состоит из трех таблиц: таблица студентов (S), таблица курсов (C) и таблица студентов и курсов (SC). Вы научитесь получать и обрабатывать данные из этих таблиц, чтобы выполнить разные задачи.

👀 Предварительный просмотр

Предварительный просмотр базы данных SQL

🎯 Задачи

В этом проекте вы научитесь:

Перечислить имена всех студентов, которые не посещали курсы, преподаваемые преподавателем "Daniel"
Перечислить номера и имена студентов, которые не сдали (оценка < 60) по двум и более курсам
Перечислить имена всех студентов, которые посещали как курс "11", так и курс "12"
Перечислить номера студентов, чьи оценки по курсу "11" выше, чем по курсу "12", и отсортировать их в порядке возрастания
Перечислить имена всех студентов, старше "John Davis"
Перечислить номер студента с самой высокой оценкой среди всех студентов, которые посещали курс "12"
Перечислить имена всех студентов, а также их выбранные идентификаторы курсов и оценки
Перечислить номера студентов и общие оценки (с псевдонимом: SG) студентов, которые посещали четыре и более курсов, отсортированные в порядке убывания общего балла

🏆 Достижения

После завершения этого проекта вы сможете:

Разобраться, как писать сложные SQL-запросы для получения и обработки данных из реляционной базы данных
Применять различные SQL-техники, такие как подзапросы, соединения, агрегации и сортировка, для решения задач анализа данных в реальном мире
Развивать навыки SQL, которые необходимы для принятия решений, основанных на данных, и решения задач

Запустить MySQL и импортировать данные

В этом шаге вы научитесь запускать службу MySQL и импортировать необходимые данные в базу данных.

Запустите службу MySQL с помощью команды sudo без пароля:

sudo service mysql start

Войдите в клиент MySQL с помощью команды sudo без пароля:

sudo mysql

Импортируйте данные из предоставленного скрипта personnel.sql в базу данных MySQL:

SOURCE /home/labex/project/initdb.sql

Добавить код запроса

В этом шаге вы научитесь перечислять имена всех студентов, которые не посещали курсы, преподаваемые преподавателем "Daniel". Следуйте шагам ниже, чтобы завершить этот шаг:

Откройте файл answer.sql в директории /home/labex/project.
Добавьте SQL-выражение ниже комментария:
Найдите комментарий -- 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
);
```
Это SQL-выражение сначала выбирает столбец sname из таблицы S. Затем оно использует предложение NOT EXISTS, чтобы проверить, есть ли записи в таблице SC, которые имеют идентификатор курса (cno), совпадающий с курсом, преподаваемым преподавателем "Daniel", и идентификатор студента (sno), совпадающий с текущим студентом. Если таких записей нет, имя студента будет включено в результат.
Найдите комментарий -- 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;
```
Это SQL-выражение сначала соединяет таблицы S и SC, чтобы получить информацию о студентах и их оценках по курсам. Затем оно фильтрует записи, где оценка меньше 60, группирует результаты по номеру студента и имени, и в конце выбирает только тех студентов, которые не сдали два и более курсов, используя предложение HAVING.
Найдите комментарий -- 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;
```
Это SQL-выражение сначала соединяет таблицы S и SC, чтобы получить информацию о студентах и их идентификаторах курсов. Затем оно фильтрует записи, где идентификатор курса равен 11 или 12, группирует результаты по номеру студента, и выбирает только тех студентов, которые прошли оба курса, используя предложение HAVING. В конце оно сортирует результаты по номеру студента.
Найдите комментарий -- 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;
```
Это SQL-выражение сначала соединяет таблицу SC с собой, чтобы получить информацию о оценках по курсу 11 и курсу 12. Затем оно фильтрует записи, где идентификатор курса равен 11 и оценка выше, чем оценка по курсу 12, и выбирает номер студента. В конце оно сортирует результаты по номеру студента.
Найдите комментарий -- 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;
```
Это SQL-выражение сначала выбирает значение sage для студента с именем "John Davis" и сохраняет его в подзапросе zs. Затем оно выбирает sname из таблицы S, где возраст студента (sage) больше возраста "John Davis".
Найдите комментарий -- 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
);
```
Это SQL-выражение сначала выбирает sno из таблицы SC, где идентификатор курса равен 12 и оценка равна максимальной оценке среди всех студентов, которые прошли курс 12. Это даст нам номер студента с самой высокой оценкой по курсу 12.
Найдите комментарий -- 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;
```
Это SQL-выражение выполняет левый внешний join между таблицами S и SC, который будет включать всех студентов из таблицы S, даже если у них нет записей в таблице SC. Это обеспечит то, что все имена студентов будут включены в результат, вместе с их идентификаторами курсов и оценками (если они есть).
Найдите комментарий -- 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;
```
Это SQL-выражение сначала группирует таблицу SC по номеру студента (sno) и вычисляет сумму оценок для каждого студента (алиас SG). Затем оно фильтрует результаты, чтобы включать только тех студентов, которые прошли четыре и более курсов, используя предложение HAVING. В конце оно сортирует результаты по общим оценкам в порядке убывания.

Запустить SQL-скрипт

В этом финальном шаге вы запустите SQL-скрипт, который вы создали в предыдущих шагах.

В клиенте MySQL выполните следующую команду, чтобы выполнить скрипт answer.sql:

SOURCE /home/labex/project/answer.sql

Это выполнит SQL-запросы в файле answer.sql и выведет результаты.

Поздравляем! Вы завершили проект. Вы должны увидеть следующий вывод:

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4 rows in set (0.013 sec)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2 rows in set (0.000 sec)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7 rows in set (0.000 sec)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4 rows in set (0.001 sec)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2 rows in set (0.013 sec)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2 rows in set (0.000 sec)

+-----------------+------+-------+
| 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 rows in set (0.001 sec)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6 rows in set (0.000 sec)

Резюме

Поздравляем! Вы завершили этот проект. Вы можете практиковаться в более многих лабораторных работах в LabEx, чтобы улучшить свои навыки.

SQL-запросы для анализа учебного процесса студентов