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
Find names and times of trains that run through stations for the local authority Chiltern.
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'
human
Find the name of the train whose route runs through greatest number of stations.
assistant
SELECT t1.name FROM train AS t1 JOIN route AS t2 ON t1.id = t2.train_id GROUP BY t2.train_id ORDER BY count(*) DESC LIMIT 1
human
How many trains have 'Express' in their names?
assistant
SELECT count(*) FROM train WHERE name LIKE '%Express%'
human
Find the number of trains for each station, as well as the station network name and services.
assistant
SELECT count(*) , t1.network_name , t1.services FROM station AS t1 JOIN route AS t2 ON t1.id = t2.station_id GROUP BY t2.station_id
human
list the local authorities and services provided by all stations.
assistant
SELECT local_authority , services FROM station
human
How many different services are provided by all stations?
assistant
SELECT COUNT( DISTINCT services ) FROM station ;