lianghsun/bird-text2sql-bench huggingface dataset:
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
What is the proportion of the papers that have the keyword "cancer"? Please provide a list of authors and their affiliations.

### Hint
Proportion refer to DIVIDE(COUNT(Keyword = ’cancer’), COUNT(PaperID))

### SQL
SELECT CAST(SUM(CASE WHEN T1.Keyword = 'cancer' THEN 1 ELSE 0 END) AS REAL) / COUNT(T1.Id), T2.Name, T2.Affiliation FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
What is the name of author with the ID of 1722?

### SQL
SELECT Name FROM Author WHERE Id = 1722
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
How many papers are preprint or not published?

### Hint
preprint or not published refer to Year = 0;

### SQL
SELECT COUNT(Id) FROM Paper WHERE Year = 0 OR (ConferenceId = 0 AND JournalId = 0)
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
List the name of the author that affiliated with University of Illinois Chicago?

### Hint
'University of Illinois Chicago' is an affiliation

### SQL
SELECT Name FROM Author WHERE Affiliation = 'University of Illinois Chicago'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
How many papers were published in 2005. Calculate the difference between the number of paper published in 2005 and the number of paper published in the previous year.

### Hint
published in 2005 refer to Year = 2005; Difference refer to SUBTRACT(SUM(Year = 2005). SUM(Year = 2004))

### SQL
SELECT SUM(CASE WHEN Year = 2005 THEN 1 ELSE 0 END) , SUM(CASE WHEN year = 2005 THEN 1 ELSE 0 END) - SUM(CASE WHEN year = 2004 THEN 1 ELSE 0 END) AS diff FROM Paper
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
State the title of papers published in the Ibm Journal of Research and Development.

### Hint
Ibm Journal of Research and Development refer to FullName

'Ibm Journal of Research and Development' is the full name of paper

### SQL
SELECT T2.Title FROM Journal AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.JournalId WHERE T1.FullName = 'Ibm Journal of Research and Development'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
State the name and affiliation of author for the 'Education, democracy and growth' paper?

### Hint
Education, democracy and growth' refer to title of paper


### SQL
SELECT T2.Name, T2.Affiliation FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Education, democracy and growth'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
How many author published papers in the 'IEEE Computer' journal?

### Hint
IEEE Computer refer to FullName; How many author published papers refer to COUNT(PaperAuthor.Name) where FullName = ’IEEE Computer’

### SQL
SELECT COUNT(T2.Name) FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId INNER JOIN Journal AS T3 ON T1.JournalId = T3.Id WHERE T3.FullName = 'IEEE Computer'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Where was the 'A context-based navigation paradigm for accessing Web data' paper published? State the name of the conference.

### Hint
A context-based navigation paradigm for accessing Web data' is the title of paper; name of conference refer to FullName


### SQL
SELECT T2.FullName FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T1.Title = 'A context-based navigation paradigm for accessing Web data'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
How many papers were published in International Workshop on Inductive Logic Programming from 2001 to 2009?

### Hint
From 2001 to 2009 refer to Year 2001 BETWEEN 2009; 'International Workshop on Inductive Logic Programming' refer to Conference.FullName


### SQL
SELECT COUNT(T1.Id) FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T2.FullName = 'International Workshop on Inductive Logic Programming' AND T1.Year BETWEEN 2001 AND 2009
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Calculate the average of authors for each paper from the year of 1990 to 2000.

### Hint
Average refer to DIVIDE(COUNT(AuthorID where Year = 1990 BETWEEN 2000), COUNT(Title where Year = 1990 BETWEEN 2000))

### SQL
SELECT CAST(COUNT(DISTINCT T2.AuthorId) AS REAL) / COUNT(DISTINCT T1.Title) FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Year BETWEEN 1990 AND 2000
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Indicate the year and a full name of the journal in which the publication named 'Area Effects in Cepaea' was published.

### Hint
'Area Effects in Cepaea' is the title of paper

### SQL
SELECT T1.Year, T2.FullName FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id WHERE T1.Title = 'Area Effects in Cepaea'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Who is the author of the publication named 'Real-Time Automata'?

### Hint
'Real-Time Automata' is the title of paper; publication refers to title;

### SQL
SELECT T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Real-Time Automata'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Provide the name of the author who is affiliated with the organization named 'ABB Electrical Machines'.

### Hint
'ABB Electrical Machines' is an affiliation

### SQL
SELECT Name FROM Author WHERE Affiliation = 'ABB Electrical Machines'
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Provide the number of publications published in the journal named 'Academic Medicine' between 2005 and 2010.

### Hint
'Academic Medicine' is the FullName of journal; between 2005 and 2010 refer to Year 2005 BETWEEN 2010

### SQL
SELECT COUNT(T2.JournalId) FROM Journal AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.JournalId WHERE T1.FullName = 'Academic Medicine' AND T2.Year BETWEEN 2005 AND 2010
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
Provide the title of the latest publication published by it's author 'Zuliang Du'.

### Hint
'Zuliang Du' is the name of paper author; latest publication refers to MAX(Year)

### SQL
SELECT T2.Title FROM PaperAuthor AS T1 INNER JOIN Paper AS T2 ON T1.PaperId = T2.Id WHERE T1.Name = 'Zuliang Du' ORDER BY T2.Year DESC LIMIT 1
### Database Schema
CREATE TABLE "Author"
(
    Id          INTEGER
        constraint Author_pk
            primary key,
    Name        TEXT,
    Affiliation TEXT
)

CREATE TABLE "Conference"
(
    Id        INTEGER
        constraint Conference_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE "Journal"
(
    Id        INTEGER
        constraint Journal_pk
            primary key,
    ShortName TEXT,
    FullName  TEXT,
    HomePage  TEXT
)

CREATE TABLE Paper
(
    Id           INTEGER
            primary key,
    Title        TEXT,
    Year         INTEGER,
    ConferenceId INTEGER,
    JournalId    INTEGER,
    Keyword      TEXT,
    foreign key (ConferenceId) references Conference(Id),
    foreign key (JournalId) references Journal(Id)
)

CREATE TABLE PaperAuthor
(
    PaperId     INTEGER,
    AuthorId    INTEGER,
    Name        TEXT,
    Affiliation TEXT,
    foreign key (PaperId) references Paper(Id),
    foreign key (AuthorId) references Author(Id)
)

### Question
How many publications were published in relation to the conference 'Adaptive Multimedia Retrieval' in 2007?

### Hint
'Adaptive Multimedia Retrieval is the FullName of paper; in 2007 refer to Year = 2007

### SQL
SELECT COUNT(T2.ConferenceId) FROM Conference AS T1 INNER JOIN Paper AS T2 ON T1.Id = T2.ConferenceId WHERE T1.FullName = 'Adaptive Multimedia Retrieval' AND T2.Year = 2007