/* Chapter 6 - Examples */
  
  CREATE TABLE CDs
  (
     CDID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     CDName VARCHAR(50) NOT NULL,
     Copyright YEAR,
     NumberDisks TINYINT UNSIGNED NOT NULL DEFAULT 1,
     NumberInStock TINYINT UNSIGNED,
     NumberOnReserve TINYINT UNSIGNED NOT NULL,
     NumberAvailable TINYINT UNSIGNED NOT NULL,
     CDType VARCHAR(20),
     RowAdded TIMESTAMP
  );
  
  INSERT INTO CDs
  VALUES (NULL, 'Ain\’t Ever Satisfied: The Steve Earle Collection',
     1996, 2, 10, 3, NumberInStock-NumberOnReserve, 'Country', NULL);
  
  INSERT LOW_PRIORITY INTO CDs (CDName, Copyright, NumberDisks,
     NumberInStock, NumberOnReserve, NumberAvailable, CDType)
  VALUES ('After the Rain: The Soft Sounds of Erik Satie',
     1995, DEFAULT, 13, 2, NumberInStock - NumberOnReserve, 'Classical');
  
  INSERT INTO CDs (CDName, Copyright, NumberDisks,
     NumberInStock, NumberOnReserve, NumberAvailable, CDType)
  VALUES ('Mule Variations', 1999, 1, 9, 0,
     NumberInStock-NumberOnReserve, 'Blues'),
  ('The Bonnie Raitt Collection', 1990, 1, 14, 2,
     NumberInStock-NumberOnReserve, 'Popular'),
  ('Short Sharp Shocked', 1988, 1, 6, 1,
     NumberInStock-NumberOnReserve, 'Folk-Rock');
  
  INSERT INTO CDs (CDName, Copyright, NumberDisks,
     NumberInStock, NumberOnReserve, NumberAvailable, CDType)
  VALUES ('Blues on the Bayou', 1998, DEFAULT,
     4, 1, NumberInStock-NumberOnReserve, 'Blues');
  
  INSERT DELAYED INTO CDs
  SET CDName='Blues on the Bayou', Copyright=1998,
     NumberDisks=DEFAULT, NumberInStock=4, NumberOnReserve=1,
     NumberAvailable=NumberInStock-NumberOnReserve, CDType='Blues';
  
  CREATE TABLE Inventory
  (
     ProductID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
     NumberInStock SMALLINT UNSIGNED NOT NULL,
     NumberOnOrder SMALLINT UNSIGNED NOT NULL,
     DateUpdated DATE
  );
  
  CREATE TABLE Books
  (
     BookID SMALLINT NOT NULL PRIMARY KEY,
     BookName VARCHAR(40) NOT NULL,
     InStock SMALLINT NOT NULL
  )
  ENGINE=INNODB;
  
  INSERT INTO Books
  VALUES (101, 'Noncomformity: Writing on Writing', 12),
  (102, 'The Shipping News', 17),
  (103, 'Hell\’s Angels', 23),
  (104, 'Letters to a Young Poet', 32),
  (105, 'A Confederacy of Dunces', 6),
  (106, 'One Hundred Years of Solitude', 28);
  
  CREATE TABLE Orders
  (
     OrderID SMALLINT NOT NULL PRIMARY KEY,
     BookID SMALLINT NOT NULL,
     Quantity TINYINT (40) NOT NULL DEFAULT 1,
     DateOrdered TIMESTAMP,
     FOREIGN KEY (BookID) REFERENCES Books (BookID)
  )
  ENGINE=INNODB;
  
  INSERT INTO Orders
  VALUES (1001, 103, 1, '2004-10-12 12:30:00'),
  (1002, 101, 1, '2004-10-12 12:31:00'),
  (1003, 103, 2, '2004-10-12 12:34:00'),
  (1004, 104, 3, '2004-10-12 12:36:00'),
  (1005, 102, 1, '2004-10-12 12:41:00'),
  (1006, 103, 2, '2004-10-12 12:59:00'),
  (1007, 101, 1, '2004-10-12 13:01:00'),
  (1008, 103, 1, '2004-10-12 13:02:00'),
  (1009, 102, 4, '2004-10-12 13:22:00'),
  (1010, 101, 2, '2004-10-12 13:30:00'),
  (1011, 103, 1, '2004-10-12 13:32:00'),
  (1012, 105, 1, '2004-10-12 13:40:00'),
  (1013, 106, 2, '2004-10-12 13:44:00'),
  (1014, 103, 1, '2004-10-12 14:01:00'),
  (1015, 106, 1, '2004-10-12 14:05:00'),
  (1016, 104, 2, '2004-10-12 14:28:00'),
  (1017, 105, 1, '2004-10-12 14:31:00'),
  (1018, 102, 1, '2004-10-12 14:32:00'),
  (1019, 106, 3, '2004-10-12 14:49:00'),
  (1020, 103, 1, '2004-10-12 14:51:00');
  
  UPDATE Orders
  SET Quantity=Quantity+1
  WHERE BookID=103
  ORDER BY DateOrdered DESC
  LIMIT 5;
  
  UPDATE Books, Orders
  SET Books.InStock=Books.InStock-Orders.Quantity
  WHERE Books.BookID=Orders.BookID
     AND Orders.OrderID=1002;
  
  UPDATE Books, Orders
  SET Orders.Quantity=Orders.Quantity+2,
     Books.InStock=Books.InStock-2
  WHERE Books.BookID=Orders.BookID
     AND Orders.OrderID = 1002;
  
  DELETE LOW_PRIORITY FROM Orders
  WHERE BookID=103
  ORDER BY DateOrdered DESC
  LIMIT 1;
  
  DELETE Orders.*
  FROM Books, Orders
  WHERE Books.BookID=Orders.BookID
     AND Books.BookName='Where I\’m Calling From';
  
  DELETE FROM Orders
  USING Books, Orders
  WHERE Books.BookID=Orders.BookID
     AND Books.BookName='Where I\’m Calling From';