--CST8215 Lab 10 Section 313: Pierre Laforest-Grant 040646430
--Q1 1. List book (i.e., title_id and title) and publisher (i.e., publisher_id,
--pub_name) information associated with the publisher with the greatest total
--revenue. Note: Total should be interpreted as SUM of rows within a group.
SELECT p.pub_id, p.pub_name, t.title, t.title_id
FROM titles t, publishers p
WHERE t.publisher_id = p.pub_id
GROUP BY p.pub_id, p.pub_name, t.title, t.title_id
HAVING SUM(t.sales*t.price) = (
SELECT MAX(SUM(t.price*t.sales))
FROM titles t
GROUP BY (t.sales*t.price));
--PUB PUB_NAME TITLE TIT
----- -------------------- ---------------------------------------- ---
--P04 Tenterhooks Press Exchange of Platitudes T05
--Q2 List the authors (i.e., au_id, fname, lname) that earn
--more total royalties (i.e., revenue * royalty_rate *
--royalty_share) than the average royalties earned by all
--authors.
SELECT a.au_id, a.fname, a.lname
FROM authors a, author_titles at, titles t
WHERE a.au_id = at.au_id
AND t.title_id = at.title_id
AND (t.price*t.sales*t.royalty_rate*at.royalty_share) > (
SELECT AVG(t.price*t.sales*t.royalty_rate*at.royalty_share)
FROM titles t, author_titles at
WHERE t.title_id = at.title_id);
--AU_ FNAME LNAME
----- --------------- ---------------
--A02 Wendy Heydemark
--A04 Klee Hull
--Q3 List the average price of books by genre for each publisher that has more
--than 2 genres and at least 3 books.
SELECT AVG(t.price), t.genre, t.publisher_id
FROM titles t
WHERE t.publisher_id IN (
SELECT t.publisher_id
FROM titles t
GROUP BY t.publisher_id
HAVING COUNT(t.genre) > 2
AND COUNT(t.title_id) >= 3)
GROUP BY t.genre, t.publisher_id;
--AVG(TITLES.PRICE) GENRE PUB
------------------- ---------- ---
-- 11,975 children P04
-- 9,31 psychology P04
--Q4 List the title_id and sales of books that have sales greater than the total
-- sales of books that have 2 authors.
SELECT t.title_id, t.sales
FROM titles t
WHERE t.sales > (
SELECT SUM(t.sales)
FROM titles t, author_titles b
WHERE b.title_id = t.title_id
AND b.au_order = 2);
--TITLE_ID SALES
---------- ----------------------
--T05 201440