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
Show the headquarters shared by more than two companies.
assistant
SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT(*) > 2
human
How many different industries are the companies in?
assistant
SELECT count(DISTINCT Industry) FROM Companies
human
List the names of buildings that have no company office.
assistant
SELECT name FROM buildings WHERE id NOT IN (SELECT building_id FROM Office_locations)
human
How many companies are there?
assistant
SELECT count(*) FROM Companies
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'
human
What are the names of companies whose headquarters are not 'USA'?
assistant
SELECT name FROM Companies WHERE Headquarters != "USA" ;