human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
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
Report the first name and last name of all the students.
assistant
SELECT DISTINCT firstname , lastname FROM list
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 all students taught by OTHA MOYER. Output the first and last names of the students.
assistant
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 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" ;