このステップでは、「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つ以上のコースを履修した学生のみを結果に含めるようにフィルタリングします。最後に、合計点数の降順で結果をソートします。
