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=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.
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.
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
EXPLAINstatement (SQLite, MySQL, ...).
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
INNER JOIN, but this problem is better solved with an inner join, because a left outer join might introduce
NULLin attributes selected from the
ASCis the default in an
ORDER BYclause, so you can omit it (but it might be clearer to you to not).
Avoid the subquery if you can.