HackerRank: [SQL Basic Join] (6/8) OLLIVANDER'S INVENTORY | inner join, min & Sub-Query in SQL

I started studying SQL from a very famous site - HackerRank. Here I will try to provide multiple approaches & solutions to the same problem. It will help you learn and understand SQL in a better way.

Please make use of my blog posts for learning purpose only and feel free to ask your questions in the comment box below in case of any doubt.

Click Here for the previous blog-post in the series.

Recommended SQL Courses:

1. Udemy: SQL - MySQL for Data Analytics and Business Intelligence
2. LinkedIn: Master SQL for Data Science
3. edX: Databases: Advanced Topics in SQL
4. edureka: SQL Essentials Training & Certification
5. Coursera: Learn SQL Basics for Data Science
6. Eduonix: SQL Queries

---

SQL Problem Statement:

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, sort the result in order of descending age.

Input Format:

The following tables contain data on the wands in Ollivander's inventory:

• Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).
  
  
  
• Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, (code1, age1) and (code2, age2), then code1!=cod2 and age1!=age2.
  

---

Sample Input:

Wands Table:

Wands_Property Table:

---

Sample Output:

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

Explanation:

The data for wands of age 45 (code 1):

• The minimum number of galleons needed for wand(age=45, power=2) = 6020
• The minimum number of galleons needed for wand(age=45, power=10) = 1647

The data for wands of age 40 (code 2):

• The minimum number of galleons needed for wand(age=40, power=1) = 5408
• The minimum number of galleons needed for wand(age=40, power=3) = 3312
• The minimum number of galleons needed for wand(age=40, power=5) = 7587
• The minimum number of galleons needed for wand(age=40, power=7) = 6018

The data for wands of age 20 (code 4):

• The minimum number of galleons needed for wand(age=20, power=5) = 504
• The minimum number of galleons needed for wand(age=20, power=6) = 7651
• The minimum number of galleons needed for wand(age=20, power=8) = 3688

The data for wands of age 17 (code 5):

• The minimum number of galleons needed for wand(age=17, power=3) = 5689
• The minimum number of galleons needed for wand(age=17, power=10) = 9897

---

Solution: Using INNER JOIN & HAVING (MySQL Query):

SELECT
  W.ID,
  WP.AGE,
  M.MIN_COINS_NEEDED,
  W.POWER
FROM
  WANDS W
  JOIN (SELECT CODE, POWER, MIN (COINS_NEEDED) as MIN_COINS_NEEDED FROM WANDS GROUP BY CODE, POWER) M 
  ON W.CODE = M.CODE AND W.POWER = M.POWER AND W.COINS_NEEDED = M.MIN_COINS_NEEDED
  JOIN WANDS_PROPERTY WP ON M.CODE = WP.CODE
WHERE WP.IS_EVIL = 0
ORDER BY M.POWER DESC, WP.AGE DESC;

NOTE: 

1. MIN is an aggregation function used to get the least (minimum) value of all the records in the column name passed to the function.
   
   
2. JOIN and INNER JOIN are the same in SQL. It returns the records that have matching values in both tables.

---

Expected Output:

1038 496 4789 10
1130 494 9439 10
1315 492 4126 10
9 491 7345 10
858 483 4352 10
1164 481 9831 10
1288 464 4952 10
861 462 8302 10
412 455 5625 10
996 451 8884 10
1608 446 8351 10
1376 443 1735 10
1330 430 5182 10

--------------------------------------------------------------------------------

Click here to see solutions for all Machine Learning Coursera Assignments.

&

Click here to see more codes for Raspberry Pi 3 and similar Family.

&

Click here to see more codes for NodeMCU ESP8266 and similar Family.

&

Click here to see more codes for Arduino Mega (ATMega 2560) and similar Family.

Feel free to ask doubts in the comment section. I will try my best to answer it.

If you find this helpful by any mean like, comment and share the post.

This is the simplest way to encourage me to keep doing such work.

Thanks & Regards,

-Akshay P Daga