SELECT a.au_fname, a.au_lname,

t.title_name

FROM authors a, title_authors ta,

titles t

WHERE a.au_id = ta.au_id

AND t.title_id = ta.title_id

ORDER BY a.au_lname ASC,

a.au_fname ASC,

t.title_name ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.18 List the author names, the names of the

books that each author wrote (or cowrote), and the

publisher names. See Figure 7.18 for the result.

*/

SELECT

    a.au_fname,

    a.au_lname,

    t.title_name,

    p.pub_name

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

    ON p.pub_id = t.pub_id

  ORDER BY a.au_lname ASC, a.au_fname ASC,

    t.title_name ASC;

SELECT a.au_fname, a.au_lname,

t.title_name, p.pub_name

FROM authors a, title_authors ta,

titles t, publishers p

WHERE a.au_id = ta.au_id

AND t.title_id = ta.title_id

AND p.pub_id = t.pub_id

ORDER BY a.au_lname ASC,

a.au_fname ASC,

t.title_name ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.19 calculates the total royalties

for all books. The gross royalty of a book is

the book’s revenue (= sales ? price) times

the royalty rate (the fraction of revenue paid

to the author). In most cases, the author

receives an initial advance against royalties.

The publisher deducts the advance from

the gross royalty to get the net royalty. If the

net royalty is positive, the publisher must

pay the author; if the net royalty is negative

or zero, the author gets nothing, because he

or she still hasn’t "earned out” the advance.

See Figure 7.19 for the result. Gross royalties

are labeled "Total royalties,” gross

advances are labeled "Total advances,”

and net royalties are labeled "Total due

to authors.”

Listing 7.19 calculates total royalties for

all books; the subsequent examples in this

section will show you how to break down

royalties by author, book, publisher, and

other groups.

*/ 

SELECT

    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;

  ------Vitalie Bancu @vitallybankou

Listing 7.20 uses a three-table join to calculate

the royalty earned by each author for each

book that the author wrote (or cowrote).

Because a book can have multiple authors,

per-author royalty calculations involve

each author’s share of a book’s royalty (and

advance). The author’s royalty share for each

book is given in the table title_authors in

the column royalty_share. For a book with

a sole author, royalty_share is 1.0 (100 percent).

For a book with multiple authors,

the royalty_share of each author is a fractional

amount between 0 and 1 (inclusive);

all the royalty_share values for a particular

book must sum to 1.0 (100 percent). See

Figure 7.20 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,

    t.title_id,

    t.pub_id,

    t.sales * t.price * r.royalty_rate * ta.royalty_share AS "Royalty share",

    r.advance * ta.royalty_share AS "Advance share",

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

      (r.advance * ta.royalty_share) AS "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

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

SELECT ta.au_id, t.title_id,

t.pub_id,

t.sales * t.price *

r.royalty_rate *

ta.royalty_share

AS "Royalty share",

r.advance * ta.royalty_share

AS "Advance share",

(t.sales * t.price *

r.royalty_rate *

ta.royalty_share) -

(r.advance *

ta.royalty_share)

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

ORDER BY ta.au_id ASC,

t.title_id ASC;

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

*/