エンジニア騎士とクエリの魔女 SQL問題獣の足跡(paizaランクA相当)を解いてみました。

獣の足跡（paizaランク：A相当）

モンスターの属性にはそれぞれ弱点となる属性がある。 「Graffiacane」の弱点となる属性をもった強力なボスモンスターを召喚し、「Graffiacane」を討伐せよ。

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」と名付けてください。

解答例1(SQL)

()のサブクエリから実行されます。Graffiacaneの属性idを取り出すところで、属性名で取り出そうとすると、弱点の属性名を取り出すときに、ややこしくなります。属性idで取り出せば、スッキリします。

-- Let's チャレンジ！！
/*召喚*/
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
WHERE Grade.name = "Boss" 
  AND Element.name = ( --弱点の属性名を受け取る
    /*弱点の属性名*/
    SELECT Element.name --弱点の属性名を渡す
    FROM ElementCompatibility
    JOIN Element
    ON ElementCompatibility.weakness_element_id = 
       Element.id -- 弱点の属性idを属性名と結びつける
    WHERE element_id = ( -- 属性idで受け取る
      /*Graffiacaneの属性id*/
      SELECT element_id -- ここは属性idで渡す
      FROM Hell
      WHERE name = "Graffiacane"
    ) 
);

解答例2(SQL)

サブクエリは全てidの受け渡しにすると、より短縮します。

-- Let's チャレンジ！！
/*召喚*/
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
WHERE Grade.name = "Boss" 
  AND Element.id = ( -- 弱点の属性idを受け取る
    /*弱点の属性id*/
    SELECT weakness_element_id -- 弱点の属性idを渡す
    FROM ElementCompatibility
    WHERE element_id = ( -- 属性idを受け取る
      /*Graffiacaneの属性id*/
      SELECT element_id  -- 属性idを渡す
      FROM Hell
      WHERE name = "Graffiacane"
    ) 
);

解答例3(SQL)

"Graffiacane"の弱点属性のidを取り出すところは、HellテーブルとElementCompatibilityテーブルをJOINすると、さらに短縮できます。

-- Let's チャレンジ！！
/*召喚*/
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
WHERE Grade.name = "Boss" 
  AND Element.id = ( -- 弱点の属性idを受け取る
    /* HellテーブルとElementCompatibilityテーブルをJOINして 
    一発で"Graffiacane"の弱点属性のidを取り出す */
    SELECT weakness_element_id -- 弱点の属性idを渡す
    FROM Hell
    JOIN ElementCompatibility
    ON Hell.element_id = ElementCompatibility.element_id
    WHERE name = "Graffiacane"
  ) 
);