HackerRank - SQL - Basic Join

hackerRank-SQL-basic-join.sql

/* Ollivander's Inventory */
SELECT w2.id, t.age, t.price, t.power
FROM Wands AS w2
JOIN Wands_Property AS p2 ON w2.code = p2.code
INNER JOIN (
    SELECT p.age AS age, MIN(w1.coins_needed) AS price, w1.power AS power
    FROM Wands AS w1
    JOIN Wands_Property AS p ON w1.code = p.code
    WHERE p.is_evil = 0
    GROUP BY p.age, w1.power
) AS t ON p2.age = t.age AND w2.coins_needed = t.price AND w2.power = t.power
ORDER BY t.power DESC, t.age DESC

/* Top Competitors */
SELECT s.hacker_id, h.name
FROM Submissions AS s
JOIN Hackers AS h ON s.hacker_id = h.hacker_id
JOIN Challenges AS c ON s.challenge_id = c.challenge_id
JOIN Difficulty AS d ON c.difficulty_level  = d.difficulty_level 
WHERE s.score = d.score
GROUP BY s.hacker_id, h.name
HAVING COUNT(s.hacker_id) > 1
ORDER BY COUNT(s.hacker_id) DESC, s.hacker_id ASC

/* The Report */
SELECT
    CASE 
        WHEN g.Grade < 8 THEN 'NULL'
        ELSE s.Name
    END
    , g.Grade, s.Marks
FROM Students AS s, Grades AS g
WHERE s.Marks >= g.Min_Mark AND s.Marks <= g.Max_Mark 
ORDER BY g.Grade DESC,
    CASE 
        WHEN g.Grade < 8 THEN s.Marks
        ELSE s.Name 
    END ASC

/* Average Population of Each Continent */
SELECT COUNTRY.Continent, FLOOR(AVG(CITY.Population))
FROM CITY, COUNTRY
WHERE CITY.CountryCode = COUNTRY.Code
GROUP BY COUNTRY.Continent

/* African Cities */
SELECT CITY.NAME
FROM CITY, COUNTRY
WHERE CITY.CountryCode = COUNTRY.Code AND COUNTRY.CONTINENT = "Africa"

/* Population Census */
SELECT SUM(CITY.POPULATION)
FROM CITY, COUNTRY
WHERE CITY.CountryCode = COUNTRY.Code AND COUNTRY.CONTINENT = "Asia"