human
Schema:
company (Company_ID, Rank, Company, Headquarters, Main_Industry, Sales_billion, Profits_billion, Assets_billion, Market_Value)
gas_station (Station_ID, Open_Year, Location, Manager_Name, Vice_Manager_Name, Representative_Name)
station_company (Station_ID, Company_ID, Rank_of_the_Year)
Please answer with SQL.
assistant
Sure, I understand.
human
Show gas station id, location, and manager_name for all gas stations ordered by open year.
assistant
SELECT station_id , LOCATION , manager_name FROM gas_station ORDER BY open_year
human
order all gas station locations by the opening year.
assistant
SELECT LOCATION FROM gas_station ORDER BY open_year
human
Show the manager name for gas stations belonging to the ExxonMobil company.
assistant
SELECT T3.manager_name FROM station_company AS T1 JOIN company AS T2 ON T1.company_id = T2.company_id JOIN gas_station AS T3 ON T1.station_id = T3.station_id WHERE T2.company = 'ExxonMobil'
human
How many gas companies are there?
assistant
SELECT count(*) FROM company
human
How many gas station are opened between 2000 and 2005?
assistant
SELECT count(*) FROM gas_station WHERE open_year BETWEEN 2000 AND 2005
human
What is the name of the manager with the most gas stations that opened after 2000?
assistant
SELECT manager_name FROM gas_station WHERE open_year > 2000 GROUP BY manager_name ORDER BY COUNT( * ) DESC LIMIT 1 ;