/* Chapter 11 - Try It Out sections */


/* Try It Out: Exporting DVDRentals Data to an Out File */

SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
   INTO OUTFILE 'AvailDVDs.txt'
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND StatID='s2'
ORDER BY DVDName;


SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
   INTO OUTFILE 'AvailDVDs2.txt'
   FIELDS TERMINATED BY '*,*'
   LINES TERMINATED BY '**\n'
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND StatID='s2'
ORDER BY DVDName;



/* Try It Out: Exporting DVDRentals Data to a Dump File */

SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
   INTO DUMPFILE 'DVD3.txt'
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND DVDID=3;



/* Try It Out: Copying Data to a New Table in the DVDRentals Database */

CREATE TABLE DVDs2
(
   DVDName VARCHAR(60) NOT NULL,
   MTypeDescrip VARCHAR(30) NOT NULL,
   FormDescrip VARCHAR(15) NOT NULL,
   RatingID VARCHAR(4) NOT NULL
)
SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND StatID='s2'
ORDER BY DVDName;



/* Try It Out: Copying Data to an Existing Table in the DVDRentals Database */

INSERT INTO DVDs2
SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND StatID='s1'
ORDER BY DVDName;



/* Try It Out: Using the LOAD DATA Statement to Import Data into the DVDRentals Database */

LOAD DATA INFILE 'AvailDVDs2.txt'
INTO TABLE DVDs2
FIELDS TERMINATED BY '*,*'
LINES TERMINATED BY '**\n';



/* Try It Out: Using the mysqlimport Utility to Import Data into the DVDRentals Database */

SELECT DVDName, MTypeDescrip, FormDescrip, d.RatingID
   INTO OUTFILE 'DVDs2.txt'
FROM DVDs AS d, MovieTypes AS m, Formats AS f
WHERE d.MTypeID=m.MTypeID AND d.FormID=f.FormID
   AND DVDID=1;