------Vitalie Bancu @vitallybankou

Listing 7.37

List all different pairs of authors who live

in New York state. 

See Figure 7.37 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

    AND a1.au_id <> a2.au_id

  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.au_id < a2.au_id

AND a1.state = 'NY'

ORDER BY a1.au_id ASC,

a2.au_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.38 List all different pairs

of authors who

live in New York state, 

with no redundancies. See

Figure 7.38 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

    AND a1.au_id < a2.au_id

  WHERE a1.state = 'NY'

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

  ------Vitalie Bancu @vitallybankouListing 8.1 List the biography publishers. See

Figure 8.1 for the result.*/  

SELECT DISTINCT pub_id

  FROM titles

  WHERE type = 'biography';

  ------Vitalie Bancu @vitallybankouListing 8.2 This query uses the result of Listing 8.1

to list the names of the biography publishers. See

Figure 8.2 for the result.*/

  SELECT pub_name, pub_id

  FROM publishers

  WHERE pub_id IN ('P01', 'P03');

  ------Vitalie Bancu @vitallybankou

Listing 8.3 List the names of the

 biography publishers

by using an inner join. See Figure 8.3

 for the result.

*/  

  SELECT DISTINCT pub_name

  FROM publishers p

  INNER JOIN titles t

    ON p.pub_id = t.pub_id

  WHERE t.type = 'biography';

  ------Vitalie Bancu @vitallybankou

Listing 8.4 List the names of the biography

 publishers

by using a subquery. See Figure 8.4 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.5a This statement uses a 

subquery to list

the authors who live in the same city 

in which a

publisher is located. See 

Figure 8.5 for the result.

*/

SELECT au_id, city

  FROM authors

  WHERE city IN

    (SELECT city FROM publishers);

  SELECT CITY

  FROM PUBLISHERS;

  ------Vitalie Bancu @vitallybankou

Listing 8.5b This statement is equivalent 

to

Listing 8.5a but uses an inner 

join instead of a

subquery. See Figure 8.5 for the result.

*/       

SELECT DISTINCT a.au_id, a.city

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city;     

  ------Vitalie Bancu @vitallybankou

Listing 8.6a This statement uses an IN subquery to

list the authors who haven’t written (or cowritten) a

book. See Figure 8.6 for the result.

*/        

SELECT au_id, au_fname, au_lname

  FROM authors

  WHERE au_id NOT IN

     (SELECT au_id FROM title_authors);

  ------Vitalie Bancu @vitallybankou

Listing 8.6b This statement is equivalent to

Listing 8.6a but uses an EXISTS subquery instead

of an IN subquery. See Figure 8.6 for the result.

*/  

SELECT au_id, au_fname, au_lname

  FROM authors a

  WHERE NOT EXISTS

    (SELECT *

      FROM title_authors ta

      WHERE a.au_id = ta.au_id);

  ------Vitalie Bancu @vitallybankou

Listing 8.6c This statement is equivalent to

Listings 8.6a and 8.6b but uses a left outer join

instead of a subquery. See Figure 8.6 for the result.

*/  

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

  FROM authors a

  LEFT OUTER JOIN title_authors ta

    ON a.au_id = ta.au_id

  WHERE ta.au_id IS NULL;