Consider the following schema, representing a portion of a publishing system: Ar

Question

Consider the following schema, representing a portion of a publishing system: Articles(ID:integer, title:string, journal:string, issue:integer, year:integer, startpage:integer, endpage:integer) Authors(ID:integer, name:string) AuthoredPapers(articleID:integer, authorID:integer) Consider creating the three tables in MySQL and populating them with data to test your queries on. Be careful to create useful data that isn't misleading. (a) Find for each article, its ID, title and the number of authors who wrote it. (b) Find the titles of all the articles for which 'John von Neumann's is an author. (c) Find the number of co-authors of 'Carl Gauss'. (d) Find the title for the longest article(s) (i.e., those with the most number of total pages).

Explanation / Answer

a)

SELECT ID title COUNT(DISTINCT authorID) FROM Articles INNER JOIN Authors ON Articles.ID= Authors.ID INNER JOIN AuthoredPapers ON Articles.ID= Authors.ID

The given query will generate thr ID, title and the count of authors who wrote it.

b)

SELECT title FROM Articles INNER JOIN Authors ON Articles.ID= Authors.ID WHERE Author.name='John von Neumann'

Above query will give the title of the articles which are wrote by 'John von Neumann'

c)

SELECT COUNT(ID) FROM Articles INNER JOIN Authors ON AuthoredPapers Articles.ID= Authors.ID WHERE Article.title='Carl Gauss'

The number of authores will be displayed who co authored 'Carl gauss'

d)

SELECT title FROM Articles WHERE MAX (endpage MINUS startpage)

Title of the article with maximum number of pages will be displayed.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---