Teachers and Students

      

Write an SQL query to identify the teachers who handle the most number of students in each of the grades.

Input :-

Teachers table :-
ID is the Primary Key column for the Teachers table.

ID	Name	Students	ClassID
1	Roopa	70	1
2	Deepthi	95	1
3	Nikitha	75	2
4	Sanjana	60	2
5	Vinutha	95	1

Grades Tabe:-

ID is the Primary Key column for the Grades table.

ID	Grade
1	Nursery
2	HighSchool

Output :-

Grade	Teacher	Students
Nursery	Deepthi	95
HighSchool	Nikitha	75
Nursery	Vinutha	95

Query :-

select Grade, Teacher, Students from
(
    select g.Grade Grade, t.name Teacher, t.Students Students,
    rank() over (partition by g.Grade order by t.Students desc) as row_rank
    from Teachers t join Grades g on t.classID=g.ID
) a
where row_rank=1;

Explanation : - 

This query retrieves information about the teacher handling the most number of students in each grade. Let's break down the query step by step:

• The inner subquery joins the "Teachers" table with the "Grades" table using the ‘ClassID’ and ‘ID’ columns respectively. This combination allows us to get the relevant information for each grade and its corresponding teacher.
• Within the subquery, the ‘rank()’ function is used to assign a ranking to each teacher within their respective grade. The ‘partition by Grade’ clause ensures that the ranking is calculated separately for each grade, while the ‘order by Students desc’ clause orders the teachers based on the number of students they handle in descending order. This means that the teacher that handles the most number of students within each grade will receive a rank of 1.
• The outer query selects the columns ‘Grade’,  ‘Teacher’, and ‘Students’ from the results of the subquery. It also includes a condition ‘where row_rank=1’ to filter only the rows where the teacher has a rank of 1, meaning they handle the most number of students in their grade.

In summary, this query identifies the teachers that handles the most number of students in each grade based on the number of students they have and returns their grade, name, and the corresponding number of students.