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 unemployed disabled students have been absent for 8 months from school?

### Hint
absent for 8 months refers to month = 8;

### SQL
SELECT COUNT(T1.name) FROM longest_absense_from_school AS T1 INNER JOIN unemployed AS T2 ON T1.name = T2.name INNER JOIN disabled AS T3 ON T2.name = T3.name WHERE T1.month = 8
### 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
State name of unemployed students who have the longest duration of absense from school.

### Hint
longest duration of absense refers to MAX(month)

### SQL
SELECT T1.name FROM longest_absense_from_school AS T1 INNER JOIN unemployed AS T2 ON T1.name = T2.name 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
Mention the name of unemployed students who have never been absent from school.

### Hint
have never been absent from school refers to month = 0

### SQL
SELECT T1.name FROM longest_absense_from_school AS T1 INNER JOIN unemployed AS T2 ON T1.name = T2.name WHERE T1.month = 0
### 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 have been absent for 3 months from school?

### Hint
have been absent for 3 months from school refers to month = 3

### SQL
SELECT COUNT(T1.name) FROM longest_absense_from_school AS T1 INNER JOIN disabled AS T2 ON T1.name = T2.name WHERE T1.month = 3
### 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
Mention the name of students who filed for bankruptcy and have never been absent from school.

### Hint
have never been absent refers to month = 0;

### SQL
SELECT T1.name FROM longest_absense_from_school AS T1 INNER JOIN filed_for_bankrupcy AS T2 ON T1.name = T2.name WHERE T1.month = 0
### 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
State name of students who filed for bankruptcy and have the longest duration of absense from school.

### Hint
longest duration of absense refers to MAX(month)

### SQL
SELECT T1.name FROM longest_absense_from_school AS T1 INNER JOIN filed_for_bankrupcy AS T2 ON T1.name = T2.name 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
What is the gender of student1000?

### Hint
student name appear in Male table means student is a male, student does not appear in Male table means student is a female

### SQL
SELECT IIF(T.result = 0, 'female', 'male') AS re FROM ( SELECT COUNT(name) AS result FROM male WHERE name = 'studenT1000' ) T
### 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 students are disabled?

### SQL
SELECT COUNT(name) FROM disabled
### 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 students have been absents for more than 6 months?

### Hint
absents for more than 6 months refers to month > 6

### SQL
SELECT COUNT(name) FROM longest_absense_from_school WHERE month > 6
### 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
Which students have absents the most?

### Hint
absents the most refers to MAX(month)

### SQL
SELECT name FROM longest_absense_from_school WHERE month = ( SELECT MAX(month) FROM longest_absense_from_school )
### 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 students are enlisted in the army?

### Hint
enlisted in the army refers to organ = 'army';

### SQL
SELECT COUNT(name) FROM enlist WHERE organ = 'army'