[데이터분석] 코드카타 87~88

87. Employee Bonus

 

https://leetcode.com/problems/employee-bonus/description/

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

Return the result table in any order.

 

(오답) 보너스가 1000 이하인 값과 Null 값을 같이 구해야하는데, 결과가 왜 안 나올까

select a.name,

           b.bonus

from employee a left join bonus b on a.empID = b.empID

where b.bonus < 1000 and b.bonus is null

group by 1

 

 

(정답) 헐..'또는'   ㅠ_ㅠ   그리고 여기선 집계함수 없으므로 group by 하면 안됨..

select a.name,

           b.bonus

from employee a left join bonus b on a.empID = b.empID

where b.bonus < 1000 or b.bonus is null

 

 

88. Student and Examinations

 

https://leetcode.com/problems/students-and-examinations/description/

Write a solution to find the number of times each student attended each exam.

Return the result table ordered by student_id and subject_name.

 

(오답)  시험을 안 본 Alex ID가 자꾸 제일 처음에 나옴

 

select a.student_id,

           a.student_name,

           b.subject_name,

            count(b.subject_name) as attended_exams

from students a right join examinations b on a.student_id= b.student_id join subjects c on b.subject_name = c.subject_name

group by 1, 3

order by a.student_id and b.subject_name

 

 

(오답2) 

select a.student_id,

          a.student_name,

          b.subject_name,

          count(c.subject_name) as attended_exams

from students a join examinations b left join subjects c on a.student_id= b.student_id and b.subject_name = c.subject_name

group by a.student_id and b.subject_name

order by a.student_id and b.subject_name

 

 

(정답) ** 위에 오답들은 left나 inner join 을 쓰면 시험을 안 본 학생이름이라던가 과목이 자꾸 누락됨  cross join 함수는 공통키가 없더라도 그냥 붙여줌 (with구문으로도 붙일 수 있음)   그렇게 학생, 과목 테이블을 다 합치고 left join 으로 시험을 안 본 학생이든 어떤 학생이 시험을 안 친 과목이든 모두 나올 수 있게 해야함.

SELECT a.student_id,

               a.student_name,

               b.subject_name,

               count(c.subject_name) as attended_exams

FROM students a cross join Subjects b left join examinations c on a.student_id = c.student_id  and  b.subject_name = c.subject_name

GROUP BY a.student_id, b.subject_name

ORDER BY a.student_id, b.subject_name

 

 

 

< with 구문으로 만들고싶은 테이블을 만들어서 구하는 식 참고.>

 

 

WITH SSB AS(

SELECT s.student_id sid,

s.student_name sn,

sb.subject_name sbn

FROM Students s JOIN Subjects sb

ON 1 = 1

),

EC AS(

SELECT student_id,

subject_name,

COUNT(subject_name) csn

FROM Examinations

GROUP BY student_id, subject_name

)

 

SELECT SSB.sid student_id,

SSB.sn student_name,

SSB.sbn subject_name,

COALESCE(EC.csn,0) attended_exams

FROM SSB LEFT JOIN EC

ON SSB.sid = EC.student_id AND SSB.sbn = EC.subject_name

ORDER BY 1, 3