New Tech Books has the following research publication database schema: Author Au

Question

New Tech Books has the following research publication database schema:

Author    AuthorID: Integer

   Name—varchar

   Homepage—varchar

Paper    PaperID—integer

   Title—varchar

Year—integer

   Conference—varchar

Writes    AuthorID—Integer [references author(authored)]

PaperID—Integer [references paper(papered)]

Cites    Cites—PaperID: Integer [references paper(paperId)]

Cites—integer [references paper(papered)]

Write SQL queries for the following problems:

•Get all possible author names sorted by authored.

•Get all papers of year 2001 and conference Association For Computing Machinery: Special Interest Group on Management of Data (ACM SIGMOD).

•Get all paper names that are published by Debbie.

•Get list of papers published since 2001 that have John Barrett and Chris Donaldson as common authors.

•Get the list of authors who published a paper with Tom Wilkins.

Explanation / Answer

If you have any doubts, please give me comment...

SELECT Name

FROM Author

ORDER BY Name;

SELECT *

FROM Paper

WHERE Year = 2001 AND Conference = ' Association For Computing Machinery: Special Interest Group on Management of Data (ACM SIGMOD)';

SELECT Title

FROM Paper P, Author A

WHERE P.Writes = A.AuthorID AND A.Name = 'Debbie';

SELECT Title

FROM Paper P, Author A

WHERE P.Writes = A.AuthorID AND Year = 2001 AND A.Name = 'John Barrett'

INTERSECT

SELECT Title

FROM Paper P, Author A

WHERE P.Writes = A.AuthorID AND Year = 2001 AND A.Name = 'Chris Donaldson';

SELECT A.Name

FROM Paper P, Author A

WHERE P.Writes = A.AuthorID AND A.PaperID IN(

SELECT P1.PaperID

FROM Paper P1, Author A1

WHERE P1.Writes = A1.AuthorID AND A1.Name = 'Tom Wilkins'

);

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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