Authors authorlD (in firstName (varchar(32)) lastName (varchar(32)) email (varch

Question

Authors authorlD (in firstName (varchar(32)) lastName (varchar(32)) email (varchar(32)) PapersByAuthor aperID (int Citations paperD (int) citationID (int) Papers paperID (int) title (varchar(100)) year (int) venuelD (int) Venues venueID (int) name (varchar(100)) acronym (varchar(16)) type (char(10)) Figure 1. Schema for the computer science bibliography database 1. (10 points) Write two significantly different SQL queries that list the titles of papers that have never been cited. (The queries must be significantly different, in the sense that one must use some SQL construct that the other does not.) 2. (10 points) Write two significantly different queries that list the names of authors who have never published a paper in a journal. (The queries must be significantly different, in the sense that one must use some SQL construct that the other does not.) 3. (10 points) Write an SQL query that lists the names of authors who have only journal publications 4. (10 points) Consider the following queries which claim to list all authors who have published at least a paper in 2012 and at least a paper in 2013. For each querv, say if it is correct or not. If you think the query is incorrect, then justify your answer in 2-3 sentences. (a) SELECT distinct A.firstname FROM Authors A NATURAL JOIN PapersByAuthor PA NATURAL JOIN Papers P1 NATURAL JOIN PapersByAuthor PA2 NATURAL JOIN Papers P2 WHERE PI . year=2012 AND P2.year=2013 AND P1.pape r IDP2.paper1D;

Explanation / Answer

1) Two separate SQL query construct is used to get the title of the paper which have not
been cited. First query uses subquery in the where clause to filter these papers.

SELECT title
FROM Papers
WHERE paperID NOT IN (SELECT paperID FROM Citations);

2nd query uses left join and inner join two separate queries then the result of both the
queries are MINUS.

SELECT title
FROM Papers AS p
LEFT JOIN Citations AS c
ON p.paperID = c.PaperID
MINUS
SELECT title
FROM Papers AS p
INNER JOIN Citations AS c
ON p.paperID = c.PaperID

2) Assuming if the AuthorID is not there in PapersByAuthor relation it means Author has
never published a paper in any journal.

SELECT firstName, lastName
FROM Authors
WHERE authorID NOT IN (SELECT authorID FROM PapersByAuthor);

SELECT DISTINCT firstName, lastName
FROM Authors AS a
LEFT JOIN PapersByAuthor AS p
ON a.authorID = p.authorID
MINUS
SELECT DISTINCT firstName, lastName
FROM Authors AS a
INNER JOIN PapersByAuthor AS p
ON a.authorID = p.authorID

3) AS no criteria has been mentioned to identify the journal publication, just assuming
all the authors available in PapersByAuthor table have journal publication. For this
query joining table Authors and PapperByAuthor based on inner join.

SELECT DISTINCT firstName, lastName
FROM Authors AS a
INNER JOIN PapersByAuthor AS p
ON a.authorID = p.authorID

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.