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 of trains starting from each origin.
assistant
SELECT origin , count(*) FROM train GROUP BY origin
human
show all train numbers and names ordered by their time from early to late.
assistant
SELECT train_number , name FROM train ORDER BY TIME
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
human
Give me the times and numbers of all trains that go to Chennai, ordered by time.
assistant
SELECT TIME , train_number FROM train WHERE destination = 'Chennai' ORDER BY TIME
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" ;