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