------Vitalie Bancu @vitallybankouListing 8.17 List each biography, its price, the average

price of all books, and the difference between the price

of the biography and the average price of all books.

See Figure 8.17 for the result.*/

   SELECT title_id,

    price,

    (SELECT AVG(price) FROM titles)

      AS "AVG(price)",

    price - (SELECT AVG(price) FROM titles)

      AS "Difference"

  FROM titles

  WHERE type='biography';   

  ------Vitalie Bancu @vitallybankou

Listing 8.18 List all the authors of each book in one

row. See Figure 8.18 for the result.

*/      

SELECT title_id,

    (SELECT au_id

      FROM title_authors ta

      WHERE au_order = 1

        AND title_id = t.title_id)

        AS "Author 1",

    (SELECT au_id

      FROM title_authors ta

      WHERE au_order = 2

        AND title_id = t.title_id)

        AS "Author 2",

    (SELECT au_id

      FROM title_authors ta

      WHERE au_order = 3

        AND title_id = t.title_id)

        AS "Author 3"

  FROM titles t;

  ------Vitalie Bancu @vitallybankou

Listing 8.19 List the number

 of books that each

author wrote (or cowrote), 

including authors who

have written no books.

 See Figure 8.19 for the result.

*/

SELECT au_id,

    (SELECT COUNT(*)

      FROM title_authors ta

      WHERE ta.au_id = a.au_id)

        AS "Num books"

  FROM authors a

  ORDER BY au_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 8.20 List each author and the latest

 date on

which he or she published a book.

 See Figure 8.20 for

the result.  

*/  

SELECT au_id, 

    (SELECT MAX(pubdate)

      FROM titles t

      INNER JOIN title_authors ta

        ON ta.title_id = t.title_id

      WHERE ta.au_id = a.au_id)

        AS "Latest pub date"

  FROM authors a;

  ------Vitalie Bancu @vitallybankou  

Listing 8.21 Compute the running sum

 of all book

sales. See Figure 8.21 for the result.

  */

SELECT t1.title_id, t1.sales,

    (SELECT SUM(t2.sales)

      FROM titles t2

      WHERE t2.title_id <= t1.title_id)

        AS "Running total"

  FROM titles t1;

  ------Vitalie Bancu @vitallybankou

Listing 8.22 Calculate the greatest number of titles

written (or cowritten) by any author. See Figure 8.22

for the result.

*/  

SELECT MAX(ta.count_titles) AS "Max titles"

  FROM (SELECT COUNT(*) AS count_titles

         FROM title_authors

         GROUP BY au_id) ta;

SELECT title_id,

MIN(CASE au_order WHEN 1

THEN au_id

END)

AS "Author 1",

MIN(CASE au_order WHEN 2

THEN au_id

END)

AS "Author 2",

MIN(CASE au_order WHEN 3

THEN au_id

END)

AS "Author 3"

FROM title_authors

GROUP BY title_id

ORDER BY title_id ASC;           

  ------Vitalie Bancu @vitallybankou

Listing 8.23

List the authors who live in the state in

which the publisher 

Tenterhooks Press is located.

*/

SELECT au_id, au_fname, au_lname, state

  FROM authors

  WHERE state =

    (SELECT state

      FROM publishers

      WHERE pub_name = 'Tenterhooks Press');

  ------Vitalie Bancu @vitallybankou

Listing 8.24 List the authors who live in the state in

which the publisher XXX is located. See Figure 8.24

for the result.

*/

SELECT au_id, au_fname, au_lname, state

  FROM authors

  WHERE state =

    (SELECT state

      FROM publishers

      WHERE pub_name = 'XXX');

  ------Vitalie Bancu @vitallybankou

Listing 8.25 List the books with

 above-average sales.

See Figure 8.25 for the result.

*/      

  SELECT title_id, sales

  FROM titles

  WHERE sales >

    (SELECT AVG(sales)

      FROM titles);

  ------Vitalie Bancu @vitallybankou      

Listing 8.26 List the authors of the

 books with aboveaverage

sales by using a join and a subquery. See

Figure 8.26 for the result.

*/

SELECT ta.au_id, ta.title_id

  FROM titles t

  INNER JOIN title_authors ta

    ON ta.title_id = t.title_id

  WHERE sales >

    (SELECT AVG(sales)

      FROM titles)

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