CREATE TABLE list (
    LastName TEXT PRIMARY KEY,
    FirstName TEXT,
    Grade NUMBER,
    Classroom NUMBER
);
CREATE TABLE teachers (
    LastName TEXT PRIMARY KEY,
    FirstName TEXT,
    Classroom NUMBER
);

Question: How many teachers does the student named MADLOCK RAY have?
SQL: SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "MADLOCK" AND T1.lastname  =  "RAY"

Question: Find the number of teachers who teach the student called MADLOCK RAY.
SQL: SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom  =  T2.classroom WHERE T1.firstname  =  "MADLOCK" AND T1.lastname  =  "RAY"

Question: Find all first-grade students who are NOT taught by OTHA MOYER. Report their first and last names.
SQL: 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"

Question: What are the first and last names of the first-grade students who are NOT taught by teacher OTHA MOYER?
SQL: 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"

Question: Find the last names of the students in third grade that are not taught by COVIN JEROME.
SQL: 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"

Question: Which students in third grade are not taught by teacher COVIN JEROME? Give me the last names of the students.
SQL: 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"

Question: For each grade, report the grade, the number of classrooms in which it is taught and the total number of students in the grade.
SQL: SELECT grade ,  count(DISTINCT classroom) ,  count(*) FROM list GROUP BY grade

Question: For each grade, return the grade number, the number of classrooms used for the grade, and the total number of students enrolled in the grade.
SQL: SELECT grade ,  count(DISTINCT classroom) ,  count(*) FROM list GROUP BY grade

Question: For each classroom, report the classroom number and the number of grades using it.
SQL: SELECT classroom ,  count(DISTINCT grade) FROM list GROUP BY classroom

Question: For each classroom, show the classroom number and count the number of distinct grades that use the room.
SQL: SELECT classroom ,  count(DISTINCT grade) FROM list GROUP BY classroom

Question: Which classroom has the most students?
SQL: SELECT classroom FROM list GROUP BY classroom ORDER BY count(*) DESC LIMIT 1

Question: Find the classroom that the most students use.
SQL: SELECT classroom FROM list GROUP BY classroom ORDER BY count(*) DESC LIMIT 1

Question: Report the number of students in each classroom.
SQL: SELECT classroom ,  count(*) FROM list GROUP BY classroom

Question: For each classroom, show the classroom number and find how many students are using it.
SQL: SELECT classroom ,  count(*) FROM list GROUP BY classroom

Question: For each grade 0 classroom, report the total number of students.
SQL: SELECT classroom ,  count(*) FROM list WHERE grade  =  "0" GROUP BY classroom

Question: For each grade 0 classroom, return the classroom number and the count of students.
SQL: SELECT classroom ,  count(*) FROM list WHERE grade  =  "0" GROUP BY classroom

Question: Report the total number of students for each fourth-grade classroom.
SQL: SELECT classroom ,  count(*) FROM list WHERE grade  =  "4" GROUP BY classroom

Question: For each fourth-grade classroom, show the classroom number and the total number of students using it.
SQL: SELECT classroom ,  count(*) FROM list WHERE grade  =  "4" GROUP BY classroom

Question: Find the name of the teacher who teaches the largest number of students.
SQL: 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

Question: Which teacher teaches the most students? Give me the first name and last name of the teacher.
SQL: 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

Question: Find the number of students in one classroom.
SQL: SELECT count(*) ,  classroom FROM list GROUP BY classroom

Question: How many students does one classroom have?
SQL: SELECT count(*) ,  classroom FROM list GROUP BY classroom