Database: train_station

CREATE TABLE "station" (
"Station_ID" int,
"Name" text,
"Annual_entry_exit" real,
"Annual_interchanges" real,
"Total_Passengers" real,
"Location" text,
"Main_Services" text,
"Number_of_Platforms" int,
PRIMARY KEY ("Station_ID")
);
CREATE TABLE "train" (
"Train_ID" int,
"Name" text,
"Time" text,
"Service" text,
PRIMARY KEY ("Train_ID")
);
CREATE TABLE "train_station" (
"Train_ID" int,
"Station_ID" int,	
PRIMARY KEY ("Train_ID","Station_ID"),
FOREIGN KEY ("Train_ID") REFERENCES "train"("Train_ID"),
FOREIGN KEY ("Station_ID") REFERENCES "station"("Station_ID")
);

Question: How many train stations are there?
SQL: SELECT count(*) FROM station

Question: Show the name, location, and number of platforms for all stations.
SQL: SELECT name ,  LOCATION ,  number_of_platforms FROM station

Question: What are all locations of train stations?
SQL: SELECT DISTINCT LOCATION FROM station

Question: Show the names and total passengers for all train stations not in London.
SQL: SELECT name ,  total_passengers FROM station WHERE LOCATION != 'London'

Question: Show the names and main services for train stations that have the top three total number of passengers.
SQL: SELECT name ,  main_services FROM station ORDER BY total_passengers DESC LIMIT 3

Question: What is the average and maximum number of total passengers for train stations in London or Glasgow?
SQL: SELECT avg(total_passengers) ,  max(total_passengers) FROM station WHERE LOCATION  =  'London' OR LOCATION  =  'Glasgow'

Question: Show all locations and the total number of platforms and passengers for all train stations in each location.
SQL: SELECT LOCATION ,  sum(number_of_platforms) ,  sum(total_passengers) FROM station GROUP BY LOCATION

Question: Show all locations that have train stations with at least 15 platforms and train stations with more than 25 total passengers.
SQL: SELECT DISTINCT LOCATION FROM station WHERE number_of_platforms  >=  15 AND total_passengers  >  25

Question: Show all locations which don't have a train station with at least 15 platforms.
SQL: SELECT LOCATION FROM station EXCEPT SELECT LOCATION FROM station WHERE number_of_platforms  >=  15

Question: Show the location with most number of train stations.
SQL: SELECT LOCATION FROM station GROUP BY LOCATION ORDER BY count(*) DESC LIMIT 1

Question: Show the name, time, and service for all trains.
SQL: SELECT name ,  TIME ,  service FROM train

Question: Show the number of trains
SQL: SELECT count(*) FROM train

Question: Show the name and service for all trains in order by time.
SQL: SELECT name ,  service FROM train ORDER BY TIME

Question: Show the station name and number of trains in each station.
SQL: SELECT T2.name ,  count(*) FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id

Question: show the train name and station name for each train.
SQL: SELECT T2.name ,  T3.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id

Question: Show all train names and times in stations in London in descending order by train time.
SQL: SELECT T3.name ,  T3.time FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T2.location  =  'London' ORDER BY T3.time DESC

Question: Show the station name with greatest number of trains.
SQL: SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id ORDER BY count(*) DESC LIMIT 1

Question: Show the station name with at least two trains.
SQL: SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id GROUP BY T1.station_id HAVING count(*)  >=  2

Question: Show all locations with only 1 station.
SQL: SELECT LOCATION FROM station GROUP BY LOCATION HAVING count(*)  =  1

Question: Show station names without any trains.
SQL: SELECT name FROM station WHERE station_id NOT IN (SELECT station_id FROM train_station)

Question: What are the names of the stations which serve both "Ananthapuri Express" and "Guruvayur Express" trains?
SQL: SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T3.Name  =  "Ananthapuri Express" INTERSECT SELECT T2.name FROM train_station AS T1 JOIN station AS T2 ON T1.station_id  =  T2.station_id JOIN train AS T3 ON T3.train_id  =  T1.train_id WHERE T3.Name  =  "Guruvayur Express"

Question: Find the names of the trains that do not pass any station located in London.
SQL: SELECT T2.name FROM train_station AS T1 JOIN train AS T2 ON T1.train_id  =  T2.train_id WHERE T1.station_id NOT IN (SELECT T4.station_id FROM train_station AS T3 JOIN station AS T4 ON T3.station_id  =  T4.station_id WHERE t4.location  =  "London")

Question: List the names and locations of all stations ordered by their yearly entry exit and interchange amounts.
SQL: SELECT name ,  LOCATION FROM station ORDER BY Annual_entry_exit ,  Annual_interchanges