If you are like me, I am starting my journey to become a SQL master but was stuck on LeetCode problem 1280: Students and Examinations.
LeetCode problem: https://leetcode.com/problems/students-and-examinations/description/?envType=study-plan-v2&envId=top-sql-50
Here is a detailed walkthrough as to how to solve the problem:
What I like to do first is set where the main data is coming from. Since I know that I will be looking at students and their IDs in relation to the Examinations table, I chose FROM Students:
Notice how I used SELECT *, which pulls every field. I placed the asterisk there as a placeholder, but selecting the columns will be the last thing I do, just like the SQL order of execution!
Now, this is where things get tricky!
We are tasked with finding the number of times each student attended each exam. We even count when a student did not take an exam (marked as 0).
Right now we have:
But how would we get each student paired with each subject name so that we can count the number of times they attended an exam?
Cross Join!
By Cross Joining, we combine every single row from the Students table with every row in the Subjects table.
This is a visual representation of what we are doing (I only showed it for Alice, but you would do this for every student):
So this is what it would look like after cross joining:
In SQL, it would look like this right now:
I used "su" as an alias, but it should work without one.
Now, to count the number of times they took an exam!
The Examinations table has the information, and all we have right now is a list of every student paired with every possible subject. Next, we use a JOIN (a LEFT JOIN, to be exact) so that we have access to the number of times each subject name appears.
Since we cross-joined, we need to join Students to Examinations, and also Subjects to Examinations, like so:
Since we are to count the number of times an exam was taken per subject and per student, we have to group them using GROUP BY!
Lastly, the ID column goes from the smallest ID number to the largest, so we have to sort by ID number. The subject name should also be listed alphabetically, so we need to sort by that as well (it is already alphabetical, but I included it as a precaution!).
Now we just select our columns and we are done!
This is what the final result would look like:
Now move forward to the next SQL question! Good luck!