Database: allergy_1

create table Allergy_Type (
       Allergy 		  VARCHAR(20) PRIMARY KEY,
       AllergyType 	  VARCHAR(20)
);
create table Has_Allergy (
       StuID 		 INTEGER,
       Allergy 		 VARCHAR(20),
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(Allergy) REFERENCES Allergy_Type(Allergy)
);
create table Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 );

Question: How many allergies are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type

Question: How many allergy entries are there?
SQL: SELECT count(DISTINCT allergy) FROM Allergy_type

Question: How many different allergy types exist?
SQL: SELECT count(DISTINCT allergytype) FROM Allergy_type

Question: How many distinct allergies are there?
SQL: SELECT count(DISTINCT allergytype) FROM Allergy_type

Question: Show all allergy types.
SQL: SELECT DISTINCT allergytype FROM Allergy_type

Question: What are the different allergy types?
SQL: SELECT DISTINCT allergytype FROM Allergy_type

Question: Show all allergies and their types.
SQL: SELECT allergy ,  allergytype FROM Allergy_type

Question: What are the allergies and their types?
SQL: SELECT allergy ,  allergytype FROM Allergy_type

Question: Show all allergies with type food.
SQL: SELECT DISTINCT allergy FROM Allergy_type WHERE allergytype  =  "food"

Question: What are all the different food allergies?
SQL: SELECT DISTINCT allergy FROM Allergy_type WHERE allergytype  =  "food"

Question: What is the type of allergy Cat?
SQL: SELECT allergytype FROM Allergy_type WHERE allergy  =  "Cat"

Question: What is allergy type of a cat allergy?
SQL: SELECT allergytype FROM Allergy_type WHERE allergy  =  "Cat"

Question: How many allergies have type animal?
SQL: SELECT count(*) FROM Allergy_type WHERE allergytype  =  "animal"

Question: How many animal type allergies exist?
SQL: SELECT count(*) FROM Allergy_type WHERE allergytype  =  "animal"

Question: Show all allergy types and the number of allergies in each type.
SQL: SELECT allergytype ,  count(*) FROM Allergy_type GROUP BY allergytype

Question: What are the allergy types and how many allergies correspond to each one?
SQL: SELECT allergytype ,  count(*) FROM Allergy_type GROUP BY allergytype

Question: Which allergy type has most number of allergies?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) DESC LIMIT 1

Question: Which allergy type is most common?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) DESC LIMIT 1

Question: Which allergy type has least number of allergies?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) ASC LIMIT 1

Question: Which allergy type is the least common?
SQL: SELECT allergytype FROM Allergy_type GROUP BY allergytype ORDER BY count(*) ASC LIMIT 1

Question: How many students are there?
SQL: SELECT count(*) FROM Student

Question: What is the total number of students?
SQL: SELECT count(*) FROM Student

Question: Show first name and last name for all students.
SQL: SELECT Fname ,  Lname FROM Student

Question: What are the full names of all students
SQL: SELECT Fname ,  Lname FROM Student

Question: How many different advisors are listed?
SQL: SELECT count(DISTINCT advisor) FROM Student

Question: How many advisors are there?
SQL: SELECT count(DISTINCT advisor) FROM Student

Question: Show all majors.
SQL: SELECT DISTINCT Major FROM Student

Question: What are the different majors?
SQL: SELECT DISTINCT Major FROM Student

Question: Show all cities where students live.
SQL: SELECT DISTINCT city_code FROM Student

Question: What cities do students live in?
SQL: SELECT DISTINCT city_code FROM Student

Question: Show first name, last name, age for all female students. Their sex is F.
SQL: SELECT Fname ,  Lname ,  Age FROM Student WHERE Sex  =  'F'

Question: What are the full names and ages for all female students whose sex is F?
SQL: SELECT Fname ,  Lname ,  Age FROM Student WHERE Sex  =  'F'

Question: Show student ids for all male students.
SQL: SELECT StuID FROM Student WHERE Sex  =  'M'

Question: What are the student ids for all male students?
SQL: SELECT StuID FROM Student WHERE Sex  =  'M'

Question: How many students are age 18?
SQL: SELECT count(*) FROM Student WHERE age  =  18

Question: How many students are 18 years old?
SQL: SELECT count(*) FROM Student WHERE age  =  18

Question: Show all student ids who are older than 20.
SQL: SELECT StuID FROM Student WHERE age  >  20

Question: What are the student ids for students over 20 years old?
SQL: SELECT StuID FROM Student WHERE age  >  20

Question: Which city does the student whose last name is "Kim" live in?
SQL: SELECT city_code FROM Student WHERE LName  =  "Kim"

