lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
For each city, how many branches opened before 2010?

### SQL
SELECT city ,  count(*) FROM branch WHERE open_year  <  2010 GROUP BY city
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
How many different levels do members have?

### SQL
SELECT count(DISTINCT LEVEL) FROM member
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the different membership levels?

### SQL
SELECT count(DISTINCT LEVEL) FROM member
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show card number, name, and hometown for all members in a descending order of level.

### SQL
SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the card numbers, names, and hometowns of every member ordered by descending level?

### SQL
SELECT card_number ,  name ,  hometown FROM member ORDER BY LEVEL DESC
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show the membership level with most number of members.

### SQL
SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the membership level with the most people?

### SQL
SELECT LEVEL FROM member GROUP BY LEVEL ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show all member names and registered branch names sorted by register year.

### SQL
SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names of the members and branches at which they are registered sorted by year of registration?

### SQL
SELECT T3.name ,  T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id ORDER BY T1.register_year
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show all branch names with the number of members in each branch registered after 2015.

### SQL
SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
For each branch id, what are the names of the branches that were registered after 2015?

### SQL
SELECT T2.name ,  count(*) FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  >  2015 GROUP BY T2.branch_id
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show member names without any registered branch.

### SQL
SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names of the members that have never registered at any branch?

### SQL
SELECT name FROM member WHERE member_id NOT IN (SELECT member_id FROM membership_register_branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
List the branch name and city without any registered members.

### SQL
SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names and cities of the branches that do not have any registered members?

### SQL
SELECT name ,  city FROM branch WHERE branch_id NOT IN (SELECT branch_id FROM membership_register_branch)
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the name and open year for the branch with most number of memberships registered in 2016?

### SQL
SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the name and opening year for the branch that registered the most members in 2016?

### SQL
SELECT T2.name ,  T2.open_year FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T1.register_year  =  2016 GROUP BY T2.branch_id ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show the member name and hometown who registered a branch in 2016.

### SQL
SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the member names and hometowns of those who registered at a branch in 2016?

### SQL
SELECT T2.name ,  T2.hometown FROM membership_register_branch AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T1.register_year  =  2016
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show all city with a branch opened in 2001 and a branch with more than 100 membership.

### SQL
SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the cities that have a branch that opened in 2001 and a branch with more than 100 members?

### SQL
SELECT city FROM branch WHERE open_year  =  2001 AND membership_amount  >  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Show all cities without a branch having more than 100 memberships.

### SQL
SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the cities that do not have any branches with more than 100 members?

### SQL
SELECT city FROM branch EXCEPT SELECT city FROM branch WHERE membership_amount  >  100
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the sum of total pounds of purchase in year 2018 for all branches in London?

### SQL
SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
How many total pounds were purchased in the year 2018 at all London branches?

### SQL
SELECT sum(total_pounds) FROM purchase AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id WHERE T2.city  =  'London' AND T1.year  =  2018
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What is the total number of purchases for members with level 6?

### SQL
SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the total purchases for members rated at level 6?

### SQL
SELECT count(*) FROM purchase AS T1 JOIN member AS T2 ON T1.member_id  =  T2.member_id WHERE T2.level  =  6
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
Find the name of branches where have some members whose hometown is in Louisville, Kentucky and some in Hiram, Georgia.

### SQL
SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the names of the branches that have some members with a hometown in Louisville, Kentucky and also those from Hiram, Goergia?

### SQL
SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Louisville ,  Kentucky' INTERSECT SELECT T2.name FROM membership_register_branch AS T1 JOIN branch AS T2 ON T1.branch_id  =  T2.branch_id JOIN member AS T3 ON T1.member_id  =  T3.member_id WHERE T3.Hometown  =  'Hiram ,  Georgia'
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
list the card number of all members whose hometown address includes word "Kentucky".

### SQL
SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"
### Database Schema
CREATE TABLE "member" (
"Member_ID" int,
"Card_Number" text,
"Name" text,
"Hometown" text,
"Level" int,
PRIMARY KEY ("Member_ID")
)

CREATE TABLE "branch" (
"Branch_ID" int,
"Name" text,
"Open_year" text,
"Address_road" text,
"City" text,
"membership_amount" text,
PRIMARY KEY ("Branch_ID")
)

CREATE TABLE "membership_register_branch" (
"Member_ID" int,
"Branch_ID" text,
"Register_Year" text,
PRIMARY KEY ("Member_ID"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

CREATE TABLE "purchase" (
"Member_ID" int,
"Branch_ID" text,
"Year" text,
"Total_pounds" real,
PRIMARY KEY ("Member_ID","Branch_ID","Year"),
FOREIGN KEY ("Member_ID") REFERENCES "member"("Member_ID"),
FOREIGN KEY ("Branch_ID") REFERENCES "branch"("Branch_ID")
)

### Question
What are the card numbers of members from Kentucky?

### SQL
SELECT card_number FROM member WHERE Hometown LIKE "%Kentucky%"
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the number of students in total.

### SQL
SELECT count(*) 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
How many students are there in total?

### SQL
SELECT count(*) 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the number of voting records in total.

### SQL
SELECT count(*) FROM VOTING_RECORD
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
How many voting records do we have?

### SQL
SELECT count(*) FROM VOTING_RECORD
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the distinct number of president votes.

### SQL
SELECT count(DISTINCT President_Vote) FROM VOTING_RECORD
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
How many distinct president votes are recorded?

### SQL
SELECT count(DISTINCT President_Vote) FROM VOTING_RECORD
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the maximum age of all the students.

### SQL
SELECT max(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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
What is the oldest age among the students?

### SQL
SELECT max(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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the last names of students with major 50.

### SQL
SELECT LName FROM STUDENT WHERE Major  =  50
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
What are the last names of students studying major 50?

### SQL
SELECT LName FROM STUDENT WHERE Major  =  50
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
Find the first names of students with age above 22.

### SQL
SELECT Fname FROM STUDENT WHERE Age  >  22
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
What are the first names of all the students aged above 22?

### SQL
SELECT Fname FROM STUDENT WHERE Age  >  22
### 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 Voting_record (
       StuID    	INTEGER,
       Registration_Date		VARCHAR(12),
       Election_Cycle		VARCHAR(12),
       President_Vote		INTEGER,
       Vice_President_Vote		INTEGER,
       Secretary_Vote		INTEGER,
       Treasurer_Vote		INTEGER,
       Class_President_Vote		INTEGER,
       Class_Senator_Vote		INTEGER,
       FOREIGN KEY(StuID) REFERENCES Student(StuID), 
       FOREIGN KEY(President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Vice_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Secretary_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Treasurer_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_President_Vote) REFERENCES Student(StuID),
       FOREIGN KEY(Class_Senator_Vote) REFERENCES Student(StuID)
)

### Question
What are the majors of male (sex is M) students?

### SQL
SELECT Major FROM STUDENT WHERE Sex  =  "M"