Question

Write the following questions as queries in SQL. Use only the operators discussed in class (in particular, no outer joins are allowed). Please use renaming consistently! The following database schema is given:

RESEARCHER(rid,name,institution,city,country)

PAPER(title,journal,volume,number,year)

AUTHOR(resid,title)

where rid is the identifier (primary key) of RESEARCHER, name is the researcher’s name, institution is where the researcher works, and city and country the city and country where the institution is located; title is the paper identifier (primary key) of PAPER, journal is the journal where it was published (in volume volume and number number), and year is the year it appeared. Finally, in AUTHOR resid is a foreign key for RESEARCHER and title is a foreign key for PAPER. A researcher may write several papers, and papers may be jointly written by several researchers; this means that the key of Author is (resid,title).

1. List the names of authors of any paper published in journal “Databases” in 2019 in volume 12.

2. List the names of authors who have published a paper in both “Nature” and in “Science” (journals).

3. List the names of authors who have published two or more papers in “Nature” (journal).

4. List the names of authors who have published a paper in “Nature” but never in “Science” (journals).

5. List the names of authors who have published a paper in “Nature” (journal) alone (i.e. without co-authors).

6. List the title of papers where all authors are from the same institution. Note: this includes sole authors too.

7. Find out the average number of authors per paper.

8. Find out the number of papers per country (count each paper once, regardless of the number of authors).

9. List the number of papers per author (identified by author id) but count each paper in proportion to the number of authors. That is, if a paper has n authors, split the credit equally among them (1/n units of credit). Example: if researcher Ullman has 3 papers, one alone, one with somebody else, and one with two other co-authors, he gets 1, .5 and .3 units of credit respectively, for a total of 1.8 papers.

10. List the journals that published more papers in 2019 than in 2018.

Answer 1

1) select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Databases' and p.year = 2019 and p.volume = 12;

2)

select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Nature'

INTERSECT

select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Science';

3)

select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Nature' GROUP BY r.name having count(*) > 1;

4)

select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Nature'

minus

select r.name from AUTHOR a, PAPER p, RESEARCHER r where a.resid = r.rid and a.title = p.title and p.journal = 'Science';

NOTE: As per Chegg policy I am allowed to answer specific number of questions (including sub-parts) on a single post. Kindly post the remaining questions separately and I will try to answer them. Sorry for the inconvenience caused.