-- COMP2400 2005 Midsemester Exam
-- A V Peterson

-- Question 2 Answers

-- (a) Produce a Table of Contents consisting of paper title and page number,
--     ordered by page number, for volume 7 of the journal 'Navel Gazette'.

SELECT Title, Page
FROM Paper
WHERE JournalName = 'Navel Gazette'
  AND Volume = 7
ORDER BY Page
/

-- (b) Name the journal and publisher of journals in which the author with
--     surname 'Watchman' has published papers.

SELECT JournalName, Publisher
FROM Journal
WHERE JournalName IN
  (SELECT JournalName FROM Paper
   WHERE PId IN
     (SELECT PId FROM Authorship
      WHERE Surname = 'Watchman'))
/


SELECT DISTINCT J.JournalName, J.Publisher
FROM Journal J, Paper P, Authorship A
WHERE J.JournalName = P.JournalName
  AND P.PId = A.AId
  AND A.Surname = 'Watchman'
/

-- (c) Which authors did not publish any papers in 2004?

SELECT Surname
FROM Author
WHERE Surname NOT IN
  (SELECT Surname FROM Authorship
   WHERE PId IN
     (SELECT PID FROM Paper
      WHERE Year = 2004)
  )
/

-- (d) Which institution has the most authors?

SELECT Institution
FROM Author
GROUP BY Institution
HAVING COUNT(*) =
  (SELECT MAX(COUNT(*))
   FROM Author
   GROUP BY Institution)
/

-- (e) List the PId and Title of papers which refer to other papers
--     published in the same year.

SELECT P.PId, P.Title
FROM Paper P, Reference R, Paper RefP
WHERE P.PId = R.PId
  AND R.RefId = RefP.PId
  AND P.Year = RefP.Year
  AND P.PId != RefP.PId
/