CREATE TABLE Student (
    StuID NUMBER PRIMARY KEY,
    LName TEXT,
    Fname TEXT,
    Age NUMBER,
    Sex TEXT,
    Major NUMBER,
    Advisor NUMBER,
    city_code TEXT
);
CREATE TABLE Dorm (
    dormid NUMBER,
    dorm_name TEXT,
    student_capacity NUMBER,
    gender TEXT
);
CREATE TABLE Dorm_amenity (
    amenid NUMBER,
    amenity_name TEXT
);
CREATE TABLE Has_amenity (
    dormid NUMBER,
    amenid NUMBER,
    FOREIGN KEY ("amenid") REFERENCES Dorm_amenity("amenid"),
    FOREIGN KEY ("dormid") REFERENCES Dorm("dormid")
);
CREATE TABLE Lives_in (
    stuid NUMBER,
    dormid NUMBER,
    room_number NUMBER,
    FOREIGN KEY ("dormid") REFERENCES Dorm("dormid"),
    FOREIGN KEY ("stuid") REFERENCES Student("StuID")
);

Question: List in alphabetic order all different amenities.
SQL: SELECT amenity_name FROM dorm_amenity ORDER BY amenity_name

Question: What are the different dorm amenity names in alphabetical order?
SQL: SELECT amenity_name FROM dorm_amenity ORDER BY amenity_name

Question: Find the code of city where most of students are living in.
SQL: SELECT city_code FROM student GROUP BY city_code ORDER BY count(*) DESC LIMIT 1

Question: What is the code of the city with the most students?
SQL: SELECT city_code FROM student GROUP BY city_code ORDER BY count(*) DESC LIMIT 1

Question: Find the first and last name of students whose age is younger than the average age.
SQL: SELECT fname ,  lname FROM student WHERE age  <  (SELECT avg(age) FROM student)

Question: What is the first and last name of all students who are younger than average?
SQL: SELECT fname ,  lname FROM student WHERE age  <  (SELECT avg(age) FROM student)

Question: List the first and last name of students who are not living in the city with code HKG, and sorted the results by their ages.
SQL: SELECT fname ,  lname FROM student WHERE city_code != 'HKG' ORDER BY age

Question: What are the first and last names of all students who are not living in the city HKG and order the results by age?
SQL: SELECT fname ,  lname FROM student WHERE city_code != 'HKG' ORDER BY age

Question: List name of all amenities which Anonymous Donor Hall has, and sort the results in alphabetic order.
SQL: SELECT T1.amenity_name FROM dorm_amenity AS T1 JOIN has_amenity AS T2 ON T2.amenid  =  T1.amenid JOIN dorm AS T3 ON T2.dormid  =  T3.dormid WHERE T3.dorm_name  =  'Anonymous Donor Hall' ORDER BY T1.amenity_name

Question: What are the amenities in alphabetical order that Anonymous Donor Hall has?
SQL: SELECT T1.amenity_name FROM dorm_amenity AS T1 JOIN has_amenity AS T2 ON T2.amenid  =  T1.amenid JOIN dorm AS T3 ON T2.dormid  =  T3.dormid WHERE T3.dorm_name  =  'Anonymous Donor Hall' ORDER BY T1.amenity_name

Question: Find the number of dorms and total capacity for each gender.
SQL: SELECT count(*) ,  sum(student_capacity) ,  gender FROM dorm GROUP BY gender

Question: How many dorms are there and what is the total capacity for each gender?
SQL: SELECT count(*) ,  sum(student_capacity) ,  gender FROM dorm GROUP BY gender

Question: Find the average and oldest age for students with different sex.
SQL: SELECT avg(age) ,  max(age) ,  sex FROM student GROUP BY sex

Question: What is the average and oldest age for each gender of student?
SQL: SELECT avg(age) ,  max(age) ,  sex FROM student GROUP BY sex

Question: Find the number of students in each major.
SQL: SELECT count(*) ,  major FROM student GROUP BY major

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

Question: Find the number and average age of students living in each city.
SQL: SELECT count(*) ,  avg(age) ,  city_code FROM student GROUP BY city_code

Question: How many students live in each city and what are their average ages?
SQL: SELECT count(*) ,  avg(age) ,  city_code FROM student GROUP BY city_code

Question: Find the average age and number of male students (with sex M) from each city.
SQL: SELECT count(*) ,  avg(age) ,  city_code FROM student WHERE sex  =  'M' GROUP BY city_code

Question: What is the average age and how many male students are there in each city?
SQL: SELECT count(*) ,  avg(age) ,  city_code FROM student WHERE sex  =  'M' GROUP BY city_code

Question: Find the number of students for the cities where have more than one student.
SQL: SELECT count(*) ,  city_code FROM student GROUP BY city_code HAVING count(*)  >  1

Question: How many students are from each city, and which cities have more than one cities?
SQL: SELECT count(*) ,  city_code FROM student GROUP BY city_code HAVING count(*)  >  1

Question: Find the first and last name of students who are not in the largest major.
SQL: SELECT fname ,  lname FROM student WHERE major != (SELECT major FROM student GROUP BY major ORDER BY count(*) DESC LIMIT 1)

Question: What is the first and last name of the students who are not in the largest major?
SQL: SELECT fname ,  lname FROM student WHERE major != (SELECT major FROM student GROUP BY major ORDER BY count(*) DESC LIMIT 1)

Question: Find the number of students whose age is older than the average age for each gender.
SQL: SELECT count(*) ,  sex FROM student WHERE age  >  (SELECT avg(age) FROM student) GROUP BY sex

Question: How many students are older than average for each gender?
SQL: SELECT count(*) ,  sex FROM student WHERE age  >  (SELECT avg(age) FROM student) GROUP BY sex

Question: Find the average age of students living in each dorm and the name of dorm.
SQL: SELECT avg(T1.age) ,  T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid GROUP BY T3.dorm_name

Question: What is the average age for each dorm and what are the names of each dorm?
SQL: SELECT avg(T1.age) ,  T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid GROUP BY T3.dorm_name

Question: Find the number of amenities for each of the dorms that can accommodate more than 100 students.
SQL: SELECT count(*) ,  T1.dormid FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid WHERE T1.student_capacity  >  100 GROUP BY T1.dormid

Question: For each dorm, how many amenities does it have?
SQL: SELECT count(*) ,  T1.dormid FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid WHERE T1.student_capacity  >  100 GROUP BY T1.dormid

Question: Find the number of students who is older than 20 in each dorm.
SQL: SELECT count(*) ,  T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T1.age  >  20 GROUP BY T3.dorm_name

Question: How many students are older than 20 in each dorm?
SQL: SELECT count(*) ,  T3.dorm_name FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T1.age  >  20 GROUP BY T3.dorm_name

Question: Find the first name of students who are living in the Smith Hall.
SQL: SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T3.dorm_name  =  'Smith Hall'

Question: What are the first names of all students in Smith Hall?
SQL: SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T3.dorm_name  =  'Smith Hall'

Question: Find the average age of students who are living in the dorm with the largest capacity.
SQL: SELECT avg(T1.age) FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T3.student_capacity  =  (SELECT max(student_capacity) FROM dorm)

Question: What is the average age of students who are living in the dorm with the largest capacity?
SQL: SELECT avg(T1.age) FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid JOIN dorm AS T3 ON T3.dormid  =  T2.dormid WHERE T3.student_capacity  =  (SELECT max(student_capacity) FROM dorm)