lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the number of distinct gender for dorms.

### SQL
SELECT count(DISTINCT gender) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many different genders are there in the dorms?

### SQL
SELECT count(DISTINCT gender) FROM dorm
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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%'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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%'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the numbers of different majors and cities.

### SQL
SELECT count(DISTINCT major) ,  count(DISTINCT city_code) FROM student
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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'
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the name of the dorm with the largest capacity.

### SQL
SELECT dorm_name FROM dorm ORDER BY student_capacity DESC LIMIT 1
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
List in alphabetic order all different amenities.

### SQL
SELECT amenity_name FROM dorm_amenity ORDER BY amenity_name
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
What are the different dorm amenity names in alphabetical order?

### SQL
SELECT amenity_name FROM dorm_amenity ORDER BY amenity_name
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the number of dorms and total capacity for each gender.

### SQL
SELECT count(*) ,  sum(student_capacity) ,  gender FROM dorm GROUP BY gender
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the average and oldest age for students with different sex.

### SQL
SELECT avg(age) ,  max(age) ,  sex FROM student GROUP BY sex
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
Find the number of students in each major.

### SQL
SELECT count(*) ,  major FROM student GROUP BY major
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### Question
How many students are there in each major?

### SQL
SELECT count(*) ,  major FROM student GROUP BY major
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

### 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)
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Dorm (
       dormid		INTEGER,
       dorm_name	VARCHAR(20),
       student_capacity	INTEGER,
       gender			VARCHAR(1)
)

CREATE TABLE Dorm_amenity (
       amenid			INTEGER,
       amenity_name		VARCHAR(25)
)

CREATE TABLE Has_amenity (
       dormid			INTEGER,
       amenid				INTEGER,
	FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid),
	FOREIGN KEY (amenid) REFERENCES `Dorm_amenity`(amenid)
)

CREATE TABLE Lives_in (
       stuid 	      INTEGER,
       dormid		INTEGER,
       room_number	INTEGER,
	FOREIGN KEY (stuid) REFERENCES `Student`(StuID),
        FOREIGN KEY (dormid) REFERENCES `Dorm`(dormid)
)

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