topical media & game development
basic-mysql-11-ch11-TryItOut.sql / sql
/* 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;
(C) Æliens
20/2/2008
You may not copy or print any of this material without explicit permission of the author or the publisher.
In case of other copyright issues, contact the author.