1280. Students and Examinations| Leetcode SQL Easy

Welcome to the Leetcode SQL series
#leetcodesolution #leetcode #sql
Link to the problem:
leetcode.com/p...
👉 Link to Dense_rank, rank, and row_number functions video:
• Dense rank vs Rank vs ...
👉 Link to the video on Database Design:
• Database Design Crash ...
Instagram- / timeout_tech
Twitter- / timeout_tech
Facebook- / timeout.tech
Visit my KZbin channel here-
/ @techtimeout
Access the "SQL Interview Queries For Product-Based Companies" series playlist here:
• SQL Interview Queries ...

[@sandeepnarwal8782]

Can we complete this question using only 1 join?

[@aidanurulain4310]

why my below syntax did not work ? SELECT S.student_id, S.student_name, U.subject_name, COUNT(E.subject_name) as attended_exams FROM Students S JOIN Examinations E ON S.student_id = E.student_id LEFT JOIN Subjects U ON U.subject_name = E.subject_name GROUP BY S.student_id, S.student_name, U.subject_name ORDER BY S.student_id, U.subject_name

[@_SanchitaPatil]

why did you count student id and why not subject name

[@techtimeout]

Interesting! I substituted Student_id by subject_id :"select s.student_id, s.student_name, sb.subject_name, ifnull(count(e.subject_name),0)attended_exams from students s cross join subjects sb left join examinations e on e.student_id = s.student_id and e.subject_name = sb.subject_name group by s.student_id, s.student_name, sb.subject_name order by s.student_id, sb.subject_name;" And it returned the correct result. This works because we want to count the number of times student appears in exam and that can be done by counting number of times a student_id appears for a subject OR number times a subject appears for a student as the count in both these cases in examination table is same. Thank you for this thought-provoking comment!

[@_SanchitaPatil]

@@techtimeout thank you for explaning

[@Sattya998]

Sound