Question: Give the city that the student whose family name is Kim lives in.
SQL: SELECT city_code FROM Student WHERE LName  =  "Kim"

Question: Who is the advisor of student with ID 1004?
SQL: SELECT Advisor FROM Student WHERE StuID  =  1004

Question: Who advises student 1004?
SQL: SELECT Advisor FROM Student WHERE StuID  =  1004

Question: How many students live in HKG or CHI?
SQL: SELECT count(*) FROM Student WHERE city_code  =  "HKG" OR city_code  =  "CHI"

Question: Give the number of students living in either HKG or CHI.
SQL: SELECT count(*) FROM Student WHERE city_code  =  "HKG" OR city_code  =  "CHI"

Question: Show the minimum, average, and maximum age of all students.
SQL: SELECT min(age) ,  avg(age) ,  max(age) FROM Student

Question: What is the minimum, mean, and maximum age across all students?
SQL: SELECT min(age) ,  avg(age) ,  max(age) FROM Student

Question: What is the last name of the youngest student?
SQL: SELECT LName FROM Student WHERE age  =  (SELECT min(age) FROM Student)

Question: Provide the last name of the youngest student.
SQL: SELECT LName FROM Student WHERE age  =  (SELECT min(age) FROM Student)

Question: Show the student id of the oldest student.
SQL: SELECT StuID FROM Student WHERE age  =  (SELECT max(age) FROM Student)

Question: What student id corresponds to the oldest student?
SQL: SELECT StuID FROM Student WHERE age  =  (SELECT max(age) FROM Student)

Question: Show all majors and corresponding number of students.
SQL: SELECT major ,  count(*) FROM Student GROUP BY major

Question: How many students are there for each major?
SQL: SELECT major ,  count(*) FROM Student GROUP BY major

Question: Which major has most number of students?
SQL: SELECT major FROM Student GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: What is the largest major?
SQL: SELECT major FROM Student GROUP BY major ORDER BY count(*) DESC LIMIT 1

Question: Show all ages and corresponding number of students.
SQL: SELECT age ,  count(*) FROM Student GROUP BY age

Question: How old is each student and how many students are each age?
SQL: SELECT age ,  count(*) FROM Student GROUP BY age

Question: Show the average age for male and female students.
SQL: SELECT avg(age) ,  sex FROM Student GROUP BY sex

Question: What are the average ages for male and female students?
SQL: SELECT avg(age) ,  sex FROM Student GROUP BY sex

Question: Show all cities and corresponding number of students.
SQL: SELECT city_code ,  count(*) FROM Student GROUP BY city_code

Question: How many students live in each city?
SQL: SELECT city_code ,  count(*) FROM Student GROUP BY city_code

Question: Show all advisors and corresponding number of students.
SQL: SELECT advisor ,  count(*) FROM Student GROUP BY advisor

Question: How many students does each advisor have?
SQL: SELECT advisor ,  count(*) FROM Student GROUP BY advisor

Question: Which advisor has most number of students?
SQL: SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1

Question: Give the advisor with the most students.
SQL: SELECT advisor FROM Student GROUP BY advisor ORDER BY count(*) DESC LIMIT 1

Question: How many students have cat allergies?
SQL: SELECT count(*) FROM Has_allergy WHERE Allergy  =  "Cat"

Question: How many students are affected by cat allergies?
SQL: SELECT count(*) FROM Has_allergy WHERE Allergy  =  "Cat"

Question: Show all student IDs who have at least two allergies.
SQL: SELECT StuID FROM Has_allergy GROUP BY StuID HAVING count(*)  >=  2

Question: What are the students ids of students who have more than one allergy?
SQL: SELECT StuID FROM Has_allergy GROUP BY StuID HAVING count(*)  >=  2

Question: What are the student ids of students who don't have any allergies?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Has_allergy

Question: Which students are unaffected by allergies?
SQL: SELECT StuID FROM Student EXCEPT SELECT StuID FROM Has_allergy

Question: How many female students have milk or egg allergies?
SQL: SELECT count(*) FROM has_allergy AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.sex  =  "F" AND T1.allergy  =  "Milk" OR T1.allergy  =  "Eggs"

Question: How many students who are female are allergic to milk or eggs?
SQL: SELECT count(*) FROM has_allergy AS T1 JOIN Student AS T2 ON T1.StuID  =  T2.StuID WHERE T2.sex  =  "F" AND T1.allergy  =  "Milk" OR T1.allergy  =  "Eggs"

Question: How many students have a food allergy?
SQL: SELECT count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy WHERE T2.allergytype  =  "food"

