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 name of the building that has the most company offices.
assistant
SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id JOIN Companies AS T3 ON T1.company_id = T3.id GROUP BY T1.building_id ORDER BY COUNT(*) DESC LIMIT 1
human
List the industry shared by the most companies.
assistant
SELECT Industry FROM Companies GROUP BY Industry ORDER BY COUNT(*) DESC LIMIT 1
human
How many companies are there?
assistant
SELECT count(*) FROM Companies
human
How many different industries are the companies in?
assistant
SELECT count(DISTINCT Industry) FROM Companies
human
What are the names of companies whose headquarters are not 'USA'?
assistant
SELECT name FROM Companies WHERE Headquarters != 'USA'
human
Which headquarter locations are used by more than 2 companies?
assistant
SELECT Headquarters FROM Companies GROUP BY Headquarters HAVING COUNT( * ) > 2 ;