<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 COLLECTIONWHERE 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 COLLECTIONSELECT * 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 COLLECTIONSSET 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_NAMEJONES          NANCYSMITH          JOHN2 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_NAMESMITH1 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></BLOCKQUOTE><DL>	<DD><B>3. </B>Using your knowledge of SQL joins (see Day 6, &quot;Joining Tables&quot;),	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, &quot;Creating Views and Indexes&quot;</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&gt; <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?