apf.htm

Learn SQL in an easy way...

</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create unique view debts as
     select * from debts_tbl;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>No. There is no such thing as a unique view.
	<P><B>5.</B> Is the following <TT>CREATE</TT> statement correct?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>drop * from view debts;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>No. The correct syntax is
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">drop view debts;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>6.</B> Is the following <TT>CREATE</TT> statement correct?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create index id_index on bills
     (account_id);</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Yes. This syntax is correct.
</DL>

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

<DL>
	<DD><B>1.</B> Examine the database system you are using. Does it support views? What
	options are you allowed to use when creating a view? Write a simple SQL statement
	that will create a view using the appropriate syntax. Perform some traditional operations
	such as <TT>SELECT</TT> or <TT>DELETE</TT> and then <TT>DROP</TT> the view.
	<P>Check your implementation's data dictionary for the proper tables to query for
	information on views.</P>
	<P><B>2.</B> Examine the database system you are using to determine how it supports
	indexes. You will undoubtedly have a wide range of options. Try out some of these
	options on a table that exists within your database. In particular, determine whether
	you are allowed to create <TT>UNIQUE</TT> or <TT>CLUSTERED</TT> indexes on a table
	within your database.</P>
	<P>Microsoft Access allows developers to use graphical tools to add indexes to a
	table. These indexes can combine multiple fields, and the sort order can also be
	set graphically. Other systems require you to type the <TT>CREATE INDEX</TT> statement
	at a command line.</P>
	<P><B>3.</B> If possible, locate a table that has several thousand records. Use a
	stopwatch or clock to time various operations against the database. Add some indexes
	and see whether you can notice a performance improvement. Try to follow the tips
	given to you today.</P>
	<P>Indexes improve performance when the operation returns a small subset of records.
	As queries return a larger portion of a table's records, the performance improvement
	gained by using indexes becomes negligible. Using indexes can even slow down queries
	in some situations.
</DL>

<H2><FONT COLOR="#000077">Day 11, &quot;Controlling Transactions&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> When nesting transactions, does issuing a <TT>ROLLBACK TRANSACTION</TT>
	command cancel the current transaction and roll back the batch of statements into
	the upper-level transaction? Why or why not?
	<P>No. When nesting transactions, any rollback of a transaction cancels all the transactions
	currently in progress. The effect of all the transactions will not truly be saved
	until the outer transaction has been committed.</P>
	<P><B>2. </B>Can savepoints be used to &quot;save off&quot; portions of a transaction?
	Why or why not?<BR>
	Yes. Savepoints allow the programmer to save off statements within a transaction.
	If desired, the transaction can then be rolled back to this savepoint instead of
	to the beginning of the transaction.</P>
	<P><B>3.</B> Can a <TT>COMMIT</TT> command be used by itself or must it be embedded?<BR>
	A <TT>COMMIT</TT> command can be issued by itself or in the transaction.</P>
	<P><B>4.</B> If you issue the <TT>COMMIT</TT> command and then discover a mistake,
	can you still use the <TT>ROLLBACK</TT> command?<BR>
	Yes and No. You can issue the command, but it will not roll back the changes.</P>
	<P><B>5.</B> Will using a savepoint in the middle of a transaction save all that
	happened before it automatically?</P>
	<P>No. A savepoint comes into play only if a <TT>ROLLBACK</TT> command is issued--and
	then only the changes made after the savepoint will be rolled back.
</DL>

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

<DL>
	<DD><B>1. </B>Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>START TRANSACTION
     INSERT INTO CUSTOMERS VALUES
     ('SMITH', 'JOHN')</B>
SQL&gt;<B> COMMIT;</B></FONT></PRE>
	<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
     INSERT INTO CUSTOMERS VALUES
     ('SMITH', 'JOHN');</B>
SQL&gt; <B>COMMIT;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2.</B> Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
     UPDATE BALANCES SET CURR_BAL = 25000;</B>
SQL&gt; <B>COMMIT;</B></FONT></PRE>
	<P><I>Answer:</I>

</BLOCKQUOTE>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
         UPDATE BALANCES SET CURR_BAL = 25000;</B>
SQL&gt; <B>COMMIT;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement is correct and will work quite well; however, you have just updated
	everyone's current balance to $25,000!
	<P><B>3. </B>Use Personal Oracle7 syntax and correct the syntax (if necessary) for
	the following:
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SET TRANSACTION;
     INSERT INTO BALANCES VALUES
     ('567.34', '230.00', '8');</B>
SQL&gt;<B> ROLLBACK;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement is correct. Nothing will be inserted.
</DL>

