公式動画が分かりやすく、早めに見れば良かった
クエリの難易度Aは終わった
CODE問題と比べると簡単だった(3時間苦戦した)

難易度A

獣の足跡

問題

Hellテーブルから、モンスターの「Graffiacane」(Hellテーブルのnameが「 Graffiacane 」のレコード) の弱点となる属性名を持ち、階級が「Boss」であるレコードをすべて出力してください。属性名はHellテーブルのelement_idを外部キーとしてElementテーブルを外部参照してください。弱点はElementCompatibilityテーブルに格納されており、弱点に対応する属性名はelement_id、weakness_element_idを外部キーとしてElementテーブルを外部参照することで取得できます。階級はHellテーブルのgrade_idを外部キーとしてGradeテーブルを外部参照してください。なお、レコードを出力する際に表示するカラムは、Hellテーブルのid, Hellテーブルのname, Elementテーブルのname, Gradeテーブルのnameとし、Elementテーブルのnameには「element」、Gradeテーブルのnameには「grade」と名付けてください。

回答

訳が分からなくなってきたので
最終的に公式の回答を見た

高難度SQL問題の解答例公開！『エンジニア騎士とクエリの魔女』でSQLをマスターしよう - paiza開発日誌

動画

www.youtube.com

1_間違い

SELECT Hell.id, Hell.name, Element.name AS element, Grade.name AS grade
FROM Hell
JOIN Element ON Hell.element_id = Element.id
JOIN Element ON ElementCompatibility.weakness_element_id = Element.id
JOIN Grade ON Hell.grade_id = Grade.id
WHERE
  Hell.name = 'Graffiacane'
  AND
  ElementCompatibillity.weakness_element_id
  AND
  Grade.name = 'Boss'

2_間違い

SELECT Hell.id, Hell.name, Element.name AS element, Grade.name AS grade
FROM Hell
JOIN Element ON Hell.element_id = Element.id
JOIN ElementCompatibility ON ElementCompatibility.id = Element.id
JOIN Element AS Weakness_element ON ElementCompatibility.weakness_element_id = Element.id
JOIN Grade ON Hell.grade_id = Grade.id
WHERE
  Hell.name = 'Graffiacane'
  AND
  Weakness_element.weakness_element_id = Element.id
  AND
  Grade.name = 'Boss'

3_間違い

SELECT Hell.id, Hell.name, Element.name AS element, Grade.name AS grade, WeaknessElement.name AS weakness_element

FROM Hell

JOIN Element ON Hell.element_id = Element.id
JOIN Grade ON Hell.grade_id = Grade.id
JOIN ElementCompatibility ON Hell.element_id = ElementCompatibility.element_id
JOIN Element AS WeaknessElement ON ElementCompatibility.weakness_element_id = WeaknessElement.id

WHERE
  Hell.name = 'Graffiacane'
  AND
  Grade.name = 'Boss'

4_正解

公式のまんま

SELECT
  Hell.id,
  Hell.name, 
  Element.name AS element,
  Grade.name AS grade
FROM 
  Hell
    JOIN Grade ON Hell.grade_id = Grade.id
    JOIN Element ON Hell.element_id = Element.id
WHERE
  element_id IN(
    SELECT
      weakness_element_id
    FROM
      Hell
      JOIN ElementCompatibility ON Hell.element_id = ElementCompatibility.element_id
    WHERE
      name = 'Graffiacane')
  AND
    Grade.name = 'Boss';

参考

あまり参考にならない参考

Hellテーブルから、

FROM Hell

モンスターの「Graffiacane」(Hellテーブルのnameが「 Graffiacane 」のレコード)

の弱点となる属性名を持ち、

階級が「Boss」であるレコードをすべて出力してください。

WHERE Hell.name = 'Graffiacane' AND ElementCompatibillity.weakness_element_id ? AND Grade.name = 'Boss'

属性名はHellテーブルのelement_idを外部キーとしてElementテーブルを外部参照してください。

JOIN Element ON Hell.element_id = Element.id

