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
list the local authorities and services provided by all stations.
assistant
SELECT local_authority , services FROM station
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
Give me the maximum low temperature and average precipitation at the Amersham station.
assistant
SELECT max(t1.low_temperature) , avg(t1.precipitation) FROM weekly_weather AS t1 JOIN station AS t2 ON t1.station_id = t2.id WHERE t2.network_name = 'Amersham'
human
Find the id and local authority of the station with has the highest average high temperature.
assistant
SELECT t2.id , t2.local_authority FROM weekly_weather AS t1 JOIN station AS t2 ON t1.station_id = t2.id GROUP BY t1.station_id ORDER BY avg(high_temperature) DESC LIMIT 1
human
Find the id and local authority of the station whose maximum precipitation is higher than 50.
assistant
SELECT t2.id , t2.local_authority FROM weekly_weather AS t1 JOIN station AS t2 ON t1.station_id = t2.id GROUP BY t1.station_id HAVING MAX( t1.precipitation ) > 50 ;