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