human
Schema:
train (id, train_number, name, origin, destination, time, interval)
station (id, network_name, services, local_authority)
route (train_id, station_id)
weekly_weather (station_id, day_of_week, high_temperature, low_temperature, precipitation, wind_speed_mph)
Please answer with SQL.
assistant
Sure, I understand.
human
Show me the names of trains that run through a station serviced by Greater Anglia.
assistant
SELECT t3.name FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id JOIN train AS t3 ON t2.train_id = t3.id WHERE t1.services = 'Greater Anglia' ;
human
How about stations for local authority Chiltern?
assistant
SELECT t3.name FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id JOIN train AS t3 ON t2.train_id = t3.id WHERE t1.local_authority = 'Chiltern' ;
human
For these, also provide the times of each train.
assistant
SELECT t3.name , t3.time FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id JOIN train AS t3 ON t2.train_id = t3.id WHERE t1.local_authority = "Chiltern" ;