
CREATE TABLE company (
	"Company_ID" INTEGER, 
	"Rank" INTEGER, 
	"Company" TEXT, 
	"Headquarters" TEXT, 
	"Main_Industry" TEXT, 
	"Sales_billion" REAL, 
	"Profits_billion" REAL, 
	"Assets_billion" REAL, 
	"Market_Value" REAL, 
	PRIMARY KEY ("Company_ID")
)


CREATE TABLE gas_station (
	"Station_ID" INTEGER, 
	"Open_Year" INTEGER, 
	"Location" TEXT, 
	"Manager_Name" TEXT, 
	"Vice_Manager_Name" TEXT, 
	"Representative_Name" TEXT, 
	PRIMARY KEY ("Station_ID")
)


CREATE TABLE station_company (
	"Station_ID" INTEGER, 
	"Company_ID" INTEGER, 
	"Rank_of_the_Year" INTEGER, 
	PRIMARY KEY ("Station_ID", "Company_ID"), 
	FOREIGN KEY("Company_ID") REFERENCES company ("Company_ID"), 
	FOREIGN KEY("Station_ID") REFERENCES gas_station ("Station_ID")
)

CREATE TABLE company (
	"Company_ID" INTEGER, 
	"Rank" INTEGER, 
	"Company" TEXT, 
	"Headquarters" TEXT, 
	"Main_Industry" TEXT, 
	"Sales_billion" REAL, 
	"Profits_billion" REAL, 
	"Assets_billion" REAL, 
	"Market_Value" REAL, 
	PRIMARY KEY ("Company_ID")
)

/*
2 rows from company table:
Company_ID	Rank	Company	Headquarters	Main_Industry	Sales_billion	Profits_billion	Assets_billion	Market_Value
1	1	ExxonMobil	USA	Oil and gas	433.5	41.1	331.1	407.4
2	3	General Electric	USA	Conglomerate	147.3	14.2	717.2	213.7
*/


CREATE TABLE gas_station (
	"Station_ID" INTEGER, 
	"Open_Year" INTEGER, 
	"Location" TEXT, 
	"Manager_Name" TEXT, 
	"Vice_Manager_Name" TEXT, 
	"Representative_Name" TEXT, 
	PRIMARY KEY ("Station_ID")
)

/*
2 rows from gas_station table:
Station_ID	Open_Year	Location	Manager_Name	Vice_Manager_Name	Representative_Name
1	1998	Herne Hill	BrianWingrave	Russell Denman	Clive Burr
2	1999	Channel Hill	SimonMarloe	Russell Brown	Rob Jefferies 
*/


CREATE TABLE station_company (
	"Station_ID" INTEGER, 
	"Company_ID" INTEGER, 
	"Rank_of_the_Year" INTEGER, 
	PRIMARY KEY ("Station_ID", "Company_ID"), 
	FOREIGN KEY("Company_ID") REFERENCES company ("Company_ID"), 
	FOREIGN KEY("Station_ID") REFERENCES gas_station ("Station_ID")
)

/*
2 rows from station_company table:
Station_ID	Company_ID	Rank_of_the_Year
11	1	1
1	3	2
*/
What are the locations of all the gas stations ordered by opening year?
SELECT LOCATION FROM gas_station ORDER BY open_year
[{'Location': 'Herne Hill'}, {'Location': 'Channel Hill'}, {'Location': 'Reading North'}, {'Location': 'Herne St'}, {'Location': 'Reading'}, {'Location': 'Herne Ave'}, {'Location': 'Hennry Hill'}, {'Location': 'Jane Ave'}, {'Location': 'Maindy Hill'}, {'Location': 'Maindy Ave'}, {'Location': 'Newport Rd'}]