SELECT a.au_id,

COUNT(ta.title_id)

AS "Num books"

FROM authors a, title_authors ta

WHERE a.au_id = ta.au_id

GROUP BY a.au_id

ORDER BY a.au_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.12 List the advance paid for each biography.

See Figure 7.12 for the result.

*/

SELECT

    t.title_id,

    t.title_name,

    r.advance

  FROM royalties r

  INNER JOIN titles t

    ON r.title_id = t.title_id

  WHERE t.type = 'biography'

    AND r.advance IS NOT NULL

  ORDER BY r.advance DESC;

  ------Vitalie Bancu @vitallybankou

Listing 7.13 List the count and total advance paid for

each type of book. See Figure 7.13 for the result.

*/

SELECT

    t.type,

    COUNT(r.advance)

      AS "COUNT(r.advance)",

    SUM(r.advance)

      AS "SUM(r.advance)"

  FROM royalties r

  INNER JOIN titles t

    ON r.title_id = t.title_id

  WHERE r.advance IS NOT NULL

  GROUP BY t.type

  ORDER BY t.type ASC;

SELECT t.type,

COUNT(r.advance)

AS "COUNT(r.advance)",

SUM(r.advance)

AS "SUM(r.advance)"

FROM royalties r, titles t

WHERE r.title_id = t.title_id

AND r.advance IS NOT NULL

GROUP BY t.type

ORDER BY t.type ASC;

SELECT t.type, t.pub_id,

COUNT(r.advance)

AS "COUNT(r.advance)",

SUM(r.advance)

AS "SUM(r.advance)"

FROM royalties r, titles t

WHERE r.title_id = t.title_id

AND r.advance IS NOT NULL

GROUP BY t.type, t.pub_id

ORDER BY t.type ASC, t.pub_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.14 List the count and total advance paid for

each type of book, by publisher. See Figure 7.14 for

the result.

*/

SELECT

    t.type,

    t.pub_id,

    COUNT(r.advance) AS "COUNT(r.advance)",

    SUM(r.advance) AS "SUM(r.advance)"

  FROM royalties r

  INNER JOIN titles t

    ON r.title_id = t.title_id

  WHERE r.advance IS NOT NULL

  GROUP BY t.type, t.pub_id

  ORDER BY t.type ASC, t.pub_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.15 List the number of coauthors of each

book written by two or more authors. See Figure 7.15

for the result.

*/  

SELECT

    ta.title_id,

    COUNT(ta.au_id) AS "Num authors"

  FROM authors a

  INNER JOIN title_authors ta

    ON a.au_id = ta.au_id

  GROUP BY ta.title_id

  HAVING COUNT(ta.au_id) > 1

  ORDER BY ta.title_id ASC;

SELECT ta.title_id,

COUNT(ta.au_id) AS "Num authors"

FROM authors a, title_authors ta

WHERE a.au_id = ta.au_id

GROUP BY ta.title_id

HAVING COUNT(ta.au_id) > 1

ORDER BY ta.title_id ASC;    

  ------Vitalie Bancu @vitallybankou

Listing 7.16 List each book whose revenue (= price ?

sales) is at least 10 times greater than its advance.

See Figure 7.16 for the result.

*/

SELECT

    t.title_id,

    t.title_name,

    r.advance,

    t.price * t.sales AS "Revenue"

  FROM titles t

  INNER JOIN royalties r

    ON t.price * t.sales > r.advance * 10

    AND t.title_id = r.title_id

  ORDER BY t.price * t.sales DESC;

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.17 List the author names and the names of

the books that each author wrote (or cowrote). See

Figure 7.17 for the result.

*/

SELECT

    a.au_fname,

    a.au_lname,

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

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

    t.title_name ASC;