SQLによるクエリ

二つの異なる科目が同じタイトルを持つ

• 二つの科目がどちらも「Robotics」と呼ばれています。タイトルのみでグループ化すると、それらの登録を合体させ、異なる科目であることを隠してしまいます。
• SQL は、あなたが望むデータ結果を記述します。妥当と思われるカウントを信用する前に、行の同一性と期待される出力を確認してください。

ジョインにおける関係性を追跡する

• JOIN は、e.course_id = c.id などの関係性を用いて行を結合します。意図した関係性を明示的に記述してください。
• クロスジョインは、片側のすべての行を他方のすべての行とペアにします。SQLite では、制約のないジョインがその積生成物产生することを許容しますが、他のSQLシステムは欠落したジョイン条件を拒否することがあります。

登録数がゼロの科目を保持する

• LEFT JOINでは、一致する履修がないコースもすべて保持されます。一致しない履修のフィールドはnullになります。
• nullを除外する e.student_id を使用し、保持された未一致行もカウントしてしまう COUNT(*) は使用しないでください。空のコースにはゼロが報告されるべきです。

既知の答えを持つクエリを実行する

• この完全なスクリプトをローカルのSQLiteデータベースで実行してください。Roboticsの2つのコースは異なるIDを持ち、Artには履修がありません。
• GROUP BY でコースIDとタイトルの両方に基づいてグループ化します。期待される行は (7, Robotics, 1)、(8, Robotics, 2)、(9, Art, 0) です。

CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (student_id INTEGER, course_id INTEGER);
INSERT INTO courses VALUES (7, 'Robotics'), (8, 'Robotics'), (9, 'Art');
INSERT INTO enrolments VALUES (1, 7), (1, 8), (2, 8);
SELECT c.id, c.title, COUNT(e.student_id) AS enrolled
FROM courses AS c
LEFT JOIN enrolments AS e ON e.course_id = c.id
GROUP BY c.id, c.title
ORDER BY c.id;

異なる段階で行とグループをフィルタリングする

• WHERE はグループ化之前に入力行をフィルタリングします。HAVING は COUNT(e.student_id) >= 2 などの条件を使用してグループをフィルタリングします。
• この例では、ORDER BYの前にHAVING条件を追加すると、コース 8 のみが表示されます。WHEREで e.student_id をフィルタリングすると、保持したいはずの未一致行が削除されてしまいます。

エッジケースを使用して結果を確認する

• 重複したタイトル、一致なし、複数一致が既知のカウントと一致するか確認してください。見かけ上妥当な結果だけでは不十分です。
• 選択されたフィールド、ジョイン条件、グループ化、カウントについて説明してください。重複する関係行が有効か、スキーマによって防止すべきかを判断してください。