Database: station_weather

CREATE TABLE "train" (
    "id" int,
    "train_number" int,
    "name" text,
    "origin" text,
    "destination" text,
    "time" text,
    "interval" text,
    primary key ("id")
);
CREATE TABLE "station" (
    "id" int,
    "network_name" text,
    "services" text,
    "local_authority" text,
    primary key ("id")
);
CREATE TABLE "route" (
    "train_id" int,
    "station_id" int,
    primary key ("train_id", "station_id"),
    foreign key ("train_id") references `train`("id"),
    foreign key ("station_id") references `station`("id")
);
CREATE TABLE "weekly_weather" (
    "station_id" int,
    "day_of_week" text,
    "high_temperature" int,
    "low_temperature" int,
    "precipitation" real,
    "wind_speed_mph" int,
    primary key ("station_id", "day_of_week"),
    foreign key ("station_id") references "station"("id")
);

Question: list the local authorities and services provided by all stations.
SQL: SELECT local_authority ,  services FROM station

Question: show all train numbers and names ordered by their time from early to late.
SQL: SELECT train_number ,  name FROM train ORDER BY TIME

Question: Give me the times and numbers of all trains that go to Chennai, ordered by time.
SQL: SELECT TIME ,  train_number FROM train WHERE destination  =  'Chennai' ORDER BY TIME

Question: How many trains have 'Express' in their names?
SQL: SELECT count(*) FROM train WHERE name LIKE "%Express%"

Question: Find the number and time of the train that goes from Chennai to Guruvayur.
SQL: SELECT train_number ,  TIME FROM train WHERE origin  =  'Chennai' AND destination  =  'Guruvayur'

Question: Find the number of trains starting from each origin.
SQL: SELECT origin ,  count(*) FROM train GROUP BY origin

Question: Find the name of the train whose route runs through greatest number of stations.
SQL: 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

Question: Find the number of trains for each station, as well as the station network name and services.
SQL: 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

Question: What is the average high temperature for each day of week?
SQL: SELECT avg(high_temperature) ,  day_of_week FROM weekly_weather GROUP BY day_of_week

Question: Give me the maximum low temperature and average precipitation at the Amersham station.
SQL: 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"

Question: Find names and times of trains that run through stations for the local authority Chiltern.
SQL: 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"

Question: How many different services are provided by all stations?
SQL: SELECT count(DISTINCT services) FROM station

Question: Find the id and local authority of the station with has the highest average high temperature.
SQL: 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

Question: Find the id and local authority of the station whose maximum precipitation is higher than 50.
SQL: 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

Question: show the lowest low temperature and highest wind speed in miles per hour.
SQL: SELECT min(low_temperature) ,  max(wind_speed_mph) FROM weekly_weather

Question: Find the origins from which more than 1 train starts.
SQL: SELECT origin FROM train GROUP BY origin HAVING count(*)  >  1