Nov 26

Student Course Instructor based SQL Queries

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
Students who enrolled more than two courses

Students who enrolled more than two courses

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
Department with maximum number of students

Department with maximum number of students

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
Top three popular courses

Top three popular courses

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
Top three instructors with maximum students in a course

Top three instructors with maximum students in a course

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
Department with maximum number of courses

Department with maximum number of courses

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.

Leave a Reply