lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE ATT_CLASSES
(
    ATT_CLASS_ID INTEGER default 0 not null
        primary key,
    ATT_CLASS    TEXT      not null
)

CREATE TABLE OBJ_CLASSES
(
    OBJ_CLASS_ID INTEGER default 0 not null
        primary key,
    OBJ_CLASS    TEXT      not null
)

CREATE TABLE IMG_OBJ
(
    IMG_ID        INTEGER default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    OBJ_CLASS_ID  INTEGER              null,
    X             INTEGER              null,
    Y             INTEGER             null,
    W             INTEGER              null,
    H             INTEGER              null,
    primary key (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (OBJ_CLASS_ID) references OBJ_CLASSES (OBJ_CLASS_ID)
)

CREATE TABLE IMG_OBJ_ATT
(
    IMG_ID        INTEGER default 0 not null,
    ATT_CLASS_ID  INTEGER    default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    primary key (IMG_ID, ATT_CLASS_ID, OBJ_SAMPLE_ID),
    foreign key (ATT_CLASS_ID) references ATT_CLASSES (ATT_CLASS_ID),
    foreign key (IMG_ID, OBJ_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

CREATE TABLE PRED_CLASSES
(
    PRED_CLASS_ID INTEGER default 0 not null
        primary key,
    PRED_CLASS    TEXT     not null
)

CREATE TABLE IMG_REL
(
    IMG_ID         INTEGER default 0 not null,
    PRED_CLASS_ID  INTEGER    default 0 not null,
    OBJ1_SAMPLE_ID INTEGER    default 0 not null,
    OBJ2_SAMPLE_ID INTEGER   default 0 not null,
    primary key (IMG_ID, PRED_CLASS_ID, OBJ1_SAMPLE_ID, OBJ2_SAMPLE_ID),
    foreign key (PRED_CLASS_ID) references PRED_CLASSES (PRED_CLASS_ID),
    foreign key (IMG_ID, OBJ1_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (IMG_ID, OBJ2_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

### Question
Name the most common predicate class of image ID 4434.

### Hint
the most common predicate class of image ID 4434 MAX(PRED_CLASS) where IMG_ID = 4434;

### SQL
SELECT T2.PRED_CLASS FROM IMG_REL AS T1 INNER JOIN PRED_CLASSES AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T1.IMG_ID = 4434 ORDER BY T2.PRED_CLASS DESC LIMIT 1
### Database Schema
CREATE TABLE ATT_CLASSES
(
    ATT_CLASS_ID INTEGER default 0 not null
        primary key,
    ATT_CLASS    TEXT      not null
)

CREATE TABLE OBJ_CLASSES
(
    OBJ_CLASS_ID INTEGER default 0 not null
        primary key,
    OBJ_CLASS    TEXT      not null
)

CREATE TABLE IMG_OBJ
(
    IMG_ID        INTEGER default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    OBJ_CLASS_ID  INTEGER              null,
    X             INTEGER              null,
    Y             INTEGER             null,
    W             INTEGER              null,
    H             INTEGER              null,
    primary key (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (OBJ_CLASS_ID) references OBJ_CLASSES (OBJ_CLASS_ID)
)

CREATE TABLE IMG_OBJ_ATT
(
    IMG_ID        INTEGER default 0 not null,
    ATT_CLASS_ID  INTEGER    default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    primary key (IMG_ID, ATT_CLASS_ID, OBJ_SAMPLE_ID),
    foreign key (ATT_CLASS_ID) references ATT_CLASSES (ATT_CLASS_ID),
    foreign key (IMG_ID, OBJ_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

CREATE TABLE PRED_CLASSES
(
    PRED_CLASS_ID INTEGER default 0 not null
        primary key,
    PRED_CLASS    TEXT     not null
)

CREATE TABLE IMG_REL
(
    IMG_ID         INTEGER default 0 not null,
    PRED_CLASS_ID  INTEGER    default 0 not null,
    OBJ1_SAMPLE_ID INTEGER    default 0 not null,
    OBJ2_SAMPLE_ID INTEGER   default 0 not null,
    primary key (IMG_ID, PRED_CLASS_ID, OBJ1_SAMPLE_ID, OBJ2_SAMPLE_ID),
    foreign key (PRED_CLASS_ID) references PRED_CLASSES (PRED_CLASS_ID),
    foreign key (IMG_ID, OBJ1_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (IMG_ID, OBJ2_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

### Question
Count the number of 'dress' object classes and include their X and Y coordinates in image ID 1764.

### Hint
dress' object classes refer to OBJ_CLASS = 'dress'; image ID 1764 refers to IMG_ID = 1764; X and Y refer to coordinates of the bounding box;

### SQL
SELECT T1.X, T1.Y FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T1.IMG_ID = 1764 AND T2.OBJ_CLASS = 'dress'
### Database Schema
CREATE TABLE ATT_CLASSES
(
    ATT_CLASS_ID INTEGER default 0 not null
        primary key,
    ATT_CLASS    TEXT      not null
)

CREATE TABLE OBJ_CLASSES
(
    OBJ_CLASS_ID INTEGER default 0 not null
        primary key,
    OBJ_CLASS    TEXT      not null
)

CREATE TABLE IMG_OBJ
(
    IMG_ID        INTEGER default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    OBJ_CLASS_ID  INTEGER              null,
    X             INTEGER              null,
    Y             INTEGER             null,
    W             INTEGER              null,
    H             INTEGER              null,
    primary key (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (OBJ_CLASS_ID) references OBJ_CLASSES (OBJ_CLASS_ID)
)

CREATE TABLE IMG_OBJ_ATT
(
    IMG_ID        INTEGER default 0 not null,
    ATT_CLASS_ID  INTEGER    default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    primary key (IMG_ID, ATT_CLASS_ID, OBJ_SAMPLE_ID),
    foreign key (ATT_CLASS_ID) references ATT_CLASSES (ATT_CLASS_ID),
    foreign key (IMG_ID, OBJ_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

CREATE TABLE PRED_CLASSES
(
    PRED_CLASS_ID INTEGER default 0 not null
        primary key,
    PRED_CLASS    TEXT     not null
)

CREATE TABLE IMG_REL
(
    IMG_ID         INTEGER default 0 not null,
    PRED_CLASS_ID  INTEGER    default 0 not null,
    OBJ1_SAMPLE_ID INTEGER    default 0 not null,
    OBJ2_SAMPLE_ID INTEGER   default 0 not null,
    primary key (IMG_ID, PRED_CLASS_ID, OBJ1_SAMPLE_ID, OBJ2_SAMPLE_ID),
    foreign key (PRED_CLASS_ID) references PRED_CLASSES (PRED_CLASS_ID),
    foreign key (IMG_ID, OBJ1_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (IMG_ID, OBJ2_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

### Question
Give the X and Y coordinates of the sample object of image ID 23 that has the 'cast' attribute class.

### Hint
X and Y refer to coordinates of the bounding box; image ID 23 refers to IMG_ID = 23; 'cast' attribute class refers to ATT_CLASS = 'cast';

### SQL
SELECT T3.OBJ_SAMPLE_ID, T3.X, T3.Y FROM ATT_CLASSES AS T1 INNER JOIN IMG_OBJ_ATT AS T2 ON T1.ATT_CLASS_ID = T2.ATT_CLASS_ID INNER JOIN IMG_OBJ AS T3 ON T2.IMG_ID = T3.IMG_ID WHERE T3.IMG_ID = 23 AND T1.ATT_CLASS = 'cast'
### Database Schema
CREATE TABLE ATT_CLASSES
(
    ATT_CLASS_ID INTEGER default 0 not null
        primary key,
    ATT_CLASS    TEXT      not null
)

CREATE TABLE OBJ_CLASSES
(
    OBJ_CLASS_ID INTEGER default 0 not null
        primary key,
    OBJ_CLASS    TEXT      not null
)

CREATE TABLE IMG_OBJ
(
    IMG_ID        INTEGER default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    OBJ_CLASS_ID  INTEGER              null,
    X             INTEGER              null,
    Y             INTEGER             null,
    W             INTEGER              null,
    H             INTEGER              null,
    primary key (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (OBJ_CLASS_ID) references OBJ_CLASSES (OBJ_CLASS_ID)
)

CREATE TABLE IMG_OBJ_ATT
(
    IMG_ID        INTEGER default 0 not null,
    ATT_CLASS_ID  INTEGER    default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    primary key (IMG_ID, ATT_CLASS_ID, OBJ_SAMPLE_ID),
    foreign key (ATT_CLASS_ID) references ATT_CLASSES (ATT_CLASS_ID),
    foreign key (IMG_ID, OBJ_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

CREATE TABLE PRED_CLASSES
(
    PRED_CLASS_ID INTEGER default 0 not null
        primary key,
    PRED_CLASS    TEXT     not null
)

CREATE TABLE IMG_REL
(
    IMG_ID         INTEGER default 0 not null,
    PRED_CLASS_ID  INTEGER    default 0 not null,
    OBJ1_SAMPLE_ID INTEGER    default 0 not null,
    OBJ2_SAMPLE_ID INTEGER   default 0 not null,
    primary key (IMG_ID, PRED_CLASS_ID, OBJ1_SAMPLE_ID, OBJ2_SAMPLE_ID),
    foreign key (PRED_CLASS_ID) references PRED_CLASSES (PRED_CLASS_ID),
    foreign key (IMG_ID, OBJ1_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (IMG_ID, OBJ2_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

### Question
How many 'blue' attribute classes are there on image ID 2355735?

### Hint
blue' attribute classes on image ID 2355735 refer to ATT_CLASS = 'blue' where IMG_ID = 2355735;

### SQL
SELECT COUNT(T1.ATT_CLASS) FROM ATT_CLASSES AS T1 INNER JOIN IMG_OBJ_ATT AS T2 ON T1.ATT_CLASS_ID = T2.ATT_CLASS_ID WHERE T2.IMG_ID = 2355735 AND T1.ATT_CLASS = 'blue'
### Database Schema
CREATE TABLE ATT_CLASSES
(
    ATT_CLASS_ID INTEGER default 0 not null
        primary key,
    ATT_CLASS    TEXT      not null
)

CREATE TABLE OBJ_CLASSES
(
    OBJ_CLASS_ID INTEGER default 0 not null
        primary key,
    OBJ_CLASS    TEXT      not null
)

CREATE TABLE IMG_OBJ
(
    IMG_ID        INTEGER default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    OBJ_CLASS_ID  INTEGER              null,
    X             INTEGER              null,
    Y             INTEGER             null,
    W             INTEGER              null,
    H             INTEGER              null,
    primary key (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (OBJ_CLASS_ID) references OBJ_CLASSES (OBJ_CLASS_ID)
)

CREATE TABLE IMG_OBJ_ATT
(
    IMG_ID        INTEGER default 0 not null,
    ATT_CLASS_ID  INTEGER    default 0 not null,
    OBJ_SAMPLE_ID INTEGER    default 0 not null,
    primary key (IMG_ID, ATT_CLASS_ID, OBJ_SAMPLE_ID),
    foreign key (ATT_CLASS_ID) references ATT_CLASSES (ATT_CLASS_ID),
    foreign key (IMG_ID, OBJ_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

CREATE TABLE PRED_CLASSES
(
    PRED_CLASS_ID INTEGER default 0 not null
        primary key,
    PRED_CLASS    TEXT     not null
)

CREATE TABLE IMG_REL
(
    IMG_ID         INTEGER default 0 not null,
    PRED_CLASS_ID  INTEGER    default 0 not null,
    OBJ1_SAMPLE_ID INTEGER    default 0 not null,
    OBJ2_SAMPLE_ID INTEGER   default 0 not null,
    primary key (IMG_ID, PRED_CLASS_ID, OBJ1_SAMPLE_ID, OBJ2_SAMPLE_ID),
    foreign key (PRED_CLASS_ID) references PRED_CLASSES (PRED_CLASS_ID),
    foreign key (IMG_ID, OBJ1_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID),
    foreign key (IMG_ID, OBJ2_SAMPLE_ID) references IMG_OBJ (IMG_ID, OBJ_SAMPLE_ID)
)

### Question
What is the average width and height of the objects in image ID 47? List their object classes as well.

### Hint
The bounding box's W and H abbreviations stand for the object's width and height in which average width and height refer to AVG(W) and AVG(H) respectively; image ID 47 refers to IMG_ID = 47; object classes refer to OBJ_CLASS;

### SQL
SELECT T2.OBJ_CLASS, AVG(T1.W), AVG(T1.H) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T1.IMG_ID = 47 GROUP BY T2.OBJ_CLASS
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
Which country has the shortest life expectancy?

### Hint
shortest life expectancy refers to MIN(LifeExpectancy);

### SQL
SELECT Name FROM Country ORDER BY LifeExpectancy LIMIT 1
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
List any five countries which use English as an official language.

### Hint
English as an official language refers to `Language` = 'English' AND IsOfficial = 'T';

### SQL
SELECT T1.Name FROM Country AS T1 INNER JOIN CountryLanguage AS T2 ON T1.Code = T2.CountryCode WHERE T2.Language = 'English' AND T2.IsOfficial = 'T' LIMIT 5
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
Calculate the average population per city in Karnataka district.

### Hint
average population = AVG(Population);

### SQL
SELECT AVG(Population) FROM City WHERE District = 'Karnataka' GROUP BY ID
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
List the languages used in the USA.

### Hint
USA refers to CountryCode = 'USA';

### SQL
SELECT Language FROM CountryLanguage WHERE CountryCode = 'USA'
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
How many countries use Portuguese?

### Hint
Portuguese refers to `Language` = 'Portuguese';

### SQL
SELECT SUM(CASE WHEN Language = 'Portuguese' THEN 1 ELSE 0 END) FROM CountryLanguage
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
How many cities are there in England?

### Hint
England refers to District = 'England';

### SQL
SELECT COUNT(ID) FROM City WHERE District = 'England'
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
How many cities are there in the country with the largest surface area?

### Hint
largest surface area refers to MAX(SurfaceArea);

### SQL
SELECT T2.ID FROM Country AS T1 INNER JOIN City AS T2 ON T1.Code = T2.CountryCode WHERE T1.SurfaceArea = ( SELECT MAX(SurfaceArea) FROM Country )
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE `City` (
  `ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  `Name` TEXT NOT NULL DEFAULT '',
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `District` TEXT NOT NULL DEFAULT '',
  `Population` INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

CREATE TABLE `Country` (
  `Code` TEXT NOT NULL DEFAULT '',
  `Name` TEXT NOT NULL DEFAULT '',
  `Continent` TEXT NOT NULL DEFAULT 'Asia',
  `Region` TEXT NOT NULL DEFAULT '',
  `SurfaceArea` REAL NOT NULL DEFAULT 0.00,
  `IndepYear` INTEGER DEFAULT NULL,
  `Population` INTEGER NOT NULL DEFAULT 0,
  `LifeExpectancy` REAL DEFAULT NULL,
  `GNP` REAL DEFAULT NULL,
  `GNPOld` REAL DEFAULT NULL,
  `LocalName` TEXT NOT NULL DEFAULT '',
  `GovernmentForm` TEXT NOT NULL DEFAULT '',
  `HeadOfState` TEXT DEFAULT NULL,
  `Capital` INTEGER DEFAULT NULL,
  `Code2` TEXT NOT NULL DEFAULT '',
  PRIMARY KEY (`Code`)
)

CREATE TABLE `CountryLanguage` (
  `CountryCode` TEXT NOT NULL DEFAULT '',
  `Language` TEXT NOT NULL DEFAULT '',
  `IsOfficial`TEXT NOT NULL DEFAULT 'F',
  `Percentage` REAL NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`CountryCode`,`Language`),
  FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
)

### Question
What is the capital city and population of San Marino?

### Hint
capital city refers to Capital; San Marino is a name of country;

### SQL
SELECT T1.Capital, T2.Population FROM Country AS T1 INNER JOIN City AS T2 ON T1.Code = T2.CountryCode WHERE T1.Name = 'San Marino'