lianghsun/bird-text2sql-bench huggingface dataset:
### 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 ids of the train running east?

### Hint
east is a direction

### SQL
SELECT 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 wheels do the long cars have?

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

### SQL
SELECT SUM(wheels) FROM cars WHERE len = 'long'
### 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
Which direction do the majority of the trains are running?

### Hint
majority of train refers to MAX(count(id))

### SQL
SELECT direction FROM trains GROUP BY direction ORDER BY COUNT(id) 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
Among the trains running east, how many trains have at least 4 cars?

### Hint
east is a direction; at least 4 cars refers to carsNum > = 4

### SQL
SELECT SUM(CASE WHEN T1.direction = 'east' THEN 1 ELSE 0 END)as count FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) AS carsNum FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id WHERE 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
Which direction do most of the trains with rectangle-shaped second cars run?

### Hint
most of the trains refers to MAX(count(id)); second cars refers to position = 2

### SQL
SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 2 AND T1.shape = 'rectangle' GROUP BY T2.direction ORDER BY COUNT(T2.id) DESC LIMIT 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
How many trains running west have double sided cars in 3rd position?

### Hint
west is a direction; double sided cars refers to sides = 'double'; 3rd position refers to position = 3

### SQL
SELECT COUNT(T.train_id) FROM (SELECT T1.train_id FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 3 AND T2.direction = 'west' AND T1.sides = 'double' GROUP BY T1.train_id)as T
### 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 eastbound trains have rectangular-shaped head cars?

### Hint
eastbound refers to direction = 'east'; head cars refers to position = 1

### SQL
SELECT COUNT(T.train_id) FROM (SELECT T1.train_id FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 1 AND T2.direction = 'east' AND T1.shape = 'rectangle' GROUP BY T1.train_id)as T
### 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 running west, how many trains have no more than one car with an open roof?

### Hint
running west refers to direction = 'west'; open roof refers to roof = 'none'

### SQL
SELECT SUM(CASE WHEN T1.direction = 'west' THEN 1 ELSE 0 END)as count FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) FROM cars WHERE roof = 'none' GROUP BY train_id HAVING COUNT(id) = 1 ) AS T2 ON T1.id = T2.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
Which direction does the majority of the trains that have 3 cars are running?

### Hint
3 cars refers to carsNum = 3

### SQL
SELECT T1.direction FROM trains AS T1 INNER JOIN ( SELECT train_id, COUNT(id) AS carsNum FROM cars GROUP BY train_id HAVING carsNum = 3 ) AS T2 ON T1.id = T2.train_id GROUP BY T1.direction
### 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 with fully loaded head cars are running east?

### Hint
fully loaded refers to load_num = 3; head cars refers to position = 1

### SQL
SELECT COUNT(DISTINCT T1.train_id) FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 1 AND T1.load_num = 3
### 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 running east have double-sided tail cars?

### Hint
east is an direction; double-sided refers to sides = 'double'; tail refers to carsposition = trailPosi

### SQL
SELECT COUNT(T1.id) FROM trains AS T1 INNER JOIN cars AS T2 ON T1.id = T2.train_id INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars GROUP BY train_id ) AS T3 ON T1.id = T3.train_id WHERE T1.direction = 'east' AND T2.position = T3.trailPosi AND T2.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
List all the directions of the trains that have empty cars.

### Hint
empty cars 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
What is the direction of the train with a diamond-shaped load in its 2nd car?

### Hint
2nd car refers to position = 2

### SQL
SELECT T2.direction FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 2 AND T1.shape = 'diamond'
### 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 running west, how many trains have three-wheeled, jagged roof cars?

### Hint
west is an direction; three-wheeled refers to wheels = 3; jagged roof refers to roof = 'jagged'

### SQL
SELECT SUM(CASE WHEN T2.direction = 'west' THEN 1 ELSE 0 END)as count FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.wheels = 3 AND T1.roof = 'jagged'
### 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
Provide the directions for all the trains that have 2 or less cars.

### Hint
2 or less cars refers to trailPosi < = 2

### SQL
SELECT T1.direction FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id WHERE T2.trailPosi <= 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
What is the percentage of all the trains with at least 4 cars? List the directions of the said trains.

### Hint
at least 4 cars refers to trailPosi > = 4; calculation = MULTIPLY(DIVIDE(count(trailPosi > = 4 then id), count(id)), 100)

