human
Schema:
buildings (id, name, City, Height, Stories, Status)
Companies (id, name, Headquarters, Industry, Sales_billion, Profits_billion, Assets_billion, Market_Value_billion)
Office_locations (building_id, company_id, move_in_year)
Please answer with SQL.
assistant
Sure, I understand.
human
How many different industries are the companies in?
assistant
SELECT count(DISTINCT Industry) FROM Companies
human
Please show each industry and the corresponding number of companies in that industry.
assistant
SELECT Industry , COUNT(*) FROM Companies GROUP BY Industry
human
List the industry shared by the most companies.
assistant
SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
human
What are the names of companies whose headquarters are not 'USA'?
assistant
SELECT name FROM Companies WHERE Headquarters != 'USA'
human
Show the headquarters shared by more than two companies.
assistant
SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*) > 2
human
Show the industries shared by companies whose headquarters are 'USA' and companies whose headquarters are 'China'.
assistant
SELECT Industry FROM Companies WHERE Headquarters = "USA" INTERSECT SELECT Industry FROM Companies WHERE Headquarters = "China" ;