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 all the distinct institution types.
assistant
SELECT DISTINCT TYPE FROM institution
human
Show the names of buildings except for those having an institution founded in 2003.
assistant
SELECT name FROM building EXCEPT SELECT T1.name FROM building AS T1 JOIN institution AS T2 ON T1.building_id = T2.building_id WHERE T2.founded = 2003
human
Show the institution type with the largest number of institutions.
assistant
SELECT TYPE FROM institution GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
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
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
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 ;