Extract the information

1. Output only the student name
2. Their best friend has Salary > them
3. ORDER Salary of Best Friends

So we need to compare the the salary of Friends and Best Friends. Questions: 1. How to write a query to compare Salary? Solution: using self join on table that has Salary which is Packages 2. We need to compare Salary based on ID and Friend_ID, how to do this? Solution: we want to compare Salary therefore Salary is the main query.

Steps 1. Let's do a SELF JOIN to get Salary of students and best friends

SELECT *
   FROM friends f
        ON s.id = f.id
INNER JOIN packages p1  --we determined p1 as students
        ON p1.id = f.id
INNER JOIN packages p2  --we determined p2 as best friend. You can see that we joined table Packages with Friend_id
        ON p2.id = f.friend_id

2. Add WHERE clause to compare the salary

       WHERE p2.salary > p1.salary
  ORDER BY p2.salary

3. Output required only the name so we need to use the Students table as main table and JOIN ther

    SELECT s.name
      FROM students s
INNER JOIN step1

4. Apply ORDER BY as required.

Below is the completed query.

SELECT s.name
FROM students s
INNER JOIN friends f ON s.id = f.id
INNER JOIN packages p1 ON s.id = p1.id
INNER JOIN packages p2 ON f.friend_id = p2.id
WHERE p2.salary > p1.salary
ORDER BY p2.salary;