human
Schema:
list (LastName, FirstName, Grade, Classroom)
teachers (LastName, FirstName, Classroom)
Please answer with SQL.
assistant
Sure, I understand.
human
Find the number of students in total.
assistant
SELECT count(*) FROM list
human
For each grade, report the grade, the number of classrooms in which it is taught and the total number of students in the grade.
assistant
SELECT grade , count(DISTINCT classroom) , count(*) FROM list GROUP BY grade
human
Find the number of students in one classroom.
assistant
SELECT count(*) , classroom FROM list GROUP BY classroom
human
Report the number of students in each classroom.
assistant
SELECT classroom , count(*) FROM list GROUP BY classroom
human
Find the number of students taught by TARRING LEIA.
assistant
SELECT count(*) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T2.firstname = 'TARRING' AND T2.lastname = 'LEIA'
human
Count the number of students the teacher LORIA ONDERSMA teaches.
assistant
SELECT COUNT( * ) FROM list AS T1 JOIN teachers AS T2 ON T1.classroom = T2.classroom WHERE T2.firstname = "LORIA" AND T2.lastname = "ONDERSMA" ;