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
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
List all headquarters and the number of companies in each headquarter.
assistant
SELECT headquarters , count(*) FROM company GROUP BY headquarters
human
find the rank, company names, market values of the companies in the banking industry order by their sales and profits in billion.
assistant
SELECT rank , company , market_value FROM company WHERE main_industry = 'Banking' ORDER BY sales_billion , profits_billion
human
List the main industry with highest total market value and its number of companies.
assistant
SELECT main_industry , count(*) FROM company GROUP BY main_industry ORDER BY sum(market_value) DESC LIMIT 1
human
List the company name and rank for all companies in the decreasing order of their sales.
assistant
SELECT company , rank FROM company ORDER BY Sales_billion DESC
human
What are the names and headquarters of all companies ordered by descending market value?
assistant
SELECT company , headquarters FROM company ORDER BY market_value DESC ;