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 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
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 institution names along with the number of proteins for each institution.
assistant
SELECT T1.institution , count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id = T2.institution_id GROUP BY T1.institution_id
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 the largest number of institutions.
assistant
SELECT TYPE FROM institution GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
human
Show all the distinct institution types.
assistant
SELECT DISTINCT TYPE FROM institution ;