lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the enrollment and primary conference for the university which was founded the earliest?

### SQL
SELECT enrollment ,  primary_conference FROM university ORDER BY founded LIMIT 1
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What is the total and minimum enrollment of all schools?

### SQL
SELECT sum(enrollment) ,  min(enrollment) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Return the total and minimum enrollments across all schools.

### SQL
SELECT sum(enrollment) ,  min(enrollment) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find the total student enrollment for different affiliation type schools.

### SQL
SELECT sum(enrollment) ,  affiliation FROM university GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the total enrollments of universities of each affiliation type?

### SQL
SELECT sum(enrollment) ,  affiliation FROM university GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
How many schools do not participate in the basketball match?

### SQL
SELECT count(*) FROM university WHERE school_id NOT IN (SELECT school_id FROM basketball_match)
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Count the number of universities that do not participate in the baketball match.

### SQL
SELECT count(*) FROM university WHERE school_id NOT IN (SELECT school_id FROM basketball_match)
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find the schools that were either founded after 1850 or public.

### SQL
SELECT school FROM university WHERE founded  >  1850 OR affiliation  =  'Public'
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the schools that were either founded before 1850 or are public?

### SQL
SELECT school FROM university WHERE founded  >  1850 OR affiliation  =  'Public'
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find how many different affiliation types there are.

### SQL
SELECT count(DISTINCT affiliation) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Count the number of different affiliation types.

### SQL
SELECT count(DISTINCT affiliation) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find how many school locations have the word 'NY'.

### SQL
SELECT count(*) FROM university WHERE LOCATION LIKE "%NY%"
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
How many universities have a location that contains NY?

### SQL
SELECT count(*) FROM university WHERE LOCATION LIKE "%NY%"
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find the team names of the universities whose enrollments are smaller than the average enrollment size.

### SQL
SELECT t2.team_name FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE enrollment  <  (SELECT avg(enrollment) FROM university)
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the names of teams from universities that have a below average enrollment?

### SQL
SELECT t2.team_name FROM university AS t1 JOIN basketball_match AS t2 ON t1.school_id  =  t2.school_id WHERE enrollment  <  (SELECT avg(enrollment) FROM university)
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find the number of universities that have over a 20000 enrollment size for each affiliation type.

### SQL
SELECT count(*) ,  affiliation FROM university WHERE enrollment  >  20000 GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the different affiliations, and how many schools with each have an enrollment size of above 20000?

### SQL
SELECT count(*) ,  affiliation FROM university WHERE enrollment  >  20000 GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Find the total number of students enrolled in the colleges that were founded after the year of 1850 for each affiliation type.

### SQL
SELECT sum(Enrollment) ,  affiliation FROM university WHERE founded  >  1850 GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the different affiliations, and what is the total enrollment of schools founded after 1850 for each enrollment type?

### SQL
SELECT sum(Enrollment) ,  affiliation FROM university WHERE founded  >  1850 GROUP BY affiliation
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What is the maximum enrollment across all schools?

### SQL
SELECT max(Enrollment) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
Return the maximum enrollment across all schools.

### SQL
SELECT max(Enrollment) FROM university
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
List all information regarding the basketball match.

### SQL
SELECT * FROM basketball_match
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What is all the information about the basketball match?

### SQL
SELECT * FROM basketball_match
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
List names of all teams in the basketball competition, ordered by all home scores in descending order.

### SQL
SELECT team_name FROM basketball_match ORDER BY All_Home DESC
### Database Schema
CREATE TABLE "basketball_match" (
"Team_ID" int,
"School_ID" int,
"Team_Name" text,
"ACC_Regular_Season" text,
"ACC_Percent" text,
"ACC_Home" text,
"ACC_Road" text,
"All_Games" text,
"All_Games_Percent" int,
"All_Home" text,
"All_Road" text,
"All_Neutral" text,
PRIMARY KEY ("Team_ID"),
FOREIGN KEY (`School_ID`) REFERENCES `university`(`School_ID`)
)

CREATE TABLE "university" (
"School_ID" int,
"School" text,
"Location" text,
"Founded" real,
"Affiliation" text,
"Enrollment" real,
"Nickname" text,
"Primary_conference" text,
PRIMARY KEY ("School_ID")
)

### Question
What are the names of all the teams in the basketball competition, sorted by all home scores in descending order?

### SQL
SELECT team_name FROM basketball_match ORDER BY All_Home DESC
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
the names of models that launched between 2002 and 2004.

### SQL
SELECT Model_name FROM chip_model WHERE Launch_year BETWEEN 2002 AND 2004;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Which model has the least amount of RAM? List the model name and the amount of RAM.

