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 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 the station name with at least two trains.
assistant
SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id = T2.station_id GROUP BY T1.station_id HAVING count(*) >= 2
human
show the train name and station name for each train.
assistant
SELECT T2.name , T3.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id = T2.station_id JOIN train AS T3 ON T3.train_id = T1.train_id
human
How many train stations are there?
assistant
SELECT count(*) FROM station
human
What are all locations of train stations?
assistant
SELECT DISTINCT LOCATION FROM station
human
What are the names of the stations which serve both 'Ananthapuri Express' and 'Guruvayur Express' trains?
assistant
SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id = T2.station_id JOIN train AS T3 ON T3.train_id = T1.train_id WHERE T3.Name = "Ananthapuri Express" INTERSECT SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id = T2.station_id JOIN train AS T3 ON T3.train_id = T1.train_id WHERE T3.Name = "Guruvayur Express" ;