弱点はElementCompatibilityテーブルに格納されており、弱点に対応する属性名はelement_id、weakness_element_idを外部キーとしてElementテーブルを外部参照することで取得できます。

JOIN Element ON ElementCompatibility.weakness_element_id = Element.id

階級はHellテーブルのgrade_idを外部キーとしてGradeテーブルを外部参照してください。

JOIN Grade ON Hell.grade_id = Grade.id

なお、レコードを出力する際に表示するカラムは、Hellテーブルのid, Hellテーブルのname, Elementテーブルのname, Gradeテーブルのnameとし、Elementテーブルのnameには「element」、Gradeテーブルのnameには「grade」と名付けてください。

SELECT Hell.id, Hell.name, Element.name AS element, Grade.name AS grade

この問題を解決するためには、SQLのJOINを使用して、ElementCompatibilityテーブルとElementテーブルを関連付ける必要があります。

ElementCompatibilityテーブルのelement_idとweakness_element_idは、どちらもElementテーブルのidに対応します。したがって、Elementテーブルを2回JOINすることで、ElementCompatibilityテーブルのそれぞれのidに対応する属性名を取得することができます。

次に示すのは一般的なSQL文の書き方の例です。これを参考にして、具体的なテーブルとカラム名に置き換えてみてください。

SELECT ec.id, e1.name AS element_name, e2.name AS weakness_element_name
FROM ElementCompatibility ec
JOIN Element e1 ON ec.element_id = e1.id
JOIN Element e2 ON ec.weakness_element_id = e2.id;

このSQL文は、ElementCompatibilityテーブルの各行について、対応するelementの名前（element_name）とweakness_elementの名前（weakness_element_name）を取得します。この結果を使って、各elementがどのweakness_elementに対して弱いのかを調べることができます。

具体的なテーブル構造や問題の詳細によっては、上記のコードを適宜調整する必要があります。例えば、特定の条件に基づいてデータをフィルタリングする必要がある場合は、WHERE句を追加します。また、特定の順序で結果を表示する必要がある場合は、ORDER BY句を使います。

提出されたSQLクエリにはいくつかの問題点があります。それらを以下に列挙します。

ElementCompatibility テーブルが FROM 句または JOIN 句で結連されていません。ElementCompatibility テーブルは弱点情報を提供するため、クエリに含める必要があります。
Element テーブルを JOIN するときに同じ名前を2回使用しています。別々のエイリアス名を使用して、各接続を区別する必要があります。
WHERE 句にある ElementCompatibillity.weakness_element_id は単独で使われていて、条件が明確でないです。この部分はどのような条件を表現したいのかがはっきりしていません。

上記の問題点を解消したクエリは以下のようになります：

SELECT Hell.id, Hell.name, Element.name AS element, Grade.name AS grade
FROM Hell
JOIN Element ON Hell.element_id = Element.id
JOIN Grade ON Hell.grade_id = Grade.id
JOIN ElementCompatibility ON Hell.element_id = ElementCompatibility.element_id
JOIN Element AS WeaknessElement ON ElementCompatibility.weakness_element_id = WeaknessElement.id
WHERE Hell.name = 'Graffiacane' AND Grade.name = 'Boss';

上記のクエリは Hell テーブルから Graffiacane という名前のモンスターを見つけ、そのモンスターが所属する属性と階級を取得し、その属性が他の属性に対して弱点を持つかどうかを調べます。ただし、ここでは弱点属性の名前を取得していません。どの情報を出力するかによって、このクエリはさらに修正が必要かもしれません。

なぜJOIN Element AS WeaknessElement ON ElementCompatibility.weakness_element_id = WeaknessElement.idをする？

もちろんです。それでは、この問題を、ある特定のアニメキャラクターについて話していると想像してみましょう。

例えば、私たちは「モンスター学園」（Hellテーブル）に通う「グラフィアケイン」（モンスターの名前）というキャラクターについて知りたいとします。特に、彼の属性（Elementテーブル）と階級（Gradeテーブル）について知りたいと思っています。

