JUNTO Practice - "Ollivander's Inventory"
Discussed on 2019-10-06.
Problem is "Ollivander's Inventory".
Hint: This query is looking for the cheapest wand for a given combination of age and power. In the
WHERE clause of your query, use a subquery to make sure the it has the minimum coins needed compared to all other wands of the same age and power.
Solution - Daniel
SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER FROM WANDS W, WANDS_PROPERTY P WHERE W.CODE = P.CODE AND P.IS_EVIL = '0' AND COINS_NEEDED = (SELECT MIN(COINS_NEEDED) FROM WANDS WHERE CODE = W.CODE AND POWER =W.POWER) GROUP BY P.AGE, W.COINS_NEEDED, W.ID, W.POWER ORDER BY W.POWER DESC, P.AGE DESC;
Instead of using a cross join and a condition in the where clause I think you should just do an inner join. Changing
... FROM WANDS W, WANDS_PROPERTY P WHERE W.CODE = P.CODE AND ...
... FROM WANDS W INNER JOIN WANDS_PROPERTY P ON W.CODE = P.CODE WHERE ...
I don't think the group by clause is needed in this case. (Test it out just to confirm that.) I also really like your use of the correlated subquery
COINS_NEEDED = (SELECT MIN(COINS_NEEDED) ...)
I find it easy to read, and I think it's a good design choice.
Solution - John
WITH Good_Wands(id, coins_needed, power, age) AS ( SELECT id, coins_needed, power, age FROM Wands INNER JOIN Wands_Property ON Wands.code = Wands_Property.code WHERE is_evil = 0 ), Cheapest(coins_needed, power, age) AS ( SELECT MIN(coins_needed), power, age FROM Good_Wands GROUP BY power, age ) SELECT id, age, coins_needed, power FROM Cheapest NATURAL JOIN Good_Wands ORDER BY power DESC, age DESC ;
I'm using a natural join instead of an inner join because I designed the common table expressions
Cheapestto have corresponding attributes. An equivalent inner join would look like
... FROM Good_Wands INNER JOIN Cheapest ON Good_Wands.coins_needed = Cheapest.coins_needed AND Good_Wands.power = Cheapest.power AND Good_Wands.age = Cheapest.age ...
Daniel, I thought about naming tables after our discussion last week.
I like the use of the CTE.
Solution - Oscar
SELECT good.id, good.age, cheap.coins_needed, good.power FROM ( SELECT WP.age, MIN(W.coins_needed) as coins_needed, W.power FROM Wands as W LEFT JOIN Wands_Property as WP ON W.Code=WP.Code WHERE WP.is_evil = 0 GROUP BY W.power,WP.age ) as cheap NATURAL JOIN ( SELECT W2.id, W2.coins_needed, W2.power, WP2.age FROM Wands as W2 LEFT JOIN Wands_Property as WP2 ON W2.Code = WP2.Code WHERE WP2.is_evil = 0 ) as good ORDER BY good.power DESC,good.age DESC;
Looks like this and John's solution have a similar technique.
In this case I don't think you need to use a left join, and in my opinion an inner join would be more clear. It looks like you are using a similar structure to my solution, your subqueries are named
goodwhich correspond with my