
CREATE TABLE route (
	train_id INTEGER, 
	station_id INTEGER, 
	PRIMARY KEY (train_id, station_id), 
	FOREIGN KEY(station_id) REFERENCES station (id), 
	FOREIGN KEY(train_id) REFERENCES train (id)
)


CREATE TABLE station (
	id INTEGER, 
	network_name TEXT, 
	services TEXT, 
	local_authority TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE train (
	id INTEGER, 
	train_number INTEGER, 
	name TEXT, 
	origin TEXT, 
	destination TEXT, 
	time TEXT, 
	interval TEXT, 
	PRIMARY KEY (id)
)


CREATE TABLE weekly_weather (
	station_id INTEGER, 
	day_of_week TEXT, 
	high_temperature INTEGER, 
	low_temperature INTEGER, 
	precipitation REAL, 
	wind_speed_mph INTEGER, 
	PRIMARY KEY (station_id, day_of_week), 
	FOREIGN KEY(station_id) REFERENCES station (id)
)

CREATE TABLE route (
	train_id INTEGER, 
	station_id INTEGER, 
	PRIMARY KEY (train_id, station_id), 
	FOREIGN KEY(station_id) REFERENCES station (id), 
	FOREIGN KEY(train_id) REFERENCES train (id)
)

/*
2 rows from route table:
train_id	station_id
1	1
1	2
*/


CREATE TABLE station (
	id INTEGER, 
	network_name TEXT, 
	services TEXT, 
	local_authority TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from station table:
id	network_name	services	local_authority
1	Amersham	Metropolitan line and Chiltern Railways	Chiltern
2	Bushey	London Overground and London Midland	Watford
*/


CREATE TABLE train (
	id INTEGER, 
	train_number INTEGER, 
	name TEXT, 
	origin TEXT, 
	destination TEXT, 
	time TEXT, 
	interval TEXT, 
	PRIMARY KEY (id)
)

/*
2 rows from train table:
id	train_number	name	origin	destination	time	interval
1	16724	Ananthapuri Express	Trivandrum	Chennai	17:15	Daily
2	16127	Guruvayur Express	Chennai	Guruvayur	22:10	Daily
*/


CREATE TABLE weekly_weather (
	station_id INTEGER, 
	day_of_week TEXT, 
	high_temperature INTEGER, 
	low_temperature INTEGER, 
	precipitation REAL, 
	wind_speed_mph INTEGER, 
	PRIMARY KEY (station_id, day_of_week), 
	FOREIGN KEY(station_id) REFERENCES station (id)
)

/*
2 rows from weekly_weather table:
station_id	day_of_week	high_temperature	low_temperature	precipitation	wind_speed_mph
1	Monday	59	54	90.0	13
1	Tuesday	66	55	20.0	12
*/
Give me the times and numbers of all trains that go to Chennai, ordered by time.
SELECT TIME ,  train_number FROM train WHERE destination  =  'Chennai' ORDER BY TIME
[{'time': '17:15', 'train_number': 16724}, {'time': '4:49', 'train_number': 16128}]