Students Whose Best Friends Have Higher Salaries SQL Query
You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and 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 Input
Sample Output
Samantha
Julia
Scarlet
Solution:
Select S.Name
From ( Students S join Friends F using(ID)
join Packages P1 on S.ID=P1.ID
join Packages P2 on F.Friend_ID=P2.ID)
Where P2.Salary > P1.Salary
Order By P2.Salary;
Explanation:
- The query joins the Students, Friends, and Packages tables to match students with their friends and their respective salaries.
- It uses the USING(ID) clause to link students with their friends.
- It compares the salary of each friend (P2.Salary) to the salary of the student (P1.Salary).
- The WHERE clause filters results to include only those where the friend's salary is greater than the student's salary.
- The query orders the resulting student names by the friend's salary in ascending order.
