We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. SQL
  3. Advanced Join
  4. Placements
  5. Discussions

Placements

  • pranavgupta892
     + 0 comments

    Here's a simple solution in MySQL: Step 1: Calculate the salary of each student Step 2: Calulate the salary of each friend Step 3: Select the students where their salary is less than that of their friend

    WITH student_salary AS
(
    SELECT s.ID AS student_id, s.Name AS student_name, p.Salary AS student_salary
    FROM Students s
    LEFT JOIN Packages p
    ON s.ID = p.ID
),
friend_salary AS
(
    SELECT f.Friend_ID AS friend_id, f.ID AS student_id, p.Salary AS friend_salary
    FROM Friends f
    LEFT JOIN Packages p
    ON f.Friend_ID = p.ID
)
SELECT ss.student_name
FROM student_salary ss
LEFT JOIN friend_salary fs
ON ss.student_id = fs.student_id
WHERE ss.student_salary < fs.friend_salary
ORDER BY fs.friend_salary;