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 ...
to
... 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.
-- John
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
Good_Wands
andCheapest
to 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.
-- John
I like the use of the CTE.
-- Daniel
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.
-- Daniel
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
cheap
andgood
which correspond with myGood_Wands
andCheapest
.
-- John