New Tech Books has the following research publication database schema: Author Au
ID: 3745017 • Letter: N
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'
);