/* Chapter 9 - Try It Out sections */
  
  /* Try It Out: Passing Values to a Comparison Function */
  
  SELECT OrderID, TransID, DVDID
  FROM Transactions
  WHERE DVDID=LEAST(@dvd1, @dvd2)
  ORDER BY OrderID, TransID;
  
  /* Try It Out: Using Control Flow Functions in a SELECT Statement */
  
  SELECT DVDName AS Title, StatID AS Status, RatingID AS Rating,
     IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
  FROM DVDs
  ORDER BY Title;
  
  SELECT DVDName, RatingID AS Rating,
     CASE
        WHEN RatingID='R' THEN 'Under 17 requires an adult.'
        WHEN RatingID='X' THEN 'No one 17 and under.'
        WHEN RatingID='NR' THEN 'Use discretion when renting.'
        ELSE 'OK to rent to minors.'
     END AS Policy
  FROM DVDs
  ORDER BY DVDName;
  
  SELECT DVDName, RatingID AS Rating,
     CASE RatingID
        WHEN 'R' THEN 'Under 17 requires an adult.'
        WHEN 'X' THEN 'No one 17 and under.'
        WHEN 'NR' THEN 'Use discretion when renting.'
        ELSE 'OK to rent to minors.'
     END AS Policy
  FROM DVDs
  ORDER BY DVDName;
  
  /* Try It Out: Converting Data to Different Types */
  
  SELECT OrderID, TransID, DVDID,
     CAST(DateOut AS UNSIGNED INTEGER) AS DateOut_INT
  FROM Transactions
  WHERE DVDID=4 OR DVDID=5 OR DVDID=7
  ORDER BY OrderID, TransID, DVDID;
  
  SELECT OrderID, TransID, DVDID,
     CONVERT(DateOut, UNSIGNED) AS DateOut_INT
  FROM Transactions
  WHERE DVDID=4 OR DVDID=5 OR DVDID=7
  ORDER BY OrderID, TransID, DVDID;
  
  /* Try It Out: Using String Functions in Your SQL Statements */
  
  SELECT DVDName, CHAR_LENGTH(DVDName) AS CharLength
  FROM DVDs
  WHERE CHAR_LENGTH(DVDName)>10
  ORDER BY DVDName;
  
  SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
     CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
  FROM Employees
  ORDER BY EmpID;
  
  SELECT EmpID, UPPER(CONCAT_WS(' ', EmpFN, EmpMN, EmpLN)) AS Name,
     CONCAT(LOWER(SUBSTRING(EmpFN, 2, 2)),
     LOWER(SUBSTRING(EmpLN, 2, 3)), EmpID) AS RegID
  FROM Employees
  ORDER BY EmpID;
  
  /* Try It Out: Using Numeric Functions in Your SQL Statements */
  
  CREATE TABLE Test
  (
     TestID SMALLINT NOT NULL PRIMARY KEY,
     Amount SMALLINT NOT NULL
  );
  
  INSERT INTO Test
  VALUES (101, 12), (102, 1), (103, 139), (104, -37), (105, 0), (106, -16);
  
  /* Try It Out: Using Date/Time Functions in Your SQL Statements */
  
  SELECT TransID, YEAR(DateOut) AS YearOut
  FROM Transactions
  WHERE TransID>15
  ORDER BY TransID;
  
  /* Try It Out: Using Functions to Perform System-Related Operations */
  
  SELECT SQL_CALC_FOUND_ROWS DVDName
  FROM DVDs
  WHERE StatID='s2'
  ORDER BY DVDName
  LIMIT 2;
  
  /* Try It Out: Passing Values to a Comparison Function */
  
  SELECT OrderID, TransID, DVDID
  FROM Transactions
  WHERE DVDID=LEAST(@dvd1, @dvd2)
  ORDER BY OrderID, TransID;