In my last post we discussed about Student, Course and Instructor based database design and relationships. In this post I will show you how interviewer will ask questions. I hope you already went through previous post.
Question: Find students who enrolled more than two courses
SELECT s.StudentId, (s.FirstName + ' ' + s.LastName) as Student, COUNT(CourseId) as CoursesTaken FROM Student AS s INNER JOIN StudentCourse AS sc ON s.StudentId = sc.StudentId GROUP BY s.StudentId, s.FirstName, s.LastName HAVING COUNT(CourseId) > 2 ORDER BY CoursesTaken DESC
Question: Find department with maximum number of students
SELECT d.DepartmentName, COUNT(SC.StudentId) AS StudentCount FROM Department d INNER JOIN Course c ON d.DepartmentId = c.DepartmentId INNER JOIN StudentCourse sc ON sc.CourseId = c.CourseId GROUP BY d.DepartmentName
Question: Top three popular courses (this means maximum number of students enrolled for course)
SELECT TOP 3 c.CourseName, COUNT(SC.StudentId) AS StudentCount FROM Course c INNER JOIN StudentCourse sc ON sc.CourseId = c.CourseId GROUP BY c.CourseName ORDER BY StudentCount DESC
Question: Top five instructors with maximum students in a course
SELECT TOP 5 (i.FirstName + ' ' + i.LastName) AS InstructorName, COUNT(SC.StudentId) AS StudentCount FROM Instructor i INNER JOIN CourseInstructor ci ON i.InstructorId = ci.InstructorId INNER JOIN StudentCourse sc ON sc.CourseId = ci.CourseId GROUP BY i.FirstName, i.LastName ORDER BY StudentCount DESC
Question: Department with maximum number of courses
SELECT DepartmentName, COUNT(c.CourseId) AS CourseCount FROM Department d LEFT JOIN Course c ON d.DepartmentId = c.DepartmentId GROUP BY d.DepartmentName ORDER BY CourseCount DESC
Similarly, the questions based on dates of birth, instructor hire date are
Question: Youngest/oldest student for each department or youngest student for course
Question: Senior most instructor (on hire date) for each department
There could be more number of questions but the general idea is questions are based on group by and having clauses. I hope you all enjoyed this post. Don’t forget to like, comment, subscribe and share this post.