Question

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand. Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power.

Answer 1

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.