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 students are filed for bankruptcy?

### SQL
SELECT COUNT(name) FROM filed_for_bankrupcy
### 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 enrolled in smc during month 1?

### Hint
smc refers to school = 'smc';

### SQL
SELECT COUNT(name) FROM enrolled WHERE school = 'smc' AND month = 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
How many students enlist in the air force organization?

### Hint
organization refers to organ; organ = 'air_force';

### SQL
SELECT COUNT(name) FROM enlist WHERE 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
What is the organization enlisted by student168?

### Hint
organization refers to organ; student168 is a name of student;

### SQL
SELECT organ FROM enlist WHERE name = 'student168'
### 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 male students joined an organization?

### Hint
organization refers to organ; disabled male students refers to disabled.name who are IN male.name;

### SQL
SELECT COUNT(T1.name) FROM disabled AS T1 LEFT JOIN male AS T2 ON T2.name = T1.name INNER JOIN enlist 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
Please provide a disability breakdown for each school.

### Hint
disability breakdown refers to the number of disabled students;

### SQL
SELECT COUNT(T1.name) FROM enrolled AS T1 INNER JOIN disabled AS T2 ON T2.name = T1.name GROUP BY T1.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
Please provide a gender breakdown for each organization.

### Hint
gender breakdown refers to the number of male and female; male are mentioned in male.name; female refers to enlist.name who are NOT in male.name; organization refers to organ;

### SQL
SELECT IIF(T2.name IS NULL, 'female', 'male') AS gender FROM enlist AS T1 LEFT JOIN male AS T2 ON T2.name = T1.name GROUP BY T1.organ
### 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 out student names that enrolled in two schools and two organizations?

### Hint
enrolled in two schools refers to COUNT(school) = 2; two organizations refers to COUNT(organ) = 2;

### SQL
SELECT T.name FROM ( SELECT T1.name, COUNT(T1.organ) AS num FROM enlist AS T1 INNER JOIN enrolled AS T2 ON T1.name = T2.name GROUP BY T1.name ) T WHERE T.num = 2
### 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 percentage difference between month 0 absence and month 9 absence?

### Hint
percentage difference = CONCAT(DIVIDE(MULTIPLY(SUBTRACT(COUNT(name WHERE month = 0), COUNT(name WHERE month = 9)), 100), COUNT(name WHERE month = 0)),'%');

### SQL
SELECT CAST(((SUM(IIF(month = 0, 1, 0)) - SUM(IIF(month = 9, 1, 0)))) AS REAL) * 100 / SUM(IIF(month = 0, 1, 0)) 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
Which school have the highest student enrollment? How many of those students are filed for bankruptcy?

### Hint
highest student enrollment refers to MAX(COUNT(school));

### SQL
SELECT T.school, num FROM ( SELECT T1.school, COUNT(T2.name) AS num FROM enrolled AS T1 LEFT JOIN filed_for_bankrupcy AS T2 ON T2.name = T1.name GROUP BY T1.school ) T ORDER BY T.num 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
How many students is disabled and unemployed at the same time?

### Hint
students who are disabled and unemployed at the same time refers to disabled.name = unemployed.name;

### SQL
SELECT COUNT(T1.name) FROM disabled AS T1 INNER JOIN unemployed AS T2 ON T2.name = T1.name