Answer:
SELECT a.id, ab.age, b.min_coins_needed, b.power
FROM(SELECT code, power, min(coins_needed) AS min_coins_needed
from Wands
group by power, code) AS b
JOIN Wands wa
ON a.coins_needed = b.min_coins_needed AND a.code = b.code AND a.power = b.power
JOIN Wands_property AS ab
ON a.code = ab.code
WHERE ab.is_evil = 0
ORDER BY b.power desc, ab.age DESC;
Explanation:
SELECT statement is used to select the columns id, age, coins_needed and power of the wands that Ron's interested in.
It is required to get the minimum coins_needed so, min() aggregate function is used to return the minimum value of coins_needed. So this is how minimum coins needed for wands with same age and power are attained and the resultant set is given the name of min_coins_needed using alias. This is then grouped by code and power using GROUP BY because code and age has one to one mapping.
Next the JOIN is used to combine columns from two or more relations. Here Wands and Wands_property are joined to obtain id and age.
WHERE clause is used to filter the wands that are not evil.
ORDERED BY is used to sort in order of power and age, the result obtained. DESC is used to sort the result in descending order of power and age.
