apf.htm

21精通SQL

FROM ORDERS
WHERE NAME ='JACKS BIKE')</B>

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
</FONT></PRE>
<H2><FONT COLOR="#000077">Day 8, &quot;Manipulating Data&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">DELETE COLLECTION;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>If you want to delete all records from the <TT>COLLECTION</TT> table, you must
	use the following syntax:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">DELETE FROM COLLECTION;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Keep in mind that this statement will delete all records. You can qualify which
	records you want to delete by using the following syntax:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">DELETE FROM COLLECTION
WHERE VALUE = 125</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement would delete all records with a value of <TT>125</TT>.
	<P><B>2.</B> What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">INSERT INTO COLLECTION SELECT * FROM TABLE_2</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement was designed to insert all the records from <TT>TABLE_2</TT> into
	the <TT>COLLECTION</TT> table. The main problem here is using the <TT>INTO</TT> keyword
	with the <TT>INSERT</TT> statement. When copying data from one table into another
	table, you must use the following syntax:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">INSERT COLLECTION
SELECT * FROM TABLE_2;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Also, remember that the data types of the fields selected from <TT>TABLE_2</TT>
	must exactly match the data types and order of the fields within the <TT>COLLECTION</TT>
	table.
	<P><B>3.</B> What is wrong with the following statement?
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">UPDATE COLLECTION (&quot;HONUS WAGNER CARD&quot;, 25000, &quot;FOUND IT&quot;);</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement confuses the <TT>UPDATE</TT> function with the <TT>INSERT</TT>
	function. To <TT>UPDATE</TT> values into the <TT>COLLECTIONS</TT> table, use the
	following syntax:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">UPDATE COLLECTIONS
SET NAME = &quot;HONUS WAGNER CARD&quot;,
  VALUE = 25000,
  REMARKS = &quot;FOUND IT&quot;;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>4. </B>What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE * FROM COLLECTION;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Nothing would be deleted because of incorrect syntax. The <TT>*</TT> is not required
	here.
	<P><B>5.</B> What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM COLLECTION;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>All rows in the <TT>COLLECTION</TT> table will be deleted.
	<P><B>6.</B> What would happen if you issued the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE COLLECTION
     SET WORTH = 555
     SET REMARKS = 'UP FROM 525';</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>All values in the <TT>COLLECTION</TT> table for the worth column are now <TT>555</TT>,
	and all remarks in the <TT>COLLECTION</TT> table now say <TT>UP FROM 525</TT>. Probably
	not a good thing!
	<P><B>7.</B> Will the following SQL statement work?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>INSERT INTO COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>No. The syntax is not correct. The <TT>INSERT</TT> and the <TT>SET</TT> do not
	go together.
	<P><B>8. </B>Will the following SQL statement work?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt;<B> UPDATE COLLECTION
     SET VALUES = 900
     WHERE ITEM = 'STRING';</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> Try inserting values with incorrect data types into a table. Note the
	errors and then insert values with correct data types into the same table.
	<P>Regardless of the implementation you are using, the errors that you receive should
	indicate that the data you are trying to insert is not compatible with the data type
	that has been assigned to the column(s) of the table.</P>
	<P><B>2.</B> Using your database system, try exporting a table (or an entire database)
	to some other format. Then import the data back into your database. Familiarize yourself
	with this capability. Also, export the tables to another database format if your
	DBMS supports this feature. Then use the other system to open these files and examine
	them.</P>
	<P>See your database documentation for the exact syntax when exporting or importing
	data. You may want to delete all rows from your table if you are performing repeated
	imports. Always test your export/import utilities before using them on production
	data. If your tables have unique constraints on columns and you fail to truncate
	the data from those tables before import, then you will be showered by unique constraint
	errors.
</DL>

<H2><FONT COLOR="#000077">Day 9, &quot;Creating and Maintaining Tables&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1. </B>True or False: The <TT>ALTER DATABASE</TT> statement is often used
	to modify an existing table's structure.
	<P>False. Most systems do not have an <TT>ALTER DATABASE</TT> command. The <TT>ALTER
	TABLE</TT> command is used to modify an existing table's structure.</P>
	<P><B>2.</B> True or False: The <TT>DROP TABLE</TT> command is functionally equivalent
	to the <TT>DELETE FROM &lt;table_name&gt;</TT> command.</P>
	<P>False. The <TT>DROP TABLE</TT> command is not equivalent to the <TT>DELETE FROM
	&lt;table_name&gt;</TT> command. The <TT>DROP TABLE</TT> command completely deletes
	the table along with its structure from the database. The <TT>DELETE FROM...</TT>
	command removes only the records from a table. The table's structure remains in the
	database.</P>
	<P><B>3.</B> True or False: To add a new table to a database, use the <TT>CREATE
	TABLE</TT> command.<BR>
	True.</P>
	<P><B>4.</B> What is wrong with the following statement?</P>
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">CREATE TABLE new_table (
ID NUMBER,
FIELD1 char(40),
FIELD2 char(80),
ID char(40);</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>This statement has two problems. The first problem is that the name <TT>ID</TT>
	is repeated within the table. Even though the data types are different, reusing a
	field name within a table is illegal. The second problem is that the closing parentheses
	are missing from the end of the statement. It should look like this:
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">CREATE TABLE new_table (
ID NUMBER,
FIELD1 char(40),
FIELD2 char(80));</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>5.</B> What is wrong with the following statement?
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>ALTER DATABASE BILLS (
COMPANY char(80));</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>The command to modify a field's data type or length is the <TT>ALTER TABLE</TT>
	command, not the <TT>ALTER DATABASE</TT> command.
	<P><B>6.</B> When a table is created, who is the owner?</P>
	<P>The owner of the new table would be whoever created the table. If you signed on
	as your ID, then your ID would be the owner. If you signed on as SYSTEM, then SYSTEM
	would be the owner.</P>
	<P><B>7.</B> If data in a character column has varying lengths, what is the best
	choice for the data type?</P>
	<P><TT>VARCHAR2</TT> is the best choice. Here's what happens with the <TT>CHAR</TT>
	data type when the data length varies:</P>
	<H5>INPUT/OUTPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>
  2  <B>FROM NAME_TABLE;</B>
