250208_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

Leetcode

196. Delete Duplicate Emails
delete는 내가 직접 실행해 볼 일은 없을테니
그런갑다 하고 넘어가자.

DELETE p1
FROM   person p1
       JOIN person p2
         ON p1.email = p2.email
            AND p1.id > p2.id;

1965. Employees With Missing Information
풀이시간 02:19

SELECT employee_id
FROM   (SELECT e.employee_id
        FROM   employees e
               LEFT JOIN salaries s
                      ON e.employee_id = s.employee_id
        WHERE  s.employee_id IS NULL
        UNION ALL
        SELECT s.employee_id
        FROM   salaries s
               LEFT JOIN employees e
                      ON s.employee_id = e.employee_id
        WHERE  e.employee_id IS NULL) result
ORDER  BY 1;

197. Rising Temperature
풀이시간 01:48

SELECT w2.id
FROM   weather w1
       LEFT JOIN weather w2
              ON w1.temperature < w2.temperature
                 AND Datediff(w2.recorddate, w1.recorddate) = 1
WHERE  w2.id IS NOT NULL;

1972. First and Last Call On the Same Day
이건 한번 더 풀어보자.
왔다갔다 해야해서 좀 산만해졌다.

1978. Employees Whose Manager Left the Company
풀이시간 03:37

SELECT e1.employee_id
FROM   employees e1
       LEFT JOIN employees e2
              ON e1.manager_id = e2.employee_id
WHERE  e1.salary < 30000
       AND e1.manager_id IS NOT NULL
       AND e2.employee_id IS NULL
ORDER  BY 1;