<H2><FONT COLOR="#000077">Day 12, &quot;Database Security&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT CONNECTION TO DAVID;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>There is no <TT>CONNECTION</TT> role. The proper syntax is
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>GRANT CONNECT TO DAVID;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>True or False (and why): Dropping a user will cause all objects owned
	by that user to be dropped as well.
	<P>This statement is true only if the <TT>DROP USER </TT>user name<TT> CASCADE</TT>
	statement is executed. The <TT>CASCADE</TT> option tells the system to drop all objects
	owned by the user as well as that user.</P>
	<P><B>3.</B> What would happen if you created a table and granted select privileges
	on the table to <TT>public</TT>?</P>
	<P>Everyone could select from your table, even users you may not want to be able
	to view your data.</P>
	<P><B>4.</B> Is the following SQL statement correct?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>create user RON
     identified by RON;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Yes. This syntax creates a user. However, the user will acquire the default settings,
	which may not be desirable. Check your implementation for these settings.
	<P><B>5. </B>Is the following SQL statement correct?
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>alter RON
     identified by RON;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>No. The user is missing. The correct syntax is
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>alter user RON
     identified by RON;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>6. </B>Is the following SQL statement correct?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>grant connect, resource to RON;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Yes. The syntax is correct.
	<P><B>7.</B> If you own a table, who can select from that table?</P>
	<P>Only users with the select privilege on your table.
</DL>

<H3><FONT COLOR="#000077">Exercise Answer</FONT></H3>
<P>Experiment with your database system's security by creating a table and then by
creating a user. Give this user various privileges and then take them away.</P>
<P>(On your own.)
<H2><FONT COLOR="#000077">Day 13, &quot;Advanced SQL Topics&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> True or False: Microsoft Visual C++ allows programmers to call the
	ODBC API directly.
	<P>False. Microsoft Visual C++ encapsulates the ODBC library with a set of C++ classes.
	These classes provide a higher-level interface to the ODBC functions, which results
	in an easier-to-use set of functions. However, the overall functionality is somewhat
	limited. If you purchase the ODBC Software Development Kit (SDK) (you can obtain
	the SDK by joining the Microsoft Developers Network), you can call the API directly
	from within a Visual C++ application.</P>
	<P><B>2.</B> True or False: The ODBC API can be called directly only from a C program.<BR>
	False. The ODBC API resides within DLLs that can be bound by a number of languages,
	including Visual Basic and Borland's Object Pascal.</P>
	<P><B>3.</B> True or False: Dynamic SQL requires the use of a precompiler.<BR>
	False. Static SQL requires a precomplier. Dynamic SQL is just that: dynamic. The
	SQL statements used with Dynamic SQL can be prepared and executed at runtime.</P>
	<P><B>4. </B>What does the <TT>#</TT> in front of a temporary table signify?<BR>
	SQL Server uses the <TT>#</TT> to flag a temporary table.</P>
	<P><B>5.</B> What must be done after closing a cursor to return memory?<BR>
	You must deallocate the cursor. The syntax is
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; deallocate cursor cursor_name;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>6.</B> Are triggers used with the <TT>SELECT</TT> statement?
	<P>No. They are executed by the use of <TT>UPDATE</TT>, <TT>DELETE</TT>, or <TT>INSERT</TT>.</P>
	<P><B>7.</B> If you have a trigger on a table and the table is dropped, does the
	trigger still exist?</P>
	<P>No. The trigger is automatically dropped when the table is dropped.
</DL>

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

<DL>
	<DD><B>1. </B>Create a sample database application. (We used a music collection to
	illustrate these points today.) Break this application into logical data groupings.
	<P><B>2.</B> List of queries you think will be required to complete this application.</P>
	<P><B>3.</B> List the various rules you want to maintain in the database.</P>
	<P><B>4. </B>Create a database schema for the various groups of data you described
	in step 1.</P>
	<P><B>5. </B>Convert the queries in step 2 to stored procedures.</P>
	<P><B>6.</B> Convert the rules in step 3 to triggers.</P>
	<P><B>7.</B> Combine steps 4, 5, and 6 into a large script file that can be used
	to build the database and all its associated procedures.</P>
	<P><B>8.</B> Insert some sample data. (This step can also be a part of the script
	file in step 7.)</P>
	<P><B>9.</B> Execute the procedures you have created to test their functionality.<BR>
	(On your own.)
</DL>

<H2><FONT COLOR="#000077">Day 14, &quot;Dynamic Uses of SQL&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> In which object does Microsoft Visual C++ place its SQL?<BR>
	In the <TT>CRecordSet</TT> object's <TT>GetDefaultSQL</TT> member. Remember, you
	can change the string held here to manipulate your table.
	<P><B>2.</B> In which object does Delphi place its SQL?<BR>
	In the <TT>TQuery</TT> object.</P>
	<P><B>3. </B>What is ODBC?<BR>
	ODBC stands for open database connectivity. This technology enables Windows-based
	programs to access a database through a driver.</P>
	<P><B>4. </B>What does Delphi do?<BR>
	Delphi provides a scalable interface to various databases.
</DL>

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

<DL>
	<DD><B>1. </B>Change the sort order in the C++ example from ascending to descending
	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>