topical media & game development

talk show tell print

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.