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 and heights of buildings with at least two institutions founded after 1880.
assistant
SELECT T1.name , T1.height_feet FROM building AS T1 JOIN institution AS T2 ON T1.building_id = T2.building_id WHERE T2.founded > 1880 GROUP BY T1.building_id HAVING count(*) >= 2
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
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
Show the number of buildings with a height above the average or a number of floors above the average.
assistant
SELECT count(*) FROM building WHERE height_feet > (SELECT avg(height_feet) FROM building) OR floors > (SELECT avg(floors) FROM building)
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
Show the name, street address, and number of floors for all buildings ordered by the number of floors.
assistant
SELECT name , street_address , floors FROM building ORDER BY floors ;