human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the name of the teacher who teaches the largest number of students.
assistant
SELECT T2.firstname , T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom GROUP BY T2.firstname , T2.lastname ORDER BY count(*) DESC LIMIT 1
human
For each classroom, report the classroom number and the number of grades using it.
assistant
SELECT classroom , count(DISTINCT grade) FROM list GROUP BY classroom
human
Which classrooms are used by grade 5?
assistant
SELECT DISTINCT classroom FROM list WHERE grade = 5
human
Find the number of students in one classroom.
assistant
SELECT count(*) , classroom FROM list GROUP BY classroom
human
Report the number of students in each classroom.
assistant
SELECT classroom , count(*) FROM list GROUP BY classroom
human
Find the classroom that the most students use.
assistant
SELECT classroom FROM list GROUP BY classroom ORDER BY COUNT( * ) DESC LIMIT 1 ;