Answer to Question #318416 in Databases | SQL | Oracle | MS Access for prathik ghosh

Question

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.)

Answer 1

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');