### SQL
SELECT Model_name ,  RAM_MiB FROM chip_model ORDER BY RAM_MiB ASC LIMIT 1;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
What are the chip model and screen mode of the phone with hardware model name "LG-P760"?

### SQL
SELECT chip_model ,  screen_mode FROM phone WHERE Hardware_Model_name = "LG-P760";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
How many phone hardware models are produced by the company named "Nokia Corporation"?

### SQL
SELECT count(*) FROM phone WHERE Company_name = "Nokia Corporation";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
What is maximum and minimum RAM size of phone produced by company named "Nokia Corporation"?

### SQL
SELECT max(T1.RAM_MiB) ,  min(T1.RAM_MiB) FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model WHERE T2.Company_name = "Nokia Corporation";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
What is the average ROM size of phones produced by the company named "Nokia Corporation"?

### SQL
SELECT avg(T1.ROM_MiB) FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model WHERE T2.Company_name = "Nokia Corporation";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the hardware model name and company name for all the phones that were launched in year 2002 or have RAM size greater than 32.

### SQL
SELECT T2.Hardware_Model_name ,  T2.Company_name FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model WHERE T1.Launch_year = 2002 OR T1.RAM_MiB  >  32;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find all phones that have word 'Full' in their accreditation types. List the Hardware Model name and Company name.

### SQL
SELECT Hardware_Model_name ,  Company_name FROM phone WHERE Accreditation_type LIKE 'Full';
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the Char cells, Pixels and Hardware colours for the screen of the phone whose hardware model name is "LG-P760".

### SQL
SELECT T1.Char_cells ,  T1.Pixels ,  T1.Hardware_colours FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T2.Hardware_Model_name = "LG-P760";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the hardware model name and company name for the phone whose screen mode type is "Graphics."

### SQL
SELECT T2.Hardware_Model_name ,  T2.Company_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.Type  =  "Graphics";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the name of the company that has the least number of phone models. List the company name and the number of phone model produced by that company.

### SQL
SELECT Company_name ,  count(*) FROM phone GROUP BY Company_name ORDER BY count(*) ASC LIMIT 1;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the name of the company that produced more than one phone model.

### SQL
SELECT Company_name FROM phone GROUP BY Company_name HAVING count(*)  >  1;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the maximum, minimum and average number of used kb in screen mode.

### SQL
SELECT max(used_kb) , min(used_kb) , avg(used_kb) FROM screen_mode;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the name of the phone model launched in year 2002 and with the highest RAM size.

### SQL
SELECT T2.Hardware_Model_name FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model WHERE T1.Launch_year = 2002 ORDER BY T1.RAM_MiB DESC LIMIT 1;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
What are the wifi and screen mode type of the hardware model named "LG-P760"?

### SQL
SELECT T1.WiFi , T3.Type FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model JOIN screen_mode AS T3 ON T2.screen_mode = T3.Graphics_mode WHERE T2.Hardware_Model_name = "LG-P760";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the hardware model name for the phones that have screen mode type "Text" or RAM size greater than 32.

### SQL
SELECT T2.Hardware_Model_name FROM chip_model AS T1 JOIN phone AS T2 ON T1.Model_name  =  T2.chip_model JOIN screen_mode AS T3 ON T2.screen_mode = T3.Graphics_mode WHERE T3.Type = "Text" OR T1.RAM_MiB  >  32;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the hardware model name for the phones that were produced by "Nokia Corporation" or whose screen mode type is "Graphics."

### SQL
SELECT DISTINCT T2.Hardware_Model_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.Type  =  "Graphics" OR t2.Company_name  =  "Nokia Corporation"
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the hardware model name for the phons that were produced by "Nokia Corporation" but whose screen mode type is not Text.

### SQL
SELECT DISTINCT T2.Hardware_Model_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE t2.Company_name  =  "Nokia Corporation" AND T1.Type != "Text";
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List the phone hardware model and company name for the phones whose screen usage in kb is between 10 and 15.

### SQL
SELECT DISTINCT T2.Hardware_Model_name , T2.Company_name FROM screen_mode AS T1 JOIN phone AS T2 ON T1.Graphics_mode = T2.screen_mode WHERE T1.used_kb BETWEEN 10 AND 15;
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the number of phones for each accreditation type.

### SQL
SELECT Accreditation_type ,  count(*) FROM phone GROUP BY Accreditation_type
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
How many phones belongs to each accreditation type?

### SQL
SELECT Accreditation_type ,  count(*) FROM phone GROUP BY Accreditation_type
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the accreditation level that more than 3 phones use.

