CREATE TABLE Allergy_Type (
    Allergy TEXT PRIMARY KEY,
    AllergyType TEXT
);
CREATE TABLE Has_Allergy (
    StuID NUMBER,
    Allergy TEXT,
    FOREIGN KEY ("Allergy") REFERENCES Allergy_Type("Allergy"),
    FOREIGN KEY ("StuID") REFERENCES Student("StuID")
);
CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);

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