/* Chapter 9 - Examples */


SELECT BookID, BookTitle, PublisherID
FROM Books
WHERE PublisherID=GREATEST(@id1, @id2, @id3, @id4, @id5)
ORDER BY BookTitle;


SELECT CASE WHEN 10*2=30 THEN '30 correct'
   WHEN 10*2=40 THEN '40 correct'
   ELSE 'Should be 10*2=20'
END;


SELECT CASE 10*2
   WHEN 20 THEN '20 correct'
   WHEN 30 THEN '30 correct'
   WHEN 40 THEN '40 correct'
END;


CREATE TABLE Classes
(
   ClassID SMALLINT NOT NULL PRIMARY KEY,
   Dept CHAR(4) NOT NULL,
   Level ENUM('Upper', 'Lower') NOT NULL,
   TotalStudents TINYINT UNSIGNED NOT NULL
);


INSERT INTO Classes
VALUES (1001, 'ANTH', 'Upper', 25),
(1002, 'ANTH', 'Upper', 25),
(1003, 'MATH', 'Upper', 18),
(1004, 'ANTH', 'Lower', 19),
(1005, 'ENGL', 'Upper', 28),
(1006, 'MATH', 'Lower', 23),
(1007, 'ENGL', 'Upper', 25),
(1008, 'MATH', 'Lower', 29),
(1009, 'ANTH', 'Upper', 25),
(1010, 'ANTH', 'Lower', 30),
(1011, 'ENGL', 'Lower', 26),
(1012, 'MATH', 'Lower', 22),
(1013, 'ANTH', 'Upper', 27),
(1014, 'ANTH', 'Upper', 21),
(1015, 'ENGL', 'Lower', 25),
(1016, 'ENGL', 'Upper', 32);


CREATE TABLE UserAccounts
(
   UserID SMALLINT NOT NULL PRIMARY KEY,
   Password VARCHAR(20) NOT NULL
);


INSERT INTO UserAccounts
VALUES (101, ENCODE('pw101', 'key101'));