/* Chapter 7 - Examples */
  
  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) NOT NULL,
     RowUpdate TIMESTAMP NOT NULL
  );
  
  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', 'General'),
  ('Cie li di Toscana', 16, 6, 8, 'Classical', 'Vocal'),
  ('Mississippi Blues', 2, 25, 6, 'Popular', 'Blues'),
  ('Pure', 32, 3, 10, 'Popular', 'Jazz'),
  ('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', 'General'),
  ('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', 'Blues'),
  ('Stages', 42, 0, 8, 'Popular', 'Blues'),
  ('Bach: Six Unaccompanied Cello Suites', 16, 8, 8, 'Classical', 'General');
  
  SELECT Category, COUNT(*) AS Total
  FROM CDs
  WHERE Department='Popular'
  GROUP BY Category;
  
  SELECT Category, COUNT(*) AS Total
  FROM CDs
  WHERE Department='Popular'
  GROUP BY Category
  HAVING Total<3;
  
  SELECT CDName, InStock, OnOrder
  FROM CDs
  WHERE InStock>20
  ORDER BY CDName DESC;
  
  SELECT Department, Category, CDName
  FROM CDs
  WHERE (InStock+OnOrder-Reserved)<15
  ORDER BY Department DESC, Category ASC;
  
  SELECT CDID, CDName, InStock
  FROM CDs
  WHERE Department='Classical'
  ORDER BY CDID DESC
  LIMIT 4;
  
  SELECT CDID, CDName, InStock
  FROM CDs
  WHERE Department='Classical'
  ORDER BY CDID DESC
  LIMIT 3,4;