Question

Consider the relations Authors(auID,name) and Authoring(articleID,authorID), containing

information on names of authors, and who is authoring which papers, respectively.

i) Write an SQL query that returns for each article, its ID, title and the number of authors.

ii) Write an SQL query that returns the titles of articles authored by 'Robert Tarjan'.

iii) Write an SQL query that returns the number of co-authors of 'Robert Tarjan'. (I.e., the number of

authors who have written at least one article together with him

Answer 1

1)

SELECT articleId, title, amount_of_authors
FROM (SELECT articleId, COUNT(authorId) as amount_of_authors
      FROM Authoring
      GROUP BY articleId) AS temp1
         JOIN Articles ON Articles.id = temp1.articleId;

2)

SELECT title
FROM Authoring
         JOIN Articles A on A.id = Authoring.articleId
         JOIN Authors A2 on A2.id = Authoring.authorId
WHERE A2.name = 'Robert Tarjan';

3)

SELECT Count(*)
FROM Authoring
         JOIN Authors A on A.id = Authoring.authorId
WHERE name != 'Robert Tarjan'
  and articleId in (SELECT A2.articleId
                    FROM Authoring A2
                             JOIN
                         Authors A3 on A3.id = A2.authorId
                    WHERE A3.name = 'Robert Tarjan');