ch09.htm

Learn SQL in an easy way...

another entered <TT>111 First St.</TT>? With today's graphical user environments,
the correct string could be entered into a list box. When a user makes a selection
from the list box, the code would convert this string to a unique ID and save this
ID to the database.</P>
<P>Now you can create the tables you used earlier today. You will use these tables
for the rest of today, so you will want to fill them with some data. Use the <TT>INSERT</TT>
command covered yesterday to load the tables with the data in Tables 9.3, 9.4, and
9.5.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>create database PAYMENTS;
</B>
Statement processed.

SQL&gt;  <B>create table BILLS (</B>
  2   <B>NAME CHAR(30) NOT NULL,</B>
  3   <B>AMOUNT NUMBER,</B>
  4  <B> ACCOUNT_ID NUMBER NOT NULL);</B>

Table created.

SQL&gt;  <B>create table BANK_ACCOUNTS (</B>
  2   <B>ACCOUNT_ID NUMBER NOT NULL,</B>
  3   <B>TYPE CHAR(30),</B>
  4  <B> BALANCE NUMBER,</B>
  5  <B> BANK CHAR(30));</B>

Table created.

SQL&gt; <B> create table COMPANY (</B>
  2   <B>NAME CHAR(30) NOT NULL,</B>
  3   <B>ADDRESS CHAR(50),</B>
  4   <B>CITY CHAR(30),</B>
  5   <B>STATE CHAR(2));</B>

Table created.
</FONT></PRE>
<H4><FONT COLOR="#000077">Table 9.3. Sample data for the BILLS table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Amount</B></TD>
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Phone Company</TD>
		<TD ALIGN="LEFT">125</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Power Company</TD>
		<TD ALIGN="LEFT">75</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Record Club</TD>
		<TD ALIGN="LEFT">25</TD>
		<TD ALIGN="LEFT">2</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Software Company</TD>
		<TD ALIGN="LEFT">250</TD>
		<TD ALIGN="LEFT">1</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Cable TV Company</TD>
		<TD ALIGN="LEFT">35</TD>
		<TD ALIGN="LEFT">3</TD>
	</TR>
</TABLE>

<H4><FONT COLOR="#000077">Table 9.4. Sample data for the BANK_ACCOUNTS table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Account_ID</B></TD>
		<TD ALIGN="LEFT"><B>Type</B></TD>
		<TD ALIGN="LEFT"><B>Balance</B></TD>
		<TD ALIGN="LEFT"><B>Band</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">1</TD>
		<TD ALIGN="LEFT">Checking</TD>
		<TD ALIGN="LEFT">500</TD>
		<TD ALIGN="LEFT">First Federal</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">2</TD>
		<TD ALIGN="LEFT">Money Market</TD>
		<TD ALIGN="LEFT">1200</TD>
		<TD ALIGN="LEFT">First Investor's</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">3</TD>
		<TD ALIGN="LEFT">Checking</TD>
		<TD ALIGN="LEFT">90</TD>
		<TD ALIGN="LEFT">Credit Union</TD>
	</TR>
</TABLE>

<H4><FONT COLOR="#000077">Table 9.5. Sample data for the COMPANY table.</FONT></H4>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Name</B></TD>
		<TD ALIGN="LEFT"><B>Address</B></TD>
		<TD ALIGN="LEFT"><B>City</B></TD>
		<TD ALIGN="LEFT"><B>State</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Phone Company</TD>
		<TD ALIGN="LEFT">111 1st Street</TD>
		<TD ALIGN="LEFT">Atlanta</TD>
		<TD ALIGN="LEFT">GA</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Power Company</TD>
		<TD ALIGN="LEFT">222 2nd Street</TD>
		<TD ALIGN="LEFT">Jacksonville</TD>
		<TD ALIGN="LEFT">FL</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Record Club</TD>
		<TD ALIGN="LEFT">333 3rd Avenue</TD>
		<TD ALIGN="LEFT">Los Angeles</TD>
		<TD ALIGN="LEFT">CA</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Software Company</TD>
		<TD ALIGN="LEFT">444 4th Drive</TD>
		<TD ALIGN="LEFT">San Francisco</TD>
		<TD ALIGN="LEFT">CA</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Cable TV Company</TD>
		<TD ALIGN="LEFT">555 5th Drive</TD>
		<TD ALIGN="LEFT">Austin</TD>
		<TD ALIGN="LEFT">TX</TD>
	</TR>
</TABLE>

