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
What is the maximum, minimum and average years spent working on a school bus?
assistant
SELECT max(years_working) , min(years_working) , avg(years_working) 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)
human
Show the name of drivers in descending order of age.
assistant
SELECT name FROM driver ORDER BY age DESC
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
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 ;