SELECT title_id, pub_id,

pub_name

FROM publishers

INNER JOIN titles

USING (pub_id);

SELECT title_id, pub_id,

pub_name, advance

FROM publishers

INNER JOIN titles

USING (pub_id)

INNER JOIN royalties

USING (title_id)

WHERE advance < 20000;

  ------Vitalie Bancu @vitallybankou

Listing 7.7 List the books that each author wrote (or

cowrote). See Figure 7.7 for the result.

*/

SELECT

    a.au_id,

    a.au_fname,

    a.au_lname,

    ta.title_id

  FROM authors a

  INNER JOIN title_authors ta

    ON a.au_id = ta.au_id

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

SELECT * 

FROM AUTHORS;

SELECT * 

FROM TITLE_AUTHORS

ORDER BY AU_ID ASC;

SELECT a.au_id, a.au_fname,

a.au_lname, ta.title_id

FROM authors a, title_authors ta

WHERE a.au_id = ta.au_id

ORDER BY a.au_id ASC,

ta.title_id ASC;

SELECT

    t.title_id,

    t.title_name,

    t.pub_id,

    p.pub_name

  FROM titles t

  INNER JOIN publishers p

    ON p.pub_id = t.pub_id

  ORDER BY t.title_name ASC;

SELECT t.title_id, t.title_name,

t.pub_id, p.pub_name

FROM titles t, publishers p

WHERE p.pub_id = t.pub_id

ORDER BY t.title_name ASC;

SELECT

    t.title_id,

    t.title_name,

    t.pub_id,

    p.pub_name

  FROM titles t

  INNER JOIN publishers p

    ON p.pub_id = t.pub_id

  ORDER BY t.title_name ASC;

SELECT

    a.au_id,

    a.au_fname,

    a.au_lname,

    a.city,

    a.state

  FROM authors a

  INNER JOIN publishers p

    ON a.city = p.city

    AND a.state = p.state

  ORDER BY a.au_id ASC;

    ------Vitalie Bancu @vitallybankou

  SELECT a.au_id, a.au_fname,

a.au_lname, a.city, a.state

FROM authors a

NATURAL JOIN publishers p

ORDER BY a.au_id ASC;

  */

SELECT t.title_id, t.title_name,

p.state, p.country

FROM titles t, publishers p

WHERE t.pub_id = p.pub_id

AND (p.state = 'CA'

OR p.country NOT IN

('USA', 'Canada', 'Mexico'))

ORDER BY t.title_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.10 List the books published in California or

outside the large North American countries. See

Figure 7.10 for the result.

*/  

SELECT

    t.title_id,

    t.title_name,

    p.state,

    p.country

  FROM titles t

  INNER JOIN publishers p

    ON t.pub_id = p.pub_id

  WHERE p.state = 'CA'

    OR p.country NOT IN

      ('USA', 'Canada', 'Mexico')

  ORDER BY t.title_id ASC;

  ------Vitalie Bancu @vitallybankou

Listing 7.11 List the number of books that each author

wrote (or cowrote). See Figure 7.11 for the result.

*/  

 SELECT

    a.au_id,

    COUNT(ta.title_id) AS "Num books"

  FROM authors a

  INNER JOIN title_authors ta

    ON a.au_id = ta.au_id

  GROUP BY a.au_id

  ORDER BY a.au_id ASC;

  ------Vitalie Bancu @vitallybankou

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;

*/