lianghsun/spider-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Sort the names of all counties in ascending order of population.

### SQL
SELECT County_name FROM county ORDER BY Population ASC
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Return the names of all counties sorted by county name in descending alphabetical order.

### SQL
SELECT County_name FROM county ORDER BY County_name DESC
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Sort the names of all counties in descending alphabetical order.

### SQL
SELECT County_name FROM county ORDER BY County_name DESC
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the name of the county with the biggest population.

### SQL
SELECT County_name FROM county ORDER BY Population DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which county has the largest population? Give me the name of the county.

### SQL
SELECT County_name FROM county ORDER BY Population DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the 3 counties with the smallest population.

### SQL
SELECT County_name FROM county ORDER BY Population ASC LIMIT 3
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
What are the 3 counties that have the smallest population? Give me the county names.

### SQL
SELECT County_name FROM county ORDER BY Population ASC LIMIT 3
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the names of counties that have at least two delegates.

### SQL
SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which counties have two or more delegates? Give me the county names.

### SQL
SELECT T1.County_name FROM county AS T1 JOIN election AS T2 ON T1.County_id  =  T2.District GROUP BY T1.County_id HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the name of the party that has at least two records.

### SQL
SELECT Party FROM party GROUP BY Party HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which party has two or more records?

### SQL
SELECT Party FROM party GROUP BY Party HAVING COUNT(*)  >=  2
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the name of the party that has the most delegates.

### SQL
SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which party has the largest number of delegates?

### SQL
SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID GROUP BY T1.Party ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the people that have been governor the most times.

### SQL
SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which people severed as governor most frequently?

### SQL
SELECT Governor FROM party GROUP BY Governor ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Show the people that have been comptroller the most times and the corresponding number of times.

### SQL
SELECT Comptroller ,  COUNT(*) FROM party GROUP BY Comptroller ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which people severed as comptroller most frequently? Give me the name of the person and the frequency count.

### SQL
SELECT Comptroller ,  COUNT(*) FROM party GROUP BY Comptroller ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
What are the names of parties that do not have delegates in election?

### SQL
SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which parties did not have any delegates in elections?

### SQL
SELECT Party FROM party WHERE Party_ID NOT IN (SELECT Party FROM election)
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
What are the names of parties that have both delegates on "Appropriations" committee and

### SQL
SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Appropriations" INTERSECT SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Economic Matters"
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which parties have delegates in both the "Appropriations" committee and the "Economic Matters" committee?

### SQL
SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Appropriations" INTERSECT SELECT T2.Party FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T1.Committee  =  "Economic Matters"
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Which committees have delegates from both democratic party and liberal party?

### SQL
SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic" INTERSECT SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Liberal"
### Database Schema
CREATE TABLE "county" (
"County_Id" int,
"County_name" text,
"Population" real,
"Zip_code" text,
PRIMARY KEY ("County_Id")
)

CREATE TABLE "party" (
"Party_ID" int,
"Year" real,
"Party" text,
"Governor" text,
"Lieutenant_Governor" text,
"Comptroller" text,
"Attorney_General" text,
"US_Senate" text,
PRIMARY KEY ("Party_ID")
)

CREATE TABLE "election" (
"Election_ID" int,
"Counties_Represented" text,
"District" int,
"Delegate" text,
"Party" int,
"First_Elected" real,
"Committee" text,
PRIMARY KEY ("Election_ID"),
FOREIGN KEY (`Party`) REFERENCES `party`(`Party_ID`),
FOREIGN KEY (`District`) REFERENCES `county`(`County_Id`)
)

### Question
Find the committees that have delegates both from from the democratic party and the liberal party.

### SQL
SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Democratic" INTERSECT SELECT T1.Committee FROM election AS T1 JOIN party AS T2 ON T1.Party  =  T2.Party_ID WHERE T2.Party  =  "Liberal"
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
How many journalists are there?

### SQL
SELECT count(*) FROM journalist
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
List the names of journalists in ascending order of years working.

### SQL
SELECT Name FROM journalist ORDER BY Years_working ASC
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
What are the nationalities and ages of journalists?

### SQL
SELECT Nationality ,  Age FROM journalist
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the names of journalists from "England" or "Wales".

### SQL
SELECT Name FROM journalist WHERE Nationality  =  "England" OR Nationality  =  "Wales"
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
What is the average number of years spent working as a journalist?

### SQL
SELECT avg(Years_working) FROM journalist
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
What is the nationality of the journalist with the largest number of years working?

### SQL
SELECT Nationality FROM journalist ORDER BY Years_working DESC LIMIT 1
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the different nationalities and the number of journalists of each nationality.

### SQL
SELECT Nationality ,  COUNT(*) FROM journalist GROUP BY Nationality
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the most common nationality for journalists.

### SQL
SELECT Nationality FROM journalist GROUP BY Nationality ORDER BY COUNT(*) DESC LIMIT 1
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the nations that have both journalists with more than 10 years of working and journalists with less than 3 years of working.

### SQL
SELECT Nationality FROM journalist WHERE Years_working  >  10 INTERSECT SELECT Nationality FROM journalist WHERE Years_working  <  3
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the dates, places, and names of events in descending order of the attendance.

### SQL
SELECT Date ,  Name ,  venue FROM event ORDER BY Event_Attendance DESC
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the names of journalists and the dates of the events they reported.

