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