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 minimum, maximum, and average market value for all companies.
assistant
SELECT min(market_value) , max(market_value) , avg(market_value) FROM company
human
List all headquarters and the number of companies in each headquarter.
assistant
SELECT headquarters , count(*) FROM company GROUP BY headquarters
human
Show the company name and the main industry for all companies whose headquarters are not from USA.
assistant
SELECT company , main_industry FROM company WHERE headquarters != 'USA'
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 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
What are the different main industries for all companies?
assistant
SELECT DISTINCT main_industry FROM company ;