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 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
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 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 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
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 ;