John Lekberg


JUNTO Practice - "Placements"

Discussed on 2019-09-18.

"Placements" problem statement.

Hint: Join the Packages table to the Friends table twice, then compare salaries.


Solution - Daniel

select s.name 
from students s 
inner join friends f on (s.id=f.id) 
inner join packages p on (s.id=p.id) 
where (select p.salary from packages p where p.id=f.friend_id)
      >
      (select p.salary from packages p where s.id=p.id)
order by (select p.salary from Packages p where p.id = f.friend_id);

Dan, I reformatted your code a bit to look better on the web page.

You don't need the parentheses on the join conditions (s.id=f.id) and (s.id=p.id). Studying joins more would probably be helpful. I recommend a book called "The Language of SQL" by Larry Rockoff, which has a few chapters on joins.

-- John


Solution - John

  SELECT Students.name
    FROM Packages P1
         INNER JOIN Friends
                    ON Friends.id = P1.id
         INNER JOIN Packages P2
                    ON P2.id = Friends.friend_id
         INNER JOIN Students
                    ON Students.id = P1.id
   WHERE P2.salary > P1.salary
ORDER BY P2.salary
;

Looks pretty concise.

-- Daniel


Using table names like "Package_Me" and "Package_Friend" could be more readable than "P1" and "P2". Here's a diagram showing how SQL joins work. As far as query performance, try using an EXPLAIN statement (SQLite, MySQL, ...).

-- John


Solution - Oscar

SELECT
    S.Name
FROM
    Students AS S
LEFT JOIN
    Packages AS P
    ON S.ID = P.ID
LEFT JOIN
    (
    SELECT
        Friends.ID,
        Packages.Salary
    FROM
        Friends
    LEFT JOIN
        Packages
        ON Friends.Friend_ID = Packages.ID
    ) AS F
    ON S.ID = F.ID
WHERE P.Salary < F.Salary
ORDER BY F.Salary ASC
;

Oscar, I fixed the formatting because your email mangled it.

Code could be clearer if you didn't use a subquery and instead joined on all the tables. The subquery produces a table of a student's ID and their friend's salary, which Daniel and I found a bit confusing. For this data, I don't think there's a difference between LEFT JOIN and INNER JOIN, but this problem is better solved with an inner join, because a left outer join might introduce NULL in attributes selected from the F and P tables. ASC is the default in an ORDER BY clause, so you can omit it (but it might be clearer to you to not).

-- John


Avoid the subquery if you can.

-- Daniel