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 attributes are used to describe the wall on image no. 27.

### Hint
What attributes refers to ATT_CLASS; wall on image no. 27 refers to OBJ_CLASS = 'wall' and IMG_ID = 27

### SQL
SELECT T4.ATT_CLASS FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID INNER JOIN IMG_OBJ_ATT AS T3 ON T1.IMG_ID = T3.IMG_ID INNER JOIN ATT_CLASSES AS T4 ON T3.ATT_CLASS_ID = T4.ATT_CLASS_ID WHERE T2.OBJ_CLASS = 'wall' AND T1.IMG_ID = 27 GROUP BY T4.ATT_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
Name the object element that is described as being scattered on image no. 10.

### Hint
Name the object element refers to OBJ_CLASS; scattered refers to ATT_CLASS = 'scattered'; image no. 10 refers to IMG_ID = 10

### 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 INNER JOIN IMG_OBJ_ATT AS T3 ON T1.IMG_ID = T3.IMG_ID INNER JOIN ATT_CLASSES AS T4 ON T3.ATT_CLASS_ID = T4.ATT_CLASS_ID WHERE T4.ATT_CLASS = 'scattered' AND T1.IMG_ID = 10 GROUP BY T2.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
How many images contain 'bridge' as an object element?

### Hint
images refers to IMG_ID; 'bridge' as an object element refers to OBJ_CLASS = 'bridge'

### 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 = 'bridge'
### 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 elements are there on average in each image?

### Hint
object elements refers to OBJ_CLASS_ID; average = divide(count(OBJ_CLASS_ID), count(IMG_ID))

### SQL
SELECT CAST(COUNT(OBJ_CLASS_ID) AS REAL) / COUNT(DISTINCT IMG_ID) FROM IMG_OBJ
### 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 colour is the van that can be spotted in image no. 1?

### Hint
colour refers to ATT_CLASS; van refers to OBJ_CLASS = 'van'; image no. 1 refers to IMG_ID = 1

### SQL
SELECT T4.ATT_CLASS FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID INNER JOIN IMG_OBJ_ATT AS T3 ON T1.IMG_ID = T3.IMG_ID INNER JOIN ATT_CLASSES AS T4 ON T3.ATT_CLASS_ID = T4.ATT_CLASS_ID WHERE T2.OBJ_CLASS = 'van' AND T1.IMG_ID = 1 GROUP BY T4.ATT_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
Describe the objects, their attributes, and the relationships that comprise the scene on image no. 1 within the bounding box, represented as (388, 369, 48, 128).

### Hint
objects refers to OBJ_CLASS; attributes refers to ATT_CLASS; relationships refers to PRED_CLASS; image no. 1 refers to IMG_ID = 1; bounding box, represented as (388, 369, 48, 128) refers to X = 388 and Y = 369 and W = 48 and H = 128

### SQL
SELECT DISTINCT T2.OBJ_CLASS, T4.ATT_CLASS, T6.PRED_CLASS FROM IMG_OBJ AS T1 INNER JOIN OBJ_CLASSES AS T2 ON T1.OBJ_CLASS_ID = T2.OBJ_CLASS_ID INNER JOIN IMG_OBJ_ATT AS T3 ON T1.IMG_ID = T3.IMG_ID INNER JOIN ATT_CLASSES AS T4 ON T3.ATT_CLASS_ID = T4.ATT_CLASS_ID INNER JOIN IMG_REL AS T5 ON T1.IMG_ID = T5.IMG_ID INNER JOIN PRED_CLASSES AS T6 ON T5.PRED_CLASS_ID = T6.PRED_CLASS_ID WHERE T1.IMG_ID = 1 AND T1.X = 388 AND T1.Y = 369 AND T1.W = 48 AND T1.H = 128
### 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 relationship between object sample no. 25 and object sample no. 2 on image no. 1?

### Hint
relationship refers to PRED_CLASS; object sample no. 25 and object sample no. 2 refers to OBJ1_SAMPLE_ID = 25 and OBJ2_SAMPLE_ID = 2; image no. 1 refers to IMG_ID = 1

### SQL
SELECT T2.PRED_CLASS FROM IMG_REL AS T1 INNER JOIN PRED_CLASSES AS T2 ON T1.PRED_CLASS_ID = T2.PRED_CLASS_ID WHERE T1.IMG_ID = 1 AND T1.OBJ1_SAMPLE_ID = 25 AND T1.OBJ2_SAMPLE_ID = 2
### 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 attributes are related to the object sample no. 7 on image no. 4?

### Hint
How many attributes refers to ATT_CLASS_ID; object sample no. 7 on image no. 4 refers to IMG_ID = 4 and OBJ_SAMPLE_ID = 7

### SQL
SELECT COUNT(ATT_CLASS_ID) FROM IMG_OBJ_ATT WHERE IMG_ID = 4 AND OBJ_SAMPLE_ID = 7
### 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 elements can be detected on image no. 31?

### Hint
How many object elements refers to OBJ_CLASS_ID; image no. 31 refers to IMG_ID = 31

### SQL
SELECT COUNT(OBJ_CLASS_ID) FROM IMG_OBJ WHERE IMG_ID = 31
### 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
On image no. 20, identify the attribute ID that is composed of the highest number of objects.

### Hint
image no. 20 refers to IMG_ID = 20; attribute ID refers to ATT_CLASS_ID; highest number of objects refers to max(count(ATT_CLASS_ID))

### SQL
SELECT ATT_CLASS_ID FROM IMG_OBJ_ATT WHERE IMG_ID = 20 GROUP BY ATT_CLASS_ID ORDER BY COUNT(ATT_CLASS_ID) 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
Define the bounding box of the object sample no. 7 on image no. 42.

### Hint
bounding box of the object refers to (X, Y, W, H); sample no.7 on image no.42 refers to IMG_ID = 42 and OBJ_SAMPLE_ID = 7

### SQL
SELECT X, Y, W, H FROM IMG_OBJ WHERE IMG_ID = 42 AND OBJ_SAMPLE_ID = 7