lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
How many disabled students are male?

### Hint
male students are mentioned in male.name;

### SQL
SELECT COUNT(T1.name) FROM male AS T1 INNER JOIN disabled AS T2 ON T1.name = T2.name
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
List the names of disabled students enlisted in the navy.

### Hint
navy refers to organ = 'navy';

### SQL
SELECT T1.name FROM enlist AS T1 INNER JOIN disabled AS T2 ON T1.name = T2.name WHERE T1.organ = 'navy'
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
Among the male students, list down the names of students with payment due.

### Hint
male students are mentioned in male.name; with payment due refers to bool = 'pos';

### SQL
SELECT T1.name FROM male AS T1 INNER JOIN no_payment_due AS T2 ON T1.name = T2.name WHERE T2.bool = 'pos'
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
What is the employment and payment status of student110?

### Hint
employment status is mentioned in unemployment; unemployed.name = 'student110' means the student is unemployed; if 'student110' can not be found in unemployed.name, it means the student is employed; payment status is mentioned in no_payment_due; bool = 'pos' means the student has payment due; bool = 'neg' means the student has no payment due; student110 is a name of student;

### SQL
SELECT T1.bool FROM no_payment_due AS T1 INNER JOIN unemployed AS T2 ON T1.name = T2.name WHERE T1.name = 'student110'
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
Among all students, calculate the percentage of male students.

### Hint
percentage = MULTIPLY(DIVIDE(COUNT(male.name), COUNT(person.name)), 100); male students are mentioned in male.name;

### SQL
SELECT CAST(COUNT(T2.name) AS REAL) * 100 / COUNT(T1.name) FROM person AS T1 LEFT JOIN male AS T2 ON T1.name = T2.name
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
What is the school and gender of student34?

### Hint
male.name = 'student34' means student34's gender is male; if 'student34' can't be found in 'male.name', it means student34 is female; student34 is a name of student;

### SQL
SELECT T1.school , IIF(T3.name IS NULL, 'female', 'male') AS gender FROM enrolled AS T1 INNER JOIN person AS T2 ON T1.name = T2.name LEFT JOIN male AS T3 ON T2.name = T3.name WHERE T2.name = 'student34'
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
List the longest duration of absense for a student enlisted in the fire department.

### Hint
longest duration of absence refers to MAX(month); department refers to organ; organ = 'fire_department';

### SQL
SELECT T1.month FROM longest_absense_from_school AS T1 INNER JOIN enlist AS T2 ON T1.name = T2.name WHERE T2.organ = 'fire_department' ORDER BY T1.month DESC LIMIT 1
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
Among students with 1 month of absenses, how many of them are enlisted in the air force department?

### Hint
1 month of absences refers to month = 1; department refers to organ; organ = 'air_force';

### SQL
SELECT COUNT(T1.name) FROM longest_absense_from_school AS T1 INNER JOIN enlist AS T2 ON T1.name = T2.name WHERE T1.month = 1 AND T2.organ = 'air_force'
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
Provide the name of disabled male students that are unemployed.

### Hint
disabled male students that are unemployed refers to unemployed.name = male.name = disabled.name;

### SQL
SELECT T2.NAME FROM unemployed AS T1 INNER JOIN male AS T2 ON T1.name = T2.name INNER JOIN disabled AS T3 ON T3.name = T2.name
### Database Schema
CREATE TABLE bool
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE person
(
    "name" TEXT default '' not null
        primary key
)

CREATE TABLE disabled
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE enlist
(
    "name"  TEXT not null,
    organ TEXT not null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE filed_for_bankrupcy
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE longest_absense_from_school
(
    "name"  TEXT default '' not null
        primary key,
    "month" INTEGER       default 0  null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE male
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE no_payment_due
(
    "name" TEXT default '' not null
        primary key,
    bool TEXT             null,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade,
        foreign key (bool) references bool ("name")
            on update cascade on delete cascade
)

CREATE TABLE unemployed
(
    "name" TEXT default '' not null
        primary key,
        foreign key ("name") references person ("name")
            on update cascade on delete cascade
)

CREATE TABLE `enrolled` (
  `name` TEXT NOT NULL,
  `school` TEXT NOT NULL,
  `month` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`name`,`school`),
  FOREIGN KEY (`name`) REFERENCES `person` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
)

### Question
How many male students are enrolled at OCC?

### Hint
male students are mentioned in male.name; OCC refers to school = 'occ';

### SQL
SELECT COUNT(T1.name) FROM enrolled AS T1 INNER JOIN male AS T2 ON T1.name = T2.name WHERE T1.school = 'occ'