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
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
### 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 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
### 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 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
### 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 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
### 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
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
### 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 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
### 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 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
### 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 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'
### 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 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'
### 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 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)
### 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 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)
### 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 total number of students living in the male dorm (with gender M).

### SQL
SELECT count(*) 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.gender  =  'M'
### 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 total number of students who are living in a male dorm?

### SQL
SELECT count(*) 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.gender  =  'M'
### 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 female students (with F sex) living in Smith Hall

### SQL
SELECT count(*) 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' AND T1.sex  =  'F'
### 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 female students live in Smith Hall?

### SQL
SELECT count(*) 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' AND T1.sex  =  'F'
### 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 amenities Smith Hall dorm have.

### SQL
SELECT T3.amenity_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 T1.dorm_name  =  'Smith Hall'
### 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 amenities that Smith Hall has?

### SQL
SELECT T3.amenity_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 T1.dorm_name  =  'Smith Hall'
### 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 amenities Smith Hall dorm have. ordered the results by amenity names.

### SQL
SELECT T3.amenity_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 T1.dorm_name  =  'Smith Hall' ORDER BY T3.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 amenities does Smith Hall have in alphabetical order?

### SQL
SELECT T3.amenity_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 T1.dorm_name  =  'Smith Hall' ORDER BY T3.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 name of amenity that is most common in all dorms.

### SQL
SELECT T1.amenity_name FROM dorm_amenity AS T1 JOIN has_amenity AS T2 ON T1.amenid  =  T2.amenid GROUP BY T2.amenid 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 most common amenity in the dorms?

### SQL
SELECT T1.amenity_name FROM dorm_amenity AS T1 JOIN has_amenity AS T2 ON T1.amenid  =  T2.amenid GROUP BY T2.amenid 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 name of students who are living in the dorm that has most number of amenities.

### SQL
SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T2.dormid FROM dorm AS T3 JOIN has_amenity AS T4 ON T3.dormid  =  T4.dormid JOIN dorm_amenity AS T5 ON T4.amenid  =  T5.amenid GROUP BY T3.dormid 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 are the first names of all students who live in the dorm with the most amenities?

### SQL
SELECT T1.fname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T2.dormid FROM dorm AS T3 JOIN has_amenity AS T4 ON T3.dormid  =  T4.dormid JOIN dorm_amenity AS T5 ON T4.amenid  =  T5.amenid GROUP BY T3.dormid 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 name and capacity of the dorm with least number of amenities.

### SQL
SELECT T1.dorm_name ,  T1.student_capacity FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid GROUP BY T2.dormid ORDER BY count(*) 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 name and capacity of the dorm with the fewest amount of amenities?

### SQL
SELECT T1.dorm_name ,  T1.student_capacity FROM dorm AS T1 JOIN has_amenity AS T2 ON T1.dormid  =  T2.dormid JOIN dorm_amenity AS T3 ON T2.amenid  =  T3.amenid GROUP BY T2.dormid ORDER BY count(*) 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 name of dorms that do not have amenity TV Lounge.

### SQL
SELECT dorm_name FROM dorm 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  =  'TV Lounge'
### 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 that does not have a TV Lounge?

### SQL
SELECT dorm_name FROM dorm 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  =  'TV Lounge'
### 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 living in the dorms that have amenity TV Lounge.

### SQL
SELECT T1.fname ,  T1.lname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T3.dormid FROM has_amenity AS T3 JOIN dorm_amenity AS T4 ON T3.amenid  =  T4.amenid WHERE T4.amenity_name  =  'TV Lounge')
### 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 living in a dorm with a TV Lounge?

### SQL
SELECT T1.fname ,  T1.lname FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid IN (SELECT T3.dormid FROM has_amenity AS T3 JOIN dorm_amenity AS T4 ON T3.amenid  =  T4.amenid WHERE T4.amenity_name  =  'TV Lounge')
### 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 name and age of students who are living in the dorms that do not have amenity TV Lounge.

### SQL
SELECT T1.fname ,  T1.age FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid NOT IN (SELECT T3.dormid FROM has_amenity AS T3 JOIN dorm_amenity AS T4 ON T3.amenid  =  T4.amenid WHERE T4.amenity_name  =  'TV Lounge')
### 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 name and age of every student who lives in a dorm with a TV Lounge?

### SQL
SELECT T1.fname ,  T1.age FROM student AS T1 JOIN lives_in AS T2 ON T1.stuid  =  T2.stuid WHERE T2.dormid NOT IN (SELECT T3.dormid FROM has_amenity AS T3 JOIN dorm_amenity AS T4 ON T3.amenid  =  T4.amenid WHERE T4.amenity_name  =  'TV Lounge')
### 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 amenities of the dorm where the student with last name Smith is living in.

### SQL
SELECT T3.amenity_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 JOIN lives_in AS T4 ON T4.dormid  =  T1.dormid JOIN student AS T5 ON T5.stuid  =  T4.stuid WHERE T5.lname  =  'Smith'
### 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 the dorm that a student who has the last name of Smith lives in?

### SQL
SELECT T3.amenity_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 JOIN lives_in AS T4 ON T4.dormid  =  T1.dormid JOIN student AS T5 ON T5.stuid  =  T4.stuid WHERE T5.lname  =  'Smith'
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
How many customers are there?

### SQL
SELECT count(*) FROM customers
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Count the number of customers.

### SQL
SELECT count(*) FROM customers
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Find the emails and phone numbers of all the customers, ordered by email address and phone number.

### SQL
SELECT email_address ,  phone_number FROM customers ORDER BY email_address ,  phone_number
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
What are the emails and phone numbers of all customers, sorted by email address and phone number?

### SQL
SELECT email_address ,  phone_number FROM customers ORDER BY email_address ,  phone_number
### Database Schema
CREATE TABLE `Staff` (
`staff_id` INTEGER PRIMARY KEY,
`gender` VARCHAR(1),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`customer_type_code` VARCHAR(20) NOT NULL,
`address_line_1` VARCHAR(80),
`address_line_2` VARCHAR(80),
`town_city` VARCHAR(80),
`state` VARCHAR(80),
`email_address` VARCHAR(255),
`phone_number` VARCHAR(80)
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`parent_product_id` INTEGER,
`product_category_code` VARCHAR(20) NOT NULL,
`date_product_first_available` DATETIME,
`date_product_discontinued` DATETIME,
`product_name` VARCHAR(80),
`product_description` VARCHAR(255),
`product_price` DECIMAL(19,4)
)

CREATE TABLE `Complaints` (
`complaint_id` INTEGER NOT NULL ,
`product_id` INTEGER NOT NULL,
`customer_id` INTEGER NOT NULL,
`complaint_outcome_code` VARCHAR(20) NOT NULL,
`complaint_status_code` VARCHAR(20) NOT NULL,
`complaint_type_code` VARCHAR(20) NOT NULL,
`date_complaint_raised` DATETIME,
`date_complaint_closed` DATETIME,
`staff_id` INTEGER NOT NULL ,
FOREIGN KEY (`staff_id` ) REFERENCES `Staff`(`staff_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
)

### Question
Which city has the least number of customers whose type code is "Good Credit Rating"?

### SQL
SELECT town_city FROM customers WHERE customer_type_code  =  "Good Credit Rating" GROUP BY town_city ORDER BY count(*) LIMIT 1