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 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 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 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
Show all train names and times in stations in London in descending order by train time.
assistant
SELECT T3.name , T3.time 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 T2.location = 'London' ORDER BY T3.time DESC
human
Show the name, time, and service for all trains.
assistant
SELECT name , TIME , service FROM train
human
Show the name and service for all trains in order by time.
assistant
SELECT name , service FROM train ORDER BY TIME ;