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