/* Chapter 8 - Examples */


CREATE TABLE Inventory
(
   ProductID SMALLINT NOT NULL PRIMARY KEY,
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL,
   Reserved SMALLINT NOT NULL
);


INSERT INTO Inventory
VALUES (101, 10, 15, 4), (102, 16, 9, 3), (103, 15, 2, 13);


INSERT INTO Inventory
VALUES (104, 16, 25-InStock, 0);


CREATE TABLE CDs
(
   CDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   CDName VARCHAR(50) NOT NULL,
   InStock SMALLINT UNSIGNED NOT NULL,
   OnOrder SMALLINT UNSIGNED NOT NULL,
   Reserved SMALLINT UNSIGNED NOT NULL,
   Department ENUM('Classical', 'Popular') NOT NULL,
   Category VARCHAR(20)
);


INSERT INTO CDs (CDName, InStock, OnOrder, Reserved, Department, Category)
VALUES ('Bloodshot', 10, 5, 3, 'Popular', 'Rock'),
('The Most Favorite Opera Duets', 10, 5, 3, 'Classical', 'Opera'),
('New Orleans Jazz', 17, 4, 1, 'Popular', 'Jazz'),
('Music for Ballet Class', 9, 4, 2, 'Classical', 'Dance'),
('Music for Solo Violin', 24, 2, 5, 'Classical', NULL),
('Cie li di Toscana', 16, 6, 8, 'Classical', NULL),
('Mississippi Blues', 2, 25, 6, 'Popular', 'Blues'),
('Pure', 32, 3, 10, 'Popular', NULL),
('Mud on the Tires', 12, 15, 13, 'Popular', 'Country'),
('The Essence', 5, 20, 10, 'Popular', 'New Age'),
('Embrace', 24, 11, 14, 'Popular', 'New Age'),
('The Magic of Satie', 42, 17, 17, 'Classical', NULL),
('Swan Lake', 25, 44, 28, 'Classical', 'Dance'),
('25 Classical Favorites', 32, 15, 12, 'Classical', 'General'),
('La Boheme', 20, 10, 5, 'Classical', 'Opera'),
('Bach Cantatas', 23, 12, 8, 'Classical', 'General'),
('Golden Road', 23, 10, 17, 'Popular', 'Country'),
('Live in Paris', 18, 20, 10, 'Popular', 'Jazz'),
('Richland Woman Blues', 22, 5, 7, 'Popular', 'Blues'),
('Morimur (after J. S. Bach)', 28, 17, 16, 'Classical', 'General'),
('The Best of Italian Opera', 10, 35, 12, 'Classical', 'Opera'),
('Runaway Soul', 15, 30, 14, 'Popular', NULL),
('Stages', 42, 0, 8, 'Popular', 'Blues'),
('Bach: Six Unaccompanied Cello Suites', 16, 8, 8, 'Classical', 'General');


SELECT CDName, Department, Category
FROM CDs
WHERE Category=NULL
ORDER BY CDName;


SELECT CDName, Department, Category
FROM CDs
WHERE Category<=>NULL
ORDER BY CDName;


SELECT CDName, Department, Category
FROM CDs
WHERE Category IS NULL
ORDER BY CDName;


SELECT CDName, Department, Category
FROM CDs
WHERE Category IS NOT NULL
ORDER BY CDName;


SELECT CDName, InStock, OnOrder, Reserved
FROM CDs
WHERE (InStock+OnOrder-Reserved)>20
ORDER BY CDName;


SELECT CDName, InStock, OnOrder, Reserved
FROM CDs
WHERE (InStock+OnOrder-Reserved)<20
ORDER BY CDName;


SELECT CDName, Category, InStock
FROM CDs
WHERE Category IN ('Blues', 'Jazz')
ORDER BY CDName;


SELECT CDName, InStock, OnOrder, Reserved
FROM CDs
WHERE (InStock+OnOrder-Reserved) NOT BETWEEN 10 AND 20
ORDER BY CDName;


SELECT CDName, InStock+OnOrder-Reserved AS Available
FROM CDs
WHERE CDName LIKE '%bach%'
ORDER BY CDName;


SELECT CDName, InStock+OnOrder-Reserved AS Available
FROM CDs
WHERE CDName REGEXP '^[a-f]'
ORDER BY CDName;


SELECT CDName, InStock
FROM CDs
WHERE CDName REGEXP '^[mn].*[sz]$'
ORDER BY CDName;


CREATE TABLE Books
(
   BookID SMALLINT NOT NULL PRIMARY KEY,
   BookName VARCHAR(40) NOT NULL,
   Category VARCHAR(15),
   InStock SMALLINT NOT NULL,
   OnOrder SMALLINT NOT NULL
);


INSERT INTO Books
VALUES (101, 'Noncomformity: Writing on Writing', 'Nonfiction', 12, 13),
(102, 'The Shipping News', 'Fiction', 17, 20),
(103, 'Hell\’s Angels', 'Nonfiction', 23, 33),
(104, 'Letters to a Young Poet', 'Nonfiction', 32, 12),
(105, 'A Confederacy of Dunces', 'Fiction', 6, 35),
(106, 'One Hundred Years of Solitude', 'Fiction', 28, 14),
(107, 'Where I\’m Calling From', NULL, 46, 3);


SELECT BookName, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' AND (InStock+OnOrder)>40
ORDER BY BookName;


SELECT BookName, Category, InStock, OnOrder
FROM Books
WHERE InStock>30 OR OnOrder>30
ORDER BY BookName;


SELECT BookName, Category, InStock, OnOrder
FROM Books
WHERE Category='Fiction' XOR InStock IS NULL
ORDER BY BookName;


SELECT BookName, Category, InStock, OnOrder
FROM Books
WHERE InStock>20 AND (Category IS NULL OR NOT (Category='Fiction'))
ORDER BY BookName;


CREATE TABLE Attributes
(
   UserID SMALLINT NOT NULL PRIMARY KEY,
   Settings TINYINT UNSIGNED NOT NULL
);


INSERT INTO Attributes
VALUES (101, 58), (102, 73), (103, 45);


CREATE TABLE ProductColors
(
   ProdID SMALLINT NOT NULL PRIMARY KEY,
   ProdColor VARCHAR(15) NOT NULL
);


INSERT INTO ProductColors
VALUES (101, 'Red'), (102, 'red'), (103, 'RED'), (104, 'REd'), (105, 'reD'),
   (106, 'Blue'), (107, 'blue'), (108, 'BLUE'), (109, 'BLue'), (110, 'blUE');