{\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\dy28\hr9\min58}{\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 #8 - Due by 10:00 am on Friday, April 4th}
\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 
\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 Precede each of your answers to the following (except for #1 and #2...) with a prompt{\b0  command containing the number of the question being "answered" by that statement/those statements. (for example,\tab }prompt === question #3 === {\b0 )}}
\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 {\ltrch\loch\f2 1.\tab Create a SQL script named {\b 180hw08.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 180hw08_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 
\par {\ltrch\loch\f2 3.\tab Use {\b UNION} in a select statement to show the titles and directors of movies directed by Lucas unioned with movies available in format Beta.}
\par 
\par {\ltrch\loch\f2 4.\tab Rewrite your query for #3 using {\b UNION} {\b ALL} --- see the difference in your results?}
\par 
\par {\ltrch\loch\f2 5.\tab Use {\b INTERSECT }in a select statement to show the titles and directors of movies directed by Lucas intersected with movies available in format Beta.}
\par 
\par {\ltrch\loch\f2 6.\tab Use {\b MINUS }in a select statement to show the titles and directors of movies directed by Lucas NOT available in format Beta (that is, movies directed by Lucas "minus" those available in format Beta).}
\par 
\par {\ltrch\loch\f2 7.\tab You know that some movies have never been rented, yet it might be nice to be able to see a result showing how many times each movie has been rented. Use UNION to show how many times each movie has been rented, with the rows in order of movie-most-rented
 to movie-least-rented, and for those rented the same number of times, put them in alphabetical order by title.}
\par 
\par {\ltrch\loch\f2 \tab (hint: union the joined query showing number of rentals that gives results only for those with at least one rental with a query showing movies and a constant 0 for movies whose videos have never been rented...)}
\par 
\par {\ltrch\loch\f2 8.\tab Drop and then create a {\b VIEW} named {\b video_plus_titles } that will show, for each video, the vid_id, the title of the movie on that video, its movie rating, its format, its movie category {\b name}, and its video rental price.}
\par 
\par {\ltrch\loch\f2 9.\tab Write a query using {\b video_plus_titles} only to show the view information for Action videos only. }
\par 
\par {\ltrch\loch\f2 10.\tab Drop and then create a {\b VIEW} named {\b videos_per_category} that USES {\b video_plus_titles} to determine how many videos there are per movie category {\b name}. It should project the movie category name, the number of videos with that category, and the average rental
 price of videos in that category.}
\par 
\par {\ltrch\loch\f2 11.\tab Write a query using {\b videos_per_category}, showing the rows in order of decreasing videos per category. Then, insert a {\b new} video into the category of your choice, and rerun your query --- see how you automatically get the "updated" count for the affected
 movie category?}
\par 
\par {\ltrch\loch\f2 12.\tab Drop and then create a {\b sequence  }named {\b rental_seq} that will start at 1000000 and increment by 2. Insert 3 new rows into rental (any rentals you wish) using this sequence to appropriately set the rental_num, and write a query to look at the rows of rent
al and see what the new rows look like.}
\par 
\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 180hw08.sql} and {\b 180hw08_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 }