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 main services for train stations that have the top three total number of passengers.
assistant
SELECT name , main_services FROM station ORDER BY total_passengers DESC LIMIT 3
human
Show the name, location, and number of platforms for all stations.
assistant
SELECT name , LOCATION , number_of_platforms FROM 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 the station name and number of trains in each station.
assistant
SELECT T2.name , count(*) FROM train_station AS T1 JOIN station AS T2 ON T1.station_id = T2.station_id GROUP BY T1.station_id
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 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 ;