### SQL
SELECT T3.Name ,  T2.Date FROM news_report AS T1 JOIN event AS T2 ON T1.Event_ID  =  T2.Event_ID JOIN journalist AS T3 ON T1.journalist_ID  =  T3.journalist_ID
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the names of journalists and the names of the events they reported in ascending order

### SQL
SELECT T3.Name ,  T2.Name FROM news_report AS T1 JOIN event AS T2 ON T1.Event_ID  =  T2.Event_ID JOIN journalist AS T3 ON T1.journalist_ID  =  T3.journalist_ID ORDER BY T2.Event_Attendance ASC
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the names of journalists and the number of events they reported.

### SQL
SELECT T3.Name ,  COUNT(*) FROM news_report AS T1 JOIN event AS T2 ON T1.Event_ID  =  T2.Event_ID JOIN journalist AS T3 ON T1.journalist_ID  =  T3.journalist_ID GROUP BY T3.Name
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Show the names of journalists that have reported more than one event.

### SQL
SELECT T3.Name FROM news_report AS T1 JOIN event AS T2 ON T1.Event_ID  =  T2.Event_ID JOIN journalist AS T3 ON T1.journalist_ID  =  T3.journalist_ID GROUP BY T3.Name HAVING COUNT(*)  >  1
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
List the names of journalists who have not reported any event.

### SQL
SELECT Name FROM journalist WHERE journalist_ID NOT IN (SELECT journalist_ID FROM news_report)
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
what are the average and maximum attendances of all events?

### SQL
SELECT avg(Event_Attendance) ,  max(Event_Attendance) FROM event
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
Find the average age and experience working length of journalists working on different role type.

### SQL
SELECT avg(t1.age) ,  avg(Years_working) ,  t2.work_type FROM journalist AS t1 JOIN news_report AS t2 ON t1.journalist_id  =  t2.journalist_id GROUP BY t2.work_type
### Database Schema
CREATE TABLE "event" (
    "Event_ID" int,
    "Date" text,
    "Venue" text,
    "Name" text,
    "Event_Attendance" int,
    PRIMARY KEY ("Event_ID")
)

CREATE TABLE "journalist" (
    "journalist_ID" int,
    "Name" text,
    "Nationality" text,
    "Age" text,
    "Years_working" int,
    PRIMARY KEY ("journalist_ID")
)

CREATE TABLE "news_report" (
    "journalist_ID" int,
    "Event_ID" int,
    "Work_Type" text,
    PRIMARY KEY ("journalist_ID","Event_ID"),
    FOREIGN KEY ("journalist_ID") REFERENCES `journalist`("journalist_ID"),
    FOREIGN KEY ("Event_ID") REFERENCES `event`("Event_ID")
)

### Question
List the event venues and names that have the top 2 most number of people attended.

### SQL
SELECT venue ,  name FROM event ORDER BY Event_Attendance DESC LIMIT 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Show me all the restaurants.

### SQL
SELECT ResName FROM Restaurant;
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
What is the address of the restaurant Subway?

### SQL
SELECT Address FROM Restaurant WHERE ResName = "Subway";
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
What is the rating of the restaurant Subway?

### SQL
SELECT Rating FROM Restaurant WHERE ResName = "Subway";
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
List all restaurant types.

### SQL
SELECT ResTypeName FROM Restaurant_Type;
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
What is the description of the restaurant type Sandwich?

### SQL
SELECT ResTypeDescription FROM Restaurant_Type WHERE ResTypeName = "Sandwich";
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which restaurants have highest rating? List the restaurant name and its rating.

### SQL
SELECT ResName , Rating FROM Restaurant ORDER BY Rating 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
What is the age of student Linda Smith?

### SQL
SELECT Age FROM Student WHERE Fname = "Linda" AND Lname = "Smith";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
What is the gender of the student Linda Smith?

### SQL
SELECT Sex FROM Student WHERE Fname = "Linda" AND Lname = "Smith";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
List all students' first names and last names who majored in 600.

### SQL
SELECT Fname , Lname FROM Student WHERE Major  =  600;
### 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 Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Which city does student Linda Smith live in?

### SQL
SELECT city_code FROM Student WHERE Fname = "Linda" AND Lname = "Smith";
### Database Schema
CREATE TABLE Student (
        StuID        INTEGER PRIMARY KEY,
        LName        VARCHAR(12),
        Fname        VARCHAR(12),
        Age      INTEGER,
        Sex      VARCHAR(1),
        Major        INTEGER,
        Advisor      INTEGER,
        city_code    VARCHAR(3)
 )

CREATE TABLE Restaurant (
       ResID       INTEGER PRIMARY KEY,
       ResName     VARCHAR(100),
       Address     VARCHAR(100),
       Rating INTEGER
)

CREATE TABLE Type_Of_Restaurant (
       ResID       INTEGER,
       ResTypeID   INTEGER,
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID),
       FOREIGN KEY(ResTypeID) REFERENCES Restaurant_Type(ResTypeID)
)

CREATE TABLE Restaurant_Type (
       ResTypeID            INTEGER PRIMARY KEY,
       ResTypeName          VARCHAR(40),
       ResTypeDescription   VARCHAR(100)
)

CREATE TABLE Visits_Restaurant (
       StuID      INTEGER,
       ResID      INTEGER,
       Time       TIMESTAMP,
       Spent      FLOAT,
       FOREIGN KEY(StuID) REFERENCES Student(StuID),
       FOREIGN KEY(ResID) REFERENCES Restaurant(ResID)
)

### Question
Advisor 1121 has how many students?

### SQL
SELECT count(*) FROM Student WHERE Advisor =  1121;