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 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 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 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
How many train stations are there?
assistant
SELECT count(*) FROM station
human
What is the average and maximum number of total passengers for train stations in London or Glasgow?
assistant
SELECT avg ( total_passengers ) , MAX( total_passengers ) FROM station WHERE LOCATION = "London" OR LOCATION = "Glasgow" ;