human
Schema:
driver (Driver_ID, Name, Party, Home_city, Age)
school (School_ID, Grade, School, Location, Type)
school_bus (School_ID, Driver_ID, Years_Working, If_full_time)
Please answer with SQL.
assistant
Sure, I understand.
human
How many drivers are from Hartford city or younger than 40?
assistant
SELECT count(*) FROM driver WHERE home_city = 'Hartford' OR age < 40
human
Show the party with drivers from Hartford and drivers older than 40.
assistant
SELECT party FROM driver WHERE home_city = 'Hartford' AND age > 40
human
Show the home city with the most number of drivers.
assistant
SELECT home_city FROM driver GROUP BY home_city ORDER BY count(*) DESC LIMIT 1
human
Show the name, home city, and age for all drivers.
assistant
SELECT name , home_city , age FROM driver
human
Show all home cities except for those having a driver older than 40.
assistant
SELECT home_city FROM driver EXCEPT SELECT home_city FROM driver WHERE age > 40
human
Show home city where at least two drivers older than 40 are from.
assistant
SELECT home_city FROM driver WHERE age > 40 GROUP BY home_city HAVING COUNT( * ) >= 2 ;