エンジニア騎士とクエリの魔女 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"
)
);