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 students studying in room 111.
assistant
SELECT lastname FROM list WHERE classroom = 111
human
Report the first name and last name of all the students.
assistant
SELECT DISTINCT firstname , lastname FROM list
human
Find the first names of students studying in room 108.
assistant
SELECT firstname FROM list WHERE classroom = 108
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
Which students study under the teacher named OTHA MOYER? Give me 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" ;