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
Can you tell me which companies are located at the Torre KOI building? | What attributes of these companies do you want to know? | The names of the companies that are present in that building.
assistant
SELECT T3.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 WHERE T2.name = 'Torre KOI';
human
What other buildings is JPMorgan Chase also located in?
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 WHERE T3.name = 'JPMorgan Chase' AND T2.name ! = 'Torre KOI';
human
Does Torre Mitkah have an HSBC located in it?
assistant
SELECT * 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 WHERE T3.name = 'HSBC' AND T2.name = 'Torre Mitikah';
human
What is the name of company with the company id 4?
assistant
SELECT name FROM companies WHERE id = 4 ;