[SQL 코드카타] 86 ~ 88번 (LeetCode)

(86번) average-time-of-process-per-machine

> 셀프 조인 활용에 대해서 배웠다.

Select a.machine_id , ROUND(avg(a.timestamp-b.timestamp),3) as "processing_time" 
from Activity a JOIN Activity b 
     on a.machine_id = b.machine_id 
     and a.process_id = b.process_id 
where 1=1 
      and b.activity_type = 'start' 
      and a.activity_type = 'end'
group by a.machine_id;

 

(87번) employee-bonus

> null 값은 연산에서 제외된다는 점을 잊지 말기

select e.name, b.bonus
from employee e left join bonus b
     on e.empid = b.empid
where 1=1
      and b.bonus < 1000
      or b.bonus is null

 

(88번) students-and-examinations

> 3중 조인에 대해서 이해

SELECT s.student_id, 
    s.student_name, 
    u.subject_name, 
    COUNT(e.subject_name) AS attended_exams
FROM Students s JOIN Subjects u
    LEFT JOIN Examinations e ON s.student_id = e.student_id AND u.subject_name = e.subject_name
GROUP BY s.student_id, u.subject_name
ORDER BY s.student_id, u.subject_name;