------Vitalie Bancu @vitallybankou

Listing 7.21 List only positive royalties earned by each author for each book that the author wrote (or cowrote). See

Figure 7.21 for the result.

*/

SELECT

    a.au_id,

    a.au_fname,

    a.au_lname,

    t.title_name,

    (t.sales * t.price * r.royalty_rate * ta.royalty_share) -

      (r.advance * ta.royalty_share) AS "Due to author"

  FROM authors a

  INNER JOIN title_authors ta

    ON a.au_id = ta.au_id

  INNER JOIN titles t

    ON t.title_id = ta.title_id

  INNER JOIN royalties r

    ON r.title_id = t.title_id

  WHERE t.sales IS NOT NULL

    AND (t.sales * t.price * r.royalty_rate * ta.royalty_share) -

      (r.advance * ta.royalty_share) > 0

  ORDER BY a.au_id ASC, t.title_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.22 Calculate the total royalties paid 

by each publisher. See Figure 7.22 for the result.

*/  

 SELECT

    t.pub_id,

    COUNT(t.sales) AS "Num books",

    SUM(t.sales * t.price * r.royalty_rate) AS "Total royalties",

    SUM(r.advance) AS "Total advances",

    SUM((t.sales * t.price * r.royalty_rate) - r.advance) AS "Total due to authors"

  FROM titles t

  INNER JOIN royalties r

    ON r.title_id = t.title_id

  WHERE t.sales IS NOT NULL

  GROUP BY t.pub_id

  ORDER BY t.pub_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.23 is similar to Listing 7.22

except that it calculates the total royalties

earned by each author for all books written

(or cowritten). See Figure 7.23 for the

result. The sum of the values in each of the

last three columns in the result equals the

corresponding total in Figure 7.19.

*/   

  SELECT

    ta.au_id,

    COUNT(sales) AS "Num books",

    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share)

    AS "Total royalties share",

    SUM(r.advance * ta.royalty_share) 

    AS "Total advances share",

    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) 

    - (r.advance * ta.royalty_share)) AS "Total due to author"

  FROM title_authors ta

  INNER JOIN titles t

    ON t.title_id = ta.title_id

  INNER JOIN royalties r

    ON r.title_id = t.title_id

  WHERE t.sales IS NOT NULL

  GROUP BY ta.au_id

  ORDER BY ta.au_id ASC;

  --Is equivalent:

SELECT

ta.au_id,

COUNT(sales) AS "Num books",

SUM(t.sales * t.price *

r.royalty_rate *

ta.royalty_share)

AS "Total royalties share",

SUM(r.advance *

ta.royalty_share)

AS "Total advances share",

SUM((t.sales * t.price *

r.royalty_rate *

ta.royalty_share) -

(r.advance *

ta.royalty_share))

AS "Total due to author"

FROM title_authors ta, titles t,

royalties r

WHERE t.title_id = ta.title_id

AND r.title_id = t.title_id

AND t.sales IS NOT NULL

GROUP BY ta.au_id

ORDER BY ta.au_id ASC; 

  ------Vitalie Bancu @vitallybankou

Listing 7.24 Calculate the positive net royalties to

 be paid by each U.S. publisher to each author for all

  books written

(or cowritten) by the author.

 See Figure 7.24 for the result.

*/

SELECT

    t.pub_id,

    ta.au_id,

    COUNT(*) AS "Num books",

    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share) AS "Total royalties share",

    SUM(r.advance * ta.royalty_share) AS "Total advances share",

    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) -

      (r.advance * ta.royalty_share)) AS "Total due to author"

  FROM title_authors ta

  INNER JOIN titles t

    ON t.title_id = ta.title_id

  INNER JOIN royalties r

    ON r.title_id = t.title_id

  INNER JOIN publishers p

    ON p.pub_id = t.pub_id

  WHERE t.sales IS NOT NULL

    AND p.country IN ('USA')

  GROUP BY t.pub_id, ta.au_id

  HAVING SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) - 

    (r.advance * ta.royalty_share)) > 0

  ORDER BY t.pub_id ASC, ta.au_id ASC;

  ------Vitalie Bancu @vitallybankou

Using WHERE syntax, Listing 7.24 is equivalent

to:

*/  

SELECT t.pub_id, ta.au_id,

COUNT(*) AS "Num books",

SUM(t.sales * t.price *

r.royalty_rate *

ta.royalty_share)

AS "Total royalties share",

SUM(r.advance *

ta.royalty_share)

AS "Total advances share",

SUM((t.sales * t.price *

r.royalty_rate *

ta.royalty_share) -

(r.advance *

ta.royalty_share))

AS "Total due to author"

FROM title_authors ta, titles t,

royalties r, publishers p

WHERE t.title_id = ta.title_id

AND r.title_id = t.title_id

AND p.pub_id = t.pub_id

AND t.sales IS NOT NULL

AND p.country IN ('USA')

GROUP BY t.pub_id, ta.au_id

HAVING SUM((t.sales * t.price *

r.royalty_rate *

ta.royalty_share) -

(r.advance * ta.royalty_share))

> 0

ORDER BY t.pub_id ASC,

ta.au_id ASC;  

  ------Vitalie Bancu @vitallybankou

Listing 7.25 List the cities of the authors and the

cities of the publishers. See Figure 7.25 for the

 result.v

*/