์ธ ๊ฐ์ ํ
์ด๋ธ์ด ์ ๊ณต
Studentsย
โข
ID
โข
Name
Friends
โข
ID
โข
Friend_ID ย (ID ย of the ONLY best friend).
Packages
โข
ID
โข
Salary
(offered salary in $ย thousandsย per month).
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.
Sample Output
Samantha
Julia
Scarlet
Plain Text
๋ณต์ฌ
โข
ํ๋ฆผ
SELECT
MyName
FROM
(
SELECT
S.Name MyName,
P1.Salary Salary1,
P2.Salary Salary2
FROM Friends F
JOIN Students S ON S.ID = F.ID
JOIN Packages P1 ON P1.ID = S.ID
JOIN Packages P2 ON P2.ID = F.Friend_ID
) T
WHERE Salary1 < Salary2
ORDER BY Salary2 DESC -- ์ด๋ถ๋ถ์ด ํ๋ฆผ
;
SQL
๋ณต์ฌ
โข
ASC order - ์ ๋ต
SELECT
MyName
FROM
(
SELECT
S.Name MyName,
P1.Salary Salary1,
P2.Salary Salary2
FROM Friends F
JOIN Students S ON S.ID = F.ID
JOIN Packages P1 ON P1.ID = S.ID
JOIN Packages P2 ON P2.ID = F.Friend_ID
) T
WHERE Salary1 < Salary2
ORDER BY Salary2
;
SQL
๋ณต์ฌ