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 are the school names of the schools without school buses?
assistant
SELECT school FROM school WHERE school_id NOT IN ( SELECT school_id FROM school_bus );
human
Can you please get me the types of those schools?
assistant
SELECT TYPE FROM school WHERE school_id NOT IN ( SELECT school_id FROM school_bus );
human
what are schools in Hawaii have school buses?
assistant
SELECT school FROM school WHERE location LIKE '%Hawaii%' AND school_id IN ( SELECT school_id FROM school_bus );
human
What about in Indonesia?
assistant
SELECT school FROM school WHERE location LIKE '%Indonesia' AND school_id IN ( SELECT school_id FROM school_bus );
human
How many private schools are in Hawaii?
assistant
SELECT COUNT( * ) FROM school WHERE location LIKE "%Hawaii" AND type = "Private" ;