Database schema:
CREATE TABLE Affiliation (
  affiliation_id NUMBER PRIMARY KEY,
  name TEXT,
  address TEXT
);
CREATE TABLE Author (
  author_id NUMBER PRIMARY KEY,
  name TEXT,
  email TEXT
);
CREATE TABLE Author_list (
  paper_id TEXT PRIMARY KEY,
  author_id NUMBER,
  affiliation_id NUMBER,
  FOREIGN KEY (affiliation_id) REFERENCES Affiliation(affiliation_id),
  FOREIGN KEY (author_id) REFERENCES Author(author_id),
  FOREIGN KEY (paper_id) REFERENCES Paper(paper_id)
);
CREATE TABLE Citation (
  paper_id TEXT PRIMARY KEY,
  cited_paper_id TEXT,
  FOREIGN KEY (cited_paper_id) REFERENCES Paper(paper_id),
  FOREIGN KEY (paper_id) REFERENCES Paper(paper_id)
);
CREATE TABLE Paper (
  paper_id TEXT PRIMARY KEY,
  title TEXT,
  venue TEXT,
  year NUMBER
);

Question: Count the number of authors.
SQL: SELECT count(*) FROM Author

Question: Find the total number of papers for each affiliation.
SQL: SELECT count(DISTINCT T2.paper_id) ,  T1.name FROM Affiliation AS T1 JOIN Author_list AS T2 ON T1.affiliation_id  =  T2.affiliation_id GROUP BY T1.affiliation_id

Question: How many papers has each affiliation published?
SQL: SELECT count(DISTINCT T2.paper_id) ,  T1.name FROM Affiliation AS T1 JOIN Author_list AS T2 ON T1.affiliation_id  =  T2.affiliation_id GROUP BY T1.affiliation_id

Question: What is the venue and year with the most number of publications?
SQL: SELECT venue ,  YEAR FROM paper GROUP BY venue ,  YEAR ORDER BY count(*) DESC LIMIT 1

Question: List the names of all authors and their number of papers in descending order by number of papers.
SQL: SELECT T1.name ,  count(*) FROM Author AS T1 JOIN Author_list AS T2 ON T1.author_id  =  T2.author_id GROUP BY T1.author_id ORDER BY count(*) DESC

Question: What is the title of the paper which cites the most other papers?
SQL: SELECT T2.title FROM Citation AS T1 JOIN Paper AS T2 ON T2.paper_id  =  T1.paper_id GROUP BY T1.paper_id ORDER BY count(*) DESC LIMIT 1

Question: How many reference papers does paper with id D12-1027 have?
SQL: SELECT count(*) FROM Citation WHERE paper_id  =  "D12-1027"

Question: How many collaborators has Mckeown , Kathleen had ?
SQL: select count (distinct t2.author_id) from author_list as t1 join author_list as t2 on t1.paper_id  =  t2.paper_id and t1.author_id != t2.author_id join author as t3 on t1.author_id  =  t3.author_id where t3.name = "mckeown ,  kathleen"

Question: List names of all authors who have more than 50 papers.
SQL: SELECT T1.name FROM Author AS T1 JOIN Author_list AS T2 ON T1.author_id  =  T2.author_id GROUP BY T1.author_id HAVING count(*)  >  50

Question: Find the name of the affiliation whose address contains 'China' and publishes the greatest number of papers.
SQL: SELECT T1.name FROM Affiliation AS T1 JOIN Author_list AS T2 ON T1.affiliation_id  =  T2.affiliation_id WHERE T1.address LIKE "%China%" GROUP BY T1.affiliation_id ORDER BY count(*) DESC LIMIT 1

Question: Find all papers with titles and paper IDs collaborated by Stanford University and Columbia University.
SQL: SELECT T1.title ,  T1.paper_id FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Affiliation AS T3 ON T2.affiliation_id  =  T3.affiliation_id WHERE T3.name LIKE "Stanford University" INTERSECT SELECT T1.title ,  T1.paper_id FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Affiliation AS T3 ON T2.affiliation_id  =  T3.affiliation_id WHERE T3.name LIKE "Columbia University"

Question: Count the number of papers Mckeown , Kathleen has cited .
SQL: select count(*) from citation as t1 join author_list as t2 on t1.paper_id  =  t2.paper_id join author as t3 on t2.author_id  =  t3.author_id where t3.name = "mckeown ,  kathleen"

Question: Find the titles of papers that have more than 50 citations.
SQL: SELECT T2.title FROM Citation AS T1 JOIN Paper AS T2 ON T1.cited_paper_id  =  T2.paper_id GROUP BY T1.cited_paper_id HAVING count(*)  >  50

Question: Count the number of affiliations.
SQL: SELECT count(*) FROM Affiliation

Question: What are the names of authors who have exactly 1 paper?
SQL: SELECT T1.name FROM Author AS T1 JOIN Author_list AS T2 ON T1.author_id  =  T2.author_id GROUP BY T1.author_id HAVING count(*)  =  1

Question: What are the ids and titles for papers that have never been cited?
SQL: SELECT paper_id ,  title FROM Paper WHERE paper_id NOT IN (SELECT cited_paper_id FROM Citation)

Question: How many papers does Stanford University have between 2000 and 2009?
SQL: SELECT count(DISTINCT T1.paper_id) FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Affiliation AS T3 ON T2.affiliation_id  =  T3.affiliation_id WHERE T1.year  >=  2000 AND T1.year  <=  2009 AND T3.name LIKE "Stanford University"

Question: Count the number of citations Mckeown , Kathleen has .
SQL: select count(*) from citation as t1 join author_list as t2 on t1.cited_paper_id  =  t2.paper_id join author as t3 on t2.author_id  =  t3.author_id where t3.name = "mckeown ,  kathleen"

Question: List top 10 most cited papers and their numbers of citations.
SQL: SELECT paper_id ,  count(*) FROM Citation GROUP BY cited_paper_id ORDER BY count(*) DESC LIMIT 10

Question: What are the titles and paper ids co-authored by Mckeown, Kathleen and Rambow, Owen?
SQL: SELECT T1.title ,  T1.paper_id FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Author AS T3 ON T2.author_id  =  T3.author_id WHERE T3.name LIKE "%Mckeown ,  Kathleen%" INTERSECT SELECT T1.title ,  T1.paper_id FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Author AS T3 ON T2.author_id  =  T3.author_id WHERE T3.name LIKE "%Rambow ,  Owen%"

Question: What are the names of the top 3 affiliations that have the most papers in year 2009?
SQL: SELECT T3.name FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Affiliation AS T3 ON T2.affiliation_id  =  T3.affiliation_id WHERE T1.year  =  2009 GROUP BY T2.affiliation_id ORDER BY count(*) DESC LIMIT 3

Question: Count the number of papers published by Columbia University in 2009.
SQL: SELECT count(DISTINCT T1.paper_id) FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Affiliation AS T3 ON T2.affiliation_id  =  T3.affiliation_id WHERE T3.name LIKE "Columbia University" AND T1.year  =  2009