LC 184 & LC 570 & LC 580 & LC 1264 & LC 1280

The following basic-level SQL problems are about ‘linking’.

LeetCode 184

Department Highest Salary (Medium) [link]

Using window function

SELECT D.name AS Department, t.name AS Employee, t.salary AS Salary
FROM(
    SELECT name, salary, departmentId,
        dense_rank() OVER(PARTITION BY departmentId ORDER BY salary DESC) as rnk
    FROM Employee
) AS t
LEFT JOIN Department D
ON t.departmentId = D.id
WHERE t.rnk = 1;

Using WHERE...IN

SELECT D.name AS Department, E.name AS Employee, E.salary AS Salary
FROM Employee E
LEFT JOIN Department D
ON E.departmentId = D.id
WHERE (E.departmentId, E.salary) IN(
    SELECT departmentId, MAX(salary) AS salary
    FROM Employee
    GROUP BY departmentId
) ;

LeetCode 570

Managers with at Least 5 Direct Reports (Medium) [link]

Using LEFT JOIN

SELECT e1.name
FROM Employee e1
LEFT JOIN Employee e2
ON e2.managerId = e1.id
GROUP BY e1.id
HAVING COUNT(e1.name) >= 5;

Using WHERE IN

SELECT name
FROM Employee
WHERE id IN (
    SELECT managerId AS id
    FROM Employee
    GROUP BY managerId
    HAVING COUNT(*) >= 5
);

LeetCode 580

Count Student Number in Departments (Medium) [link]

SELECT D.dept_name, IFNULL(COUNT(S.student_id),0) AS student_number
FROM Department D
LEFT JOIN Student S
ON S.dept_id = D.dept_id
GROUP BY D.dept_id
ORDER BY student_number DESC, D.dept_name;

LeetCode 1264

Page Recommendations (Medium) [link]

SELECT DISTINCT L.page_id AS recommended_page
FROM (
    SELECT IF(user1_id < user2_id, user1_id, user2_id) AS user1_id, 
        IF(user1_id < user2_id, user2_id, user1_id) AS user2_id
    FROM Friendship
) AS t
LEFT JOIN Likes L
ON t.user2_id = L.user_id AND t.user1_id = 1 
WHERE L.page_id IS NOT NULL AND L.page_id NOT IN (SELECT page_id FROM Likes WHERE user_id = 1);

SELECT DISTINCT page_id AS recommended_page FROM Likes
    WHERE user_id IN (
        SELECT user1_id FROM Friendship WHERE user2_id = 1
        UNION ALL
        SELECT user2_id FROM Friendship WHERE user1_id = 1
    ) AND page_id NOT IN (
        SELECT page_id FROM Likes WHERE user_id = 1
    );

LeetCode 1280

Students and Examinations (Easy) [link]

Using CROSS JOIN

SELECT S.student_id, S.student_name, S.subject_name, IFNULL(E.attended_exams, 0) AS attended_exams
FROM (
    SELECT *
    FROM Students 
    CROSS JOIN Subjects
) S
LEFT JOIN (
    SELECT *, COUNT(student_id) AS attended_exams
    FROM Examinations
    GROUP BY student_id, subject_name
) E
ON E.student_id = S.student_id AND E.subject_name = S.subject_name
ORDER BY S.student_id, S.subject_name;

A simplified version

SELECT a.student_id, a.student_name, b.subject_name, IFNULL(COUNT(E.subject_name), 0) AS attended_exams
FROM Students a
CROSS JOIN Subjects b
LEFT JOIN Examinations E
ON a.student_id = E.student_id AND b.subject_name = E.subject_name
GROUP BY a.student_id, b.subject_name
ORDER BY a.student_id, b.subject_name;