CREATE TABLE Inst (
    instID NUMBER PRIMARY KEY,
    name TEXT,
    country TEXT
);
CREATE TABLE Authors (
    authID NUMBER PRIMARY KEY,
    lname TEXT,
    fname TEXT
);
CREATE TABLE Papers (
    paperID NUMBER PRIMARY KEY,
    title TEXT
);
CREATE TABLE Authorship (
    authID NUMBER PRIMARY KEY,
    instID NUMBER,
    paperID NUMBER,
    authOrder NUMBER,
    FOREIGN KEY ("paperID") REFERENCES Papers("paperID"),
    FOREIGN KEY ("instID") REFERENCES Inst("instID"),
    FOREIGN KEY ("authID") REFERENCES Authors("authID")
);

Question: How many papers are written by authors from the institution "University of Pennsylvania"?
SQL: SELECT count(DISTINCT t1.title) FROM papers AS t1 JOIN authorship AS t2 ON t1.paperid  =  t2.paperid JOIN inst AS t3 ON t2.instid  =  t3.instid WHERE t3.name  =  "University of Pennsylvania"

Question: Find the papers which have "Olin Shivers" as an author.
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Olin" AND t1.lname  =  "Shivers"

Question: Which papers did the author "Olin Shivers" write? Give me the paper titles.
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Olin" AND t1.lname  =  "Shivers"

Question: Which papers have "Stephanie Weirich" as an author?
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Stephanie" AND t1.lname  =  "Weirich"

Question: Find the titles of the papers the author "Stephanie Weirich" wrote.
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t1.fname  =  "Stephanie" AND t1.lname  =  "Weirich"

Question: Which paper is published in an institution in "USA" and have "Turon" as its second author?
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "USA" AND t2.authorder  =  2 AND t1.lname  =  "Turon"

Question: Find papers whose second author has last name "Turon" and is affiliated with an institution in the country "USA".
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "USA" AND t2.authorder  =  2 AND t1.lname  =  "Turon"

Question: Find the titles of papers whose first author is affiliated with an institution in the country "Japan" and has last name "Ohori"?
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "Japan" AND t2.authorder  =  1 AND t1.lname  =  "Ohori"

Question: Which papers' first author is affiliated with an institution in the country "Japan" and has last name "Ohori"? Give me the titles of the papers.
SQL: SELECT t3.title FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid JOIN inst AS t4 ON t2.instid  =  t4.instid WHERE t4.country  =  "Japan" AND t2.authorder  =  1 AND t1.lname  =  "Ohori"

Question: What is the last name of the author that has published the most papers?
SQL: SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.fname ,  t1.lname ORDER BY count(*) DESC LIMIT 1

Question: Which author has written the most papers? Find his or her last name.
SQL: SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.fname ,  t1.lname ORDER BY count(*) DESC LIMIT 1

Question: Retrieve the country that has published the most papers.
SQL: SELECT t1.country FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.country ORDER BY count(*) DESC LIMIT 1

Question: Find the country that the most papers are affiliated with.
SQL: SELECT t1.country FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.country ORDER BY count(*) DESC LIMIT 1

Question: Find the name of the organization that has published the largest number of papers.
SQL: SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1

Question: Which institution has the most papers? Find the name of the institution.
SQL: SELECT t1.name FROM inst AS t1 JOIN authorship AS t2 ON t1.instid  =  t2.instid JOIN papers AS t3 ON t2.paperid  =  t3.paperid GROUP BY t1.name ORDER BY count(*) DESC LIMIT 1

Question: Find the titles of the papers that contain the word "ML".
SQL: SELECT title FROM papers WHERE title LIKE "%ML%"

Question: Which papers have the substring "ML" in their titles? Return the titles of the papers.
SQL: SELECT title FROM papers WHERE title LIKE "%ML%"

Question: Which paper's title contains the word "Database"?
SQL: SELECT title FROM papers WHERE title LIKE "%Database%"

Question: Which papers have the substring "Database" in their titles? Show the titles of the papers.
SQL: SELECT title FROM papers WHERE title LIKE "%Database%"

Question: Find the first names of all the authors who have written a paper with title containing the word "Functional".
SQL: SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Functional%"

Question: Who has written a paper that has the word "Functional" in its title? Return the first names of the authors.
SQL: SELECT t1.fname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Functional%"

Question: Find the last names of all the authors that have written a paper with title containing the word "Monadic".
SQL: SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Monadic%"

Question: Which authors have written a paper with title containing the word "Monadic"? Return their last names.
SQL: SELECT t1.lname FROM authors AS t1 JOIN authorship AS t2 ON t1.authid  =  t2.authid JOIN papers AS t3 ON t2.paperid  =  t3.paperid WHERE t3.title LIKE "%Monadic%"

Question: Retrieve the title of the paper that has the largest number of authors.
SQL: SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid  =  t2.paperid WHERE t1.authorder  =  (SELECT max(authorder) FROM authorship)

Question: Which paper has the most authors? Give me the paper title.
SQL: SELECT t2.title FROM authorship AS t1 JOIN papers AS t2 ON t1.paperid  =  t2.paperid WHERE t1.authorder  =  (SELECT max(authorder) FROM authorship)

Question: What is the first name of the author with last name "Ueno"?
SQL: SELECT fname FROM authors WHERE lname  =  "Ueno"

Question: Which authors have last name "Ueno"? List their first names.
SQL: SELECT fname FROM authors WHERE lname  =  "Ueno"

Question: Find the last name of the author with first name "Amal".
SQL: SELECT lname FROM authors WHERE fname  =  "Amal"

Question: Which authors have first name "Amal"? List their last names.
SQL: SELECT lname FROM authors WHERE fname  =  "Amal"

Question: Find the first names of all the authors ordered in alphabetical order.
SQL: SELECT fname FROM authors ORDER BY fname

Question: Sort the first names of all the authors in alphabetical order.
SQL: SELECT fname FROM authors ORDER BY fname