{\rtf1\ansi\deff0\adeflang1025
{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman{\*\falt Thorndale};}{\f1\froman\fprq2\fcharset0 Times New Roman{\*\falt Thorndale};}{\f2\froman\fprq2\fcharset0 Times New Roman;}{\f3\fnil\fprq2\fcharset0 HG Mincho Light J;}{\f4\fnil\fprq2\fcharset0 Arial Unicode MS;}}
{\colortbl;\red0\green0\blue0;\red128\green128\blue128;}
{\stylesheet{\s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\snext1 Default;}
{\s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\sbasedon1\snext2 Normal;}
{\*\cs4\cf1\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033 Numbering Symbols;}
}
{\info{\author Sharon Tuttle}{\creatim\yr2003\mo2\dy24\hr9\min6}{\operator Sharon Tuttle}{\revtim\yr2003\mo3\dy14\hr12\min41}{\printim\yr2003\mo3\dy14\hr9\min51}{\comment StarWriter}{\vern6410}}\deftab1250
{\*\pgdsctbl
{\pgdsc0\pgdscuse195\pgwsxn12240\pghsxn15840\marglsxn1800\margrsxn1800\margtsxn1440\margbsxn1440\pgdscnxt0 Default;}}
\paperh15840\paperw12240\margl1800\margr1800\margt1440\margb1440\sectd\sbknone\pgwsxn12240\pghsxn15840\marglsxn1800\margrsxn1800\margtsxn1440\margbsxn1440\ftnbj\ftnstart1\ftnrstcont\ftnnar\aenddoc\aftnrstcont\aftnstart1\aftnnrlc
\pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\qc\ltrch\loch\f2\fs22\b {\ltrch\loch\f2 HUMBOLDT STATE UNIVERSITY}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\qc\ltrch\loch\f2\fs22\b {\ltrch\loch\f2 Spring 2003 - CIS 180 L - Section 3 - Intro to SQL}
\par {\ltrch\loch\f2 Homework #6 - Due by 10:00 am on Monday, March 24th}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Precede each of your answers to the following (except for #1 {\b and} #2...) with a {\b prompt}{\b0  command containing the number of the question being "answered" by that statement/those statements. (for example,\tab }{\b prompt === question #3 === }{\b0 )}}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 1.\tab Create a SQL script named {\b 180hw06.sql}. Make sure that a copy of {\b hw3_setup.sql}{\b0  is in your local directory. }Then, {\b inside}{\b0  of this SQL script, start by:}}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 *\tab {\b running} hw3_setup.sql from within your script --- so that you always start with a fresh  standard set of these tables when this script begins;}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 *\tab {\b THEN} start spooling to an output file {\b 180hw06_results.txt}.}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 (reminder: here are the structures for the tables set up in {\b hw3_setup.sql}:)}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Movie_category({\b\ul CATEGORY_CODE}, category_name)}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs12 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Renter({\b RENTER{\ul _NUM}}, renter_lname, renter_fname, renter_phone, renter_credit_rtg, renter_fave_cat)}
\par {\ltrch\loch\f2 \tab (column {\b renter_fave_cat}{\b0  is a }{\b foreign key}{\b0  referencing movie_category's category_code)}}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs12 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Movie({\b\ul MOVIE_NUM}, movie_title, movie_director_lname, movie_yr_released, movie_rating, category_code)}
\par {\ltrch\loch\f2 \tab (column {\b category_code}{\b0  is a }{\b foreign key}{\b0  referencing movie_category's category_code)}}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs12 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Video({\b\ul VID_ID}, vid_format, vid_purchase_date, vid_rental_price, movie_num)}
\par {\ltrch\loch\f2 \tab (column {\b movie_num}{\b0  is a }{\b foreign key}{\b0  referencing movie's movie_num)}}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs12 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Rental({\b\ul RENTAL_NUM}, renter_num, vid_id, date_out, date_due, date_returned)}
\par {\ltrch\loch\f2 \tab (column {\b renter_num}{\b0  is a }{\b foreign key}{\b0  referencing renter's renter_num)}}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 \tab (column {\b vid_id} is a {\b foreign key} referencing video's vid_id)}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs12 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22{\ltrch\loch\f2{\b0 2.\tab Use }{\b prompt}{\b0  commands to write a line containing your name, and then an empty line. }}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22\b {\ltrch\loch\f2 \tab Also execute a COMMIT command now, so that subsequent rollbacks do not undo  the hw3_setup.sql's inserts into the Rental table!!!}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par {\ltrch\loch\f2 3.\tab Use an {\b update} command to change every renter's {\b renter_credit_rtg} to {\b 5}, followed by a {\b select} to show the resulting change to the {\b renter} table, followed by a {\b rollback} to undo the credit rating changes.}
\par 
\par {\ltrch\loch\f2 4.\tab Use a {\b delete }command to delete every row of the {\b rental} table, followed by a {\b select} to show the resulting changes to the {\b rental} table, followed by a {\b rollback} to restore the {\b rental} rows!}
\par 
\par {\ltrch\loch\f2 5.\tab Select the rows of the {\b renter} table. Now increase the {\b renter_cred_rtg} of renters whose renter_fave_cat is {\b 200} by {\b 0.5}, and select the rows of the {\b renter} table again, so you can see that just those rows have had their credit rating changed. Finally, {\b commi
t} this change.}
\par 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420{\ltrch\loch\f2{\fs22\f2{\b0 6.\tab Select the rows of the }{\b rental}{\b0  table. Now delete the rows of the }{\b rental}{\b0  table that involve the renter with renter_num }{\b '3333'}{\b0 , and then delete the row of the }{\b renter}{\b0  table for renter_num }{\b '3333'}{\b0 . (Question to think about, not to turn in: why did I have to d
elete those rows from the }{\b rental}{\b0  table before I could delete the row from the }{\b renter}{\b0  table?). Select the rows of the }{\b rental}{\b0  table again, and select the rows of the }{\b renter}{\b0  table again. Finally, }{\b rollback}{\b0  these changes, and select the rows of the }{\b renter}{\b0  table
 again. (Notice how renter '3333' is back, but the renters with favorite category of '}{\b 200' }{\b0 still have their higher credit ratings...?)}}}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par {\ltrch\loch\f2 7.\tab Update the rows of the {\b video} table so that all videos of movies in movie category {\b '200'} that have format {\b 'DVD' }have  their rental price changed to {\b 2.22}. Write a {\b select} statement that shows the video_id, vid_format, and vid_rental_price for all videos of
 movies in movie_category 200 that will verify that only those that are DVD have the 2.22 rental price.}
\par 
\par {\ltrch\loch\f2 8.\tab Delete the rows of {\b rental} for renters whose credit rating is less than {\b 3}.}
\par 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420{\ltrch\loch\f2{\fs22\f2{\b0 9.\tab Write a }{\b select}{\b0  statement that will project the }{\b vid_rental_price }{\b0 of the video with vid_id }{\b '410041'}{\b0 . }}}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par {\ltrch\loch\f2 \tab Now, write a {\b select} statement that will project the {\b movie_num} for the movie with title {\b 'Gone with the Wind'}.}
\par 
\par {\ltrch\loch\f2 \tab Use these two {\b select} statements within an {\b update} command to change all videos of movie {\b 'Gone with the Wind'} to have the same rental price as the video with vid_id {\b '410041'} (so note that the only literal values in your {\b update }statement will be {\b '410041'} and
 {\b 'Gone with the Wind'}).}
\par {\ltrch\loch\f2 \tab }
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Don't forget to {\b spool off} at the end of your script!}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\ltrch\loch\f2\fs22\b 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af4\afs24\lang255\ltrch\dbch\af3\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 Run your script several times, and study the results file carefully until you are satisfied with it. Then, e-mail to me the final versions of your {\b 180hw06.sql} and {\b 180hw06_results.txt}, each in a separate e-mail address whose Subject: line is the name of the
 file, and with the file contents{\b  included} in the message,{\b\ul  not attached}{\ulnone .}{\b\ul  }}
\par }