lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
State the Wikipedia page title that has revision page id of 28040864.

### Hint
revision page id of 28040864 refers to revision = 28040864;

### SQL
SELECT title FROM pages WHERE revision = 28040864
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
How many times that the word pair of "barcelona" and "precolombina" occur?

### Hint
Pair is a relationship of two words: w1st and w2nd, where w1st is word id of the first word and w2nd is a word id of the second word; w1st or w2nd = 'barcelona'; w1st or w2nd = 'precolombina';

### SQL
SELECT SUM(occurrences) FROM biwords WHERE w1st = ( SELECT wid FROM words WHERE word = 'barcelona' ) AND w2nd = ( SELECT wid FROM words WHERE word = 'precolombina' )
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
What is the locale of the language of the page titled "Anys 90"?

### Hint
page titled "Anys 90" refers to title = 'Anys 90';

### SQL
SELECT T1.locale FROM langs AS T1 INNER JOIN pages AS T2 ON T1.lid = T2.lid WHERE T2.title = 'Anys 90'
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
Which word that has 71303 appearance in the Catalan language?

### Hint
has 71303 appearance refers to occurrences = 71303;

### SQL
SELECT T1.word FROM words AS T1 INNER JOIN langs_words AS T2 ON T1.wid = T2.wid WHERE T2.occurrences = 71303
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
What is the locale of the language of the page titled "Abril"?

### Hint
the page titled "Abril" refers to title = 'Abril';

### SQL
SELECT T1.locale FROM langs AS T1 INNER JOIN pages AS T2 ON T1.lid = T2.lid WHERE T2.title = 'Abril'
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
What is the total number of words in page containing pair of word id "100" and "317"?

### Hint
Pair is a relationship of two words: w1st and w2nd, where w1st is word id of the first word and w2nd is a word id of the second word; w1st = 100; w2nd = 317;

### SQL
SELECT words FROM langs WHERE lid = ( SELECT lid FROM biwords WHERE w1st = 100 AND w2nd = 317 )
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
State the total pages of the words that has repeated times of 2593.

### Hint
repeated times of 2593 refers to occurrences = 2593;

### SQL
SELECT COUNT(T1.pages) FROM langs AS T1 INNER JOIN langs_words AS T2 ON T1.lid = T2.lid WHERE T2.occurrences = 2593
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
List out the title of the word have id less than 20.

### Hint
word have id less than 20 refers to wid < 20;

### SQL
SELECT DISTINCT T1.title FROM pages AS T1 INNER JOIN pages_words AS T2 ON T1.pid = T2.pid WHERE T2.wid < 20
### Database Schema
CREATE TABLE langs(lid    INTEGER PRIMARY KEY AUTOINCREMENT,
                            lang   TEXT UNIQUE,
                            locale TEXT UNIQUE,
                            pages  INTEGER DEFAULT 0,  -- total pages in this language
                            words  INTEGER DEFAULT 0)

CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE pages(pid INTEGER PRIMARY KEY AUTOINCREMENT,
                            lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                            page INTEGER DEFAULT NULL, -- wikipedia page id
                            revision INTEGER DEFAULT NULL, -- wikipedia revision page id
                            title TEXT,
                            words INTEGER DEFAULT 0, -- number of different words in this page
                            UNIQUE(lid,page,title))

CREATE TABLE words(wid INTEGER PRIMARY KEY AUTOINCREMENT,
                            word        TEXT UNIQUE,
                            occurrences INTEGER DEFAULT 0)

CREATE TABLE langs_words(lid INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER, -- repetitions of this word in this language
                                        PRIMARY KEY(lid,wid))
                                        WITHOUT ROWID

CREATE TABLE pages_words(pid INTEGER REFERENCES pages(pid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        wid INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                        occurrences INTEGER DEFAULT 0, -- times this word appears into this page
                                        PRIMARY KEY(pid,wid))
                                        WITHOUT ROWID

CREATE TABLE biwords(lid    INTEGER REFERENCES langs(lid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w1st   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                w2nd   INTEGER REFERENCES words(wid) ON UPDATE CASCADE ON DELETE CASCADE,
                                occurrences INTEGER DEFAULT 0, -- times this pair appears in this language/page
                                PRIMARY KEY(lid,w1st,w2nd))
                                WITHOUT ROWID

### Question
How many word that has number of different words equal to 3?

### Hint
This is not;

### SQL
SELECT COUNT(T2.wid) FROM pages AS T1 INNER JOIN pages_words AS T2 ON T1.pid = T2.pid WHERE T1.words = 3