Database: protein_institute

CREATE TABLE "building" (
"building_id" text,
"Name" text,
"Street_address" text,
"Years_as_tallest" text,
"Height_feet" int,
"Floors" int,
PRIMARY KEY("building_id")
);
CREATE TABLE "Institution" (
"Institution_id"  text,
"Institution" text,
"Location" text,
"Founded" real,
"Type" text,
"Enrollment" int,
"Team" text,
"Primary_Conference" text,
"building_id" text,
PRIMARY KEY("Institution_id"),
FOREIGN  KEY ("building_id") REFERENCES "building"("building_id")
);
CREATE TABLE "protein" (
"common_name" text,
"protein_name" text,
"divergence_from_human_lineage" real,
"accession_number" text,
"sequence_length" real,
"sequence_identity_to_human_protein" text,
"Institution_id" text,
PRIMARY KEY("common_name"),
FOREIGN KEY("Institution_id") REFERENCES "Institution"("Institution_id")
);

Question: How many buildings are there?
SQL: SELECT count(*) FROM building

Question: Show the name, street address, and number of floors for all buildings ordered by the number of floors.
SQL: SELECT name ,  street_address ,  floors FROM building ORDER BY floors

Question: What is the name of the tallest building?
SQL: SELECT name FROM building ORDER BY height_feet DESC LIMIT 1

Question: What are the average, maximum, and minimum number of floors for all buildings?
SQL: SELECT avg(floors) ,  max(floors) ,  min(floors) FROM building

Question: Show the number of buildings with a height above the average or a number of floors above the average.
SQL: SELECT count(*) FROM building WHERE height_feet  >  (SELECT avg(height_feet) FROM building) OR floors  >  (SELECT avg(floors) FROM building)

Question: List the names of buildings with at least 200 feet of height and with at least 20 floors.
SQL: SELECT name FROM building WHERE height_feet  >=  200 AND floors  >=  20

Question: Show the names and locations of institutions that are founded after 1990 and have the type "Private".
SQL: SELECT institution ,  LOCATION FROM institution WHERE founded  >  1990 AND TYPE  =  'Private'

Question: Show institution types, along with the number of institutions and total enrollment for each type.
SQL: SELECT TYPE ,  count(*) ,  sum(enrollment) FROM institution GROUP BY TYPE

Question: Show the institution type with the largest number of institutions.
SQL: SELECT TYPE FROM institution GROUP BY TYPE ORDER BY count(*) DESC LIMIT 1

Question: Show the institution type with an institution founded after 1990 and an institution with at least 1000 enrollment.
SQL: SELECT TYPE FROM institution WHERE founded  >  1990 AND enrollment  >=  1000

Question: Show the name of buildings that do not have any institution.
SQL: SELECT name FROM building WHERE building_id NOT IN (SELECT building_id FROM institution)

Question: Show the names of buildings except for those having an institution founded in 2003.
SQL: SELECT name FROM building EXCEPT SELECT T1.name FROM building AS T1 JOIN institution AS T2 ON T1.building_id  =  T2.building_id WHERE T2.founded  =  2003

Question: For each building, show the name of the building and the number of institutions in it.
SQL: SELECT T1.name ,  count(*) FROM building AS T1 JOIN institution AS T2 ON T1.building_id  =  T2.building_id GROUP BY T1.building_id

Question: Show the names and heights of buildings with at least two institutions founded after 1880.
SQL: SELECT T1.name ,  T1.height_feet FROM building AS T1 JOIN institution AS T2 ON T1.building_id  =  T2.building_id WHERE T2.founded  >  1880 GROUP BY T1.building_id HAVING count(*)  >=  2

Question: Show all the distinct institution types.
SQL: SELECT DISTINCT TYPE FROM institution

Question: Show institution names along with the number of proteins for each institution.
SQL: SELECT T1.institution ,  count(*) FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id GROUP BY T1.institution_id

Question: How many proteins are associated with an institution founded after 1880 or an institution with type "Private"?
SQL: 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'

Question: Show the protein name and the institution name.
SQL: SELECT T2.protein_name ,  T1.institution FROM institution AS T1 JOIN protein AS T2 ON T1.institution_id  =  T2.institution_id

Question: How many proteins are associated with an institution in a building with at least 20 floors?
SQL: 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

Question: How many institutions do not have an associated protein in our record?
SQL: SELECT count(*) FROM institution WHERE institution_id NOT IN (SELECT institution_id FROM protein)