ch09.htm

Learn SQL in an easy way...

  2   <B>ADD COMMENTS CHAR(80);</B>

Table altered.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement would add a new column named <TT>COMMENTS</TT> capable of holding
80 characters. The field would be added to the right of all the existing fields.</P>
<P>Several restrictions apply to using the <TT>ALTER TABLE</TT> statement. You cannot
use it to add or delete fields from a database. It can change a column from <TT>NOT
NULL</TT> to <TT>NULL</TT>, but not necessarily the other way around. A column specification
can be changed from <TT>NULL</TT> to <TT>NOT NULL</TT> only if the column does not
contain any <TT>NULL</TT> values. To change a column from <TT>NOT NULL</TT> to <TT>NULL</TT>,
use the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">  ALTER TABLE table_name  MODIFY (column_name data_type NULL);
</FONT></PRE>
<P>To change a column from <TT>NULL</TT> to <TT>NOT NULL</TT>, you might have to
take several steps:

<DL>
	<DD><B>1.</B> Determine whether the column has any <TT>NULL</TT> values.
	<P><B>2.</B> Deal with any <TT>NULL</TT> values that you find. (Delete those records,
	update the column's value, and so on.)</P>
	<P><B>3.</B> Issue the <TT>ALTER TABLE</TT> command.
</DL>



<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Some database management systems
	allow the use of the <TT>MODIFY</TT> clause; others do not. Still others have added
	other clauses to the <TT>ALTER TABLE</TT> statement. In Oracle, you can even alter
	the table's storage parameters. Check the documentation of the system you are using
	to determine the implementation of the <TT>ALTER TABLE</TT> statement. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The DROP TABLE Statement</FONT></H2>
<P>SQL provides a command to completely remove a table from a database. The <TT>DROP
TABLE</TT> command deletes a table along with all its associated views and indexes.
(See Day 10 for details.) After this command has been issued, there is no turning
back. The most common use of the <TT>DROP TABLE</TT> statement is when you have created
a table for temporary use. When you have completed all operations on the table that
you planned to do, issue the <TT>DROP TABLE</TT> statement with the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">DROP TABLE table_name;
</FONT></PRE>
<P>Here's how to drop the <TT>NEW_BILLS</TT> table:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DROP TABLE NEW_BILLS;
</B>
Table dropped.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice the absence of system prompts. This command did not ask <TT>Are you sure?
(Y/N)</TT>. After the <TT>DROP TABLE</TT> command is issued, the table is permanently
deleted.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>WARNING:</B></FONT><B> </B>If you issue</P>
	<PRE><FONT COLOR="#0066FF">SQL&gt; DROP TABLE NEW_BILLS;</FONT></PRE>
	<P>you could be dropping the incorrect table. When dropping tables, you should always
	use the owner or schema name. The recommended syntax is</P>
	<PRE><FONT COLOR="#0066FF">SQL&gt; DROP TABLE OWNER.NEW_BILLS;</FONT></PRE>
	<P>We are stressing this syntax because we once had to repair a production database
	from which the wrong table had been dropped. The table was not properly identified
	with the schema name. Restoring the database was an eight-hour job, and we had to
	work until well past midnight. 
<HR>


</BLOCKQUOTE>

<H2><FONT COLOR="#000077">The DROP DATABASE Statement</FONT></H2>
<P>Some database management systems also provide the <TT>DROP DATABASE</TT> statement,
which is identical in usage to the <TT>DROP TABLE</TT> statement. The syntax for
this statement is as follows:</P>
<PRE><FONT COLOR="#0066FF">DROP DATABASE database_name
</FONT></PRE>
<P>Don't drop the <TT>BILLS</TT> database now because you will use it for the rest
of today, as well as on Day 10.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The various relational database
	implementations require you to take diff-erent steps to drop a database. After the
	database is dropped, you will need to clean up the operating system files that compose
	the database. 
<HR>


</BLOCKQUOTE>

<H4><FONT COLOR="#000077">Exercise 9.2</FONT></H4>
<P>Create a database with one table in it. Issue the <TT>DROP TABLE</TT> command
and the issue the <TT>DROP DATABASE</TT> command. Does your database system allow
you to do this? Single-file-based systems, such as Microsoft Access, do not support
this command. The database is contained in a single file. To create a database, you
must use the menu options provided in the product itself. To delete a database, simply
delete the file from the hard drive.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Day 9 covers the major features of SQL's Data Manipulation Language (DML). In
particular, you learned five new statements: <TT>CREATE DATABASE</TT>, <TT>CREATE
TABLE</TT>, <TT>ALTER TABLE</TT>, <TT>DROP TABLE</TT>, and <TT>DROP DATABASE</TT>.
Today's lesson also discusses the importance of creating a good database design.</P>
<P>A data dictionary is one of the most important pieces of documentation you can
create when designing a database. This dictionary should include a complete description
of all objects in the database: tables, fields, views, indexes, stored procedures,
triggers, and so forth. A complete data dictionary also contains a brief comment
explaining the purpose behind each item in the database. You should update the data
dictionary whenever you make changes to the database.</P>
<P>Before using any of the data manipulation statements, it is also important to
create a good database design. Break down the required information into logical groups
and try to identify a primary key field that other groups (or tables) can use to
reference this logical group. Use foreign key fields to point to the primary or foreign
key fields in other tables.</P>
<P>You learned that the <TT>CREATE DATABASE</TT> statement is not a standard element
within database systems. This variation is primarily due to the many different ways
vendors store their databases on disk. Each implementation enables a different set
of features and options, which results in a completely different <TT>CREATE DATABASE</TT>
statement. Simply issuing <TT>CREATE DATABASE database_name</TT> creates a default
database with a default size on most systems. The <TT>DROP DATABASE</TT> statement
permanently removes that database.</P>
<P>The <TT>CREATE TABLE</TT> statement creates a new table. With this command, you
can create the fields you need and identify their data types. Some database management
systems also allow you to specify other attributes for the field, such as whether
it can allow <TT>NULL</TT> values or whether that field should be unique throughout
the table. The <TT>ALTER TABLE</TT> statement can alter the structure of an existing
table. The <TT>DROP TABLE</TT> statement can delete a table from a database.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q Why does the <TT>CREATE DATABASE</TT> statement vary so much from one system
	to another?</B>
	<P><B>A</B> <TT>CREATE DATABASE</TT> varies because the actual process of creating
	a database varies from one database system to another. Small PC-based databases usually
	rely on files that are created within some type of application program. To distribute
	the database on a large server, related database files are simply distributed over
	several disk drives. When your code accesses these databases, there is no database
	process running on the computer, just your application accessing the files directly.
	More powerful database systems must take into account disk space management as well
	as support features such as security, transaction control, and stored procedures
	embedded within the database itself. When your application program accesses a database,
	a database server manages your requests (along with many others' requests) and returns
	data to you through a sometimes complex layer of middleware. These topics are discussed
	more in Week 3. For now, learn all you can about how your particular database management
	system creates and manages databases.</P>
	<P><B>Q Can I create a table temporarily and then automatically drop it when I am
	done with it?</B></P>
	<P><B>A</B> Yes. Many database management systems support the concept of a temporary
	table. This type of table is created for temporary usage and is automatically deleted
	when your user's process ends or when you issue the <TT>DROP TABLE</TT> command.
	The use of temporary tables is discussed on Day 14, &quot;Dynamic Uses of SQL.&quot;</P>
	<P><B>Q Can I remove columns with the <TT>ALTER TABLE</TT> statement?</B></P>
	<P><B>A</B> No. The <TT>ALTER TABLE</TT> command can be used only to add or modify
	columns within a table. To remove columns, create a new table with the desired format
	and then select the records from the old table into the new table.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</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><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><B>3.</B> True or False: To add a new table to a database, use the <TT>CREATE
	TABLE</TT> command.</P>
	<P><B>4.</B> What is wrong with the following statement?</P>
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>CREATE TABLE new_table (
ID NUMBER,
FIELD1 char(40),
FIELD2 char(80),
ID char(40);</B></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><B>6.</B> When a table is created, who is the owner?
	<P><B>7.</B> If data in a character column has varying lengths, what is the best
	choice for the data type?</P>
	<P><B>8.</B> Can you have duplicate table names?
</DL>

<H3><FONT COLOR="#000077">Exercises</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><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.</P>
	<P><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.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch08/ch08.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch10/ch10.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>