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
Give the title and author's name of the papers published between 2000 and 2005 that include the topic optical properties.

### Hint
published between 2000 and 2005 refers to Year BETWEEN 2000 AND 2005; include the topic optical properties refers to Keyword LIKE '%optical properties%'

### SQL
SELECT T1.Title, T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Keyword LIKE '%optical properties%' AND T1.Year BETWEEN 2000 AND 2005 AND T1.Title <> ''
### 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 average number of papers published in the World Computer Congress each year?

### Hint
published in the World Computer Congress refers to FullName = 'World Computer Congress'; average refers to DIVIDE(COUNT(FullName = 'World Computer Congress'), COUNT(Id))

### SQL
SELECT CAST(SUM(CASE WHEN T2.FullName = 'International Congress Series' THEN 1 ELSE 0 END) AS REAL) / COUNT(T1.Id) AS Div1, T1.Year FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id GROUP BY T1.YEAR HAVING Div1 != 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
Give the Title and author's name of the books that were preprint in 1997.

### Hint
in 1997 refers to Year = 1997; books that were preprint refers to ConferenceId = 0 AND JournalId = 0

### SQL
SELECT DISTINCT T2.Name, T1.Title FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.ConferenceId = 0 AND T1.JournalId = 0 AND T1.Year = 1997 AND T1.Title <> ''
### 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
Write the titles of papers published by Adam Jones and the journal name in which it was published from 2005 to 2010.

### Hint
published from 2005 to 2010 refers to Year BETWEEN 2005 AND 2010; published by Adam Jones refers to Name = 'Adam Jones'

### SQL
SELECT T1.Title 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 T2.Name = 'Adam Jones' AND T1.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
How many authors is affiliated to the organization "Otterbein University"?

### Hint
Otterbein University is an Affiliation

### SQL
SELECT COUNT(Name) FROM Author WHERE Affiliation = 'Otterbein University'
### 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 authors drafted the paper "Subcellular localization of nuclease in barley aleurone"?

### Hint
'Subcellular localization of nuclease in barley aleurone' is the title of paper

### SQL
SELECT COUNT(DISTINCT T2.Name) FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Subcellular localization of nuclease in barley aleurone'
### 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 full name of the conference in which the paper titled "Extended Fuzzy Regression Models" was published?

### Hint
'Extended Fuzzy Regression Models' is the title of paper; full name of the conference refers to FullName

### SQL
SELECT T2.FullName FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T1.Title = 'Extended Fuzzy Regression Models'
### 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 published under the conference "Mathematics of Program Construction
"?

### Hint
'Mathematics of Program Construction' is the FullName of conference

### SQL
SELECT COUNT(T1.Id) FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T2.FullName = 'Mathematics of Program Construction'
### 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 paper titled "Open Sourcing Social Solutions (Building Communities of Change)"?

### Hint
'Open Sourcing Social Solutions (Building Communities of Change)' is a title of the paper; author refers to PaperAuthor.Name

### SQL
SELECT T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Open Sourcing Social Solutions (Building Communities of Change)'
### 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 all the title of the paper that Jianli Hua published.

### Hint
Jianli Hua is the author of a paper

### SQL
SELECT T1.Title FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T2.Name = 'Jianli Hua'
### 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 authored the paper titled "Testing timed automata
"?

### Hint
'Testing timed automata' is a title of a paper; Who authored refers to PaperAuthor.Name

### SQL
SELECT T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T1.Title = 'Testing timed 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
How many papers are published in year 2000 under the conference "SSPR"?

### Hint
SSPR is a ShortName; papers refers to Paper.Id

### SQL
SELECT COUNT(T1.Id) FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T1.Year = 2000 AND T2.ShortName = 'SSPR'
### 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 all the paper that the journal "Theoretical Computer Science
" published in 2003.

### Hint
'Theoretical Computer Science' is the FullName; paper refers to Title; published in 2003 refers to Year = 2003

### SQL
SELECT DISTINCT T1.Title FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id WHERE T2.FullName = 'Theoretical Computer Science' AND T1.Year = 2003 AND T1.Title <> ''
### 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 conference homepage URL of the paper titled "Quality evaluation of long duration audiovisual content"?

### Hint
homepage URL refers to HomePage; 'Quality evaluation of long duration audiovisual content' is the Title

### SQL
SELECT T2.HomePage FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T1.Title = 'Quality evaluation of long duration audiovisual content'
### 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
Among the author who drafted the paper "A Randomized Comparison of Sirolimus- Versus Paclitaxel-Eluting Stent Implantation in Patients With Diabetes Mellitus", which of them is/are affiliated with the Asan Medical Center, University of Ulsan College of Medicine, Seoul, Korea
?

### Hint
author refers to PaperAuthor.Name; 'A Randomized Comparison of Sirolimus- Versus Paclitaxel-Eluting Stent Implantation in Patients With Diabetes Mellitus' is the title; 'Asan Medical Center, University of Ulsan College of Medicine, Seoul, Korea' is an Affiliation

### SQL
SELECT T2.Name FROM Paper AS T1 INNER JOIN PaperAuthor AS T2 ON T1.Id = T2.PaperId WHERE T2.Affiliation = 'Asan Medical Center, University of Ulsan College of Medicine, Seoul, Korea' AND T1.Title = 'A Randomized Comparison of Sirolimus- Versus Paclitaxel-Eluting Stent Implantation in Patients With Diabetes Mellitus'
### 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 published under the journal "Software - Practice and Experience"?

### Hint
papers refers to Paper.Id; 'Software - Practice and Experience' is the FullName of a journal;

### SQL
SELECT COUNT(T1.Id) FROM Paper AS T1 INNER JOIN Journal AS T2 ON T1.JournalId = T2.Id WHERE T2.FullName = 'Software - Practice and Experience'
### 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 all the paper that were under the conference homepage URL "http://www.irma-international.org/".

### Hint
paper refers to Paper.Title; http://www.irma-international.org/ is the HomePage

### SQL
SELECT T1.Title FROM Paper AS T1 INNER JOIN Conference AS T2 ON T1.ConferenceId = T2.Id WHERE T2.HomePage = 'http://www.irma-international.org/'