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 the largest number of institutions.
assistant
SELECT TYPE FROM institution GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1
human
How many proteins are associated with an institution in a building with at least 20 floors?
assistant
SELECT count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id = T2.institution_id JOIN building AS T3 ON T3.building_id = T1.building_id WHERE T3.floors >= 20
human
How many proteins are associated with an institution founded after 1880 or an institution with type 'Private'?
assistant
SELECT count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id = T2.institution_id WHERE T1.founded > 1880 OR T1.type = 'Private'
human
How many institutions do not have an associated protein in our record?
assistant
SELECT count(*) FROM institution WHERE institution_id NOT IN (SELECT institution_id FROM protein)
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 the protein name and the institution name.
assistant
SELECT T2.protein_name , T1.institution FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id = T2.institution_id ;