<BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_NAME,EMPLOYEE.ADDRESS, EMPLOYEE.PHONE_NUMBER, PAYROLL.SALARY, PAYROLL.POSITION,EMPLOYEE.SSN, PAYROLL.START_DATE FROM EMPLOYEE, PAYROLL WHEREEMPLOYEE.SSN = PAYROLL.SSN AND EMPLOYEE.LAST_NAME LIKE 'S%' ANDPAYROLL.SALARY &gt; 20000;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>You should reformat the SQL statement as follows, depending on the consistent	format of your choice:</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME,       E.ADDRESS, E.PHONE_NUMBER, P.SALARY,       P.POSITION, E.SSN, P.START_DATEFROM EMPLOYEE E,     PAYROLL PWHERE E.SSN = P.SSN  AND E.LAST_NAME LIKE 'S%'  AND P.SALARY &gt; 20000;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>2.</B> Rearrange the conditions in the following query to optimize data retrieval	time.Use the following statistics (on the tables in their entirety) to determine	the order of the conditions:</DL><BLOCKQUOTE>	<P>593 individuals have the last name <TT>SMITH</TT>.</P>	<P>712 individuals live in <TT>INDIANAPOLIS</TT>.</P>	<P>3,492 individuals are <TT>MALE</TT>.</P>	<P>1,233 individuals earn a salary &gt;= <TT>30,000</TT>.</P>	<P>5,009 individuals are single.</BLOCKQUOTE><DL>	<DD><TT>Individual_id</TT> is the primary key for both tables.	<PRE><FONT COLOR="#0066FF"><B>SELECT M.INDIVIDUAL_NAME, M.ADDRESS, M.CITY, M.STATE, M.ZIP_CODE,       S.SEX, S.MARITAL_STATUS, S.SALARYFROM MAILING_TBL M,     INDIVIDUAL_STAT_TBL SWHERE M.NAME LIKE 'SMITH%'  AND M.CITY = 'INDIANAPOLIS'  AND S.SEX = 'MALE'  AND S.SALARY &gt;= 30000  AND S.MARITAL_STATUS = 'S'  AND M.INDIVIDUAL_ID = S.INDIVIDUAL_ID;</B>--------------</FONT></PRE>	<DD><I>Answer:</I>	<P>According to the statistics, your new query should look similar to the following	answer. <TT>Name like 'SMITH%'</TT> is the most restrictive condition because it	will return the fewest rows:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT M.INDIVIDUAL_NAME, M.ADDRESS, M.CITY, M.STATE, M.ZIP_CODE,       S.SEX, S.MARITAL_STATUS, S.SALARYFROM MAILING_TBL M,     INDIVIDUAL_STAT_TBL SWHERE M.INDIVIDUAL_ID = S.INDIVIDUAL_ID  AND S.MARITAL_STATUS = 'S'  AND S.SEX = 'MALE'  AND S.SALARY &gt;= 30000  AND M.CITY = 'INDIANAPOLIS'  AND M.NAME LIKE 'SMITH%';</B></FONT></PRE></BLOCKQUOTE><PRE><FONT COLOR="#0066FF"><B></B></FONT></PRE><H2><FONT COLOR="#000077">Day 16, &quot;Using Views to Retrieve Useful Informationfrom the Data Dictionary&quot;</FONT></H2><H3><FONT COLOR="#000077">Quiz Answers</FONT></H3><DL>	<DD><B>1.</B> In Oracle, how can you find out what tables and views you own?	<P>By selecting from <TT>USER_CATALOG</TT> or <TT>CAT</TT>. The name of the data	dictionary object will vary by implementation, but all versions have basically the	same information about objects such as tables and views.</P>	<P><B>2.</B> What types of information are stored in the data dictionary?<BR>	Database design, user statistics, processes, objects, growth of objects, performance	statistics, stored SQL code, database security.</P>	<P><B>3.</B> How can you use performance statistics?</P>	<P>Performance statistics suggest ways to improve database performance by modifying	database parameters and streamlining SQL, which may also include the use of indexes	and an evaluation of their efficiency.</P>	<P><B>4. </B>What are some database objects?<BR>	Tables, indexes, synonyms, clusters, views.</DL><H3><FONT COLOR="#000077">Exercise Answers</FONT></H3><P>Suppose you are managing a small to medium-size database. Your job responsibilitiesinclude developing and managing the database. Another individual is inserting largeamounts of data into a table and receives an error indicating a lack of space. Youmust determine the cause of the problem. Does the user's tablespace quota need tobe increased, or do you need to allocate more space to the tablespace? Prepare astep-by-step list that explains how you will gather the necessary information fromthe data dictionary. You do not need to list specific table or view names.<DL>	<DD><B>1. </B>Look up the error in your database documentation.	<P><B>2.</B> Query the data dictionary for information on the table, its current	size, tablespace quota on the user, and space allocated in the tablespace (the tablespace	that holds the target table).</P>	<P><B>3.</B> Determine how much space the user needs to finish inserting the data.</P>	<P><B>4.</B> What is the real problem? Does the user's tablespace quota need to be	increased, or do you need to allocate more space to the tablespace?</P>	<P><B>5.</B> If the user does not have a sufficient quota, then increase the quota.	If the current tablespace is filled, you may want to allocate more space or move	the target table to a tablespace with more free space.</P>	<P><B>6.</B> You may decide not to increase the user's quota or not to allocate more	space to the tablespace. In either case you may have to consider purging old data	or archiving the data off to tape.</P>	<P>These steps are not irrevocable. Your action plan may vary depending upon your	company policy or your individual situation.</DL><H2><FONT COLOR="#000077">Day 17, &quot;Using SQL to Generate SQL Statements&quot;</FONT></H2><H3><FONT COLOR="#000077">Quiz Answers</FONT></H3><DL>	<DD><B>1.</B> From which two sources can you generate SQL scripts?	<P>You can generate SQL scripts from database tables and the data dictionary.</P>	<P><B>2. </B>Will the following SQL statement work? Will the generated output work?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET ECHO OFF</B>SQL&gt; <B>SET FEEDBACK OFF</B>SQL&gt; <B>SPOOL CNT.SQL</B>SQL&gt; <B>SELECT 'COUNT(*) FROM  ' || TABLE_NAME || ';'</B>  2  <B>FROM CAT</B>  3  <B>/</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>Yes the SQL statement will generate an SQL script, but the generated script will	not work. You need <TT>select 'select'</TT> in front of <TT>count(*)</TT>:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>Otherwise, your output will look like this:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>COUNT(*) FROM TABLE_NAME;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>which is not a valid SQL statement.	<P><B>3. </B>Will the following SQL statement work? Will the generated output work?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET ECHO OFF</B>SQL&gt; <B>SET FEEDBACK OFF</B>SQL&gt; <B>SPOOL GRANT.SQL</B>SQL&gt;<B> SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'</B>  2  <B>FROM SYS.DBA_USERS</B>  3  <B>WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')</B>  4 <B> /</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>Once again, yes and no. The statement will generate an SQL script, but the SQL	that it generates will be incomplete. You need to add a comma between the privileges	<TT>CONNECT</TT> and <TT>DBA</TT>:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>4.</B> Will the following SQL statement work? Will the generated output work?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET ECHO OFF</B>SQL&gt; <B>SET FEEDBACK OFF</B>SQL&gt; <B>SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'</B>  2  <B>FROM SYS.DBA_USERS</B>  3  <B>WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')</B>  4 <B> /</B></FONT></PRE></BLOCKQUOTE><DL>	<DD>Yes. The syntax of the main statement is valid, and the SQL that will be generated	will grant <TT>CONNECT</TT> and <TT>DBA </TT>to all users selected.	<P><B>5. </B>True or False: It is best to set feedback <TT>on</TT> when generating	SQL.</P>	<P>False. You do not care how many rows are being selected, as that will not be part	of the syntax of your generated statements.</P>	<P><B>6.</B> True or False: When generating SQL from SQL, always spool to a list	or log file for a record of what happened.</P>	<P>False. You should spool to an <TT>.sql</TT> file, or whatever your naming convention	is for an SQL file. However, you may choose to spool within your generated file.</P>	<P><B>7. </B>True or False: Before generating SQL to truncate tables, you should	always make sure you have a good backup of the tables.</P>	<P>True. Just to be safe.</P>	<P><B>8.</B> What is the <TT>ed</TT> command?</P>	<P>The <TT>ed</TT> command takes you into a full screen text editor. <TT>ed</TT>	is very similar to <TT>vi</TT> on a UNIX system and appears like a Windows Notepad	file.</P>	<P><B>9. </B>What does the <TT>spool off</TT> command do?<BR>	The <TT>spool off</TT> command closes an open spool file.</DL><H3><FONT COLOR="#000077">Exercise Answers</FONT></H3><DL>	<DD><B>1. </B>Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statement	that will generate a series of <TT>GRANT</TT> statements to five new users: John,	Kevin, Ryan, Ron, and Chris. Use the column called <TT>USERNAME</TT>. Grant them	Select access to <TT>history_tbl</TT>.</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET ECHO OFF</B>SQL&gt; <B>SET FEEDBACK OFF</B>SQL&gt; <B>SPOOL GRANTS.SQL</B>SQL&gt; <B>SELECT 'GRANT SELECT ON HISTORY_TBL TO ' || USERNAME || ';'</B>  2  <B>FROM SYS.DBA_USERS</B>  3  <B>WHERE USERNAME IN ('JOHN','KEVIN','RYAN','RON','CHRIS')</B>  4 <B> /</B>grant select on history_tbl to JOHN;grant select on history_tbl to KEVIN;grant select on history_tbl to RYAN;grant select on history_tbl to RON;grant select on history_tbl to CHRIS;</FONT></PRE></BLOCKQUOTE><DL>	<DD><B>2. </B>Using the examples in this chapter as guidelines, create some SQL statements	that will generate SQL that you can use.	<P>There are no wrong answers as long as the syntax is correct in your generated	statements.</DL><BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>Until you completely understand	the concepts presented in this chapter, take caution when generating SQL statements	that will modify existing data or database structures. <HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Day 18, &quot;PL/SQL: An Introduction&quot;</FONT></H2><H3><FONT COLOR="#000077">Quiz Answers</FONT></H3><DL>	<DD><B>1.</B> How is a database trigger used?	<P>A database trigger takes a specified action when data in a specified table is	manipulated. For instance, if you make a change to a table, a trigger could insert	a row of data into a history table to audit the change.</P>	<P><B>2.</B> Can related procedures be stored together?<BR>	Related procedures may be stored together in a package.</P>	<P><B>3.</B> True or False: Data Manipulation Language can be used in a PL/SQL statement.<BR>	True.</P>	<P><B>4. </B>True or False: Data Definition Language can be used in a PL/SQL statement.</P>	<P>False. DDL cannot be used in a PL/SQL statement. It is not a good idea to automate	the process of making structural changes to a database.</P>	<P><B>5. </B>Is text output directly a part of the PL/SQL syntax?<BR>	Text output is not directly a part of the language of PL/SQL; however, text output	is supported by the standard package <TT>DBMS_OUTPUT</TT>.</P>	<P><B>6.</B> List the three major parts of a PL/SQL statement.<BR>	<TT>DECLARE</TT> section, <TT>PROCEDURE</TT> section, <TT>EXCEPTION</TT> section.</P>	<P><B>7.</B> List the commands that are associated with cursor control.<BR>	<TT>DECLARE</TT>, <TT>OPEN</TT>, <TT>FETCH</TT>, <TT>CLOSE</TT>.</DL><H3><FONT COLOR="#000077">Exercise Answers</FONT></H3><DL>	<DD><B>1.</B> Declare a variable called <TT>HourlyPay</TT> in which the maximum accepted	value is <TT>99.99</TT>/hour.</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>DECLARE     HourlyPay number(4,2);</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>2.</B> Define a cursor whose content is all the data in the <TT>CUSTOMER_TABLE</TT>	where the <TT>CITY</TT> is <TT>INDIANAPOLIS</TT>.</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>DECLARE      cursor c1 is      select * from customer_table      where city = 'INDIANAPOLIS';</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>3.</B> Define an exception called <TT>UnknownCode</TT>.</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>DECLARE      UnknownCode EXCEPTION;</B></FONT></PRE></BLOCKQUOTE><DL>	<DD><B>4.</B> Write a statement that will set the <TT>AMT</TT> in the <TT>AMOUNT_TABLE</TT>	to <TT>10</TT> if <TT>CODE</TT> is <TT>A</TT>, set the <TT>AMT</TT> to <TT>20</TT>	if <TT>CODE</TT> is <TT>B</TT>, and raise an exception called <TT>UnknownCode</TT>	if <TT>CODE</TT> is neither <TT>A</TT> nor <TT>B</TT>. The table has one row.</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>IF ( CODE = 'A' ) THEN      update AMOUNT_TABLE      set AMT = 10;    ELSIF ( CODE = 'B' ) THEN       update AMOUNT_TABLE      set AMT = 20;    ELSE      raise UnknownCode;    END IF; </B></FONT></PRE></BLOCKQUOTE><H2><FONT COLOR="#000077">Day 19, &quot;Transact-SQL: An Introduction&quot;</FONT></H2><H3><FONT COLOR="#000077">Quiz Answers</FONT></H3><DL>	<DD><B>1.</B> True or False: The use of the word SQL in Oracle's PL/SQL and Microsoft/Sybase's	Transact-SQL implies that these products are fully compliant with the ANSI standard.	<P>False. The word is not protected by copyright. The products mentioned do comply	with much of the ANSI standard, but they do not fully comply with everything in that	standard.</P>	<P><B>2. </B>True or False: Static SQL is less flexible than Dynamic SQL, although	the performance of static SQL can be better.</P>	<P>True. Static SQL requires the use of a precompiler, and its queries cannot be	prepared at runtime. Therefore, static SQL is less flexible than dynamic SQL, but	because the query is already processed, the performance can be better.</DL><H3><FONT COLOR="#000077">Exercise Answers</FONT></H3><DL>	<DD><B>1. </B>If you are not using Sybase/Microsoft SQL Server, compare your product's	extensions to ANSI SQL to the extensions mentioned today.	<P>Because nearly all of Day 19 deals with Transact-SQL, we did not explore the many	other extensions to ANSI SQL. Most documentation that accompanies database products	makes some eff