Prepare > SQL > Advanced Join > Placements

Placements | HackerRank

 

 

문제

---

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

 

=> 자기보다 친구 급여가 더 높은 놈들의 이름을 친구 급여 순으로 쿼리하라. 모든 학생의 급여는 다르다.

 

 

 

 

 

코드

---

SELECT S.NAME
FROM STUDENTS AS S
    INNER JOIN FRIENDS AS F ON S.ID = F.ID
        INNER JOIN PACKAGES AS P1 ON S.ID = P1.ID 
            LEFT JOIN PACKAGES AS P2 ON F.FRIEND_ID = P2.ID
WHERE P1.SALARY < P2.SALARY
ORDER BY P2.SALARY;

두 번째 시도 풀이

SELECT NAME
FROM 
    STUDENTS AS S
    LEFT JOIN PACKAGES AS P1 ON S.ID = P1.ID
    LEFT JOIN FRIENDS AS F ON S.ID = F.ID
    LEFT JOIN PACKAGES AS P2 ON F.Friend_ID = P2.ID
WHERE P1.Salary < P2.Salary
ORDER BY P2.Salary;

 

 

 

노트

---

필요 테이블 

SELECT *
FROM STUDENTS AS S
    INNER JOIN FRIENDS AS F ON S.ID = F.ID
        INNER JOIN PACKAGES AS P1 ON S.ID = P1.ID 
            LEFT JOIN PACKAGES AS P2 ON F.FRIEND_ID = P2.ID
;

1 Samantha 1 14 1 15.5 14 15.1
2 Julia 2 15 2 15.6 15 17.1
3 Britney 3 18 3 16.7 18 13.15
4 Kristeen 4 19 4 18.8 19 33.33
5 Dyana 5 20 5 31.5 20 22.16
6 Jenny 6 5 6 45 5 31.5
7 Christene 7 6 7 47 6 45
8 Meera 8 7 8 46 7 47
9 Priya 9 8 9 39 8 46
10 Priyanka 10 1 10 11.1 1 15.5
11 Paige 11 2 11 12.1 2 15.6
12 Jane 12 3 12 13.1 3 16.7
13 Belvet 13 4 13 14.1 4 18.8
14 Scarlet 14 9 14 15.1 9 39
15 Salma 15 10 15 17.1 10 11.1
16 Amanda 16 11 16 21.1 11 12.1
17 Maria 17 12 17 31.1 12 13.1
18 Stuart 18 13 18 13.15 13 14.1
19 Aamina 19 16 19 33.33 16 21.1
20 Amina 20 17 20 22.16 17 31.1

• 모두 1 ㄷ 1 조인이라 쉽게 접근함
• JOIN할 때 같은 테이블을 두 번 이상 써야한다면 서로 다른 별칭 붙여줘야 함