{\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\dy10\hr9\min14}{\printim\yr2003\mo3\dy10\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 #5 - Due by 10:00 am on Friday, March 14th}
\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\fs12 
\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 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\f3 prompt === question #3 === }{\b0 )}}
\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\fs12 
\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 1.\tab Create a SQL script named {\b 180hw05.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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li840\ri0\fi-420\ltrch\loch\f2\fs22 {\ltrch\loch\f2 *\tab THEN start spooling to an output file {\b 180hw05_results.txt}.}
\par \pard\plain \s1\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\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 \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\fs12 
\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 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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs20\lang255\li825\ri0\fi-405\ltrch\loch\f2\fs22{\ltrch\loch\f2{\b0 \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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\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\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs24\lang1033\li420\ri0\fi-420\ltrch\loch\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\fs22 {\ltrch\loch\f2 3.\tab Do not use {\b any}{\b0  joins or Cartesian products in the following SQL select statements.}}
\par {\ltrch\loch\f2 \tab a) Consider: how can you write a query that would tell you the {\b category code} for category name {\b 'Action'}?}
\par 
\par {\ltrch\loch\f2 \tab b) Now, nest your answer to (a) in a query to tell the {\b titles} of movies whose category code is the the category code for category name {\b 'Action'}.}
\par 
\par {\ltrch\loch\f2 \tab c) Now, add something to (b) to cause these titles to be projected in alphabetical order.}
\par 
\par {\ltrch\loch\f2 4.\tab Do not use {\b any} joins or Cartesian products in the following SQL select statements.}
\par {\ltrch\loch\f2 \tab a) Consider: how can you write a query that would tell you the {\b renter numbers} from rentals that have not yet been returned?}
\par 
\par {\ltrch\loch\f2{\b0 \tab b) Now, nest your answer to (a) in a query to tell the }{\b renter last names}{\b0  of renters involved in rentals that have not yet been returned, showing the last names in alphabetical order.}}
\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\fs22
\par \page\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 5.\tab Do not use {\b any} joins or Cartesian products in the following SQL select statements.}
\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{\b0 \tab a) Consider: how can you write a query that would tell you the }{\b vid_id}{\b0 's for videos with format }{\b 'DVD'}{\b0 ?}}
\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 \tab b) Now, nest your answer to (a) in a query to tell the{\b  renter numbers{\b0  from }rentals} involving videos with format DVD.}
\par 
\par {\ltrch\loch\f2{\b0 \tab c) Finally, nest your answer to (b) in a query to give the }{\b renter last names}{\b0  of renters involved in rentals of DVD's. Show these renter last names in alphabetical order.}}
\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 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs20\lang255\li420\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 6.\tab Do not use {\b any} joins or Cartesian products in the following SQL select statements.}
\par {\ltrch\loch\f2 \tab a) Consider: how can you write a query that would tell you the {\b video rental prices} for all movies with format {\b VHS}?}
\par 
\par {\ltrch\loch\f2{\b0 \tab b) Now, nest your answer to (a) in a query to tell the vid_id's and vid_rental_price of videos with format }{\b DVD}{\b0  that have a rental price }{\b less than}{\b0  ANY (at least one) video with format }{\b VHS}{\b0 . (Hint: you will not use }{\b IN}{\b0  in this nested query...)}}
\par 
\par {\ltrch\loch\f2 7.\tab Consider the predicates {\b EXISTS} and {\b NOT EXISTS}. Use one of these predicates --- and {\b no} joins or Cartesian products --- in writing the following query: What are the last names and phone numbers of renters who rented a movie on October 10th, 2002? }
\par 
\par {\ltrch\loch\f2 8.\tab Consider the predicates {\b EXISTS }and {\b NOT EXISTS}. }
\par {\ltrch\loch\f2 \tab a) Use one of these predicates --- and {\b no} joins or Cartesian products --- in writing the following query: What are the vid_id's and vid_formats of videos which have {\b never} been involved in any rental? }
\par 
\par {\ltrch\loch\f2 \tab b) Now, write a query (however you'd like) that will give the {\b movie titles}  for which {\b no }{\b0 rental of }{\b any}{\b0  video of that movie has ever taken place. (Note: this one's a bit tricky...)}}
\par 
\par {\ltrch\loch\f2 9.\tab Consider the question: what movies are available on videos with the format Beta?}
\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 a)\tab Answer this question using a {\b nested select} and predicate {\b IN}. Do not use any Cartesian products or joins.}
\par 
\par {\ltrch\loch\f2 b) \tab Answer this same question using a {\b join}, and no nesting. Use the feature that prevents duplicate rows in your answer.}
\par 
\par {\ltrch\loch\f2{\b0 c)\tab Answer this same question using a{\b  nested select}{\b0  and }predicate }{\b EXISTS}{\b0 . Do not use any Cartesian products or joins.}}
\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 
\par \pard\plain \s2\cf1{\*\hyphen2\hyphlead2\hyphtrail2\hyphmax0}\aspalpha\rtlch\af5\afs24\lang255\ltrch\dbch\af4\afs24\langfe255\loch\f0\fs20\lang255\li420\ri0\fi-405\ltrch\loch\f2\fs22 {\ltrch\loch\f2 10.\tab You may write the following query however you wish (that works! 8-) ).}
\par {\ltrch\loch\f2 \tab Consider all movies whose rating is {\b PG}{\b0 . Consider, too, the rental prices for videos of those movies (whose rating is {\b PG}{\b0 )}. What are the titles, movie ratings, and video formats of movies whose rental price is greater than }{\b ALL}{\b0  rental prices of such PG-rated
 movies?}\tab }
\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 180hw05.sql} and {\b 180hw05_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 }