<H3><FONT COLOR="#000077">Table Storage and Sizing</FONT></H3>
<P>Most major RDBMSs have default settings for table sizes and table locations. If
you do not specify table size and location, then the table will take the defaults.
The defaults may be very undesirable, especially for large tables. The default sizes
and locations will vary among the implementations. Here is an example of a <TT>CREATE
TABLE</TT> statement with a storage clause (from Oracle).</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;   <B>CREATE TABLE TABLENAME</B>
  2    <B>(COLUMN1    CHAR   NOT NULL,</B>
  3     <B>COLUMN2    NUMBER,</B>
  4    <B> COLUMN3    DATE)</B>
  5     <B>TABLESPACE TABLESPACE NAME</B>
  6     <B>STORAGE</B>
  7   <B>  INITIAL SIZE,</B>
  8     <B>NEXT SIZE,</B>
  9    <B> MINEXTENTS value,</B>
 10    <B> MAXEXTENTS value,</B>
 11     <B>PCTINCREASE value);</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">Table created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In Oracle you can specify a tablespace in which you want the table to reside.
A decision is usually made according to the space available, often by the database
administrator (DBA). <TT>INITIAL SIZE</TT> is the size for the initial extent of
the table (the initial allocated space). <TT>NEXT SIZE</TT> is the value for any
additional extents the table may take through growth. <TT>MINEXTENTS</TT> and <TT>MAXEXTENTS</TT>
identify the minimum and maximum extents allowed for the table, and <TT>PCTINCREASE</TT>
identifies the percentage the next extent will be increased each time the table grows,
or takes another extent.
<H3><FONT COLOR="#000077">Creating a Table from an Existing Table</FONT></H3>
<P>The most common way to create a table is with the <TT>CREATE TABLE</TT> command.
However, some database management systems provide an alternative method of creating
tables, using the format and data of an existing table. This method is useful when
you want to select the data out of a table for temporary modification. It can also
be useful when you have to create a table similar to the existing table and fill
it with similar data. (You won't have to reenter all this information.) The syntax
for Oracle follows.</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">CREATE TABLE NEW_TABLE(FIELD1, FIELD2, FIELD3)
AS (SELECT FIELD1, FIELD2, FIELD3
   FROM OLD_TABLE &lt;WHERE...&gt;
</FONT></PRE>
<P>This syntax allows you to create a new table with the same data types as those
of the fields that are selected from the old table. It also allows you to rename
the fields in the new table by giving them new names.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>CREATE TABLE NEW_BILLS(NAME, AMOUNT, ACCOUNT_ID)</B>
  2   <B>AS (SELECT * FROM BILLS WHERE AMOUNT &lt; 50);
</B>
Table created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding statement creates a new table (<TT>NEW_BILLS</TT>) with all the
records from the <TT>BILLS</TT> table that have an <TT>AMOUNT</TT> less than <TT>50</TT>.</P>
<P>Some database systems also allow you to use the following syntax:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">INSERT NEW_TABLE
SELECT &lt;field1, field2... | *&gt; from OLD_TABLE
&lt;WHERE...&gt;
</FONT></PRE>
<P>The preceding syntax would create a new table with the exact field structure and
data found in the old table. Using SQL Server's Transact-SQL language in the following
example illustrates this technique.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>INSERT NEW_BILLS</B>
1&gt; <B>select * from BILLS where AMOUNT &lt; 50</B>
2&gt;<B> go</B>
</FONT></PRE>
<P>(The <TT>GO</TT> statement in SQL Server processes the SQL statements in the command
buffer. It is equivalent to the semicolon (<TT>;</TT>) used in Oracle7.)
<H2><FONT COLOR="#000077">The ALTER TABLE Statement</FONT></H2>
<P>Many times your database design does not account for everything it should. Also,
requirements for applications and databases are always subject to change. The <TT>ALTER
TABLE</TT> statement enables the database administrator or designer to change the
structure of a table after it has been created.</P>
<P>The <TT>ALTER TABLE</TT> command enables you to do two things:

<UL>
	<LI>Add a column to an existing table
</UL>


<UL>
	<LI>Modify a column that already exists
</UL>

<P>The syntax for the <TT>ALTER TABLE</TT> statement is as follows:</P>
<H5>SYNTAX:</H5>
<PRE><FONT COLOR="#0066FF">ALTER TABLE table_name
  &lt;ADD column_name data_type; |
  MODIFY column_name data_type;&gt;
</FONT></PRE>
<P>The following command changes the <TT>NAME</TT> field of the <TT>BILLS</TT> table
to hold 40 characters:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>ALTER TABLE BILLS</B>
  2   <B>MODIFY NAME CHAR(40);</B>

Table altered.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You can increase or decrease the
	length of columns; however, you can not decrease a column's length if the current
	size of one of its values is greater than the value you want to assign to the column
	length. 
<HR>


</BLOCKQUOTE>

<P>Here's a statement to add a new column to the <TT>NEW_BILLS</TT> table:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>ALTER TABLE NEW_BILLS</B>