</FONT></PRE>
	<PRE><FONT COLOR="#0066FF">LAST_NAME      FIRST_NAME
JONES          NANCY
SMITH          JOHN
2 rows selected.
</FONT></PRE>
	<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>SELECT LAST_NAME</B>
  2  <B> FROM NAME_TABLE</B>
  3  <B> WHERE LAST_NAME LIKE '%MITH';</B>

No rows selected.</FONT></PRE>

</BLOCKQUOTE>

<H5>ANALYSIS:</H5>

<DL>
	<DD>You were looking for <TT>SMITH</TT>, but <TT>SMITH</TT> does exist in our table.
	The query finds <TT>SMITH</TT> because the column <TT>LAST_NAME</TT> is <TT>CHAR</TT>
	and there are spaces after <TT>SMITH</TT>. The <TT>SELECT</TT> statement did not
	ask for these spaces. Here's the correct statement to find <TT>SMITH</TT>:
	<H5>INPUT/OUTPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>SELECT LAST_NAME</B>
  2   <B>FROM NAME_TABLE</B>
  3   <B>WHERE LAST_NAME LIKE '%MITH%';</B>

LAST_NAME
SMITH
1 row selected.</FONT></PRE>

</BLOCKQUOTE>

<H5>ANALYSIS:</H5>

<DL>
	<DD>By adding the <TT>%</TT> after <TT>MITH</TT>, the <TT>SELECT</TT> statement found
	<TT>SMITH</TT> and the spaces after the name.
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>When creating tables, plan your data
	types to avoid this type of situation. Be aware of how your data types act. If you
	allocate 30 bytes for a column and some values in the column contain fewer than 30
	bytes, does the particular data type pad spaces to fill up 30 bytes? If so, consider
	how this may affect your select statements. Know your data and its structure. 
<HR>


</BLOCKQUOTE>


<DL>
	<DD><B>8. </B>Can you have duplicate table names?
	<P>Yes. Just as long as the owner or schema is not the same.
</DL>

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

<DL>
	<DD><B>1.</B> Add two tables to the <TT>BILLS</TT> database named <TT>BANK</TT> and
	<TT>ACCOUNT_TYPE</TT> using any format you like. The <TT>BANK</TT> table should contain
	information about the <TT>BANK</TT> field used in the <TT>BANK_ACCOUNTS</TT> table
	in the examples. The <TT>ACCOUNT_TYPE</TT> table should contain information about
	the <TT>ACCOUNT_TYPE</TT> field in the <TT>BANK_ACCOUNTS</TT> table also. Try to
	reduce the data as much as possible.
	<P>You should use the <TT>CREATE TABLE</TT> command to make the tables. Possible
	SQL statements would look like this:
</DL>

<PRE><FONT COLOR="#0066FF">       SQL&gt; <B>CREATE TABLE BANK</B>
         2   <B>( ACCOUNT_ID    NUMBER(30)    NOT NULL,
               BANK_NAME     VARCHAR2(30)  NOT NULL,
               ST_ADDRESS    VARCHAR2(30)  NOT NULL,
               CITY          VARCHAR2(15)  NOT NULL,
               STATE         CHAR(2)       NOT NULL,
               ZIP           NUMBER(5)     NOT NULL;
</B>
        SQL&gt; <B>CREATE TABLE ACCOUNT_TYPE
             ( ACCOUNT_ID   NUMBER(30)    NOT NULL,
               SAVINGS      CHAR(30),
               CHECKING     CHAR(30);
</B></FONT></PRE>

<DL>
	<DD><B>2.</B> With the five tables that you have created--<TT>BILLS</TT>, <TT>BANK_ACCOUNTS</TT>,
	<TT>COMPANY</TT>, <TT>BANK</TT>, and <TT>ACCOUNT_TYPE</TT>--change the table structure
	so that instead of using <TT>CHAR</TT> fields as keys, you use integer <TT>ID</TT>
	fields as keys.
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>ALTER TABLE BILLS DROP PRIMARY KEY;</B>
SQL&gt; <B>ALTER TABLE BILLS ADD (PRIMARY KEY (ACCOUNT_ID));</B>
SQL&gt; <B>ALTER TABLE COMPANY ADD (PRIMARY KEY (ACCOUNT_ID));</B></FONT></PRE>