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: Find the name of dorms only for female (F gender).
SQL: SELECT dorm_name FROM dorm WHERE gender  =  'F'

Question: What are the names of the all-female dorms?
SQL: SELECT dorm_name FROM dorm WHERE gender  =  'F'

Question: Find the name of dorms that can accommodate more than 300 students.
SQL: SELECT dorm_name FROM dorm WHERE student_capacity  >  300

Question: What are the names of all the dorms that can accomdate more than 300 students?
SQL: SELECT dorm_name FROM dorm WHERE student_capacity  >  300

Question: How many female students (sex is F) whose age is below 25?
SQL: SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25

Question: How many girl students who are younger than 25?
SQL: SELECT count(*) FROM student WHERE sex  =  'F' AND age  <  25

Question: Find the first name of students who is older than 20.
SQL: SELECT fname FROM student WHERE age  >  20

Question: What are the first names of all students who are older than 20?
SQL: SELECT fname FROM student WHERE age  >  20

Question: Find the first name of students living in city PHL whose age is between 20 and 25.
SQL: SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25

Question: What is the first name of the students who are in age 20 to 25 and living in PHL city?
SQL: SELECT fname FROM student WHERE city_code  =  'PHL' AND age BETWEEN 20 AND 25

Question: How many dorms are there?
SQL: SELECT count(*) FROM dorm

Question: How many dorms are in the database?
SQL: SELECT count(*) FROM dorm

Question: Find the number of distinct amenities.
SQL: SELECT count(*) FROM dorm_amenity

Question: How many diffrent dorm amenities are there?
SQL: SELECT count(*) FROM dorm_amenity

Question: Find the total capacity of all dorms.
SQL: SELECT sum(student_capacity) FROM dorm

Question: What is the total student capacity of all dorms?
SQL: SELECT sum(student_capacity) FROM dorm

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

Question: How many students exist?
SQL: SELECT count(*) FROM student

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

Question: What is the average age for each city and what are those cities?
SQL: SELECT avg(age) ,  city_code FROM student GROUP BY city_code

Question: Find the average and total capacity of dorms for the students with gender X.
SQL: SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'

Question: What is the average and total capacity for all dorms who are of gender X?
SQL: SELECT avg(student_capacity) ,  sum(student_capacity) FROM dorm WHERE gender  =  'X'

Question: Find the number of dorms that have some amenity.
SQL: SELECT count(DISTINCT dormid) FROM has_amenity

Question: How many dorms have amenities?
SQL: SELECT count(DISTINCT dormid) FROM has_amenity

Question: Find the name of dorms that do not have any amenity
SQL: SELECT dorm_name FROM dorm WHERE dormid NOT IN (SELECT dormid FROM has_amenity)

Question: What are the names of all the dorms that don't have any amenities?
SQL: SELECT dorm_name FROM dorm WHERE dormid NOT IN (SELECT dormid FROM has_amenity)

Question: Find the number of distinct gender for dorms.
SQL: SELECT count(DISTINCT gender) FROM dorm

Question: How many different genders are there in the dorms?
SQL: SELECT count(DISTINCT gender) FROM dorm

Question: Find the capacity and gender type of the dorm whose name has substring ‘Donor’.
SQL: SELECT student_capacity ,  gender FROM dorm WHERE dorm_name LIKE '%Donor%'

Question: What is the student capacity and type of gender for the dorm whose name as the phrase Donor in it?
SQL: SELECT student_capacity ,  gender FROM dorm WHERE dorm_name LIKE '%Donor%'

Question: Find the name and gender type of the dorms whose capacity is greater than 300 or less than 100.
SQL: SELECT dorm_name ,  gender FROM dorm WHERE student_capacity  >  300 OR student_capacity  <  100

Question: What are the names and types of the dorms that have a capacity greater than 300 or less than 100?
SQL: SELECT dorm_name ,  gender FROM dorm WHERE student_capacity  >  300 OR student_capacity  <  100

Question: Find the numbers of different majors and cities.
SQL: SELECT count(DISTINCT major) ,  count(DISTINCT city_code) FROM student

Question: How many different majors are there and how many different city codes are there for each student?
SQL: SELECT count(DISTINCT major) ,  count(DISTINCT city_code) FROM student

Question: Find the name of dorms which have both TV Lounge and Study Room as amenities.
SQL: SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'TV Lounge' INTERSECT SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'Study Room'

Question: What is the name of the dorm with both a TV Lounge and Study Room listed as amenities?
SQL: SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'TV Lounge' INTERSECT SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'Study Room'

Question: Find the name of dorms which have TV Lounge but no Study Room as amenity.
SQL: SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'TV Lounge' EXCEPT SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'Study Room'

Question: What is the name of each dorm that has a TV Lounge but no study rooms?
SQL: SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'TV Lounge' EXCEPT SELECT T1.dorm_name FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid WHERE T3.amenity_name  =  'Study Room'

Question: Find the last name of students who is either female (sex is F) and living in the city of code BAL or male (sex is M) and in age of below 20.
SQL: SELECT lname FROM student WHERE sex  =  'F' AND city_code  =  'BAL' UNION SELECT lname FROM student WHERE sex  =  'M' AND age  <  20

Question: What is the last name of every student who is either female or living in a city with the code BAL or male and under 20?
SQL: SELECT lname FROM student WHERE sex  =  'F' AND city_code  =  'BAL' UNION SELECT lname FROM student WHERE sex  =  'M' AND age  <  20

Question: Find the name of the dorm with the largest capacity.
SQL: SELECT dorm_name FROM dorm ORDER BY student_capacity DESC LIMIT 1

Question: What are the names of the dorm with the largest capacity?
SQL: SELECT dorm_name FROM dorm ORDER BY student_capacity DESC LIMIT 1