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
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 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 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
Show the institution type with an institution founded after 1990 and an institution with at least 1000 enrollment.
assistant
SELECT TYPE FROM institution WHERE founded > 1990 AND enrollment >= 1000
human
Show the names and locations of institutions that are founded after 1990 and have the type 'Private'.
assistant
SELECT institution , LOCATION FROM institution WHERE founded > 1990 AND TYPE = "Private" ;