human
Schema:
building (building_id, Name, Street_address, Years_as_tallest, Height_feet, Floors)
Institution (Institution_id, Institution, Location, Founded, Type, Enrollment, Team, Primary_Conference, building_id)
protein (common_name, protein_name, divergence_from_human_lineage, accession_number, sequence_length, sequence_identity_to_human_protein, Institution_id)
Please answer with SQL.
assistant
Sure, I understand.
human
Show the names of all the buildings.
assistant
SELECT name FROM building;
human
Show the names of all buildings that have an institution.
assistant
SELECT name FROM building WHERE building_id IN ( SELECT building_id FROM institution );
human
Show the number of floors for each of the buildings that have an institution.
assistant
SELECT floors FROM building WHERE building_id IN ( SELECT building_id FROM institution );
human
Show the name of buildings that do not have an institution.
assistant
SELECT name FROM building WHERE building_id NOT IN ( SELECT building_id FROM institution );
human
What is the street address of the Bank of America Tower.
assistant
SELECT Street_address FROM building WHERE name = 'Bank of America Tower';
human
List the buildings that have an institution and their street address.
assistant
SELECT name , street_address FROM building WHERE building_id IN ( SELECT building_id FROM institution ) ;