Chapter 7 Notes (Joins)

authors who live in the same city as a publisher

SELECT au_id, authors.city
  FROM authors
  INNER JOIN publishers
    ON authors.city = publishers.city;

With an alias

SELECT au_fname, au_lname, a.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;

inner join syntax

SELECT au_fname, au_lname, a.city
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;

where snytax

SELECT au_fname, au_lname, a.city
  FROM authors a, publishers p
  WHERE a.city = p.city;

all possible combinations of rows from two tables

SELECT
    au_id,
    pub_id,
    a.state AS "au_state",
    p.state AS "pub_state"
  FROM authors a
  CROSS JOIN publishers p;

list each books publisher (natural join)

SELECT
    title_id,
    pub_id,
    pub_name
  FROM publishers
  NATURAL JOIN titles;

list books publishers and advance with advance < 20000

SELECT
    title_id,
    pub_id,
    pub_name,
    advance
  FROM publishers
  NATURAL JOIN titles
  NATURAL JOIN royalties
  WHERE advance < 20000;

list books that authors wrote or cowrote

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;

list books titles and publishers

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;

list authors who live in the same cityand state as a publisher

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;

list books published in CA or outside north america

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;

list the number of books that each author wrote

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;

list the advance paid for each biography

SELECT
    t.title_id,
    t.title_name,
    r.advance
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE t.type = 'biography'
    AND r.advance IS NOT NULL
  ORDER BY r.advance DESC;

list the count and advance for each biography

SELECT
    t.type,
    COUNT(r.advance)
      AS "COUNT(r.advance)",
    SUM(r.advance)
      AS "SUM(r.advance)"
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE r.advance IS NOT NULL
  GROUP BY t.type
  ORDER BY t.type ASC;

list count and advance for each type of book by publisher

SELECT
    t.type,
    t.pub_id,
    COUNT(r.advance) AS "COUNT(r.advance)",
    SUM(r.advance) AS "SUM(r.advance)"
  FROM royalties r
  INNER JOIN titles t
    ON r.title_id = t.title_id
  WHERE r.advance IS NOT NULL
  GROUP BY t.type, t.pub_id
  ORDER BY t.type ASC, t.pub_id ASC;

list the number of coauthors for each book written by two or more authors

SELECT
    ta.title_id,
    COUNT(ta.au_id) AS "Num authors"
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  GROUP BY ta.title_id
  HAVING COUNT(ta.au_id) > 1
  ORDER BY ta.title_id ASC;

list each book whose revenue (price * sales) is 10 times greater than adv

SELECT
    a.au_fname,
    a.au_lname,
    t.title_name
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  ORDER BY a.au_lname ASC, a.au_fname ASC,
    t.title_name ASC;

list authors, books and publishers

SELECT
    a.au_fname,
    a.au_lname,
    t.title_name,
    p.pub_name
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN publishers p
    ON p.pub_id = t.pub_id
  ORDER BY a.au_lname ASC, a.au_fname ASC,
    t.title_name ASC;

Calculate the total royalties for each book

SELECT
    SUM(t.sales * t.price * r.royalty_rate) AS "Total royalties",
    SUM(r.advance) AS "Total advances",
    SUM((t.sales * t.price * r.royalty_rate) - r.advance) AS "Total due to authors"
  FROM titles t
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL;

calculate royalties fro weach author/book

SELECT
    ta.au_id,
    t.title_id,
    t.pub_id,
    t.sales * t.price * r.royalty_rate * ta.royalty_share AS "Royalty share",
    r.advance * ta.royalty_share AS "Advance share",
    (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) AS "Due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  ORDER BY ta.au_id ASC, t.title_id ASC;

list only positive royalties for each author/book

SELECT
    a.au_id,
    a.au_fname,
    a.au_lname,
    t.title_name,
    (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) AS "Due to author"
  FROM authors a
  INNER JOIN title_authors ta
    ON a.au_id = ta.au_id
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
    AND (t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share) > 0
  ORDER BY a.au_id ASC, t.title_id ASC;

calc total royalties by publisher

SELECT
    t.pub_id,
    COUNT(t.sales) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate) AS "Total royalties",
    SUM(r.advance) AS "Total advances",
    SUM((t.sales * t.price * r.royalty_rate) - r.advance) AS "Total due to authors"
  FROM titles t
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  GROUP BY t.pub_id
  ORDER BY t.pub_id ASC;

calc total royalties by author

SELECT
    ta.au_id,
    COUNT(sales) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share) AS "Total royalties share",
    SUM(r.advance * ta.royalty_share) AS "Total advances share",
    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share)) AS "Total due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  WHERE t.sales IS NOT NULL
  GROUP BY ta.au_id
  ORDER BY ta.au_id ASC;

calc positive royalties to be paid by each US publisher/author

SELECT
    t.pub_id,
    ta.au_id,
    COUNT(*) AS "Num books",
    SUM(t.sales * t.price * r.royalty_rate * ta.royalty_share) AS "Total royalties share",
    SUM(r.advance * ta.royalty_share) AS "Total advances share",
    SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) -
      (r.advance * ta.royalty_share)) AS "Total due to author"
  FROM title_authors ta
  INNER JOIN titles t
    ON t.title_id = ta.title_id
  INNER JOIN royalties r
    ON r.title_id = t.title_id
  INNER JOIN publishers p
    ON p.pub_id = t.pub_id
  WHERE t.sales IS NOT NULL
    AND p.country IN ('USA')
  GROUP BY t.pub_id, ta.au_id
  HAVING SUM((t.sales * t.price * r.royalty_rate * ta.royalty_share) - 
    (r.advance * ta.royalty_share)) > 0
  ORDER BY t.pub_id ASC, ta.au_id ASC;

list the cities of the authors

SELECT a.au_fname, a.au_lname, a.city
  FROM authors a;

list the cities of the publishers

SELECT p.pub_name, p.city
  FROM publishers p;

list the authors that live in a city with a publisher (inner join or equi-join)

SELECT a.au_fname, a.au_lname, p.pub_name
  FROM authors a
  INNER JOIN publishers p
    ON a.city = p.city;

same as above ??? (LEFT join)

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;

same as above (RIGHT join)

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;

use full outer join (Does not work in mySQL)

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;

### use full outer join (works in mySQL)

(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)
UNION
(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);

list the number of books each author wrote including authors with no books

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;

list the authors with NO books

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;

list authors and any book that sold more than 100,000 copies

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;

list each employee and their boss

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;

list the authors that live in the same state as A04

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

for each biography list the ID sand sales of other biographies that outsold it

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;

list the pairs of all authors that live in NY

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;

same ??

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;

same ??

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;