### SQL
SELECT Accreditation_level FROM phone GROUP BY Accreditation_level HAVING count(*)  >  3
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the details for all chip models.

### SQL
SELECT * FROM chip_model
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
How many models do not have the wifi function?

### SQL
SELECT count(*) FROM chip_model WHERE wifi  =  'No'
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Count the number of chip model that do not have wifi.

### SQL
SELECT count(*) FROM chip_model WHERE wifi  =  'No'
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
List all the model names sorted by their launch year.

### SQL
SELECT model_name FROM chip_model ORDER BY launch_year
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the average ram mib size of the chip models that are never used by any phone.

### SQL
SELECT avg(RAM_MiB) FROM chip_model WHERE model_name NOT IN (SELECT chip_model FROM phone)
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the names of the chip models that are not used by any phone with full accreditation type.

### SQL
SELECT model_name FROM chip_model EXCEPT SELECT chip_model FROM phone WHERE Accreditation_type  =  'Full'
### Database Schema
CREATE TABLE "chip_model" (
"Model_name" text,
"Launch_year" real,
"RAM_MiB" real,
"ROM_MiB" real,
"Slots" text,
"WiFi" text,
"Bluetooth" text,
PRIMARY KEY ("Model_name")
)

CREATE TABLE "screen_mode" (
"Graphics_mode" real,
"Char_cells" text,
"Pixels" text,
"Hardware_colours" real,
"used_kb" real,
"map" text,
"Type" text,
PRIMARY KEY ("Graphics_mode")
)

CREATE TABLE "phone" (
"Company_name" text,
"Hardware_Model_name" text,
"Accreditation_type" text,
"Accreditation_level" text,
"Date" text,
"chip_model" text,
"screen_mode" text,
PRIMARY KEY("Hardware_Model_name"),
FOREIGN KEY (`screen_mode`) REFERENCES `screen_mode`(`Graphics_mode`),
FOREIGN KEY (`chip_model`) REFERENCES `chip_model`(`Model_name`)
)

### Question
Find the pixels of the screen modes that are used by both phones with full accreditation types and phones with Provisional accreditation types.

### SQL
SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode  =  t2.screen_mode WHERE t2.Accreditation_type  =  'Provisional' INTERSECT SELECT t1.pixels FROM screen_mode AS t1 JOIN phone AS t2 ON t1.Graphics_mode  =  t2.screen_mode WHERE t2.Accreditation_type  =  'Full'
### Database Schema
CREATE TABLE "country" (
    "Country_id" int,
    "Country_name" text,
    "Capital" text,
    "Official_native_language" text,
    PRIMARY KEY ("Country_id")
)

CREATE TABLE `team` (
      `Team_id` int,
      `Name` text,
      PRIMARY KEY (`Team_id`)
)

CREATE TABLE "match_season" (
    "Season" real,
    "Player" text,
    "Position" text,
    "Country" int,
    "Team" int,
    "Draft_Pick_Number" int,
    "Draft_Class" text,
    "College" text,
    PRIMARY KEY ("Season"),
    FOREIGN KEY (`Country`) REFERENCES `country`(`Country_id`),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
)

CREATE TABLE "player" (
    "Player_ID" int,
    "Player" text,
    "Years_Played" text,
    "Total_WL" text,
    "Singles_WL" text,
    "Doubles_WL" text,
    "Team" int,
    PRIMARY KEY ("Player_ID"),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
)

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

### SQL
SELECT count(*) FROM country
### Database Schema
CREATE TABLE "country" (
    "Country_id" int,
    "Country_name" text,
    "Capital" text,
    "Official_native_language" text,
    PRIMARY KEY ("Country_id")
)

CREATE TABLE `team` (
      `Team_id` int,
      `Name` text,
      PRIMARY KEY (`Team_id`)
)

CREATE TABLE "match_season" (
    "Season" real,
    "Player" text,
    "Position" text,
    "Country" int,
    "Team" int,
    "Draft_Pick_Number" int,
    "Draft_Class" text,
    "College" text,
    PRIMARY KEY ("Season"),
    FOREIGN KEY (`Country`) REFERENCES `country`(`Country_id`),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
)

CREATE TABLE "player" (
    "Player_ID" int,
    "Player" text,
    "Years_Played" text,
    "Total_WL" text,
    "Singles_WL" text,
    "Doubles_WL" text,
    "Team" int,
    PRIMARY KEY ("Player_ID"),
    FOREIGN KEY (`Team`) REFERENCES `team`(`Team_id`)
)

### Question
Count the number of countries.

### SQL
SELECT count(*) FROM country