human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the first names of students studying in room 108.
assistant
SELECT firstname FROM list WHERE classroom = 108
human
Find the last names of teachers teaching in classroom 109.
assistant
SELECT lastname FROM teachers WHERE classroom = 109
human
Find the first names of all the teachers that teach in classroom 110.
assistant
SELECT firstname FROM teachers WHERE classroom = 110
human
Find the number of students in one classroom.
assistant
SELECT count(*) , classroom FROM list GROUP BY classroom
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
Find the number of teachers who teach the student called MADLOCK RAY.
assistant
SELECT COUNT( * ) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T1.firstname = "MADLOCK" AND T1.lastname = "RAY" ;