------Vitalie Bancu @vitallybankou

Listing 7.32 List all authors and any

 books written (or

cowritten) that sold more than 

100,000 copies. See

Figure 7.32 for the result.

*/

SELECT a.au_id, a.au_fname, a.au_lname,

    tta.title_id, tta.title_name, tta.sales

  FROM authors a

  LEFT OUTER JOIN

  (SELECT ta.au_id, t.title_id,

      t.title_name, t.sales

    FROM title_authors ta

    INNER JOIN titles t

      ON t.title_id = ta.title_id

    WHERE sales > 100000) tta

    ON a.au_id = tta.au_id

  ORDER BY a.au_id ASC, tta.title_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.32 combines an inner join and a

left outer join to list all authors and any

books they wrote (or cowrote) that sold

more than 100,000 copies. In this example,

first I created a filtered INNER JOIN result

and then OUTER JOINed it with the table

authors, from which I wanted all rows. See

Figure 7.32 for the result.

*/  

SELECT a.au_id, a.au_fname, a.au_lname,

    tta.title_id, tta.title_name, tta.sales

  FROM authors a

  LEFT OUTER JOIN

  (SELECT ta.au_id, t.title_id,

      t.title_name, t.sales

    FROM title_authors ta

    INNER JOIN titles t

      ON t.title_id = ta.title_id

    WHERE sales > 100000) tta

    ON a.au_id = tta.au_id

  ORDER BY a.au_id ASC, tta.title_id ASC;

SELECT a.au_id, a.au_fname,

a.au_lname,

tta.title_id, tta.title_name,

tta.sales

FROM authors a,

(SELECT ta.au_id, t.title_id,

t.title_name, t.sales

FROM title_authors ta,

titles t

WHERE t.title_id =

ta.title_id

AND sales > 100000) tta

WHERE a.au_id = tta.au_id (+)

ORDER BY a.au_id ASC,

tta.title_id ASC;

  ------Vitalie Bancu @vitallybankou

c

*/

SELECT

    e1.emp_name AS "Employee name",

    e2.emp_name AS "Boss name"

FROM employees e1

INNER JOIN employees e2

  ON e1.boss_id = e2.emp_id;

  ------Vitalie Bancu @vitallybankou

Listing 7.34 List the authors who live 

in the same

state as author A04 (Klee Hull).

 See Figure 7.34 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';

SELECT *

FROM authors;

  ------Vitalie Bancu @vitallybankouListing 7.34 uses a WHERE search condition

and self-join from the column state to itself

to find all authors who live in the same state

as author A04 (Klee Hull). See Figure 7.34

for the result.

*/

SELECT a1.au_id, a1.au_fname,

a1.au_lname, a1.state

FROM authors a1, authors a2

WHERE a1.state = a2.state

AND a2.au_id = 'A04';

  ------Vitalie Bancu @vitallybankou

Self-joins often can be 

restated as subqueries

(see Chapter 8). Using a subquery,

Listing 7.34 is equivalent to:

*/

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 7.35 For every biography, list 

the title ID and

sales of the other biographies that outsold it. See

Figure 7.35 for the result.

*/

SELECT t1.title_id, t1.sales,

    t2.title_id AS "Better seller",

    t2.sales AS "Higher sales"

  FROM titles t1

  INNER JOIN titles t2

    ON t1.sales < t2.sales

  WHERE t1.type = 'biography'

    AND t2.type = 'biography'

  ORDER BY t1.title_id ASC, t2.sales ASC;

SELECT t1.title_id, t1.sales,

t2.title_id AS "Better seller",

t2.sales AS "Higher sales"

FROM titles t1, titles t2

WHERE t1.sales < t2.sales

AND t1.type = 'biography'

AND t2.type = 'biography'

ORDER BY t1.title_id ASC,

t2.sales ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.36 List all pairs of authors who live

in New

York state. See Figure 7.36 for the result.

*/

SELECT

    a1.au_fname, a1.au_lname,

    a2.au_fname, a2.au_lname

  FROM authors a1

  INNER JOIN authors a2

    ON a1.state = a2.state

  WHERE a1.state = 'NY'

  ORDER BY a1.au_id ASC, a2.au_id ASC;

  SELECT

a1.au_fname, a1.au_lname,

a2.au_fname, a2.au_lname

FROM authors a1, authors a2

WHERE a1.state = a2.state

AND a1.state = 'NY'

ORDER BY a1.au_id ASC,

a2.au_id ASC;

  ------Vitalie Bancu @vitallybankou

a1.au_fname, a1.au_lname,

a2.au_fname, a2.au_lname

FROM authors a1, authors a2

WHERE a1.state = a2.state

AND a1.au_id <> a2.au_id

AND a1.state = 'NY'

ORDER BY a1.au_id ASC,

a2.au_id ASC;

*/