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