学生コース分析のための SQL クエリをマスターする

はじめに

このプロジェクトでは、学生コースデータベースで様々な SQL クエリを実行する方法を学びます。このデータベースは、3 つのテーブルで構成されています。学生テーブル（S）、コーステーブル（C）、および学生コーステーブル（SC）です。これらのテーブルからデータを取得して操作し、さまざまなタスクを達成する方法を学びます。

👀 プレビュー

SQL データベースプレビュー画像

🎯 タスク

このプロジェクトでは、以下を学びます。

「Daniel」先生が教えるコースを履修していないすべての学生の名前をリストアップする方法
2 つ以上のコースで不合格（点数 < 60）となった学生の学籍番号と名前をリストアップする方法
コース「11」とコース「12」の両方を履修したすべての学生の名前をリストアップする方法
コース「11」の点数がコース「12」の点数より高いすべての学生の学籍番号をリストアップし、昇順にソートする方法
「John Davis」より年上のすべての学生の名前をリストアップする方法
コース「12」を履修したすべての学生の中で最も高い点数を獲得した学生の学籍番号をリストアップする方法
すべての学生の名前と、選択したコース ID と点数をリストアップする方法
4 つ以上のコースを履修した学生の学籍番号と合計点数（エイリアス：SG）を、合計点数の降順にソートしてリストアップする方法

🏆 成果

このプロジェクトを完了すると、以下のことができるようになります。

関係型データベースからデータを取得して操作するための複雑な SQL クエリを書く方法を理解する
サブクエリ、結合、集約、ソートなどのさまざまな SQL テクニックを適用して、現実世界のデータ分析問題を解決する
データ駆動型の意思決定と問題解決に不可欠な SQL スキルを身につける

MySQL を起動してデータをインポートする

このステップでは、MySQL サービスを起動し、必要なデータをデータベースにインポートする方法を学びます。

パスワードなしでsudoコマンドを使用して MySQL サービスを起動します。

sudo service mysql start

パスワードなしでsudoコマンドを使用して MySQL クライアントにアクセスします。

sudo mysql

提供されたpersonnel.sqlスクリプトのデータを MySQL データベースにインポートします。

SOURCE /home/labex/project/initdb.sql

クエリコードを追加する

このステップでは、「Daniel」先生が教えるコースを履修していないすべての学生の名前をリストアップする方法を学びます。以下の手順に従ってこのステップを完了します。

/home/labex/project ディレクトリにある answer.sql ファイルを開きます。
コメントの下に 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 ステートメントはまず、S テーブルから sname 列を選択します。そして、NOT EXISTS 句を使用して、「Daniel」先生が教えるコースのコース ID（cno）が一致し、学生 ID（sno）が現在の学生と一致する SC テーブルにレコードがあるかどうかを確認します。そのようなレコードが存在しない場合、学生の名前が結果に含まれます。
コメント -- 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 句を使用して 2 つ以上のコースで不合格となった学生のみを選択します。
コメント -- 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 テーブルを結合して学生情報とそのコース ID を取得します。そして、コース ID が 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 の両方の点数情報を取得します。そして、コース ID が 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 ステートメントはまず、名前が「John Davis」の学生の sage 値を選択してサブクエリ zs に格納します。そして、学生の年齢（sage）が「John Davis」の年齢より大きい S テーブルから sname を選択します。
コメント -- 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 ステートメントはまず、コース ID が 12 で点数がコース 12 を履修したすべての学生の最高点数と等しい SC テーブルから sno を選択します。これにより、コース 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 ステートメントは、S テーブルと SC テーブルの間で左結合を行います。これにより、SC テーブルにレコードがなくても、S テーブルのすべての学生が含まれます。これにより、すべての学生の名前と、それらのコース ID と点数（もしあれば）が結果に含まれます。
コメント -- 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 句を使用して4つ以上のコースを履修した学生のみを結果に含めるようにフィルタリングします。最後に、合計点数の降順で結果をソートします。

SQL スクリプトを実行する

この最後のステップでは、前のステップで作成した SQL スクリプトを実行します。

MySQL クライアントで、次のコマンドを実行して answer.sql スクリプトを実行します。

SOURCE /home/labex/project/answer.sql

これにより、answer.sql ファイル内の SQL クエリが実行され、結果が表示されます。

おめでとうございます！このプロジェクトを完了しました。以下の出力が表示されるはずです。

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4行セット (0.013秒)

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2行セット (0.000秒)

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7行セット (0.000秒)

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4行セット (0.001秒)

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2行セット (0.013秒)

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2行セット (0.000秒)

+-----------------+------+-------+
| 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行セット (0.001秒)

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6行セット (0.000秒)

まとめ

おめでとうございます！このプロジェクトを完了しました。あなたは LabEx でさらに多くの実験を行って、あなたのスキルを向上させることができます。