--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