lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "Method"
(
    Id            INTEGER not null
        primary key autoincrement,
    Name          TEXT,
    FullComment   TEXT,
    Summary       TEXT,
    ApiCalls      TEXT,
    CommentIsXml  INTEGER,
    SampledAt     INTEGER,
    SolutionId    INTEGER,
    Lang          TEXT,
    NameTokenized TEXT
)

CREATE TABLE "MethodParameter"
(
    Id       INTEGER not null
        primary key autoincrement,
    MethodId TEXT,
    Type     TEXT,
    Name     TEXT
)

CREATE TABLE Repo
(
    Id            INTEGER not null
        primary key autoincrement,
    Url           TEXT,
    Stars         INTEGER,
    Forks         INTEGER,
    Watchers      INTEGER,
    ProcessedTime INTEGER
)

CREATE TABLE Solution
(
    Id            INTEGER not null
        primary key autoincrement,
    RepoId        INTEGER,
    Path          TEXT,
    ProcessedTime INTEGER,
    WasCompiled   INTEGER
)

### Question
How many solution path does the repository with 111 stars, 58 forks, and 111 watchers?

### Hint
solution path refers to Path;

### SQL
SELECT COUNT(T2.Path) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T1.Stars = 111 AND T1.Forks = 58 AND T1.Watchers = 111
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "Method"
(
    Id            INTEGER not null
        primary key autoincrement,
    Name          TEXT,
    FullComment   TEXT,
    Summary       TEXT,
    ApiCalls      TEXT,
    CommentIsXml  INTEGER,
    SampledAt     INTEGER,
    SolutionId    INTEGER,
    Lang          TEXT,
    NameTokenized TEXT
)

CREATE TABLE "MethodParameter"
(
    Id       INTEGER not null
        primary key autoincrement,
    MethodId TEXT,
    Type     TEXT,
    Name     TEXT
)

CREATE TABLE Repo
(
    Id            INTEGER not null
        primary key autoincrement,
    Url           TEXT,
    Stars         INTEGER,
    Forks         INTEGER,
    Watchers      INTEGER,
    ProcessedTime INTEGER
)

CREATE TABLE Solution
(
    Id            INTEGER not null
        primary key autoincrement,
    RepoId        INTEGER,
    Path          TEXT,
    ProcessedTime INTEGER,
    WasCompiled   INTEGER
)

### Question
What is the average processed time of the solution with a repository of 254 likes, 88 followers, and 254 watchers?

### Hint
average processed time = AVG(ProcessedTime);

### SQL
SELECT CAST(SUM(T2.ProcessedTime) AS REAL) / COUNT(T2.ProcessedTime) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId WHERE T1.Stars = 254 AND T1.Forks = 88 AND T1.Watchers = 254
### Database Schema
CREATE TABLE sqlite_sequence(name,seq)

CREATE TABLE "Method"
(
    Id            INTEGER not null
        primary key autoincrement,
    Name          TEXT,
    FullComment   TEXT,
    Summary       TEXT,
    ApiCalls      TEXT,
    CommentIsXml  INTEGER,
    SampledAt     INTEGER,
    SolutionId    INTEGER,
    Lang          TEXT,
    NameTokenized TEXT
)

CREATE TABLE "MethodParameter"
(
    Id       INTEGER not null
        primary key autoincrement,
    MethodId TEXT,
    Type     TEXT,
    Name     TEXT
)

CREATE TABLE Repo
(
    Id            INTEGER not null
        primary key autoincrement,
    Url           TEXT,
    Stars         INTEGER,
    Forks         INTEGER,
    Watchers      INTEGER,
    ProcessedTime INTEGER
)

CREATE TABLE Solution
(
    Id            INTEGER not null
        primary key autoincrement,
    RepoId        INTEGER,
    Path          TEXT,
    ProcessedTime INTEGER,
    WasCompiled   INTEGER
)

### Question
How many language code of method is used for the github address "https://github.com/managedfusion/managedfusion.git
"?

### Hint
language code of method refers to Lang; github address refers to Url; Url = 'https://github.com/managedfusion/managedfusion.git';

### SQL
SELECT COUNT(DISTINCT T3.Lang) FROM Repo AS T1 INNER JOIN Solution AS T2 ON T1.Id = T2.RepoId INNER JOIN Method AS T3 ON T2.Id = T3.SolutionId WHERE T1.Url = 'https://github.com/managedfusion/managedfusion.git'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many trains are there that run in the east direction?

### Hint
east is a direction

### SQL
SELECT COUNT(id) FROM trains WHERE direction = 'east'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many cars are there on train no.1?

### Hint
train no.1 refers to train_id = 1

### SQL
SELECT COUNT(id) FROM cars WHERE train_id = 1
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
What is the shape of the tail car on train no.1?

### Hint
train no.1 refers to train_id = 1; tail car refers to position = 4

### SQL
SELECT shape FROM cars WHERE train_id = 1 AND position = 4
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the IDs of all the trains with at least one car in a non-regular shape.

### Hint
non-regular shape refers to shape in ('elipse', 'bucket')

### SQL
SELECT train_id FROM cars WHERE shape IN ('elipse', 'bucket') GROUP BY train_id
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many cars on train no.1 have the roof open?

### Hint
train no. 1 refers to train_id = 1; roof open refers to roof = 'none'

### SQL
SELECT COUNT(id) FROM cars WHERE train_id = 1 AND roof = 'none'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the IDs of all the cars on train no.1 that have 2 wheels.

