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