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
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
Find the stories of the building with the largest height.
assistant
SELECT Stories FROM buildings ORDER BY Height DESC LIMIT 1
human
List the names of buildings in descending order of building height.
assistant
SELECT name FROM buildings ORDER BY Height DESC
human
Find the names of the buildings in 'on-hold' status, and sort them in ascending order of building stories.
assistant
SELECT name FROM buildings WHERE Status = "on-hold" ORDER BY Stories ASC ;