### SQL
SELECT CAST(COUNT(CASE WHEN T2.trailPosi >= 4 THEN T1.id ELSE NULL END) AS REAL) * 100 / COUNT(T1.id) FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars GROUP BY train_id ) AS T2 ON T1.id = T2.train_id UNION ALL SELECT T1.direction FROM trains AS T1 INNER JOIN ( SELECT train_id, MAX(position) AS trailPosi FROM cars t GROUP BY train_id ) AS T2 ON T1.id = T2.train_id AND T2.trailPosi >= 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
List all the load shapes of all head cars of each train and identify which load shape has the highest number. Calculate the percentage of the trains with the said head car that are running eas

### Hint
which load shape has the highest number refers to MAX(load_shape); head car refers to position = 1; east is a direction; calculation = MULTIPLY(DIVIDE(count(direction = 'east' where MAX(load_shape) where position = 1 then id), count(id)), 100)

### SQL
SELECT DISTINCT T3.load_shape FROM ( SELECT load_shape, train_id FROM cars WHERE position = 1 ORDER BY train_id DESC ) AS T3 UNION ALL SELECT T4.load_shape FROM ( SELECT load_shape, train_id FROM cars WHERE position = 1 ORDER BY train_id DESC LIMIT 1 ) AS T4 UNION ALL SELECT (CAST(COUNT(DISTINCT CASE WHEN T2.direction = 'east' THEN T2.id ELSE NULL END) AS REAL) * 100 / COUNT(DISTINCT T2.id)) FROM cars AS T1 INNER JOIN trains AS T2 ON T1.train_id = T2.id WHERE T1.position = 1 AND T1.load_shape = ( SELECT T4.load_shape FROM ( SELECT load_shape, train_id FROM cars AS T WHERE position = 1 ORDER BY train_id DESC LIMIT 1 ) AS T4 )
### Database Schema
CREATE TABLE actor
(
    ActorID           INTEGER
        constraint actor_pk
            primary key,
    Name              TEXT,
    "Date of Birth"   DATE,
    "Birth City"      TEXT,
    "Birth Country"   TEXT,
    "Height (Inches)" INTEGER,
    Biography         TEXT,
    Gender            TEXT,
    Ethnicity         TEXT,
    NetWorth          TEXT
)

CREATE TABLE movie
(
    MovieID        INTEGER
        constraint movie_pk
            primary key,
    Title          TEXT,
    "MPAA Rating"  TEXT,
    Budget         INTEGER,
    Gross          INTEGER,
    "Release Date" TEXT,
    Genre          TEXT,
    Runtime        INTEGER,
    Rating         REAL,
    "Rating Count" INTEGER,
    Summary        TEXT
)

CREATE TABLE characters
(
    MovieID          INTEGER,
    ActorID          INTEGER,
    "Character Name" TEXT,
    creditOrder      INTEGER,
    pay              TEXT,
    screentime       TEXT,
    primary key (MovieID, ActorID),
    foreign key (ActorID) references actor(ActorID),
    foreign key (MovieID) references movie(MovieID)
)

### Question
Please list the names of the characters in the movie Look Who's Talking.

### Hint
movie Look Who's Talking refers to title = 'Look Who's Talking'

### SQL
SELECT T2.`Character Name` FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID WHERE T1.Title = 'Look Who''s Talking'
### Database Schema
CREATE TABLE actor
(
    ActorID           INTEGER
        constraint actor_pk
            primary key,
    Name              TEXT,
    "Date of Birth"   DATE,
    "Birth City"      TEXT,
    "Birth Country"   TEXT,
    "Height (Inches)" INTEGER,
    Biography         TEXT,
    Gender            TEXT,
    Ethnicity         TEXT,
    NetWorth          TEXT
)

CREATE TABLE movie
(
    MovieID        INTEGER
        constraint movie_pk
            primary key,
    Title          TEXT,
    "MPAA Rating"  TEXT,
    Budget         INTEGER,
    Gross          INTEGER,
    "Release Date" TEXT,
    Genre          TEXT,
    Runtime        INTEGER,
    Rating         REAL,
    "Rating Count" INTEGER,
    Summary        TEXT
)

CREATE TABLE characters
(
    MovieID          INTEGER,
    ActorID          INTEGER,
    "Character Name" TEXT,
    creditOrder      INTEGER,
    pay              TEXT,
    screentime       TEXT,
    primary key (MovieID, ActorID),
    foreign key (ActorID) references actor(ActorID),
    foreign key (MovieID) references movie(MovieID)
)

### Question
Which character has the longest screen time in the movie Batman?

### Hint
longest screen time refers to max(screentime); movie Batman refers to title = 'Batman'

