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

Question #318416

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


1
Expert's answer
2022-03-28T08:02:52-0400

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


Need a fast expert's response?

Submit order

and get a quick answer at the best price

for any assignment or question with DETAILED EXPLANATIONS!

Comments

No comments. Be the first!

Leave a comment

LATEST TUTORIALS
New on Blog
APPROVED BY CLIENTS