{\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\dy7\hr9\min41}{\printim\yr2003\mo3\dy3\hr8\min26}{\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 #4 - Due by 10:00 am on Monday, March 10th}
\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 {\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 
\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\f2 1.\tab Create a SQL script named {\b 180hw04.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\fs22 
\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 
\par {\ltrch\loch\f2{\b0 *\tab THEN start spooling to an output file }{\b 180hw04_results.txt}{\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{\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{\fs22\f2{\b0 3.\tab Let's practice some }{\b insert}{\b0  statements, first. Remember the tables set up in }{\b hw3_setup.sql}{\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 \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  is a }{\b foreign key}{\b0  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  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 \tab (column {\b vid_id} is a {\b foreign key} referencing video's vid_id)}
\par 
\par {\ltrch\loch\f2 Use the version of insert that starts with:}
\par 
\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\b {\ltrch\loch\f2 insert into {\b0 tbl}}
\par {\ltrch\loch\f2 values}
\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
\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\fs24 {\ltrch\loch\f2 \tab ...to insert one new row each into the {\b movie_category} table, into the {\b movie} table (make this new movie have as its category your new movie category), and into the {\b video} table (make this be a new video of your new movie).}
\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\fs24 
\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\fs24 {\ltrch\loch\f2 4.\tab Now, use the version of insert that starts with:}
\par 
\par {\ltrch\loch\f2 \tab {\b insert into }tbl(desired_col, desired_col, ...)}
\par {\ltrch\loch\f2 \tab {\b values}}
\par 
\par {\ltrch\loch\f2 \tab ...to insert a new rental into the rental table, where the customer of your choice is renting your new video today, and it should be due in 3 days. Do not fill in the {\b date_returned} column; use this insert properly so that {\b date_returned} ends up starting wi
th the value {\b null}.}
\par 
\par {\ltrch\loch\f2 5.\tab Now that you know you can insert, let's create some tables of our own.}
\par 
\par {\ltrch\loch\f2 \tab Create a table {\b concessions} that has the following columns:}
\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\fs24 {\ltrch\loch\f2 *\tab {\b conc_code}: this should be a character string of exactly 3 characters.}
\par 
\par {\ltrch\loch\f2 *\tab {\b conc_name}: this should be a varying-length character string of up to {\b 20} characters; it should not be allowed to be null.}
\par 
\par {\ltrch\loch\f2 *\tab {\b conc_price}: this should be able to hold a value with 2 fractional places, and able to hold up to 9.99; by default, this should be {\b 0.99}{\b0 , if it isn't explicitly set. It is intended to be the price a renter pays if he/she buys one of this concession.}}
\par 
\par {\ltrch\loch\f2 *\tab {\b conc_qty_per_box}: this should be able to hold an integer --- the quantity per box for this concession. Make sure that it is between {\b 1}{\b0  and }{\b 144}{\b0  inclusive.}}
\par 
\par {\ltrch\loch\f2{\b0 *\tab make }{\b conc_code}{\b0  the primary key for this table.}}
\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\b 
\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\fs24 {\ltrch\loch\f2 6.\tab Now create a table {\b sale} that has the following columns:}
\par 
\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\fs24 {\ltrch\loch\f2 *\tab {\b sale_num}, a character string with exactly 6 characters.}
\par 
\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{\b0\fs24\f2 *\tab {\b conc_code}, a character string of exactly 3 characters; it must not be allowed to be null.}}
\par 
\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\fs24 {\ltrch\loch\f2 *\tab {\b sale_qty}{\b0 , an integer telling the number of this concession sold in this sale; if it is not set explicitly, then it should have a default value of }{\b 1}{\b0 .}}
\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\fs24 
\par {\ltrch\loch\f2 *\tab {\b sale_date}, the date that this sale occurred; it should not be allowed to be null.}
\par 
\par {\ltrch\loch\f2 *\tab {\b renter_num}, a character string with exactly 4 characters;}
\par 
\par {\ltrch\loch\f2 *\tab make {\b sale_num} the primary key;}
\par 
\par {\ltrch\loch\f2 *\tab make {\b conc_code} a foreign key referencing table {\b concessions},}
\par 
\par {\ltrch\loch\f2{\b0 *\tab make }{\b renter_num}{\b0  a foreign key referencing table }{\b renter}{\b0 .}}
\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\fs24 {\ltrch\loch\f2 7.\tab Insert at least {\b 3} rows into your {\b concessions} table. Use each version of insert at least once --- when you use the 2nd version of insert, use it at least once such that you do not explicitly fill column {\b conc_price}, so you can test if your default clause 
is working.}
\par 
\par {\ltrch\loch\f2{\b0 8.\tab Insert at least }{\b 3}{\b0  rows into your }{\b sale }{\b0 table. Use each version of insert at least once --- when you use the 2nd version of insert, use it at least once such that you do not explicitly fill column }{\b sale_qty}{\b0 , so that you can test if your default clause is w
orking.}}
\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 
\par {\ltrch\loch\f2 9.\tab Do {\b describe} and {\b select} for your new table {\b concessions}, to see its structure and contents.}
\par 
\par {\ltrch\loch\f2 10.\tab Do {\b describe }and {\b select} for your new table {\b sale}, to see its structure and contents.}
\par 
\par {\ltrch\loch\f2 11.\tab Write a SELECT statement that will show, for each{\b  sale{\b0  made, the last name of the renter involved in the sale, the} name{\b0  of the concession bought, and how many of that concession were bought. Order these rows by the }name} of the concession bought.}
\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 180hw04.sql} and {\b 180hw04_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 }