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
order all gas station locations by the opening year.
assistant
SELECT LOCATION FROM gas_station ORDER BY open_year
human
Show company name and main industry without a gas station.
assistant
SELECT company , main_industry FROM company WHERE company_id NOT IN (SELECT company_id FROM station_company)
human
Show all locations and the number of gas stations in each location ordered by the count.
assistant
SELECT LOCATION , count(*) FROM gas_station GROUP BY LOCATION ORDER BY count(*)
human
Show the company name with the number of gas station.
assistant
SELECT T2.company , count(*) FROM station_company AS T1 JOIN company AS T2 ON T1.company_id = T2.company_id GROUP BY T1.company_id
human
find the location and Representative name of the gas stations owned by the companies with top 3 Asset amounts.
assistant
SELECT T3.location , T3.Representative_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 ORDER BY T2.Assets_billion DESC LIMIT 3
human
Show all locations where a gas station for company with market value greater than 100 is located.
assistant
SELECT T3.location 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.market_value > 100 ;