📄 apf.htm
字号:
</BLOCKQUOTE>
<DL>
<DD><B>3. </B>Using your knowledge of SQL joins (see Day 6, "Joining Tables"),
write several queries to join the tables in the <TT>BILLS</TT> database.
<P>Because we altered the tables in the previous exercise and made the key field
the <TT>ACCOUNT_ID</TT> column, all the tables can be joined by this column. You
can join the tables in any combination; you can even join all five tables. Don't
forget to qualify your columns and tables.
</DL>
<H2><FONT COLOR="#000077">Day 10, "Creating Views and Indexes"</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>
<DL>
<DD><B>1.</B> What will happen if a unique index is created on a nonunique field?
<P>Depending on which database you are using, you will receive some type of error
and no index at all will be created. The constituent fields of a unique index must
form a unique value.</P>
<P><B>2.</B> Are the following statements true or false?</P>
<P>Both views and indexes take up space in the database and therefore must be factored
in the planning of the database size.</P>
<P>False. Only indexes take up physical space.<BR>
If someone updates a table on which a view has been created, the view must have an
identical update performed on it to see the same data.</P>
<P>False. If someone updates a table, then the view will see the updated data.<BR>
If you have the disk space and you really want to get your queries smoking, the more
indexes the better.</P>
<P>False. Sometimes too many indexes can actually slow down your queries.</P>
<P><B>3.</B> Is the following <TT>CREATE</TT> statement correct?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>create view credit_debts as
(select all from debts
where account_id = 4);</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>No. You do not need the parentheses; also the word <TT>all</TT> should been an
<TT>*</TT>.
<P><B>4. </B>Is the following <TT>CREATE</TT> statement correct?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <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> <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> <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, "Controlling Transactions"</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 "save off" 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> <B>START TRANSACTION
INSERT INTO CUSTOMERS VALUES
('SMITH', 'JOHN')</B>
SQL><B> COMMIT;</B></FONT></PRE>
<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION;
INSERT INTO CUSTOMERS VALUES
('SMITH', 'JOHN');</B>
SQL> <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> <B>SET TRANSACTION;
UPDATE BALANCES SET CURR_BAL = 25000;</B>
SQL> <B>COMMIT;</B></FONT></PRE>
<P><I>Answer:</I>
</BLOCKQUOTE>
<PRE></PRE>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF">SQL> <B>SET TRANSACTION;
UPDATE BALANCES SET CURR_BAL = 25000;</B>
SQL> <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> <B>SET TRANSACTION;
INSERT INTO BALANCES VALUES
('567.34', '230.00', '8');</B>
SQL><B> ROLLBACK;</B></FONT></PRE>
</BLOCKQUOTE>
<DL>
<DD>This statement is correct. Nothing will be inserted.
</DL>
<H2><FONT COLOR="#000077">Day 12, "Database Security"</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> <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> <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> <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> <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> <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> <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, "Advanced SQL Topics"</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> 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, "Dynamic Uses of SQL"</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
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -