{\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\fmodern\fprq1\fcharset0 Courier New;}{\f4\fnil\fprq2\fcharset0 HG Mincho Light J;}{\f5\fnil\fprq2\fcharset0 Arial Unicode MS;}}
{\colortbl;\red0\green0\blue0;\red128\green128\blue128;}
{\stylesheet{\s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\snext1 Default;}
{\s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs20\lang255\sbasedon1\snext2 Normal;}
{\*\cs4\cf1\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033 Numbering Symbols;}
}
{\info{\author Sharon Tuttle}{\creatim\yr2003\mo2\dy24\hr9\min6}{\operator Sharon Tuttle}{\revtim\yr2003\mo3\dy3\hr1\min6}{\printim\yr2003\mo2\dy28\hr9\min14}{\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\qc\ltrch\loch\fs22\b {\ltrch\loch\f0 HUMBOLDT STATE UNIVERSITY}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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 #3 - Due by 10:00 am on Friday, March 7th}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par {\ltrch\loch\f2 Precede each of your answers to the following (except for #1! 8-) ) with a {\b prompt}{\b0  command containing the number of the question being "answered" by that statement/those statements. (for example,\tab }{\b\f3 prompt === question #2 === }{\b0 )}}
\par 
\par {\ltrch\loch\f2 1.\tab Create a SQL script named {\b 180hw03.sql}. Then, {\b inside}{\b0  of this SQL script, start by beginning spooling to an output file named }{\b 180hw03_results.txt}{\b0 .}}
\par 
\par {\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420{\ltrch\loch\f2{\b0\fs22\f2 3.\tab You'll find a SQL script on the course web page, {\b hw3_setup.sql}. It sets up tables with the following names and columns; the primary keys of each are in all-caps and underlined:}}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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 
\par {\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 
\par {\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 
\par {\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  {\b0 is a }{\b foreign key} referencing movie's movie_num)}}
\par 
\par {\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  {\b0 is a }{\b foreign key} referencing renter's renter_num)}}
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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 
\par {\ltrch\loch\f2 Create a file with this name {\b hw3_setup.sql }on your redwood account, and {\b copy}{\b0  the contents from the web page to your new file.{\b  Run }this SQL script from your SQL script. (Remember how to do this? See }{\b 180class1.sql}{\b0  from the posted class examples, if not...)}}
\par 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 4.\tab Perform a Cartesian product between the {\b movie_category} and {\b renter} tables.}
\par 
\par {\ltrch\loch\f2 5.\tab Perform an equi-join between the{\b  movie_category }and {\b renter} tables.}
\par 
\par {\ltrch\loch\f2 6.\tab Perform an equi-join between the {\b movie_category }and {\b movie} tables. Use {\b table aliases }{\b0 to save yourself some typing in this statement.}}
\par 
\par {\ltrch\loch\f2 7.\tab Perform an equi-join between the{\b  video }and{\b  movie }tables, but only project the {\b movie_title,} {\b vid_format,} and{\b  movie_rating }columns.}
\par 
\par {\ltrch\loch\f2 8.\tab Perform the same query as #6, {\b except} now order the rows by vid_format, and for those with the same format, order by movie_title.}
\par 
\par {\ltrch\loch\f2 9.\tab Perform the same query as #6, {\b except} now order the rows by movie_rating, and for those with the same rating, order by vid_format, and for those with the same rating and format, order by movie_title.}
\par 
\par {\ltrch\loch\f2 10.\tab Perform an equi-join of the {\b rental}, {\b video}, and {\b movie} tables, except only project the {\b movie_title}, {\b vid_rental_price}, and {\b date_due} columns. Order this result by the {\b date_due} column.}
\par 
\par {\ltrch\loch\f2 11.\tab What if you'd like to know who has rentals out that have not yet been returned? Perform an equi-join of the {\b renter} and {\b rental} table, except further select just those rentals that have not yet been returned (how can you tell if a rental has not been ret
urned?), and only project the renter_lname, renter_phone, and vid_id columns.}
\par 
\par {\ltrch\loch\f2 12.\tab Use an equi-join of the {\b movie} and {\b movie_category} tables with additional selection restriction(s) added to show just those movies in the {\b 'Action'} category --- and only project their {\b movie_title}.}
\par 
\par {\ltrch\loch\f2 13.\tab Use an equi-join of the {\b renter{\b0 ,} rental}, {\b video}, and {\b movie} tables with additional selection restriction(s) added to only show what has been rented by renter {\b '3333'}{\b0  --- and only project the }{\b movie_title}{\b0  and }{\b date_out }{\b0 for those rentals.}}
\par 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\ltrch\loch\f2\fs22\b 
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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 180hw03.sql} and {\b 180hw03_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 }