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 names of the buildings that have more than one 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 HAVING COUNT(*) > 1
human
List the name of a building along with the name of a company whose office is in the building.
assistant
SELECT T3.name , 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
human
Please show the names of the buildings whose status is 'on-hold', in ascending order of stories.
assistant
SELECT name FROM buildings WHERE Status = 'on-hold' ORDER BY Stories ASC
human
What are the name and assets of each company, sorted in ascending order of company name?
assistant
SELECT name , Assets_billion FROM Companies ORDER BY name ASC
human
Find the stories of the building with the largest height.
assistant
SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
human
What are the names of buildings sorted in descending order of building height?
assistant
SELECT name FROM buildings ORDER BY Height DESC ;