b) 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.)
i)
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;
ii)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';
iii)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');
Comments
Leave a comment