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 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
List the names and locations of all stations ordered by their yearly entry exit and interchange amounts.
assistant
SELECT name , LOCATION FROM station ORDER BY Annual_entry_exit , Annual_interchanges
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 with only 1 station.
assistant
SELECT LOCATION FROM station GROUP BY LOCATION HAVING count(*) = 1
human
Show the name, location, and number of platforms for all stations.
assistant
SELECT name , LOCATION , number_of_platforms FROM station ;