해커랭크 - INNER JOIN, LEFT JOIN

 African Cities 
https://www.hackerrank.com/challenges/african-cities/problem

SELECT city.name FROM city
      INNER JOIN COUNTRY ON CITY.CountryCode = COUNTRY.CODE
WHERE continent = 'Africa'

 

 

Population Census
https://www.hackerrank.com/challenges/asian-population/problem

SELECT SUM(city.population) FROM city
       INNER JOIN country ON  city.CountryCode = Country.Code
WHERE CONTINENT = 'Asia'

 

 

New Companies - Medium 
https://www.hackerrank.com/challenges/the-company/problem

SELECT C.company_code 
       ,C.founder
       ,COUNT(DISTINCT LM.lead_Manager_code)
       ,COUNT(DISTINCT SM.senior_Manager_code)
       ,COUNT(DISTINCT M.manager_code)
       ,COUNT(DISTINCT E.employee_code)
       
FROM Company C
     LEFT JOIN Lead_Manager LM ON C.company_code = LM.company_code
     LEFT JOIN Senior_Manager SM ON LM.lead_manager_code = SM.lead_manager_code
     LEFT JOIN Manager M ON SM.senior_manager_code = M.senior_manager_code
     LEFT JOIN Employee E ON M.manager_code = E.manager_code

GROUP BY C.company_code, C.founder
ORDER BY C.Company_code

 

 

Top Competitors - Medium
https://www.hackerrank.com/challenges/full-score/problem

SELECT H.hacker_id
      ,H.name
FROM Submissions S
    INNER JOIN Challenges C ON C.Challenge_id = S.Challenge_id
    INNER JOIN Difficulty D ON C.Difficulty_level = D.Difficulty_level
    INNER JOIN Hackers H ON S.hacker_id =  H.hacker_id
WHERE S.score = D.score
GROUP BY H.hacker_id, H.name
HAVING COUNT(DISTINCT S.submission_id)>1     
ORDER BY COUNT(DISTINCT S.submission_id) DESC, H.hacker_id

 

Placements - Medium
https://www.hackerrank.com/challenges/placements/problem

SELECT NAME
FROM Friends F
     INNER JOIN Students S ON S.ID=F.ID
     INNER JOIN Packages P1 ON F.ID = P1.ID
     INNER JOIN Packages P2 ON F.Friend_ID = P2.ID
WHERE P2.salary > P1.salary
ORDER BY P2.salary