[hackerrank][SQL][Intermediate]#2

↑https://www.hackerrank.com/domains/sql?filters%5Bskills%5D%5B%5D=SQL%20%28Intermediate%29 

Solve SQL Code Challenges

☆The Report

select if(g.Grade >=8, s.Name, NULL), g.Grade, s.Marks 

from Students s
    left join Grades g 
        on s.Marks between g.Min_Mark and g.Max_Mark

order by g.Grade desc, s.Name

▶ join의 on조건은 등호가 아니더라도 사용이 가능 > on s.Marks between g.Min_Mark and g.Max_Mark

 

/* another solution */
SELECT IF(GRADE < 8, NULL, NAME), GRADE, MARKS
FROM STUDENTS JOIN GRADES
WHERE MARKS BETWEEN MIN_MARK AND MAX_MARK
ORDER BY GRADE DESC, NAME

▶ ON 조건 없이 JOIN (=CROSS JOIN)으로 연결시켜 놓은 데이터 들 중 WHERE 조건으로 조건 설정해서 조회도 가능

 

☆Top Competitors

select H.hacker_id, H.name

/* C.hacker_id isn't same with H.hacker_id */
from Submissions S
    inner join Hackers H on H.hacker_id = S.hacker_id
    inner join Challenges C on C.Challenge_id = S.challenge_id
    inner join DIfficulty D on C.difficulty_level = D.difficulty_level

where D.score = S.score 

group by H.hacker_id, H.name
having count(H.hacker_id) >1

order by count(H.hacker_id) desc, H.hacker_id asc

▶ 연속 join , where와 having 동시 사용

 

☆Ollivander's Inventory

SELECT W.id, WP.age, W.coins_needed, W.power 
FROM Wands W LEFT JOIN Wands_property WP 
    ON W.code = WP.code    
where WP.is_evil=0
AND W.coins_needed = (SELECT min(W2.coins_needed)
                      FROM Wands W2 
                       LEFT JOIN Wands_property WP2
                         ON W2.code = WP2.code
                       WHERE WP2.is_evil=0
                         AND WP.age = WP2.age
                         AND W.power = W2.power)
order by power desc, age desc

▶ 서브쿼리에서 메인쿼리 변수와 조건을 설정 > WP.age = WP2.age

 

☆☆Challenges

SELECT H.hacker_id, H.name, count(C.challenge_id) challenges_created

FROM Hackers H 
    LEFT JOIN Challenges C 
    ON H.hacker_id = C.hacker_id

GROUP BY H.hacker_id, H.name
HAVING 
/* condition 1 : max */
        challenges_created =
        (
        select max(sub1.CNT) 
        from (select count(*) CNT
              from Challenges C
              group by C.hacker_id) sub1
        )
     or 
/* condition 2 : counts=1 */
        challenges_created in 
        (
        select sub2.CNT
        from (select C.hacker_id, count(*) CNT 
              from Challenges C
              group by C.hacker_id) sub2    
        group by sub2.CNT
        having count(*) = 1
        )
ORDER BY challenges_created DESC, H.hacker_id

▶ 서브쿼리의 alias를 잘 설정해주기 ex ) sub1.CNT, sub2.CNT

▶ 조건에 맞는 절 잘 찾기