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