-- CSE 444, Summer 2010
--
-- Sample solutions for the "More examples" slide problems 
-- (slides 14-17) for section 2 on July 1, 2010.
-- Based on section AB responses.

-- Total word count by author
SELECT name, COUNT(*) AS total
FROM Author a, Auth_Doc ad, Doc_Words 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 did) AS avg_word_count
FROM Author a, Auth_Doc ad, Doc_Words 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, COUNT(*) / COUNT(DISTINCT did) AS avg_word_count
FROM Author a, Auth_Doc ad, Doc_Words dw
WHERE a.aid = ad.aid AND ad.did = dw.did
GROUP BY a.aid, a.name
ORDER BY avg_word_count DESC
LIMIT 1;
-- Note that LIMIT and similar code (TOP and friends) to take
-- only the first 1 or 2 tuples in the result set are non-standard.
-- Most standard (but likely slower) way is to embed the computation
-- of the max average word count as a subquery, then use the 
-- witness-finding pattern we saw in class.
--
-- If you are running this query from a Java or C++ (with ODBC) program,
-- you can run the query above without LIMIT and simply close the cursor
-- after the first tuple.
