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
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 all the distinct institution types.
assistant
SELECT DISTINCT TYPE FROM institution
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'
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 institution types, along with the number of institutions and total enrollment for each type.
assistant
SELECT TYPE , count(*) , sum(enrollment) FROM institution GROUP BY TYPE
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 ;