Question: How many students are affected by food related allergies?
SQL: SELECT count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy WHERE T2.allergytype  =  "food"

Question: Which allergy has most number of students affected?
SQL: SELECT Allergy FROM Has_allergy GROUP BY Allergy ORDER BY count(*) DESC LIMIT 1

Question: Which allergy is the most common?
SQL: SELECT Allergy FROM Has_allergy GROUP BY Allergy ORDER BY count(*) DESC LIMIT 1

Question: Show all allergies with number of students affected.
SQL: SELECT Allergy ,  count(*) FROM Has_allergy GROUP BY Allergy

Question: How many students have each different allergy?
SQL: SELECT Allergy ,  count(*) FROM Has_allergy GROUP BY Allergy

Question: Show all allergy type with number of students affected.
SQL: SELECT T2.allergytype ,  count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy GROUP BY T2.allergytype

Question: How many students are affected by each allergy type?
SQL: SELECT T2.allergytype ,  count(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy  =  T2.allergy GROUP BY T2.allergytype

Question: Find the last name and age of the student who has allergy to both milk and cat.
SQL: SELECT lname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" INTERSECT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")

Question: What are the last names and ages of the students who are allergic to milk and cat?
SQL: SELECT lname ,  age FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" INTERSECT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")

Question: What are the allergies and their types that the student with first name Lisa has? And order the result by name of allergies.
SQL: SELECT T1.Allergy ,  T1.AllergyType FROM Allergy_type AS T1 JOIN Has_allergy AS T2 ON T1.Allergy  =  T2.Allergy JOIN Student AS T3 ON T3.StuID  =  T2.StuID WHERE T3.Fname  =  "Lisa" ORDER BY T1.Allergy

Question: What are the allergies the girl named Lisa has? And what are the types of them? Order the result by allergy names.
SQL: SELECT T1.Allergy ,  T1.AllergyType FROM Allergy_type AS T1 JOIN Has_allergy AS T2 ON T1.Allergy  =  T2.Allergy JOIN Student AS T3 ON T3.StuID  =  T2.StuID WHERE T3.Fname  =  "Lisa" ORDER BY T1.Allergy

Question: Find the first name and gender of the student who has allergy to milk but not cat.
SQL: SELECT fname ,  sex FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" EXCEPT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")

Question: What are the first name and gender of the students who have allergy to milk but can put up with cats?
SQL: SELECT fname ,  sex FROM Student WHERE StuID IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Milk" EXCEPT SELECT StuID FROM Has_allergy WHERE Allergy  =  "Cat")

Question: Find the average age of the students who have allergies with food and animal types.
SQL: SELECT avg(age) FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" INTERSECT SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "animal")

Question: How old are the students with allergies to food and animal types on average?
SQL: SELECT avg(age) FROM Student WHERE StuID IN ( SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" INTERSECT SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "animal")

Question: List the first and last name of the students who do not have any food type allergy.
SQL: SELECT fname ,  lname FROM Student WHERE StuID NOT IN (SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")

Question: What is the full name of each student who is not allergic to any type of food.
SQL: SELECT fname ,  lname FROM Student WHERE StuID NOT IN (SELECT T1.StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")

Question: Find the number of male (sex is 'M') students who have some food type allery.
SQL: SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")

Question: How many male students (sex is 'M') are allergic to any type of food?
SQL: SELECT count(*) FROM Student WHERE sex  =  "M" AND StuID IN (SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food")

Question: Find the different first names and cities of the students who have allergy to milk or cat.
SQL: SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"

Question: What are the distinct first names and cities of the students who have allergy either to milk or to cat?
SQL: SELECT DISTINCT T1.fname ,  T1.city_code FROM Student AS T1 JOIN Has_Allergy AS T2 ON T1.stuid  =  T2.stuid WHERE T2.Allergy  =  "Milk" OR T2.Allergy  =  "Cat"

Question: Find the number of students who are older than 18 and do not have allergy to either food or animal.
SQL: SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")

Question: How many students are over 18 and do not have allergy to food type or animal type?
SQL: SELECT count(*) FROM Student WHERE age  >  18 AND StuID NOT IN ( SELECT StuID FROM Has_allergy AS T1 JOIN Allergy_Type AS T2 ON T1.Allergy  =  T2.Allergy WHERE T2.allergytype  =  "food" OR T2.allergytype  =  "animal")

Question: Find the first name and major of the students who are not allegry to soy.
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")

Question: What are the first name and major of the students who are able to consume soy?
SQL: SELECT fname ,  major FROM Student WHERE StuID NOT IN (SELECT StuID FROM Has_allergy WHERE Allergy  =  "Soy")