human
Schema:
station (Station_ID, Name, Annual_entry_exit, Annual_interchanges, Total_Passengers, Location, Main_Services, Number_of_Platforms)
train (Train_ID, Name, Time, Service)
train_station (Train_ID, Station_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names and total passengers for all train stations not in London.
assistant
SELECT name , total_passengers FROM station WHERE LOCATION != 'London'
human
Show station names without any trains.
assistant
SELECT name FROM station WHERE station_id NOT IN (SELECT station_id FROM train_station)
human
Show the location with most number of train stations.
assistant
SELECT LOCATION FROM station GROUP BY LOCATION ORDER BY count(*) DESC LIMIT 1
human
Show all locations and the total number of platforms and passengers for all train stations in each location.
assistant
SELECT LOCATION , sum(number_of_platforms) , sum(total_passengers) FROM station GROUP BY LOCATION
human
Show all locations that have train stations with at least 15 platforms and train stations with more than 25 total passengers.
assistant
SELECT DISTINCT LOCATION FROM station WHERE number_of_platforms >= 15 AND total_passengers > 25
human
Show all locations which don't have a train station with at least 15 platforms.
assistant
SELECT LOCATION FROM station EXCEPT SELECT LOCATION FROM station WHERE number_of_platforms >= 15 ;