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
How many buildings are there?
assistant
SELECT COUNT( * ) FROM buildings;
human
How many have a company office?
assistant
SELECT COUNT( distinct building_id ) FROM Office_locations;
human
What are their building names?
assistant
SELECT T2.name FROM Office_locations AS T1 JOIN buildings AS T2 ON T1.building_id = T2.id GROUP by T2.id;
human
What are the names of the buildings other than those?
assistant
SELECT name FROM buildings WHERE id NOT IN ( SELECT building_id FROM Office_locations ) ;