-- COMP6240 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 institution has the most authors?

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

-- (d) 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
/

-- (e) Which authors always had co-authors on their papers,
--     i.e. have no papers of which they are the only author.

SELECT Surname
FROM Author
WHERE Surname NOT IN
  (SELECT Au1.Surname   -- authors of single-author papers
   FROM Authorship Au1
   WHERE Au1.PId IN
     (SELECT Au2.PId   -- single-author papers
      FROM Authorship Au2
      GROUP BY Au2.PId
      HAVING COUNT(*) = 1))
/