/* 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;