### SQL
SELECT T2.`Character Name` FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID WHERE T1.Title = 'Batman' ORDER BY T2.screentime DESC LIMIT 1
### Database Schema
CREATE TABLE actor
(
    ActorID           INTEGER
        constraint actor_pk
            primary key,
    Name              TEXT,
    "Date of Birth"   DATE,
    "Birth City"      TEXT,
    "Birth Country"   TEXT,
    "Height (Inches)" INTEGER,
    Biography         TEXT,
    Gender            TEXT,
    Ethnicity         TEXT,
    NetWorth          TEXT
)

CREATE TABLE movie
(
    MovieID        INTEGER
        constraint movie_pk
            primary key,
    Title          TEXT,
    "MPAA Rating"  TEXT,
    Budget         INTEGER,
    Gross          INTEGER,
    "Release Date" TEXT,
    Genre          TEXT,
    Runtime        INTEGER,
    Rating         REAL,
    "Rating Count" INTEGER,
    Summary        TEXT
)

CREATE TABLE characters
(
    MovieID          INTEGER,
    ActorID          INTEGER,
    "Character Name" TEXT,
    creditOrder      INTEGER,
    pay              TEXT,
    screentime       TEXT,
    primary key (MovieID, ActorID),
    foreign key (ActorID) references actor(ActorID),
    foreign key (MovieID) references movie(MovieID)
)

### Question
Which actor played the role of Joker in the movie Batman?

### Hint
role of Joker refers to character_name = 'Joker'; movie Batman refers to title = 'Batman'

### SQL
SELECT T3.Name FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID INNER JOIN actor AS T3 ON T3.ActorID = T2.ActorID WHERE T1.Title = 'Batman' AND T2.`Character Name` = 'Joker'
### Database Schema
CREATE TABLE actor
(
    ActorID           INTEGER
        constraint actor_pk
            primary key,
    Name              TEXT,
    "Date of Birth"   DATE,
    "Birth City"      TEXT,
    "Birth Country"   TEXT,
    "Height (Inches)" INTEGER,
    Biography         TEXT,
    Gender            TEXT,
    Ethnicity         TEXT,
    NetWorth          TEXT
)

CREATE TABLE movie
(
    MovieID        INTEGER
        constraint movie_pk
            primary key,
    Title          TEXT,
    "MPAA Rating"  TEXT,
    Budget         INTEGER,
    Gross          INTEGER,
    "Release Date" TEXT,
    Genre          TEXT,
    Runtime        INTEGER,
    Rating         REAL,
    "Rating Count" INTEGER,
    Summary        TEXT
)

CREATE TABLE characters
(
    MovieID          INTEGER,
    ActorID          INTEGER,
    "Character Name" TEXT,
    creditOrder      INTEGER,
    pay              TEXT,
    screentime       TEXT,
    primary key (MovieID, ActorID),
    foreign key (ActorID) references actor(ActorID),
    foreign key (MovieID) references movie(MovieID)
)

### Question
Please list the names of the actors who played a role in the movie Batman.

### Hint
movie Batman refers to title = 'Batman'

### SQL
SELECT T3.Name FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID INNER JOIN actor AS T3 ON T3.ActorID = T2.ActorID WHERE T1.Title = 'Batman'
### Database Schema
CREATE TABLE actor
(
    ActorID           INTEGER
        constraint actor_pk
            primary key,
    Name              TEXT,
    "Date of Birth"   DATE,
    "Birth City"      TEXT,
    "Birth Country"   TEXT,
    "Height (Inches)" INTEGER,
    Biography         TEXT,
    Gender            TEXT,
    Ethnicity         TEXT,
    NetWorth          TEXT
)

CREATE TABLE movie
(
    MovieID        INTEGER
        constraint movie_pk
            primary key,
    Title          TEXT,
    "MPAA Rating"  TEXT,
    Budget         INTEGER,
    Gross          INTEGER,
    "Release Date" TEXT,
    Genre          TEXT,
    Runtime        INTEGER,
    Rating         REAL,
    "Rating Count" INTEGER,
    Summary        TEXT
)

CREATE TABLE characters
(
    MovieID          INTEGER,
    ActorID          INTEGER,
    "Character Name" TEXT,
    creditOrder      INTEGER,
    pay              TEXT,
    screentime       TEXT,
    primary key (MovieID, ActorID),
    foreign key (ActorID) references actor(ActorID),
    foreign key (MovieID) references movie(MovieID)
)

### Question
Which movie is the character Dr. Archibald 'Moonlight' Graham from?

### Hint
movie name refers to title; character Dr. Archibald 'Moonlight' Graham refers to character_name = 'Dr. Archibald 'Moonlight' Graham'

### SQL
SELECT T1.Title FROM movie AS T1 INNER JOIN characters AS T2 ON T1.MovieID = T2.MovieID WHERE T2.`Character Name` = 'Dr. Archibald ''Moonlight'' Graham'