Wednesday, October 15, 2014

Trick SQL Interview Question and team work

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

After the dinner, Aweas Dar who's 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 computer science from Univerty of Gujrat(UoG) in 2012.
All of us sat down and started talking about the query, the problem statement is given below:
subjects-table-design"
Suppose we have two tables, students and subjects. The students table contains two fields id,  and name. The subjects table contains three fields: id, std_id, subject.
students-table-design
We need a query to which when executed produces the result set with columns describing how many courses each student is taking. In a better version of result set we will have a student id, student name, and number of subjects.
"



Table structures are also given above for reference.
Everybody knew that we will need to do a count on the subject field  of subjects table. So we wrote a couple of queries, poked some jokes on 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, a screen shot image of student table data and subjects table data is given below.

select-top-students

select-top-subjects

Next it occurred to us that we will need to use SQL Group By in this situation. 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 beautiful by adding an ORDER BY clause, the result of our quick team work 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 keep thinking about it for a long time. Some of us would even get a guilty feeling that they were unable to sort it out. This event shows the importance of team work, and how a cooperative thought process can help us to solve puzzling questions cracking jokes.

1 comment:

Feel free to talk back...