/* Chapter 10 - Try It Out sections */
  
  /* Try It Out: Using Full Joins to Retrieve Data */
  
  SELECT DVDName, MTypeDescrip As MovieType
  FROM DVDs AS d, MovieTypes AS mt
  WHERE d.MTypeID=mt.MTypeID AND StatID='s2'
  ORDER BY DVDName;
  
  SELECT DVDName, MTypeDescrip As MovieType,
     CONCAT(d.RatingID, ': ', r.RatingDescrip) AS Rating
  FROM DVDs AS d, MovieTypes AS mt, Ratings AS r
  WHERE d.MTypeID=mt.MTypeID AND d.RatingID=r.RatingID
     AND StatID='s2'
  ORDER BY DVDName;
  
  SELECT DVDName, MTypeDescrip AS MovieType,
     CONCAT(d.RatingID, ': ', r.RatingDescrip) AS Rating
  FROM MovieTypes AS mt CROSS JOIN DVDs AS d USING (MTypeID)
     CROSS JOIN Ratings AS r USING (RatingID)
  WHERE StatID='s2'
  ORDER BY DVDName;
  
  SELECT CONCAT_WS(' ', CustFN, CustMN, CustLN) AS Customer, TransID
  FROM Customers INNER JOIN Orders USING (CustID) 
     INNER JOIN Transactions USING (OrderID)
  WHERE DVDID=4
  ORDER BY CustLN;
  
  SELECT CONCAT_WS(' ', PartFN, PartMN, PartLN) AS Participant, DVDName,
     RoleDescrip AS Role
  FROM DVDs AS d, DVDParticipant AS dp, Participants AS p, Roles AS r
  WHERE d.DVDID=dp.DVDID AND p.PartID=dp.PartID AND r.RoleID=dp.RoleID
  ORDER BY PartLN;
  
  /* Try It Out: Including Subqueries in Your SELECT Statements */
  
  SELECT DVDName
  FROM DVDs
  WHERE StatID<>
     (SELECT StatID FROM Status WHERE StatDescrip='Available')
  ORDER BY DVDName;
  
  SELECT DVDName, MTypeDescrip As MovieType
  FROM DVDs AS d, MovieTypes AS mt
  WHERE d.MTypeID=mt.MTypeID AND StatID=
     (SELECT StatID FROM Status WHERE StatDescrip='Available')
  ORDER BY DVDName;
  
  SELECT DVDName, MTypeDescrip As MovieType
  FROM DVDs AS d, MovieTypes AS mt
  WHERE d.MTypeID=mt.MTypeID AND StatID IN
     (SELECT StatID FROM Status WHERE StatDescrip<>'Available')
  ORDER BY DVDName;
  
  SELECT MTypeID, RatingID, COUNT(*) AS TotalDVDs
  FROM DVDs
  GROUP BY MTypeID, RatingID
  HAVING MTypeID IN
     (SELECT MTypeID FROM MovieTypes WHERE MTypeDescrip<>'Documentary');
  
  /* Try It Out: Using Unions to Join SELECT Statements */
  
  CREATE TABLE Employees2
     (
     EmpID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
     EmpFN VARCHAR(20) NOT NULL,
     EmpLN VARCHAR(20) NOT NULL
     )
  ENGINE=INNODB;
  
  INSERT INTO Employees2
  VALUES (NULL, 'Rebecca', 'Reynolds'),
  (NULL, 'Charlie', 'Waverly');
  
  (SELECT EmpLN, EmpFN FROM Employees)
  UNION
  (SELECT EmpLN, EmpFN FROM Employees2)
  ORDER BY EmpLN;