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
Please list the IDs of the object samples in class no. 297 in image no.1.

### Hint
IDs of the object samples refers to OBJ_SAMPLE_ID; class no. 297 in image no.1 refers to IMG_ID = 1 and OBJ_CLASS_ID = 297

### SQL
SELECT OBJ_SAMPLE_ID FROM IMG_OBJ WHERE IMG_ID = 1 AND OBJ_CLASS_ID = 297
### 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 self-relations are there between the object samples in image no.5?

### Hint
self-relations refers to OBJ1_SAMPLE_ID = OBJ2_SAMPLE_ID; image no.5 refers to IMG_ID = 5

### SQL
SELECT SUM(CASE WHEN IMG_ID = 5 THEN 1 ELSE 0 END) FROM IMG_REL WHERE OBJ1_SAMPLE_ID = OBJ2_SAMPLE_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
What is the bounding box of the object sample in image no.5 that has a self-relation?

### Hint
bounding box of the object sample refers to (x, y, W, H); image no.5 refers to IMG_ID = 5; has a self-relation refers to OBJ1_SAMPLE_ID = OBJ2_SAMPLE_ID

### SQL
SELECT T2.X, T2.Y, T2.W, T2.H FROM IMG_REL AS T1 INNER JOIN IMG_OBJ AS T2 ON T1.IMG_ID = T2.IMG_ID WHERE T1.IMG_ID = 5 AND T1.OBJ1_SAMPLE_ID = T1.OBJ2_SAMPLE_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 object samples in image no.1 are in the class of "man"?

### Hint
object samples refers to OBJ_CLASS_ID; image no.1 refers to IMG_ID = 1; in the class of "man" refers to OBJ_CLASS = 'man'

### SQL
SELECT SUM(CASE WHEN T1.OBJ_CLASS = 'man' THEN 1 ELSE 0 END) FROM OBJ_CLASSES AS T1 INNER JOIN IMG_OBJ AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T2.IMG_ID = 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
How many images have at least one object sample in the class of "man"?

### Hint
have at least one object sample in the class of "man" refers to count(IMG_ID where OBJ_CLASS = 'man') > = 1

### SQL
SELECT COUNT(T.IMG_ID) FROM ( SELECT T2.IMG_ID FROM OBJ_CLASSES AS T1 INNER JOIN IMG_OBJ AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T1.OBJ_CLASS = 'man' GROUP BY T2.IMG_ID ) T
### 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
Please list the classes of all the object samples in image no.1.

### Hint
classes of all the object samples refers to OBJ_CLASS; image no.1 refers to IMG_ID = 1

### SQL
SELECT T1.OBJ_CLASS FROM OBJ_CLASSES AS T1 INNER JOIN IMG_OBJ AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID WHERE T2.IMG_ID = 1 GROUP BY T1.OBJ_CLASS
### 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 relation between object sample no.8 and object sample no.4 in image no.1?

### Hint
relation refers to PRED_CLASS; object sample no.8 and object sample no.4 refers to OBJ1_SAMPLE_ID = 8 AND OBJ2_SAMPLE_ID = 4; image no.1 refers to IMG_ID = 1

### SQL
SELECT T1.PRED_CLASS FROM PRED_CLASSES AS T1 INNER JOIN IMG_REL AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T2.IMG_ID = 1 AND T2.OBJ1_SAMPLE_ID = 8 AND T2.OBJ2_SAMPLE_ID = 4
### 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 pairs of object samples in image no.1 have the relation of "parked on"?

### Hint
pairs of object samples refers to OBJ1_SAMPLE_ID and OBJ2_SAMPLE_ID; image no.1 refers to IMG_ID = 1; relation of "parked on" refers to PRED_CLASS = 'parked on'

### SQL
SELECT SUM(CASE WHEN T1.PRED_CLASS = 'parked on' THEN 1 ELSE 0 END) FROM PRED_CLASSES AS T1 INNER JOIN IMG_REL AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T2.IMG_ID = 1 AND T2.OBJ1_SAMPLE_ID != OBJ2_SAMPLE_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
Please list all the predicted relation classes of object sample no.14 in image no.1.

### Hint
predicted relation classes refers to PRED_CLASS; object sample no.14 in image no.1 refers to OBJ1_SAMPLE_ID = 14 AND OBJ2_SAMPLE_ID = 14 and IMG_ID = 1

### SQL
SELECT T1.PRED_CLASS FROM PRED_CLASSES AS T1 INNER JOIN IMG_REL AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T2.OBJ1_SAMPLE_ID = 14 AND T2.OBJ2_SAMPLE_ID = 14
### 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 have at least one pair of object samples with the relation "parked on"?

### Hint
How many images have at least one pair of object samples with the relation "parked on" refers to count(IMG_ID) where OBJ1_SAMPLE_ID ! = OBJ2_SAMPLE_ID and PRED_CLASS = 'parked on'

### SQL
SELECT SUM(CASE WHEN T1.PRED_CLASS = 'parked on' THEN 1 ELSE 0 END) FROM PRED_CLASSES AS T1 INNER JOIN IMG_REL AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T2.OBJ1_SAMPLE_ID != T2.OBJ2_SAMPLE_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
Please list the IDs of all the images with more than 2 pairs of object samples with the relation "parked on".

### Hint
IDs of all the images refers to IMG_ID; relation "parked on" refers to PRED_CLASS = 'parked on'; more than 2 pairs refers to count(IMG_ID) where OBJ1_SAMPLE_ID ! = OBJ2_SAMPLE_ID

### SQL
SELECT T2.IMG_ID FROM PRED_CLASSES AS T1 INNER JOIN IMG_REL AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T1.PRED_CLASS = 'parked on' AND T2.OBJ1_SAMPLE_ID != T2.OBJ2_SAMPLE_ID GROUP BY T2.IMG_ID HAVING COUNT(T2.IMG_ID) > 2