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 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 first names of all the students who have vice president votes and whose city code is not PIT.

### SQL
SELECT DISTINCT T1.Fname FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  T2.VICE_PRESIDENT_Vote EXCEPT SELECT DISTINCT Fname FROM STUDENT WHERE city_code  =  "PIT"
### 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 distinct first names of the students who have vice president votes and reside in a city whose city code is not PIT?

### SQL
SELECT DISTINCT T1.Fname FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  T2.VICE_PRESIDENT_Vote EXCEPT SELECT DISTINCT Fname FROM STUDENT WHERE city_code  =  "PIT"
### 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 last names of all the students who have president votes and whose advisor is not 2192.

### SQL
SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"
### 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 distinct last names of the students who have president votes but do not have 2192 as the advisor?

### SQL
SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote EXCEPT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "2192"
### 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 last names of all the students who have president votes and whose advisor is 8741.

### SQL
SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"
### 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 distinct last names of the students who have president votes and have 8741 as the advisor?

### SQL
SELECT DISTINCT T1.LName FROM STUDENT AS T1 JOIN VOTING_RECORD AS T2 ON T1.StuID  =  PRESIDENT_Vote INTERSECT SELECT DISTINCT LName FROM STUDENT WHERE Advisor  =  "8741"
### 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
For each advisor, report the total number of students advised by him or her.

### SQL
SELECT Advisor ,  count(*) FROM STUDENT GROUP BY Advisor
### 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 does each advisor have?

### SQL
SELECT Advisor ,  count(*) FROM STUDENT GROUP BY Advisor
### 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
Report all advisors that advise more than 2 students.

### SQL
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2
### 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
Which advisors have more than two students?

### SQL
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING COUNT(*)  >  2
### 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
Report all majors that have less than 3 students.

### SQL
SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3
### 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 only less than three students are studying?

### SQL
SELECT Major FROM STUDENT GROUP BY Major HAVING COUNT(*)  <  3
### 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
For each election cycle, report the number of voting records.

### SQL
SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle
### 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
Count the number of voting records for each election cycle.

### SQL
SELECT Election_Cycle ,  count(*) FROM VOTING_RECORD GROUP BY Election_Cycle
### 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
Which major has the most students?

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

CREATE TABLE 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 major that is studied by the largest number of students.

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

CREATE TABLE 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 most common major among female (sex is F) students?

### SQL
SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE 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 major that is studied by the most female students.

### SQL
SELECT Major FROM STUDENT WHERE Sex  =  "F" GROUP BY major ORDER BY count(*) DESC LIMIT 1
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE 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 city_code of the city that the most students live in?

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

CREATE TABLE 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
Return the code of the city that has the most students.

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

CREATE TABLE 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
Report the distinct advisors who have more than 2 students.

### SQL
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2
### 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
Which advisors are advising more than 2 students?

### SQL
SELECT Advisor FROM STUDENT GROUP BY Advisor HAVING count(*)  >  2
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many products are there?

### SQL
SELECT count(*) FROM products
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Count the number of products.

### SQL
SELECT count(*) FROM products
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many colors are there?

### SQL
SELECT count(*) FROM ref_colors
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Count the number of colors.

### SQL
SELECT count(*) FROM ref_colors
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
How many characteristics are there?

### SQL
SELECT count(*) FROM CHARACTERISTICS
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Count the number of characteristics.

### SQL
SELECT count(*) FROM CHARACTERISTICS
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the names and buying prices of all the products?

### SQL
SELECT product_name ,  typical_buying_price FROM products
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Return the names and typical buying prices for all products.

### SQL
SELECT product_name ,  typical_buying_price FROM products
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
List the description of all the colors.

### SQL
SELECT color_description FROM ref_colors
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the descriptions for each color?

### SQL
SELECT color_description FROM ref_colors
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
Find the names of all the product characteristics.

### SQL
SELECT DISTINCT characteristic_name FROM CHARACTERISTICS
### Database Schema
CREATE TABLE `Ref_Characteristic_Types` (
`characteristic_type_code` VARCHAR(15) PRIMARY KEY,
`characteristic_type_description` VARCHAR(80)
)

CREATE TABLE `Ref_Colors` (
`color_code` VARCHAR(15) PRIMARY KEY,
`color_description` VARCHAR(80)
)

CREATE TABLE `Ref_Product_Categories` (
`product_category_code` VARCHAR(15) PRIMARY KEY,
`product_category_description` VARCHAR(80),
`unit_of_measure` VARCHAR(20)
)

CREATE TABLE `Characteristics` (
`characteristic_id` INTEGER PRIMARY KEY,
`characteristic_type_code` VARCHAR(15) NOT NULL,
`characteristic_data_type` VARCHAR(10),
`characteristic_name` VARCHAR(80),
`other_characteristic_details` VARCHAR(255),
FOREIGN KEY (`characteristic_type_code` ) REFERENCES `Ref_Characteristic_Types`(`characteristic_type_code` )
)

CREATE TABLE `Products` (
`product_id` INTEGER PRIMARY KEY,
`color_code` VARCHAR(15) NOT NULL,
`product_category_code` VARCHAR(15) NOT NULL,
`product_name` VARCHAR(80),
`typical_buying_price` VARCHAR(20),
`typical_selling_price` VARCHAR(20),
`product_description` VARCHAR(255),
`other_product_details` VARCHAR(255),
FOREIGN KEY (`product_category_code` ) REFERENCES `Ref_Product_Categories`(`product_category_code` ),FOREIGN KEY (`color_code` ) REFERENCES `Ref_Colors`(`color_code` )
)

CREATE TABLE `Product_Characteristics` (
`product_id` INTEGER NOT NULL,
`characteristic_id` INTEGER NOT NULL,
`product_characteristic_value` VARCHAR(50),
FOREIGN KEY (`characteristic_id` ) REFERENCES `Characteristics`(`characteristic_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products`(`product_id` )
)

### Question
What are the different names of the product characteristics?

### SQL
SELECT DISTINCT characteristic_name FROM CHARACTERISTICS