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
How many times on page number 16 does the second word in the pair of words 1 and 109 appear?

### Hint
How many times appear refer to occurrences; page number 16 refers to pid = 16; 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 = 1; w2nd = 109;

### SQL
SELECT SUM(T1.occurrences) FROM pages_words AS T1 INNER JOIN biwords AS T2 ON T2.w2nd = T1.wid WHERE T2.w2nd = 109 AND T2.w1st = 1 AND T1.pid = 16
### 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 percentage of the words that have been repeated under 180 times in the Catalan language?

### Hint
repeated under 180 times refers to occurrences < 180; Catalan language refers to lang = 'ca'; DIVIDE(COUNT(words WHERE occurrences < 180 and lang = 'ca'), COUNT(words WHERE lang = 'ca')) as percentage;

### SQL
SELECT CAST(COUNT(CASE WHEN T2.occurrences < 180 THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T1.lid) FROM langs AS T1 INNER JOIN langs_words AS T2 ON T1.lid = T2.lid WHERE T1.lang = 'ca'
### 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 percentage of Catalan-language Wikipedia pages have more than 10,000 words?

### Hint
Catalan-language refers to lang = 'ca'; more than 10,000 words refer to words > 10000; DIVIDE(COUNT(pages WHERE words > 10000 and lang = 'ca'), COUNT(pages WHERE lang = 'ca')) as percentage;

### SQL
SELECT CAST(COUNT(CASE WHEN T2.words > 10000 THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(T2.page) FROM langs AS T1 INNER JOIN pages AS T2 ON T1.lid = T2.lid WHERE T1.lang = 'ca'
### 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 the word "desena" occurs?

### Hint
How many times occurs refers to occurrences;

### SQL
SELECT occurrences FROM words WHERE word = 'desena'
### 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 words has the appearance times greater than 10?

### Hint
appearance times greater than 10 refers to occurrences > 10;

### SQL
SELECT COUNT(w1st) AS countwords FROM biwords WHERE occurrences > 10
### 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 total pages of Wikipedia in Catalan language.

### Hint
This is not;

### SQL
SELECT pages FROM langs
### 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 words have repetitions greater than 2000 and lower than 5000?

### Hint
repetitions greater than 2000 and lower than 5000 refer to occurrences between 2000 and 5000;

### SQL
SELECT COUNT(wid) FROM langs_words WHERE occurrences BETWEEN '2000' AND '5000'
### 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 Catalan language Wikipedia page that has wikipedia revision page id as 106601.

### Hint
Wikipedia revision page id as 106601 refers to revision = 106601;

### SQL
SELECT title FROM pages WHERE revision = 106601