{\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\mo4\dy4\hr9\min57}{\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 #9 - Due by 10:00 am on Monday, April 7th}
\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 180hw09.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 180hw09_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 Write a set of commands that will create the following two columns: the first column contains a renter's last name, then a comma and a space, and then the renter's first name, and the second column has the renter's credit rating; the rows should be in a
lphabetical order by the renter's last name.}
\par 
\par {\ltrch\loch\f2 4.\tab Add a top title and bottom title of your choice, making sure that at least one of these is a multi-line title. Then rerun your query from #3 (using / instead of retyping the query!)}
\par 
\par {\ltrch\loch\f2 5.\tab Now increase the {\b pagesize} so that you get all of the rows from query #3 in a single "page" (write the command to increase the pagesize, then rerun the query from #3 using / instead of retyping the query).}
\par 
\par {\ltrch\loch\f2 6.\tab Create a {\b view} to hold #3's results, so that you can make a {\b column} command to format the renter credit ratings to a single fractional place. Also make a column command to only show the first 15 characters of the last-name, first-name column, {\b truncating} a
ny that are longer than that, and give both columns "pretty" column headings using mixed-case. Write a simple query using your view, to show your new column settings in action.}
\par 
\par {\ltrch\loch\f2 7.\tab Write a view that will show the movie category {\b name}, then the average rental price of videos of movies of that category. Write a column command to give the first column of the view an attractive heading, and write another column command to format the se
cond column of the view to contain a $, to be printed to two fractional places, and to have an attractive heading. Then write a simple query using your view, to show your new column settings in action.}
\par 
\par {\ltrch\loch\f2 8.\tab Write a {\b break} command that will break on movie category name, skipping one line after each new movie category name. Then write a query that shows, for each movie, the movie category name of that movie, followed by the movie title, ordering the rows by m
ovie category name first, and then by movie title (for movies in the same category). (Note that there is {\b no} group by clause in this query!)}
\par 
\par {\ltrch\loch\f2 9.\tab Now write a {\b compute} command that will {\b count} the number of movie titles in each category name (again, do NOT use group by --- {\b compute} will do this, instead). Rerun your query from #8 by using /, to see the new effect.}
\par 
\par {\ltrch\loch\f2 10.\tab Finally, reset everything changed by this homework script back to its default value: turn off top and bottom titles, reset pagesize to its default value of 14, clear columns, clear breaks, and clear computes.}
\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 }