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
What is the bounding box of "spoon" in image id 1344?

### Hint
the bounding box refers to X, Y, W, H from IMG_OBJ; image id 1344 refers to IMG_ID = 1344; "spoon" refers to OBJ_CLASS = 'spoon';

### SQL
SELECT T1.X, T1.Y, T1.W, 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 = 1344 AND T2.OBJ_CLASS = 'spoon'
### 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 percentage of "surface" object samples in image No.2654?

### Hint
DIVIDE(SUM(OBJ_CLASS_ID where OBJ_CLASS = 'surface'), COUNT(OBJ_CLASS_ID)) as percentage where IMG_ID = 2654;

### SQL
SELECT CAST(SUM(CASE WHEN T2.OBJ_CLASS = 'surface' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T1.OBJ_CLASS_ID) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T1.IMG_ID = 2654
### 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 images include the "wood" objects?

### Hint
images refer to IMG_ID; "wood" objects refer to OBJ_CLASS = 'wood';

### SQL
SELECT COUNT(DISTINCT T1.IMG_ID) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T2.OBJ_CLASS = 'wood'
### 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
State the object class of the image with tallest bounding box.

### Hint
bounding box refers to X, Y, W, H from IMG_OBJ; tallest relates to the height of the bounding box which refers to MAX(H); object class refers to OBJ_CLASS;

### SQL
SELECT T2.OBJ_CLASS FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID ORDER BY T1.H 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
Calculate the percentage of "airplane" object class in the table.

### Hint
DIVIDE(SUM(OBJ_SAMPLE_ID where OBJ_CLASS = 'airplane'), COUNT(OBJ_CLASS)) as percentage;

### SQL
SELECT CAST(SUM(CASE WHEN T2.OBJ_CLASS = 'airplane' THEN 1 ELSE 0 END) AS REAL) * 100 / COUNT(T2.OBJ_CLASS) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID
### 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 samples of animal objects are there in image no.660?

### Hint
samples of animal objects refer to OBJ_SAMPLE_ID where OBJ_CLASS = 'animal'; image no.660 refers to IMG_ID = 660;

### SQL
SELECT COUNT(T1.IMG_ID) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T2.OBJ_CLASS = 'animal' AND T1.IMG_ID = 660
### 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 number of samples of "bed" object are there in the image No.1098?

### Hint
samples of "bed" object refer to OBJ_SAMPLE_ID where OBJ_CLASS = 'bed'; image No.1098 refers to IMG_ID = 1098;

### SQL
SELECT SUM(CASE WHEN T2.OBJ_CLASS = 'bed' THEN 1 ELSE 0 END) FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T1.IMG_ID = 1098
### 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 object class of the image with lowest bounding box.

### Hint
bounding box refers to X, Y, W, H from IMG_OBJ; lowest relates to the height of the bounding box which refers to MIN(H);

### SQL
SELECT T2.OBJ_CLASS FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID ORDER BY T1.H 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
Indicating the bounding box of "kitchen" in image id 250.

### Hint
bounding box refers to X, Y, W, H from IMG_OBJ; "kitchen" in image id 250 refers to OBJ_CLASS = 'kitchen' where IMG_ID = 250;

### SQL
SELECT T1.X, T1.Y, T1.W, 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 = 250 AND T2.OBJ_CLASS = 'kitchen'
### 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
Which images have more than 20 object samples?

### Hint
images have more than 20 object samples refer to IMG_ID where COUNT(OBJ_SAMPLE_ID) > 20;

### SQL
SELECT IMG_ID FROM IMG_OBJ GROUP BY IMG_ID HAVING COUNT(IMG_ID) > 20
### 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
Which object in image 8 is the widest? State its object sample ID.

### Hint
widest relates to the width of the bounding
box of the object which refers to MAX(W); object in image 8 refers to OBJ_SAMPLE_ID where IMG_ID = 8;

### SQL
SELECT OBJ_SAMPLE_ID FROM IMG_OBJ WHERE IMG_ID = 8 ORDER BY W DESC LIMIT 1