/*
	Solutions to some exercises.
	CSE 444, fall 2010
	Section 2, October 7, 2010
 */

-- Authors whose last name is "Crick"
SELECT *
FROM Author
WHERE name LIKE '%Crick';
-- _ or % are LIKE wildcards.
-- _ means "any single character" (like shell ? or egrep .)
-- % means "any (possibly empty) string" (like shell * or egrep .*)

-- All documents written in 2000 or later
SELECT *
FROM Document
WHERE year >= 2000;

-- Names and years of all documents from earliest to latest
SELECT title, year
FROM Document
ORDER BY year ASC;
-- ASC is default, so it can be omitted

-- Authors of the double-helix paper
SELECT a.name
FROM Author a, Auth_Doc ad, Document d
WHERE a.aid = ad.aid AND
	  ad.did = d.did AND
	  d.year = 1953 AND
	  d.title = 'Molecular structure of nucleic acids: a structure for deoxyribose nucleic acid';
-- We could break up that long quoted string by concatenating
-- two shorter character strings.
-- For SQL Server, use + as the concat operator;
-- most other DBMSen use || (double-pipe).

-- Word count of the double-helix paper
SELECT COUNT (DISTINCT dw.word)
FROM Author a, Auth_Doc ad, Document d, Doc_Word dw
WHERE a.aid = ad.aid AND
	  ad.did = d.did AND
	  d.did = dw.did AND
	  d.year = 1953 AND
	  d.title = 'Molecular structure of nucleic acids: a structure for deoxyribose nucleic acid';
-- We need DISTINCT in the COUNT(), otherwise duplicate "word" values
-- will be counted multiple times (although NULLs will not be counted).

-- Total word count by author
SELECT name, COUNT(*) AS total
FROM Author a, Auth_Doc ad, Doc_Word dw
WHERE a.aid = ad.aid AND ad.did = dw.did
GROUP BY a.aid, a.name;

-- Average word count by author
SELECT name, COUNT(*) / COUNT(DISTINCT ad.did) AS avg_word_count
FROM Author a, Auth_Doc ad, Doc_Word dw
WHERE a.aid = ad.aid AND ad.did = dw.did
GROUP BY a.aid, a.name;

-- Author who uses most words per paper on average
SELECT name
FROM Author a, Auth_Doc ad, Doc_Word dw
WHERE a.aid = ad.aid AND ad.did = dw.did
GROUP BY a.aid, a.name
ORDER BY COUNT(*) / COUNT(DISTINCT ad.did) DESC
LIMIT 1;
/* 
   The LIMIT clause, used to take only the first 1 or 2 tuples 
   in the result set (as we do here), was not in early versions
   of the SQL standard, so different DBMSen use different syntaxes
   for it.  

   The above syntax works in Postgres.
   SQL Server and Sybase would use SELECT TOP 1 name...
 */

-- Author who uses most words per paper on average (without LIMIT)
SELECT name
FROM Author a, Auth_Doc ad, Doc_Word dw
WHERE a.aid = ad.aid AND ad.did = dw.did
GROUP BY a.aid, a.name
HAVING COUNT(*) / COUNT(DISTINCT ad.did) >= ALL (
	   SELECT COUNT(*) / COUNT(DISTINCT ad.did) AS avg_word_count
	   FROM Author a, Auth_Doc ad, Doc_Word dw
	   WHERE a.aid = ad.aid AND ad.did = dw.did
	   GROUP BY a.aid, a.name
);
