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 all main industry and total market value in each industry.
assistant
SELECT main_industry , sum(market_value) FROM company GROUP BY main_industry
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
Show minimum, maximum, and average market value for all companies.
assistant
SELECT min(market_value) , max(market_value) , avg(market_value) FROM company
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
Show all company names and headquarters in the descending order of market value.
assistant
SELECT company , headquarters FROM company ORDER BY market_value DESC ;