### Hint
train no.1 refers to train_id = 1; 2 wheels refers to wheels = 2

### SQL
SELECT id FROM cars WHERE train_id = 1 AND wheels = 2
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Among the trains that run in the east direction, how many of them have at least one car in a non-regular shape?

### Hint
non-regular shape refers to shape in ('elipse', 'bucket')

### SQL
SELECT SUM(CASE WHEN T1.shape IN ('bucket', 'elipse') THEN 1 ELSE 0 end)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the IDs of all the trains that run in the east direction and have less than 4 cars.

### Hint
less than 4 cars refers to carsNum < 4

### SQL
SELECT T1.id FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS carsNum FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id WHERE T1.direction = 'east' AND T2.carsNum < 4
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the IDs of all the cars with double sides on trains that run in the west direction.

### Hint
double sides on trains refers to sides = 'double'

### SQL
SELECT T1.id FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east' AND T1.sides = 'double'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Among the trains that run in the east direction, how many of them have more than 2 long cars?

### Hint
more than 2 long cars refers to longCarsNum > 2

### SQL
SELECT SUM(CASE WHEN T2.longCarsNum > 2 THEN 1 ELSE 0 END)as count FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) AS longCarsNum FROM cars WHERE len = 'long' GROUP BY train_id ) AS T2 ON T1.id = T2.train_id WHERE T1.direction = 'west'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the directions in which the trains with at least one empty-loaded car run.

### Hint
at least one empty-loaded car run refers to load_num = 0

### SQL
SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.load_num = 0
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
In which direction does the train with an ellipse-shape car run?

### Hint
shape = 'ellipse'

### SQL
SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.shape = 'ellipse'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
What is the total number of short cars on all the trains that run in the east direction?

### Hint
short cars refers to len = 'short'

### SQL
SELECT SUM(CASE WHEN T1.len = 'short' then 1 ELSE 0 END)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the shapes of all the head cars on the trains that run in the east direction.

### Hint
head cars refers to position = 1;

### SQL
SELECT T1.shape FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east' AND T1.position = 1 GROUP BY T1.shape
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many cars on a train that runs in the east direction have a flat roof?

### Hint
flat roof refers to roof = 'flat'

### SQL
SELECT SUM(CASE WHEN T1.roof = 'flat' THEN 1 ELSE 0 END)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Among the cars on a train that runs in the east direction, how many of them have a flat roof and a circle load shape?

### Hint
flat roof refers to roof = 'flat'; load_shape = 'circle'

### SQL
SELECT SUM(CASE WHEN T1.load_shape = 'circle' THEN 1 ELSE 0 END)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east' AND T1.roof = 'flat'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Trains that run in which direction have more rectangle-shaped cars in total?

### Hint
more rectangle-shaped cars refers to MAX(rectCarsNum)

### SQL
SELECT T1.direction FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) AS rectCarsNum FROM cars WHERE shape = 'rectangle' GROUP BY train_id ) AS T2 ON T1.id = T2.train_id ORDER BY T2.rectCarsNum DESC
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Please list the directions in which the trains with 4 short cars run.

### Hint
short refers to len = 'short'; 4 cars run refers to position = 4

### SQL
SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.len = 'short' AND T1.position = 4
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
What is the average number of cars on trains that run in the east direction?

### Hint
calculation = DIVIDE(count(id), count(train_id))

### SQL
SELECT CAST(COUNT(T1.id) AS REAL) / COUNT(DISTINCT T1.train_id) FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T2.direction = 'east'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
Among the trains that have at least one non-regular shaped car, what is the percentage of it running in the east direction?

### Hint
non-regular shaped car refers to shape in ('bucket', 'ellipse'); calculation = MULTIPLY(DIVIDE(count(direction = 'east' then train_id)), count(train_id), 100)

### SQL
SELECT CAST(COUNT(DISTINCT CASE WHEN T2.direction = 'east' THEN T1.train_id ELSE NULL END) AS REAL) * 100 / COUNT(DISTINCT T1.train_id) FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.shape IN ('bucket', 'ellipse')
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many short cars are in the shape of hexagon?

### Hint
short cars refers to len = 'short'; in the shape of hexagon refers to shape = 'hexagon'

### SQL
SELECT COUNT(id) FROM cars WHERE shape = 'hexagon' AND len = 'short'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
How many trains are running west?

### Hint
west is a direction

### SQL
SELECT COUNT(id) FROM trains WHERE direction = 'west'
### Database Schema
CREATE TABLE `cars` (
  `id` INTEGER NOT NULL,
  `train_id` INTEGER DEFAULT NULL,
  `position` INTEGER DEFAULT NULL,
  `shape` TEXT DEFAULT NULL,
  `len`TEXT DEFAULT NULL,
  `sides` TEXT DEFAULT NULL,
  `roof` TEXT DEFAULT NULL,
  `wheels` INTEGER DEFAULT NULL,
  `load_shape` TEXT DEFAULT NULL,
  `load_num` INTEGER DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`train_id`) REFERENCES `trains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE TABLE `trains` (
  `id` INTEGER NOT NULL,
  `direction` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
)

### Question
What are the load shapes of all the short ellipse cars?

### Hint
short refers to len = 'short'; ellipse cars refers to shape = 'ellipse'

### SQL
SELECT load_shape FROM cars WHERE shape = 'ellipse' AND len = 'short'