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
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
Show all the distinct institution types.
assistant
SELECT DISTINCT TYPE FROM institution
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 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
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 ;