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