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: What are the 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: Which venue has the fewest publications?
SQL: SELECT venue FROM paper GROUP BY venue ORDER BY count(*) LIMIT 1

Question: Count the number of papers Stanford University had 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: What was the venue and year with the most publications?
SQL: SELECT venue ,  YEAR FROM paper GROUP BY venue ,  YEAR ORDER BY count(*) DESC LIMIT 1

Question: What are the titles and paper ids for papers that have Mckeown, Kathleen or Rambow, Owen in their author list?
SQL: SELECT DISTINCT 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%" OR T3.name LIKE "%Rambow ,  Owen%"

Question: What are the titles for papers with 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: What is the title of the paper that has most number of authors?
SQL: SELECT T2.title FROM Author_list AS T1 JOIN Paper AS T2 ON T1.paper_id  =  T2.paper_id GROUP BY T2.paper_id ORDER BY count(*) DESC LIMIT 1

Question: How many authors have not published a paper with more than 50 citations?
SQL: SELECT count(*) FROM Author WHERE Author_id NOT IN ( SELECT T2.author_id FROM Citation AS T1 JOIN Author_list AS T2 ON T1.cited_paper_id  =  T2.paper_id GROUP BY T1.cited_paper_id HAVING count(DISTINCT T1.paper_id)  >  50)

Question: Which author had the most papers in the year 2009?
SQL: SELECT T3.name FROM Paper AS T1 JOIN Author_list AS T2 ON T1.paper_id  =  T2.paper_id JOIN Author AS T3 ON T3.author_id  =  T2.author_id WHERE T1.year  =  2009 GROUP BY T2.author_id ORDER BY count(*) DESC LIMIT 1

Question: What are the names of authors who published in both NAACL and ACL in 2009?
SQL: SELECT name FROM Author WHERE author_id IN (SELECT T1.author_id FROM Author_list AS T1 JOIN Paper AS T2 ON T1.paper_id  =  T2.paper_id WHERE T2.venue  =  "ACL" AND T2.year  =  2009 INTERSECT SELECT T1.author_id FROM Author_list AS T1 JOIN Paper AS T2 ON T1.paper_id  =  T2.paper_id WHERE T2.venue  =  "NAACL" AND T2.year  =  2009)

Question: Count the number of collaborators that Mckeown , Kathleen has 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 all venues and years for papers ordered by year.
SQL: SELECT DISTINCT venue ,  YEAR FROM paper ORDER BY YEAR

Question: Find the titles and paper IDs for papers which have Mckeown but not Rambow in author list.
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%" EXCEPT 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%"

Question: Find the name of authors who have never published a paper in ACL.
SQL: SELECT name FROM Author WHERE author_id NOT IN (SELECT T1.author_id FROM Author_list AS T1 JOIN Paper AS T2 ON T1.paper_id  =  T2.paper_id WHERE T2.venue  =  "ACL")

Question: Find the titles and paper IDs for papers written by Harvard University.
SQL: SELECT DISTINCT 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  =  "Harvard University"

Question: Count the number of papers which cited a paper with id A00-1002.
SQL: SELECT count(*) FROM Citation WHERE cited_paper_id  =  "A00-1002"

Question: Give the title of the paper which cites most number of 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: Find all papers with titles and paper IDs written by Mckeown.
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 T3.author_id  =  T2.author_id WHERE T3.name LIKE "%Mckeown%"

Question: What is the name of the author who has co-authored the most papers with Mckeown , Kathleen ?
SQL: select t4.name 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 join author as t4 on t2.author_id  =  t4.author_id where t3.name = "mckeown ,  kathleen" group by t2.author_id order by count(*) desc limit 1

Question: Find the number of papers published in different conferences each year.
SQL: SELECT count(*) ,  venue ,  YEAR FROM Paper GROUP BY venue ,  YEAR

Question: How many papers do we have in NAACL 2000?
SQL: SELECT count(*) FROM Paper WHERE venue  =  "NAACL" AND YEAR  =  2000

Question: What are the names of authors who have not published a paper in ACL?
SQL: SELECT name FROM Author WHERE author_id NOT IN (SELECT T1.author_id FROM Author_list AS T1 JOIN Paper AS T2 ON T1.paper_id  =  T2.paper_id WHERE T2.venue  =  "ACL")

Question: What are the venues and years where Columbia University had papers ?
SQL: select distinct t1.venue ,  t1.year 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 = "columbia university"

Question: How many authors do we have?
SQL: SELECT count(*) FROM Author

Question: Find the id of the papers whose title has the key word 'translation'.
SQL: SELECT paper_id FROM Paper WHERE title LIKE "%translation%"

Question: List all affiliations with ascending ordered number of papers.
SQL: SELECT T1.name FROM Affiliation AS T1 JOIN Author_list AS T2 ON T1.affiliation_id  =  T2.affiliation_id GROUP BY T1.affiliation_id ORDER BY count(*) DESC

Question: What is the venue with the least number of publications?
SQL: SELECT venue FROM paper GROUP BY venue ORDER BY count(*) LIMIT 1

Question: How many papers did each author publish, ordered 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 name of the affiliation which publishes the greatest number of papers among those whose address contains 'China'.
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