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 the number and time of the train that goes from Chennai to Guruvayur.
assistant
SELECT train_number , TIME FROM train WHERE origin = 'Chennai' AND destination = 'Guruvayur'
human
Find the origins from which more than 1 train starts.
assistant
SELECT origin FROM train GROUP BY origin HAVING count(*) > 1
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 number of trains starting from each origin.
assistant
SELECT origin , count(*) FROM train GROUP BY origin
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
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 ;