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
find the name of driver who is driving the school bus with the longest working history.
assistant
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
human
Show the types of schools that have two schools.
assistant
SELECT TYPE FROM school GROUP BY TYPE HAVING count(*) = 2
human
Show the type of school and the number of buses for each type.
assistant
SELECT T2.type , count(*) FROM school_bus AS T1 JOIN school AS T2 ON T1.school_id = T2.school_id GROUP BY T2.type
human
Show the school name and driver name for all school buses.
assistant
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
human
Show the names of the drivers without a school bus.
assistant
SELECT name FROM driver WHERE driver_id NOT IN (SELECT driver_id FROM school_bus)
human
Show the school name and type for schools without a school bus.
assistant
SELECT school , TYPE FROM school WHERE school_id NOT IN ( SELECT school_id FROM school_bus ) ;