Trick SQL Interview Question and team work

I was sitting with friends at our legendary Firdos Market flat 94/H just yesterday. Adeel my brother, told us about a query that an interviewer had asked him to solve. It was dinner time, so we handed the query to Zaheer Chaudhary, who'd eaten a couple of hours ago.

After the dinner, Aweas Dar, Adeel's class fellow (Zaheer is also a class fellow), opened SQL Server and created table structures needed for the operation. The trio I just mentioned graduated in computer science from the University of Gujrat(UoG) in 2012.
All of us sat down and started talking about the query. The problem statement is given below:
"
Suppose we have two tables, students and subjects. The student's table contains two fields id and name. The subjects table contains three fields: id, std_id, and subject.

When executed, we need a query to produce the result set with columns describing how many courses each student is taking. In a better version of the result set, we will have a student id, student name, and several subjects.
"



Table structures are also given above for reference.
Everybody knew that we would need to do a count on the subject field of the subjects table. So we wrote a couple of queries, poked some jokes at each other, and ultimately achieved the desired result.
Unfortunately, I couldn't save various versions of the query as it evolved.







Aweas also keyed in dummy data rows. Below is a screenshot of student table data and subjects table data.

Next, it occurred to us that we will need to use SQL Group By in this situation. The possibility of using a join was ruled out quickly, and finally, the SQL where clause was also needed.
The final SQL query was made even more beautiful by adding an ORDER BY clause. The result of our quick teamwork is the SQL Query given below.

SELECT students.id AS StudentID, students.name AS Students, COUNT(subjects.subject) AS NoOfSubjects
FROM subjects, students WHERE subjects.std_id=students.id GROUP BY students.name, students.id
ORDER BY StudentID

There are small emotional holes that we leave unplugged due to laziness. We could have left the query unsolved and kept thinking about it for a long time. Some of us would even feel guilty that we could not sort it out. This event shows the importance of teamwork and how a cooperative thought process can help us to solve puzzling questions and crack jokes.