Answer to Question #317248 in Databases | SQL | Oracle | MS Access for M m roman

Question #317248

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-26T02:38:28-0400

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

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