text: CREATE TABLE Has_allergy (allergy VARCHAR); CREATE TABLE Allergy_type (allergy VARCHAR, allergytype VARCHAR) -- How many students have a food allergy? SELECT COUNT(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy = T2.allergy WHERE T2.allergytype = "food" CREATE TABLE Has_allergy (Allergy VARCHAR) -- Which allergy has most number of students affected? SELECT Allergy FROM Has_allergy GROUP BY Allergy ORDER BY COUNT(*) DESC LIMIT 1 CREATE TABLE Has_allergy (Allergy VARCHAR) -- Show all allergies with number of students affected. SELECT Allergy, COUNT(*) FROM Has_allergy GROUP BY Allergy CREATE TABLE Has_allergy (allergy VARCHAR); CREATE TABLE Allergy_type (allergytype VARCHAR, allergy VARCHAR) -- Show all allergy type with number of students affected. SELECT T2.allergytype, COUNT(*) FROM Has_allergy AS T1 JOIN Allergy_type AS T2 ON T1.allergy = T2.allergy GROUP BY T2.allergytype CREATE TABLE Has_allergy (lname VARCHAR, age VARCHAR, StuID VARCHAR, Allergy VARCHAR); CREATE TABLE Student (lname VARCHAR, age VARCHAR, StuID VARCHAR, Allergy VARCHAR) -- Find the last name and age of the student who has allergy to both milk and cat. 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") CREATE TABLE Has_allergy (Allergy VARCHAR, StuID VARCHAR); CREATE TABLE Student (StuID VARCHAR, Fname VARCHAR); CREATE TABLE Allergy_type (Allergy VARCHAR, AllergyType VARCHAR) -- What are the allergies and their types that the student with first name Lisa has? And order the result by name of allergies. 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