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 the teachers that teach first grade.
assistant
SELECT DISTINCT T2.firstname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE grade = 1
human
How many teachers does the student named MADLOCK RAY have?
assistant
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T1.firstname = 'MADLOCK' AND T1.lastname = 'RAY'
human
What are the first names of students studying in room 107?
assistant
SELECT DISTINCT firstname FROM list WHERE classroom = 107
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 last names of the students in third grade that are not taught by COVIN JEROME.
assistant
SELECT DISTINCT T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T1.grade = 3 AND T2.firstname != 'COVIN' AND T2.lastname != 'JEROME'
human
Which are the first and last names of the students taught by MARROTTE KIRK?
assistant
SELECT T1.firstname , T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T2.firstname = "MARROTTE" AND T2.lastname = "KIRK" ;