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
What type of restaurant is most common in Monterey county?

### Hint
type refers to food_type; most common refers to max(count(food_type))

### SQL
SELECT T2.food_type FROM geographic AS T1 INNER JOIN generalinfo AS T2 ON T1.city = T2.city WHERE T1.county = 'Monterey' GROUP BY T2.food_type ORDER BY COUNT(T2.food_type) DESC LIMIT 1
### 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
Which street in San Francisco has the most burger restaurants?

### Hint
street refers to street_name; San Francisco refers to city = 'san francisco'; burger restaurant refers to food_type = 'burgers'; the most burger restaurants refers to max(count(street_name where food_type = 'burgers' and city = 'san francisco'))

### SQL
SELECT T2.street_name FROM generalinfo AS T1 INNER JOIN location AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.city = 'san francisco' AND T1.food_type = 'burgers' GROUP BY T2.street_name ORDER BY COUNT(T2.id_restaurant) DESC LIMIT 1
### 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 region of 1149 El Camino Real?

### Hint
1149 El Camino Real refers to street_num = 1149 and street_name = 'el camino real'

### SQL
SELECT T2.region FROM location AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T1.street_num = 1149 AND T1.street_name = 'el camino real'
### 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 county of the Sankee restaurant?

### Hint
Sankee restaurant refers to label = 'sankee'

### SQL
SELECT T2.county FROM generalinfo AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T1.label = 'sankee'
### 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 streets with restaurants are there in the Northern California region?

### SQL
SELECT COUNT(T1.city) FROM geographic AS T1 INNER JOIN location AS T2 ON T1.city = T2.city WHERE T1.region = 'northern california'
### 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 all of the restaurants on Park St.

### Hint
restaurant refers to label; Park St refers to street_name = 'park st'

### SQL
SELECT T2.label FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T1.street_name = 'park 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
What percentage of restaurants are from the Bay Area?

### Hint
Bay Area refers to region = 'bay area'; percentage = divide(count(id_restaurant where region = 'bay area'), count(id_restaurant)) * 100%

### SQL
SELECT CAST(SUM(IIF(T1.region = 'bay area', 1, 0)) AS REAL) * 100 / COUNT(T2.id_restaurant) FROM geographic AS T1 INNER JOIN location AS T2 ON T1.city = T2.city
### 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 all the average reviews of Chinese restaurants for each county from highest to lowest.

### Hint
Chinese restaurant refers to food_type = 'chinese'; average review refers to divide(sum(review), count(review))

### SQL
SELECT AVG(T1.review) FROM generalinfo AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T1.food_type = 'chinese' GROUP BY T1.id_restaurant ORDER BY AVG(T1.review) 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
List street names in San Francisco city.

### SQL
SELECT street_name FROM location WHERE 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
List restaurant ids located in Danville city.

### SQL
SELECT id_restaurant FROM location WHERE city = 'Danville'
### 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 located in the Bay Area?

### Hint
the Bay Area refers to region = 'bay area'

### SQL
SELECT COUNT(city) FROM geographic WHERE region = 'bay area'
### 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 labels of the restaurant have an unknown country?

### Hint
unknown county refers to county = 'unknown'

### SQL
SELECT COUNT(T1.label) FROM generalinfo AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T2.county = 'unknown'
### 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
Please indicate the street names of restaurants with food type is American.

### SQL
SELECT T1.street_name FROM location AS T1 INNER JOIN generalinfo AS T2 ON T1.city = T2.city WHERE T2.food_type = 'American'
### 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
Please indicate which labels have the city located in Santa Cruz.

### Hint
Santa Cruz refers to county = 'Santa Cruz county'

### SQL
SELECT T1.label FROM generalinfo AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T2.county = 'Santa Cruz county'
### 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 at 430, Broadway.

### Hint
430 Broadway refers to street_num = 430 and street_name = 'Broadway'

### SQL
SELECT T1.review FROM generalinfo AS T1 INNER JOIN location AS T2 ON T1.id_restaurant = T2.id_restaurant WHERE T2.street_name = 'Broadway' AND T2.street_num = 430
### 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
Indicate the address of the restaurant with the most popular reviews.

### Hint
address refers to street_num, street_name; the most popular review refers to max(review)

### SQL
SELECT T2.street_num, T2.street_name FROM generalinfo AS T1 INNER JOIN location AS T2 ON T1.id_restaurant = T2.id_restaurant ORDER BY T1.review DESC LIMIT 1
### 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
Which country has the most restaurants with Italian food?

### Hint
Italian food refers to food_type = 'Italian'

### SQL
SELECT T2.county FROM generalinfo AS T1 INNER JOIN geographic AS T2 ON T1.city = T2.city WHERE T1.food_type = 'Italian' GROUP BY T2.county ORDER BY COUNT(T1.id_restaurant) DESC LIMIT 1
### 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
Find the percentage of restaurant in Napa Valley.

### Hint
Napa Valley refers to region = 'Napa Valley'; percentage = divide(count(id_restaurant where region = 'Napa Valley'), count(id_restaurant)) * 100%

### SQL
SELECT CAST(SUM(IIF(region = 'Napa Valley', 1, 0)) AS REAL) * 100 / COUNT(region) FROM geographic
### 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 of the cities are in a Bay Area?

### Hint
Bay Area refers to region = 'bay area'

### SQL
SELECT COUNT(city) FROM geographic WHERE region = 'bay area'
### 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 cities with unknown country.

### Hint
unknown county refers to county = 'unknown'

### SQL
SELECT city FROM geographic WHERE county = 'unknown'
### 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 city located in Bay Area of Santa Clara?

### Hint
Bay Area refers to region = 'bay area'; Santa Clara refers to county = 'santa clara county'

### SQL
SELECT city FROM geographic WHERE region = 'bay area' AND county = 'santa clara county'