human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
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
Find the last names of the teachers that teach fifth grade.
assistant
SELECT DISTINCT T2.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE grade = 5
human
Which grade is studying in classroom 103?
assistant
SELECT DISTINCT grade FROM list WHERE classroom = 103
human
Which classroom has the most students?
assistant
SELECT classroom FROM list GROUP BY classroom ORDER BY count(*) DESC LIMIT 1
human
Which classrooms are used by grade 4?
assistant
SELECT DISTINCT classroom FROM list WHERE grade = 4
human
Which classrooms are used by grade 5?
assistant
SELECT DISTINCT classroom FROM list WHERE grade = 5 ;