さらに、「モンスター学園」には「属性相性」（ElementCompatibilityテーブル）というものがあり、これはどの属性がどの属性に対して強いか、弱いかを示しています。例えば、「水属性」は「火属性」に強い、「火属性」は「風属性」に強い、などといった関係です。

ここで、「グラフィアケイン」の属性が何であれ、その属性が弱点とする他の属性（weakness_element_id）を見つけたいと思います。しかし、ElementCompatibilityテーブルでは、それらの属性はただの数字（ID）で表されていて、何が何だか分からない状態です。

そこで、再度属性（Elementテーブル）を参照し、その数字（ID）が何の属性を表しているのかを見つける必要があります。しかし、すでに一度Elementテーブルを参照しています（モンスター自身の属性を見つけるため）。もし、もう一度同じテーブルを使おうとすると、コンピュータは混乱してしまいます。「えっ、どの属性を指しているの？グラフィアケインの属性？それともグラフィアケインの弱点属性？」となります。

そこで、コンピュータに混乱させないために、もう一度属性テーブルを参照するときには、別の名前（ここでは "WeaknessElement"）を使います。このようにすることで、「グラフィアケインの属性（Element）」と「グラフィアケインの弱点属性（WeaknessElement）」を明確に区別することができます。

したがって、"JOIN Element AS WeaknessElement ON ElementCompatibility.weakness_element_id = WeaknessElement.id"は、各モンスターの弱点属性を明確にするための重要なステップなのです。

JOIN Element AS WeaknessElement ON Element.id = ElementCompatibility.weakness_element_idが間違えで JOIN Element AS WeaknessElement ON ElementCompatibility.weakness_element_id = WeaknessElement.idが正解な理由

それはJOINの基本的な作業方法によるものです。JOINは、2つのテーブルを結合する際に、それらが共通の値を持つレコードをマッチさせる操作です。

「ON」句は、テーブルをどの列で結合するかを定義します。したがって、"ON ElementCompatibility.weakness_element_id = WeaknessElement.id"のように書くと、「ElementCompatibilityテーブルのweakness_element_id」列の値が「WeaknessElement（元のElementテーブル）のid」列の値と一致する行をマッチさせます。

逆に"ON Element.id = ElementCompatibility.weakness_element_id"と書くと、「Elementテーブルのid」列の値が「ElementCompatibilityテーブルのweakness_element_id」列の値と一致する行をマッチさせます。しかし、この場合、ElementテーブルはWeaknessElementとしてエイリアスされていません。したがって、このコードはWeaknessElementを正しく参照していないためエラーとなります。

したがって、テーブルを結合する際は、結合する列が正しくエイリアス化されたテーブルから選択されていることを確認することが重要です。

この理解を表形式で表すと以下のようになります。次のテーブルを考えてみてください：

Elementテーブル

id	name
1	Fire
2	Water
3	Air
4	Earth

ElementCompatibilityテーブル

element_id	weakness_element_id
1	2
2	3
3	4
4	1

JOIN操作をする際に、"ON ElementCompatibility.weakness_element_id = WeaknessElement.id"のように書くと、以下のような結果になります。

weakness_element_id	name
2	Water
3	Air
4	Earth
1	Fire

逆に、"ON Element.id = ElementCompatibility.weakness_element_id"と書くと、JOIN操作は期待した結果を返さないでしょう。なぜなら、この記述はElementテーブルのidとElementCompatibilityテーブルのweakness_element_idを一致させようとしていますが、我々が欲しいのはWeaknessElementのidとElementCompatibilityテーブルのweakness_element_idの一致です。

そのため、元の記述（"ON ElementCompatibility.weakness_element_id = WeaknessElement.id"）が正しいのです。

火属性の弱点 = 水属性って感じか
間違いの方は、element_id と weakness_element_id を一致させようとするから間違えなのか

公式動画

https://www.youtube.com/watch?v=htTNHUZVmnI&t=1588s