/* Chapter 10 - Examples */
  
  CREATE TABLE Books
  (
     BookID SMALLINT NOT NULL PRIMARY KEY,
     BookTitle VARCHAR(60) NOT NULL,
     Copyright YEAR NOT NULL
  )
  ENGINE=INNODB;
  
  INSERT INTO Books
  VALUES (12786, 'Letters to a Young Poet', 1934),
  (13331, 'Winesburg, Ohio', 1919),
  (14356, 'Hell\’s Angels', 1966),
  (15729, 'Black Elk Speaks', 1932),
  (16284, 'Noncomformity', 1996),
  (17695, 'A Confederacy of Dunces', 1980),
  (19264, 'Postcards', 1992),
  (19354, 'The Shipping News', 1993);
  
  CREATE TABLE Authors
  (
     AuthID SMALLINT NOT NULL PRIMARY KEY,
     AuthFN VARCHAR(20),
     AuthMN VARCHAR(20),
     AuthLN VARCHAR(20)
  )
  ENGINE=INNODB;
  
  INSERT INTO Authors
  VALUES (1006, 'Hunter', 'S.', 'Thompson'),
  (1007, 'Joyce', 'Carol', 'Oates'),
  (1008, 'Black', NULL, 'Elk'),
  (1009, 'Rainer', 'Maria', 'Rilke'),
  (1010, 'John', 'Kennedy', 'Toole'),
  (1011, 'John', 'G.', 'Neihardt'),
  (1012, 'Annie', NULL, 'Proulx'),
  (1013, 'Alan', NULL, 'Watts'),
  (1014, 'Nelson', NULL, 'Algren');
  
  CREATE TABLE AuthorBook
  (
     AuthID SMALLINT NOT NULL,
     BookID SMALLINT NOT NULL,
     PRIMARY KEY (AuthID, BookID),
     FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
     FOREIGN KEY (BookID) REFERENCES Books (BookID)
  )
  ENGINE=INNODB;
  
  INSERT INTO AuthorBook
  VALUES (1006, 14356), (1008, 15729), (1009, 12786), (1010, 17695),
  (1011, 15729), (1012, 19264), (1012, 19354), (1014, 16284);
  
  SELECT BookTitle, Copyright, AuthID
  FROM Books AS b, AuthorBook AS ab
  WHERE b.BookID=ab.BookID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, ab.AuthID
  FROM Books AS b, AuthorBook AS ab
  WHERE b.BookID=ab.BookID AND Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b, AuthorBook AS ab, Authors AS a
  WHERE b.BookID=ab.BookID AND ab.AuthID=a.AuthID AND Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, AuthID FROM Books, AuthorBook;
  SELECT BookTitle, AuthID FROM Books JOIN AuthorBook;
  SELECT BookTitle, AuthID FROM Books INNER JOIN AuthorBook;
  SELECT BookTitle, AuthID FROM Books CROSS JOIN AuthorBook;
  
  SELECT BookTitle, Copyright, ab.AuthID
  FROM Books AS b JOIN AuthorBook AS ab
     ON b.BookID=ab.BookID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, ab.AuthID
  FROM Books JOIN AuthorBook AS ab
     USING (BookID)
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, ab.AuthID
  FROM Books AS b JOIN AuthorBook AS ab
     ON b.BookID=ab.BookID
  WHERE Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b CROSS JOIN AuthorBook AS ab ON b.BookID=ab.BookID
     CROSS JOIN Authors AS a ON ab.AuthID=a.AuthID
  WHERE Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books JOIN AuthorBook USING (BookID)
     JOIN Authors USING (AuthID)
  WHERE Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b STRAIGHT_JOIN AuthorBook AS ab STRAIGHT_JOIN Authors AS a
  WHERE b.BookID=ab.BookID AND ab.AuthID=a.AuthID AND Copyright<1980
  ORDER BY BookTitle;
  
  SELECT STRAIGHT_JOIN BookTitle, Copyright,
     CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b, AuthorBook AS ab, Authors AS a
  WHERE b.BookID=ab.BookID AND ab.AuthID=a.AuthID AND Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, AuthID
  FROM Books AS b LEFT JOIN AuthorBook AS ab
     ON b.BookID=ab.BookID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, AuthID
  FROM Books LEFT JOIN AuthorBook
     USING (BookID)
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b LEFT JOIN AuthorBook AS ab ON b.BookID=ab.BookID
     LEFT JOIN Authors AS a ON ab.AuthID=a.AuthID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, AuthFN, AuthMN, AuthLN
  FROM Books AS b LEFT JOIN AuthorBook AS ab ON b.BookID=ab.BookID
     LEFT JOIN Authors AS a ON ab.AuthID=a.AuthID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright, CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b RIGHT JOIN AuthorBook AS ab ON b.BookID=ab.BookID
     RIGHT JOIN Authors AS a ON ab.AuthID=a.AuthID
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright,
     CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b NATURAL JOIN AuthorBook AS ab
     NATURAL JOIN Authors AS a
  WHERE Copyright<1980
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright,
     CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Books AS b NATURAL LEFT JOIN AuthorBook AS ab
     NATURAL LEFT JOIN Authors AS a
  WHERE Copyright<1980
  ORDER BY BookTitle;
  
  UPDATE Authors AS a, AuthorBook AS ab, Books AS b
  SET AuthLN='Wats'
  WHERE a.AuthID=ab.AuthID AND ab.BookID=b.BookID
     AND BookTitle='Tao: The Watercourse Way';
  
  UPDATE Authors CROSS JOIN AuthorBook USING (AuthID)
     CROSS JOIN Books USING (BookID)
  SET AuthLN='Watts'
  WHERE BookTitle='Tao: The Watercourse Way';
  
  DELETE ab
  FROM AuthorBook AS ab, Authors AS a
  WHERE ab.AuthID=a.AuthID AND AuthLN='Watts';
  
  DELETE ab, b
  FROM Authors AS a, AuthorBook AS ab, Books AS b
  WHERE a.AuthID=ab.AuthID AND ab.BookID=b.BookID
     AND AuthLN='Watts';
  
  DELETE ab, b
  FROM Authors AS a INNER JOIN AuthorBook AS ab ON a.AuthID=ab.AuthID
     INNER JOIN Books AS b ON ab.BookID=b.BookID
  WHERE AuthLN='Watts';
  
  SELECT CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Authors
  WHERE AuthID=
     (
        SELECT ab.AuthID
        FROM AuthorBook AS ab, Books AS b
        WHERE ab.BookID=b.BookID AND BookTitle='Noncomformity'
     );
  
  SELECT DISTINCT CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Authors AS a JOIN AuthorBook AS ab ON a.AuthID=ab.AuthID
     JOIN Books AS b ON ab.BookID=b.BookID
  WHERE BookTitle='Noncomformity';
  
  SELECT CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Authors
  WHERE AuthID=
     (
        SELECT ab.AuthID
        FROM AuthorBook AS ab, Books AS b
        WHERE ab.BookID=b.BookID AND BookTitle='Black Elk Speaks'
     );
  
  SELECT DISTINCT CONCAT_WS(' ', AuthFN, AuthMN, AuthLN) AS Author
  FROM Authors
  WHERE AuthID<>
     (
        SELECT ab.AuthID
        FROM AuthorBook AS ab, Books AS b
        WHERE ab.BookID=b.BookID AND BookTitle='Noncomformity'
     )
  ORDER BY AuthLN;
  
  SELECT BookTitle, Copyright
  FROM Books
  WHERE Copyright<(SELECT MAX(Copyright)-50 FROM Books)
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright
  FROM Books
  WHERE Copyright > ANY
     (
        SELECT b.copyright
        FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
           JOIN Authors AS a USING (AuthID)
        WHERE AuthLN='Proulx'
     )
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright
  FROM Books
  WHERE Copyright > ALL
     (
        SELECT b.copyright
        FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
           JOIN Authors AS a USING (AuthID)
        WHERE AuthLN='Proulx'
     )
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright
  FROM Books
  WHERE Copyright IN
     (
        SELECT b.copyright
        FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
           JOIN Authors AS a USING (AuthID)
        WHERE AuthLN='Proulx'
     )
  ORDER BY BookTitle;
  
  SELECT BookTitle, Copyright
  FROM Books
  WHERE Copyright NOT IN
     (
        SELECT b.copyright
        FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
           JOIN Authors AS a USING (AuthID)
        WHERE AuthLN='Proulx'
     )
  ORDER BY BookTitle;
  
  SELECT BookID, BookTitle
  FROM Books AS b
  WHERE EXISTS
     (
        SELECT BookID
        FROM AuthorBook AS ab
        WHERE b.BookID=ab.BookID
     )
  ORDER BY BookTitle;
  
  SELECT BookID, BookTitle
  FROM Books AS b
  WHERE NOT EXISTS
     (
        SELECT BookID
        FROM AuthorBook AS ab
        WHERE b.BookID=ab.BookID
     )
  ORDER BY BookTitle;
  
  CREATE TABLE BookOrders
  (
     OrderID SMALLINT NOT NULL,
     BookID SMALLINT NOT NULL,
     Quantity SMALLINT NOT NULL,
     PRIMARY KEY (OrderID, BookID),
     FOREIGN KEY (BookID) REFERENCES Books (BookID)
  )
  ENGINE=INNODB;
  
  INSERT INTO BookOrders
  VALUES (101, 13331, 1), (101, 12786, 1), (101, 16284, 2), (102, 19354, 1),
  (102, 15729, 3), (103, 12786, 2), (103, 19264, 1), (103, 13331, 1),
  (103, 14356, 2), (104, 19354, 1), (105, 15729, 1), (105, 14356, 2),
  (106, 16284, 2), (106, 13331, 1), (107, 12786, 3), (108, 19354, 1),
  (108, 16284, 4), (109, 15729, 1), (110, 13331, 2), (110, 12786, 2),
  (110, 14356, 2), (111, 14356, 2);
  
  SELECT OrderID, SUM(Quantity) AS Total
  FROM BookOrders
  GROUP BY OrderID
  HAVING Total>(SELECT AVG(Quantity) FROM BookOrders);
  
  SELECT BookID, SUM(Quantity) AS Total
  FROM BookOrders
  GROUP BY BookID
  HAVING BookID IN
     (SELECT BookID FROM AuthorBook WHERE AuthID IN
        (
           SELECT AuthID FROM AuthorBook
           GROUP BY AuthID
           HAVING COUNT(*)>1
        )
     );
  
  SELECT BookID FROM AuthorBook WHERE AuthID IN
     (
        SELECT AuthID FROM AuthorBook
        GROUP BY AuthID
        HAVING COUNT(*)>1
     );
  
  UPDATE Books
  SET BookTitle='The Way of Zen', Copyright=1957
  WHERE BookID=
     (
        SELECT ab.BookID
        FROM Authors AS a, AuthorBook AS ab
        WHERE a.AuthID=ab.AuthID AND a.AuthLN='Watts'
     );
  
  DELETE ab, b
  FROM AuthorBook AS ab, Books AS b
  WHERE ab.BookID=b.BookID
     AND ab.AuthID=(SELECT AuthID FROM Authors WHERE AuthLN='Watts');
  
  CREATE TABLE Authors2
  (
     AuthID SMALLINT NOT NULL PRIMARY KEY,
     AuthFN VARCHAR(20),
     AuthMN VARCHAR(20),
     AuthLN VARCHAR(20)
  );
  
  INSERT INTO Authors2
  VALUES (1006, 'Mark', NULL, 'Twain'),
  (2205, 'E.', 'M.', 'Forster'),
  (2206, 'Gabriel', 'Garcia', 'Marquez'),
  (2207, 'Raymond', NULL, 'Carver'),
  (2208, 'Mary', NULL, 'Shelley'),
  (2209, 'Albert', NULL, 'Camus');
  
  (SELECT AuthFN, AuthMN, AuthLN FROM Authors)
  UNION
  (SELECT AuthFN, AuthMN, AuthLN FROM Authors2)
  ORDER BY AuthLN;