human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
human
Find all students taught by MARROTTE KIRK. Output first and last names of students.
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'
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
Find the first names of all the teachers that teach in classroom 110.
assistant
SELECT firstname FROM teachers WHERE classroom = 110
human
Find all first-grade students who are NOT taught by OTHA MOYER. Report their first and last names.
assistant
SELECT DISTINCT T1.firstname , T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T1.grade = 1 EXCEPT SELECT T1.firstname , T1.lastname FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T2.firstname = 'OTHA' AND T2.lastname = 'MOYER'
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 students in third grade are not taught by teacher COVIN JEROME? Give me the last names of the students.
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" ;