lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
List down the restaurant ID of restaurants located in Sunnyvale.

### Hint
Sunnyvale refers to city = 'sunnyvale'

### SQL
SELECT id_restaurant FROM location WHERE city = 'sunnyvale'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among the restaurants on street number below 1000, how many of them are in Railroad St.?

### Hint
street number below 1000 refers to street_num < 1000; Railroad St. refers to street_name = 'railroad'

### SQL
SELECT COUNT(city) FROM location WHERE street_name = 'railroad' AND street_num < 1000
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
What is the name of the 24 hour diner at San Francisco?

### Hint
name refers to label; 24 hour diner refers to food_type = '24 hour diner'; San Francisco refers to city = 'san francisco'

### SQL
SELECT label FROM generalinfo WHERE food_type = '24 hour diner' AND city = 'san francisco'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Give the review of the restaurant located in Ocean St., Santa Cruz.

### Hint
Ocean St. refers to street_name = 'ocean st'; Santa Cruz refers to city = 'santa cruz'

### SQL
SELECT T2.review FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.city = 'santa cruz' AND T1.street_name = 'ocean st'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Give the street number of a bar in Oakland with a 2.7 review.

### Hint
street number refers to street_num; bar refers to food_type = 'bar'; Oakland refers to city = 'oakland'; 2.7 review refers to review = 2.7

### SQL
SELECT T2.street_num FROM generalinfo AS T1 INNER JOIN location AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.review = 2.7 AND T2.city = 'oakland' AND T1.food_type = 'bar'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among the bakeries, what is total number of bakery located at University Avenue, Palo Alto?

### Hint
bakery refers to food_type = 'bakery'; University Avenue refers to street_name = 'university ave.'; Palo Alto refers to city = 'palo alto'

### SQL
SELECT COUNT(T1.id_restaurant) FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.food_type = 'bakery' AND T2.city = 'palo alto' AND T1.street_name = 'university ave.'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among the listed winery, what is the street number of the winery named "Tulocay Winery"?

### Hint
winery refers to food_type = 'winery'; street number refers to street_num; "Tulocay Winery" refers to label = 'Tulocay winery'

### SQL
SELECT T1.street_num FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.label = 'Tulocay winery' AND T2.food_type = 'winery'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
List the review and label of the restaurants in Mission Blvd., Hayward.

### Hint
Mission Blvd. refers to street_name = 'mission blvd'; Hayward refers to city = 'hayward'

### SQL
SELECT T2.review, T2.label FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.city = 'hayward' AND T1.street_name = 'mission blvd'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among all indian restaurants in Castro St., Mountainview, how many of them is about cookhouse in their label?

### Hint
indian restaurant refers to food_type = 'indian'; Castro St. refers to street_name = 'castro st'; Mountainview refers to city = 'mountainview'; have the word "Indian" in label refers to label = 'indian'

### SQL
SELECT COUNT(T1.id_restaurant) FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.street_name = 'castro st' AND T1.city = 'mountain view' AND T2.food_type = 'indian' AND T2.label LIKE '%cookhouse%'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
In restaurants with a review of 2, how many restaurants have a street number below 500?

### Hint
review of 2 refers to review = 2; street number below 500 refers to street_num < 500

### SQL
SELECT COUNT(T1.id_restaurant) FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.review = 2 AND T1.street_num < 500
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among all asian restaurants in N. Milpitas Blvd., Milpitas, how many of them have restaurant ID greater than 385?

### Hint
asian restaurant refers to food_type = 'asian'; N. Milpitas Blvd. refers to street_name = 'n milpitas blvd'; Milpitas refers to city = 'milpitas'; restaurant ID greater than 385 refers to id_restaurant > 385

### SQL
SELECT COUNT(T1.id_restaurant) AS num FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.city = 'milpitas' AND T2.food_type = 'asian' AND T1.street_name = 'n milpitas blvd' AND T1.id_restaurant > 385
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
What is the restaurant's name and ID located at Ocean Avenue, San Francisco?

### Hint
restaurant's name refers to label; Ocean Avenue refers to street_name = 'ocean avenue'; San Francisco refers to city = 'san francisco'

### SQL
SELECT T2.label, T1.id_restaurant FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.city = 'san francisco' AND T1.street_name = 'ocean avenue'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
What is the full address of the restaurant named "Sanuki Restaurant"?

### Hint
full address refers to city, street_num, street_name; restaurant named "Sanuki Restaurant" refers to label = 'sanuki restaurant'

### SQL
SELECT T2.city, T1.street_num, T1.street_name FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.label = 'sanuki restaurant'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
List the food type of the restaurant located in 22779 6th St., Hayward City.

### Hint
22779 refers to street_num = 22779; 6th St. refers to street_name = '6th St'

### SQL
SELECT T2.food_type FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.street_num = 22779 AND T1.street_name = '6th St' AND T2.city = 'hayward'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
How many American restaurants are located in Front, San Francisco?

### Hint
American restaurant refers to food_type = 'american'; Front refers to street_name = 'front'; San Francisco refers to city = 'san francisco'

### SQL
SELECT COUNT(T2.food_type = 'american') FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.city = 'san francisco' AND T1.street_name = 'front'
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
List the restaurant's ID that has a review greater than the 70% of average review of all American restaurants with street number greater than 2000.

### Hint
American restaurant refers to food_type = 'american'; street number greater than 2000 refers to street_num > 2000; review greater than the 70% of average review refers to review > multiply(avg(review), 0.7)

### SQL
SELECT T1.id_restaurant FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.food_type = 'american' AND T1.street_num > 2000 GROUP BY T1.id_restaurant ORDER BY AVG(T2.review) * 0.7 DESC
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
Among the restaurants located on the street number ranges from 1000 to 2000, what is the percentage of Afghani restaurants are there?

### Hint
street number ranges from 1000 to 2000 refers to 1000 < = street_num < = 2000; Afghani restaurant refers to food_type = 'afghani'; percentage = divide(count(id_restaurant where food_type = 'afghani'), count(id_restaurant)) * 100%

### SQL
SELECT CAST(SUM(IIF(T2.food_type = 'afghani', 1, 0)) AS REAL) * 100 / COUNT(T1.id_restaurant) FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE 1000 <= T1.street_num <= 2000
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
What is the name of the most popular restaurant serving Asian foods in San Francisco?

### Hint
the most popular refers to max(review); Asian food refers to food_type = 'asian'; San Francisco refers to city = 'san francisco'

### SQL
SELECT label FROM generalinfo WHERE food_type = 'asian' AND city = 'san francisco' AND review = ( SELECT MAX(review) FROM generalinfo WHERE food_type = 'asian' AND city = 'san francisco' )
### Database Schema
CREATE TABLE geographic
(
    city   TEXT not null
        primary key,
    county TEXT null,
    region TEXT null
)

CREATE TABLE generalinfo
(
    id_restaurant INTEGER         not null
        primary key,
    label         TEXT  null,
    food_type     TEXT  null,
    city          TEXT  null,
    review        REAL null,
    foreign key (city) references geographic(city)
            on update cascade on delete cascade
)

CREATE TABLE location
(
    id_restaurant INTEGER        not null
        primary key,
    street_num    INTEGER          null,
    street_name   TEXT null,
    city          TEXT null,
    foreign key (city) references geographic (city)
            on update cascade on delete cascade,
    foreign key (id_restaurant) references generalinfo (id_restaurant)
            on update cascade on delete cascade
)

### Question
How many cities are there in Monterey?

### Hint
Monterey refers to region = 'monterey'

### SQL
SELECT COUNT(DISTINCT city) FROM geographic WHERE region = 'monterey'