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 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
Find the stories of the building with the largest height.
assistant
SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
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 names of buildings in descending order of building height.
assistant
SELECT name FROM buildings ORDER BY Height DESC
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 ;