Database: course_teach

CREATE TABLE "course" (
"Course_ID" int,
"Staring_Date" text,
"Course" text,
PRIMARY KEY ("Course_ID")
);
CREATE TABLE "teacher" (
"Teacher_ID" int,
"Name" text,
"Age" text,
"Hometown" text,
PRIMARY KEY ("Teacher_ID")
);
CREATE TABLE "course_arrange" (
"Course_ID" int,
"Teacher_ID" int,
"Grade" int,
PRIMARY KEY ("Course_ID","Teacher_ID","Grade"),
FOREIGN KEY ("Course_ID") REFERENCES `course`("Course_ID"),
FOREIGN KEY ("Teacher_ID") REFERENCES `teacher`("Teacher_ID")
);

Question: How many teachers are there?
SQL: SELECT count(*) FROM teacher

Question: What is the total count of teachers?
SQL: SELECT count(*) FROM teacher

Question: List the names of teachers in ascending order of age.
SQL: SELECT Name FROM teacher ORDER BY Age ASC

Question: What are the names of the teachers ordered by ascending age?
SQL: SELECT Name FROM teacher ORDER BY Age ASC

Question: What are the age and hometown of teachers?
SQL: SELECT Age ,  Hometown FROM teacher

Question: What is the age and hometown of every teacher?
SQL: SELECT Age ,  Hometown FROM teacher

Question: List the name of teachers whose hometown is not `` Little Lever Urban District '' .
SQL: select name from teacher where hometown != "little lever urban district"

Question: What are the names of the teachers whose hometown is not `` Little Lever Urban District '' ?
SQL: select name from teacher where hometown != "little lever urban district"

Question: Show the name of teachers aged either 32 or 33?
SQL: SELECT Name FROM teacher WHERE Age  =  32 OR Age  =  33

Question: What are the names of the teachers who are aged either 32 or 33?
SQL: SELECT Name FROM teacher WHERE Age  =  32 OR Age  =  33

Question: What is the hometown of the youngest teacher?
SQL: SELECT Hometown FROM teacher ORDER BY Age ASC LIMIT 1

Question: Where is the youngest teacher from?
SQL: SELECT Hometown FROM teacher ORDER BY Age ASC LIMIT 1

Question: Show different hometown of teachers and the number of teachers from each hometown.
SQL: SELECT Hometown ,  COUNT(*) FROM teacher GROUP BY Hometown

Question: For each hometown, how many teachers are there?
SQL: SELECT Hometown ,  COUNT(*) FROM teacher GROUP BY Hometown

Question: List the most common hometown of teachers.
SQL: SELECT Hometown FROM teacher GROUP BY Hometown ORDER BY COUNT(*) DESC LIMIT 1

Question: What is the most commmon hometowns for teachers?
SQL: SELECT Hometown FROM teacher GROUP BY Hometown ORDER BY COUNT(*) DESC LIMIT 1

Question: Show the hometowns shared by at least two teachers.
SQL: SELECT Hometown FROM teacher GROUP BY Hometown HAVING COUNT(*)  >=  2

Question: What are the towns from which at least two teachers come from?
SQL: SELECT Hometown FROM teacher GROUP BY Hometown HAVING COUNT(*)  >=  2

Question: Show names of teachers and the courses they are arranged to teach.
SQL: SELECT T3.Name ,  T2.Course FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID

Question: What is the name of each teacher and what course they teach?
SQL: SELECT T3.Name ,  T2.Course FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID

Question: Show names of teachers and the courses they are arranged to teach in ascending alphabetical order of the teacher's name.
SQL: SELECT T3.Name ,  T2.Course FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID ORDER BY T3.Name

Question: What are the names of the teachers and the courses they teach in ascending alphabetical order by the name of the teacher?
SQL: SELECT T3.Name ,  T2.Course FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID ORDER BY T3.Name

Question: Show the name of the teacher for the math course.
SQL: SELECT T3.Name FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID WHERE T2.Course  =  "Math"

Question: What are the names of the people who teach math courses?
SQL: SELECT T3.Name FROM course_arrange AS T1 JOIN course AS T2 ON T1.Course_ID  =  T2.Course_ID JOIN teacher AS T3 ON T1.Teacher_ID  =  T3.Teacher_ID WHERE T2.Course  =  "Math"

Question: Show names of teachers and the number of courses they teach.
SQL: SELECT T2.Name ,  COUNT(*) FROM course_arrange AS T1 JOIN teacher AS T2 ON T1.Teacher_ID  =  T2.Teacher_ID GROUP BY T2.Name

Question: What are the names of the teachers and how many courses do they teach?
SQL: SELECT T2.Name ,  COUNT(*) FROM course_arrange AS T1 JOIN teacher AS T2 ON T1.Teacher_ID  =  T2.Teacher_ID GROUP BY T2.Name

Question: Show names of teachers that teach at least two courses.
SQL: SELECT T2.Name FROM course_arrange AS T1 JOIN teacher AS T2 ON T1.Teacher_ID  =  T2.Teacher_ID GROUP BY T2.Name HAVING COUNT(*)  >=  2

Question: What are the names of the teachers who teach at least two courses?
SQL: SELECT T2.Name FROM course_arrange AS T1 JOIN teacher AS T2 ON T1.Teacher_ID  =  T2.Teacher_ID GROUP BY T2.Name HAVING COUNT(*)  >=  2

Question: List the names of teachers who have not been arranged to teach courses.
SQL: SELECT Name FROM teacher WHERE Teacher_id NOT IN (SELECT Teacher_id FROM course_arrange)

Question: What are the names of the teachers whose courses have not been arranged?
SQL: SELECT Name FROM teacher WHERE Teacher_id NOT IN (SELECT Teacher_id FROM course_arrange)