{\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\dy23\hr16\min17}{\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 #7 - Due by 10:00 am on Friday, March 28th}
\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 180hw07.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 180hw07_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 Remember how we talked about the {\b LIKE }predicate on March 14th? (See {\b 180class6.sql} script, to refresh your memory...)}
\par 
\par {\ltrch\loch\f2{\b0 \tab Write a query that will show the movie_title and movie_yr_released for movies whose year of release *ends* with a 9. (Yes, our movie_yr_released column *is* a character string ... even though it only contains digits...)}}
\par 
\par {\ltrch\loch\f2 4.\tab Write a query that will show the average rental price for videos with format DVD.}
\par 
\par {\ltrch\loch\f2 5.\tab Write a query that will show the average credit rating for customers who have rentals that are currently not returned (whose date_returned is null).}
\par 
\par {\ltrch\loch\f2 6.\tab Write a query that will show how many videos there are, and how many *different* formats there are amongst those videos. (Hint: what was that new use of DISTINCT that we mentioned in class 7?)}
\par 
\par {\ltrch\loch\f2 7.\tab Show the vid_id, vid_format, and vid_rental_price for those videos whose rental price is {\b less} than the average rental price for a video.}
\par 
\par {\ltrch\loch\f2 8.\tab Write a query that will show the video format, the average rental price for videos with that format, and the number of videos with that format. (That is, this query will result in 3 columns, and can have more than one row.)}
\par 
\par {\ltrch\loch\f2 9.\tab Show, for each possible pair of a movie rating and a video format, the movie rating, the video format, the average video rental price of movies with that rating on videos of that format, and how many such videos there are. (Hint: join the video and movi
e tables, and note that there should be 4 columns in your result.) }
\par 
\par {\ltrch\loch\f2 10.\tab Write a version of #9 in which you only show rows for which there are at least 3 videos with that rating and format.}
\par 
\par {\ltrch\loch\f2{\b0 11.\tab }Write a query that will show, for each movie_title, the number of videos of that movie_title --- but only for movies that have 2 or more videos of that movie, and show them in reverse order by number of videos, and for movies with the same number of vi
deos, in alphabetical order by title.}
\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 180hw07.sql} and {\b 180hw07_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 }