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 papers Columbia University had during or prior to 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  <=  2009 and t3.name = "columbia university"

Question: How many papers are published in each venue in each year?
SQL: SELECT count(*) ,  venue ,  YEAR FROM Paper GROUP BY venue ,  YEAR

Question: What are the titles and paper ids for papers 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: Give the id and the number of citations of the most cited paper.
SQL: SELECT paper_id ,  count(*) FROM Citation GROUP BY cited_paper_id ORDER BY count(*) DESC LIMIT 1

Question: Who has the most papers co-authored 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 names of authors who published some paper on NAACL and ACL in the year 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: How many papers does Columbia University have in or before 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  <=  2009 and t3.name = "columbia university"

Question: List names and addresses for all affiliations.
SQL: SELECT DISTINCT name ,  address FROM Affiliation

Question: Which venues and years did Mckeown , Kathleen have 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 author as t3 on t2.author_id  =  t3.author_id where t3.name = "mckeown ,  kathleen"

Question: Find the id and title of the papers that are never cited by others.
SQL: SELECT paper_id ,  title FROM Paper WHERE paper_id NOT IN (SELECT cited_paper_id FROM Citation)

Question: Count the number of references the paper with id D12-1027 has.
SQL: SELECT count(*) FROM Citation WHERE paper_id  =  "D12-1027"

Question: Give the title of the paper with the most 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: What is the name and number of citations of the author with the greatest number of citations among authors?
SQL: SELECT T3.name ,  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 GROUP BY T2.author_id ORDER BY count(*) DESC LIMIT 1

Question: List names of all authors who have only 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 titles and paper ids for papers which were affiliated with both Stanford 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: What are the 10 most cited papers, and how many citations did each have?
SQL: SELECT paper_id ,  count(*) FROM Citation GROUP BY cited_paper_id ORDER BY count(*) DESC LIMIT 10

Question: What is the name of the author with the most papers in 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: How many affiliations do we have?
SQL: SELECT count(*) FROM Affiliation

Question: What are the ids for papers with titles containing 'translation'?
SQL: SELECT paper_id FROM Paper WHERE title LIKE "%translation%"

Question: How many papers are published in year 2009 by Columbia University?
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

Question: How many citations does Mckeown , Kathleen have ?
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: What are the names of all affiliations, ordered by 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: Find the number of authors who did not publish any paper that is cited more than 50 times.
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: What are the venues and years where Mckeown , Kathleen 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 author as t3 on t2.author_id  =  t3.author_id where t3.name = "mckeown ,  kathleen"

Question: Count the number of papers.
SQL: SELECT count(*) FROM Paper

Question: Which venues and years did Columbia University have 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: Find the titles and paper IDs for papers which have Mckeown, Kathleen or Rambow, Owen in 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: Which 3 affiliations had the most papers in 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