------Vitalie Bancu @vitallybankou

Listing 7.25 List the cities of the authors and the

cities of the publishers. See Figure 7.25 for the

 result.v

*/

SELECT a.au_fname, a.au_lname, a.city

  FROM authors a;

SELECT p.pub_name, p.city

  FROM publishers p;

  ------Vitalie Bancu @vitallybankou

Listing 7.26 List the authors who live

in cities in which

a publisher is located. See Figure 7.26

for the result.

*/

SELECT a.au_fname, a.au_lname, p.pub_name

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city;

  ------Vitalie Bancu @vitallybankouc This left outer join includes all

 rows in the

table authors in the result, whether or not there’s a

match in the column city in the table publishers. See

Figure 7.27 for the result.

*/

SELECT a.au_fname, a.au_lname, p.pub_name

  FROM authors a

  LEFT OUTER JOIN publishers p

    ON a.city = p.city

  ORDER BY p.pub_name ASC,

    a.au_lname ASC, a.au_fname ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.28 uses a right outer join to

include all publishers in the result, regardless

of whether an author lives in the publisher’s

city. See Figure 7.28 for the result.

*/    

SELECT a.au_fname, a.au_lname, p.pub_name

  FROM authors a

  RIGHT OUTER JOIN publishers p

    ON a.city = p.city

  ORDER BY p.pub_name ASC,

    a.au_lname ASC, a.au_fname ASC;

SELECT a.au_fname, a.au_lname,

p.pub_name

FROM authors a, publishers p

WHERE a.city (+) = p.city

ORDER BY p.pub_name ASC,

a.au_lname ASC, a.au_fname ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.29 This full outer join includes all rows in

the tables authors and publishers in the result,

whether or not there’s a match in the city columns.

See Figure 7.29 for the result.

*/

SELECT a.au_fname, a.au_lname, p.pub_name

  FROM authors a

  FULL OUTER JOIN publishers p

    ON a.city = p.city

  ORDER BY p.pub_name ASC,

    a.au_lname ASC, a.au_fname ASC;

SELECT a.au_fname, a.au_lname,

p.pub_name

FROM authors a, publishers p

WHERE a.city  = p.city (+)

UNION ALL

SELECT a.au_fname, a.au_lname,

p.pub_name

FROM authors a, publishers p

WHERE a.city (+) = p.city

AND a.city IS NULL;

  ------Vitalie Bancu @vitallybankou

Listing 7.30 List the number

 of books that each

author wrote (or cowrote), 

including authors who

have written no books. 

See Figure 7.30 for the result.

*/

SELECT

    a.au_id,

    COUNT(ta.title_id) AS "Num books"

  FROM authors a

  LEFT OUTER JOIN title_authors ta

    ON a.au_id = ta.au_id

  GROUP BY a.au_id

  ORDER BY a.au_id ASC;

  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.31 uses a WHERE condition to

test for null and list only the authors who

haven’t written a book. See Figure 7.31 for

the result.

*/

SELECT a.au_id, a.au_fname,

a.au_lname

FROM authors a, title_authors ta

WHERE a.au_id = ta.au_id (+)

AND ta.au_id IS NULL;

DESCRIBE title_authors;

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;