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

SQLSQLBeginner
Практиковаться сейчас

💡 Этот учебник переведен с английского с помощью ИИ. Чтобы просмотреть оригинал, вы можете перейти на английский оригинал

Введение

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

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

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

🎯 Задачи

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

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

🏆 Достижения

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

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

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

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

  1. Запустите службу MySQL с помощью команды sudo без пароля:
sudo service mysql start
  1. Войдите в клиент MySQL с помощью команды sudo без пароля:
sudo mysql
  1. Импортируйте данные из предоставленного скрипта personnel.sql в базу данных MySQL:
SOURCE /home/labex/project/initdb.sql

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

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

  1. Откройте файл answer.sql в директории /home/labex/project.

  2. Добавьте SQL-выражение ниже комментария:

  3. Найдите комментарий -- 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), совпадающий с текущим студентом. Если таких записей нет, имя студента будет включено в результат.

  4. Найдите комментарий -- 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.

  5. Найдите комментарий -- 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. В конце оно сортирует результаты по номеру студента.

  6. Найдите комментарий -- 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, и выбирает номер студента. В конце оно сортирует результаты по номеру студента.

  7. Найдите комментарий -- 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".

  8. Найдите комментарий -- 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.

  9. Найдите комментарий -- 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. Это обеспечит то, что все имена студентов будут включены в результат, вместе с их идентификаторами курсов и оценками (если они есть).

  10. Найдите комментарий -- 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-скрипт, который вы создали в предыдущих шагах.

  1. В клиенте 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, чтобы улучшить свои навыки.