apf.htm

21精通SQL

	on the <TT>State</TT> field.
	<P>Change the return value of <TT>GetDefaultSQL</TT> as shown in the following code
	fragment:
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>CString CTyssqlSet::GetDefaultSQL()
{
return &quot; SELECT * FROM CUSTOMER ORDER DESC BY STATE &quot;;
}</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>Go out, find an application that needs SQL, and use it.<BR>
	(On your own.)
</DL>

<H2><FONT COLOR="#000077">Day 15, &quot;Streamlining SQL Statements for Improved
Performance&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> What does <I>streamline an SQL statement</I> mean?<BR>
	Streamlining an SQL statement is taking the path with the least resistance by carefully
	planning your statement and arranging the elements within your clauses properly.
	<P><B>2.</B> Should tables and their corresponding indexes reside on the same disk?<BR>
	Absolutely not. If possible, always store tables and indexes separately to avoid
	disk contention.</P>
	<P><B>3. </B>Why is the arrangement of conditions in an SQL statement important?<BR>
	For more efficient data access (the path with the least resistance).</P>
	<P><B>4. </B>What happens during a full-table scan?<BR>
	A table is read row by row instead of using an index that points to specific rows.</P>
	<P><B>5.</B> How can you avoid a full-table scan?<BR>
	A full-table scan can be avoided by creating an index or rearranging the conditions
	in an SQL statement that are indexed.</P>
	<P><B>6. </B>What are some common hindrances of general performance?<BR>
	Common performance pitfalls include</P>
	<UL>
		<LI>Insufficient shared memory
		<P>
		<LI>Limited number of available disk drives
		<P>
		<LI>Improper usage of available disk drives
		<P>
		<LI>Running large batch loads that are unscheduled
		<P>
		<LI>Failing to commit or rollback transactions
		<P>
		<LI>Improper sizing of tables and indexes
	</UL>
</DL>

<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>

<DL>
	<DD><B>1. </B>Make the following SQL statement more readable.
</DL>



<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 WHERE
EMPLOYEE.SSN = PAYROLL.SSN AND EMPLOYEE.LAST_NAME LIKE 'S%' AND
PAYROLL.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_DATE
FROM EMPLOYEE E,
     PAYROLL P
WHERE 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.SALARY
FROM MAILING_TBL M,
     INDIVIDUAL_STAT_TBL S
WHERE 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.SALARY
FROM MAILING_TBL M,
     INDIVIDUAL_STAT_TBL S
WHERE 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 Information
from 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 responsibilities
include developing and managing the database. Another individual is inserting large
amounts of data into a table and receives an error indicating a lack of space. You
must determine the cause of the problem. Does the user's tablespace quota need to
be increased, or do you need to allocate more space to the tablespace? Prepare a
step-by-step list that explains how you will gather the necessary information from
the 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>
	<P