------Vitalie Bancu @vitallybankou

Listing 8.7a This statement uses a subquery to list

the authors who live in the same state as author A04

(Klee Hull). See Figure 8.7 for the result.

*/  

SELECT au_id, au_fname, au_lname, state

  FROM authors

  WHERE state IN

    (SELECT state

      FROM authors

      WHERE au_id = 'A04');

  ------Vitalie Bancu @vitallybankou

Listing 8.7b This statement is equivalent to

Listing 8.7a but uses an inner join instead of a

subquery. See Figure 8.7 for the result.

*/      

SELECT a1.au_id, a1.au_fname,

    a1.au_lname, a1.state

  FROM authors a1

  INNER JOIN authors a2

    ON a1.state = a2.state

  WHERE a2.au_id = 'A04';

  ------Vitalie Bancu @vitallybankou

Listing 8.8 List all books whose price equals the

highest book price. See Figure 8.8 for the result.

*/  

SELECT title_id, price

  FROM titles

  WHERE price = 

    (SELECT MAX(price)

      FROM titles);

SELECT *

FROM TITLES;

  ------Vitalie Bancu @vitallybankou

Listing 8.9 List the authors who live in 

the same city

in which a publisher is located,

and include the

publisher in the result. See Figure

8.9 for the result.

*/      

SELECT a.au_id, a.city, p.pub_id

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city;

  ------Vitalie Bancu @vitallybankou

Listing 8.10 List the authors who live in the same city

in which a publisher is located. See Figure 8.10 for the

result.

*/

SELECT au_id, city

  FROM authors

  WHERE city IN

    (SELECT city

      FROM publishers);

SELECT

candidate.title_id,

candidate.type,

candidate.sales

FROM titles candidate

WHERE sales >=

(SELECT AVG(sales)

FROM titles average

WHERE average.type = candidate.type);

SELECT AVG(sales)

FROM titles average

WHERE average.type = 'history';

SELECT AVG(sales)

FROM titles average

WHERE average.type = 'history';

SELECT DISTINCT  au_id

FROM title_authors

WHERE royalty_share = 1.0;

SELECT au_id, au_fname, au_lname

FROM authors

WHERE au_id IN

(SELECT au_id

FROM title_authors

WHERE royalty_share = 1.0);

SELECT au_id, au_fname, au_lname

  FROM authors

  WHERE 1.0 IN

    (SELECT royalty_share

      FROM title_authors

      WHERE title_authors.au_id =

            authors.au_id);

  ------Vitalie Bancu @vitallybankou

Listing 8.15a The tables publishers and 

titles both

contain a column named pub_id, 

but you don’t have

to qualify pub_id in this query 

because of the implicit

assumptions about table names that SQL makes.

See Figure 8.15 for the result.

*/            

SELECT pub_name

  FROM publishers

  WHERE pub_id IN

    (SELECT pub_id

      FROM titles

      WHERE type = 'biography');

  ------Vitalie Bancu @vitallybankou

Listing 8.15b This query is equivalent 

to Listing 8.15a,

but with explicit qualification

 of pub_id. See Figure

8.15 for the result.

*/

SELECT pub_name

  FROM publishers

  WHERE publishers.pub_id IN

    (SELECT titles.pub_id

      FROM titles

      WHERE type = 'biography');

  ------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';