Database: school_bus

CREATE TABLE "driver" (
"Driver_ID" int,
"Name" text,
"Party" text,
"Home_city" text,
"Age" int,
PRIMARY KEY ("Driver_ID")
);
CREATE TABLE "school" (
"School_ID" int,
"Grade" text,
"School" text,
"Location" text,
"Type" text,
PRIMARY KEY ("School_ID")
);
CREATE TABLE "school_bus" (
"School_ID" int,
"Driver_ID" int,
"Years_Working" int,
"If_full_time" bool,
PRIMARY KEY ("School_ID","Driver_ID"),
FOREIGN KEY ("School_ID") REFERENCES `school`("School_ID"),
FOREIGN KEY ("Driver_ID") REFERENCES `driver`("Driver_ID")
);

Question: How many drivers are there?
SQL: SELECT count(*) FROM driver

Question: Show the name, home city, and age for all drivers.
SQL: SELECT name ,  home_city ,  age FROM driver

Question: Show the party and the number of drivers in each party.
SQL: SELECT party ,  count(*) FROM driver GROUP BY party

Question: Show the name of drivers in descending order of age.
SQL: SELECT name FROM driver ORDER BY age DESC

Question: Show all different home cities.
SQL: SELECT DISTINCT home_city FROM driver

Question: Show the home city with the most number of drivers.
SQL: SELECT home_city FROM driver GROUP BY home_city ORDER BY count(*) DESC LIMIT 1

Question: Show the party with drivers from Hartford and drivers older than 40.
SQL: SELECT party FROM driver WHERE home_city  =  'Hartford' AND age  >  40

Question: Show home city where at least two drivers older than 40 are from.
SQL: SELECT home_city FROM driver WHERE age  >  40 GROUP BY home_city HAVING count(*)  >=  2

Question: Show all home cities except for those having a driver older than 40.
SQL: SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age  >  40

Question: Show the names of the drivers without a school bus.
SQL: SELECT name FROM driver WHERE driver_id NOT IN (SELECT driver_id FROM school_bus)

Question: Show the types of schools that have two schools.
SQL: SELECT TYPE FROM school GROUP BY TYPE HAVING count(*)  =  2

Question: Show the school name and driver name for all school buses.
SQL: SELECT T2.school ,  T3.name FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id JOIN driver AS T3 ON T1.driver_id  =  T3.driver_id

Question: What is the maximum, minimum and average years spent working on a school bus?
SQL: SELECT max(years_working) ,  min(years_working) ,  avg(years_working) FROM school_bus

Question: Show the school name and type for schools without a school bus.
SQL: SELECT school ,  TYPE FROM school WHERE school_id NOT IN (SELECT school_id FROM school_bus)

Question: Show the type of school and the number of buses for each type.
SQL: SELECT T2.type ,  count(*) FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id  =  T2.school_id GROUP BY T2.type

Question: How many drivers are from Hartford city or younger than 40?
SQL: SELECT count(*) FROM driver WHERE home_city  =  'Hartford' OR age  <  40

Question: List names for drivers from Hartford city and younger than 40.
SQL: SELECT name FROM driver WHERE home_city  =  'Hartford' AND age  <  40

Question: find the name of driver who is driving the school bus with the longest working history.
SQL: SELECT t1.name FROM driver AS t1 JOIN school_bus AS t2 ON t1.driver_id  =  t2.driver_id ORDER BY years_working DESC LIMIT 1