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 name of buildings that do not have any institution.
assistant
SELECT name FROM building WHERE building_id NOT IN (SELECT building_id FROM institution)
human
For each building, show the name of the building and the number of institutions in it.
assistant
SELECT T1.name , count(*) FROM building AS T1 JOIN institution AS T2 ON T1.building_id = T2.building_id GROUP BY T1.building_id
human
What are the average, maximum, and minimum number of floors for all buildings?
assistant
SELECT avg(floors) , max(floors) , min(floors) FROM building
human
What is the name of the tallest building?
assistant
SELECT name FROM building ORDER BY height_feet DESC LIMIT 1
human
List the names of buildings with at least 200 feet of height and with at least 20 floors.
assistant
SELECT name FROM building WHERE height_feet >= 200 AND floors >= 20
human
How many buildings are there?
assistant
SELECT COUNT( * ) FROM building ;