ch10.htm

21精通SQL

		<TD ALIGN="LEFT">25 College Blvd</TD>
		<TD ALIGN="LEFT">Columbia</TD>
		<TD ALIGN="LEFT">SC</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Florida Water Company</TD>
		<TD ALIGN="LEFT">1883 Hwy 87</TD>
		<TD ALIGN="LEFT">Navarre</TD>
		<TD ALIGN="LEFT">FL</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">U-O-Us Insurance</TD>
		<TD ALIGN="LEFT">295 Beltline Hwy</TD>
		<TD ALIGN="LEFT">Macon</TD>
		<TD ALIGN="LEFT">GA</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Company</TD>
		<TD ALIGN="LEFT"></TD>
		<TD ALIGN="LEFT"></TD>
		<TD ALIGN="LEFT"></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">Debtor's Credit Card</TD>
		<TD ALIGN="LEFT">115 2nd Avenue</TD>
		<TD ALIGN="LEFT">Newark</TD>
		<TD ALIGN="LEFT">NJ</TD>
	</TR>
</TABLE>
</P>
<P>Now that you have successfully used the <TT>CREATE DATABASE</TT>, <TT>CREATE TABLE</TT>,
and <TT>INSERT</TT> commands to input all this information, you are ready for an
in-depth discussion of the view.
<H3><FONT COLOR="#000077">A Simple View</FONT></H3>
<P>Let's begin with the simplest of all views. Suppose, for some unknown reason,
you want to make a view on the <TT>BILLS</TT> table that looks identical to the table
but has a different name. (We call it <TT>DEBTS</TT>.) Here's the statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW DEBTS AS</B>
     <B>SELECT * FROM BILLS;</B>
</FONT></PRE>
<P>To confirm that this operation did what it should, you can treat the view just
like a table:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM DEBTS;</B>

NAME                     AMOUNT  ACCOUNT_ID
Phone Company            125     1
Power Company             75     1
Record Club               25     2
Software Company         250     1
Cable TV Company          35     3
Joe's Car Palace         350     5
S.C. Student Loan        200     6
Florida Water Company     20     1
U-O-Us Insurance Company 125     5
Debtor's Credit Card      35     4
10 rows selected.
</FONT></PRE>
<P>You can even create new views from existing views. Be careful when creating views
of views. Although this practice is acceptable, it complicates maintenance. Suppose
you have a view three levels down from a table, such as a view of a view of a view
of a table. What do you think will happen if the first view on the table is dropped?
The other two views will still exist, but they will be useless because they get part
of their information from the first view. Remember, after the view has been created,
it functions as a virtual table.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW CREDITCARD_DEBTS AS</B>
  2   <B>SELECT * FROM DEBTS</B>
  3   <B>WHERE ACCOUNT_ID = 4;</B>
SQL&gt; <B>SELECT * FROM CREDITCARD_DEBTS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                   AMOUNT    ACCOUNT_ID
Debtor's Credit Card       35    4

1 row selected.
</FONT></PRE>
<P>The <TT>CREATE VIEW</TT> also enables you to select individual columns from a
table and place them in a view. The following example selects the <TT>NAME</TT> and
<TT>STATE</TT> fields from the <TT>COMPANY</TT> table.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW COMPANY_INFO (NAME, STATE) AS</B>
  2   <B>SELECT * FROM COMPANY;</B>
SQL&gt; <B>SELECT * FROM COMPANY_INFO;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                      STATE
Phone Company                GA
Power Company                FL
Record Club                  CA
Software Company             CA
Cable TV Company             TX
Joe's Car Palace             FL
S.C. Student Loan            SC
Florida Water Company        FL
U-O-Us Insurance Company     GA
Debtor's Credit Card         NJ

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


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Users may create views to query
	specific data. Say you have a table with 50 columns and hundreds of thousands of
	rows, but you need to see data in only 2 columns. You can create a view on these
	two columns, and then by querying from the view, you should see a remarkable difference
	in the amount of time it takes for your query results to be returned. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">Renaming Columns</FONT></H3>
<P>Views simplify the representation of data. In addition to naming the view, the
SQL syntax for the <TT>CREATE VIEW</TT> statement enables you to rename selected
columns. Consider the preceding example a little more closely. What if you wanted
to combine the <TT>ADDRESS</TT>, <TT>CITY</TT>, and <TT>STATE</TT> fields from the
<TT>COMPANY</TT> table to print them on an envelope? The following example illustrates
this. This example uses the SQL <TT>+</TT> operator to combine the address fields
into one long address by combining spaces and commas with the character data.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW ENVELOPE (COMPANY, MAILING_ADDRESS) AS</B>
  2   <B>SELECT NAME, ADDRESS + &quot; &quot; + CITY + &quot;, &quot; + STATE</B>
  3   <B>FROM COMPANY;</B>
SQL&gt; <B>SELECT * FROM ENVELOPE;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">COMPANY                      MAILING_ADDRESS
Phone Company                111 1st Street Atlanta, GA
Power Company                222 2nd Street Jacksonville, FL
Record Club                  333 3rd Avenue Los Angeles, CA
Software Company             444 4th Drive San Francisco, CA
Cable TV Company             555 5th Drive Austin, TX
Joe's Car Palace             1000 Govt. Blvd Miami, FL
S.C. Student Loan            25 College Blvd. Columbia, SC
Florida Water Company        1883 Hwy. 87 Navarre, FL
U-O-Us Insurance Company     295 Beltline Hwy. Macon, GA
Debtor's Credit Card         115 2nd Avenue Newark, NJ

10 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The SQL syntax requires you to supply a virtual field name whenever the view's
virtual field is created using a calculation or SQL function. This pro- cedure makes
sense because you wouldn't want a view's column name to be <TT>COUNT(*)</TT> or <TT>AVG(PAYMENT)</TT>.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Check your implementation for the
	use of the <TT>+</TT> operator. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">SQL View Processing</FONT></H3>
<P>Views can represent data within tables in a more convenient fashion than what
actually exists in the database's table structure. Views can also be extremely convenient
when performing several complex queries in a series (such as within a stored procedure
or application program). To solidify your understanding of the view and the <TT>SELECT</TT>
statement, the next section examines the way in which SQL processes a query against
a view. Suppose you have a query that occurs often, for example, you routinely join
the <TT>BILLS</TT> table with the <TT>BANK_ACCOUNTS</TT> table to retrieve information
on your payments.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE,</B>
  2  <B> BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS</B>
  3   <B>WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">BILLS.NAME	           BILLS.AMOUNT   BANK_ACCOUNTS.BALANCE   BANK_ACCOUNTS.BANK  
Phone Company	           125            500                     First Federal
Power Company	           75             500                     First Federal
Record Club	           25             1200                    First Investor's
Software Company	      250            500                     First  Federal
Cable TV Company	      35             90                      Credit Union
Joe's Car Palace	      350            2500                    Second Mutual
S.C. Student Loan	      200            4500                    Fidelity
Florida Water Company     20             500                     First Federal
U-O-Us Insurance Company  125            2500                    Second Mutual

9 rows selected.
</FONT></PRE>
<P>You could convert this process into a view using the following statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW BILLS_DUE (NAME, AMOUNT, ACCT_BALANCE, BANK) AS</B>
  2  <B> SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE,</B>
  3  <B> BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS</B>
  4  <B> WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;</B>

View created.
</FONT></PRE>
<P>If you queried the <TT>BILLS_DUE</TT> view using some condition, the statement
would look like this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS_DUE</B>
  2   <B>WHERE ACCT_BALANCE &gt; 500;</B>

NAME                         AMOUNT     ACCT_BALANCE     BANK
Record Club                  25         1200             First Investor's
Joe's Car Palace             350        2500             Second Mutual
S.C. Student Loan            200        4500             Fidelity
U-O-Us Insurance Company     125        2500             Second Mutual

4 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>SQL uses several steps to process the preceding statement. Because <TT>BILLS_DUE</TT>
is a view, not an actual table, SQL first looks for a table named <TT>BILLS_DUE</TT>
and finds nothing. The SQL processor will probably (depending on what database system
you are using) find out from a system table that <TT>BILLS_DUE</TT> is a view. It
will then use the view's plan to construct the following query:</P>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT BILLS.NAME, BILLS.AMOUNT, BANK_ACCOUNTS.BALANCE,</B>
  2   <B>BANK_ACCOUNTS.BANK FROM BILLS, BANK_ACCOUNTS</B>
  3   <B>WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID</B>
  4   <B>AND BANK_ACCOUNTS.BALANCE &gt; 500;</B>
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 10.1</FONT></H4>
<P>Construct a view that shows all states to which the bills are being sent. Also
display the total amount of money and the total number of bills being sent to each
state.</P>
<P>First of all, you know that the <TT>CREATE VIEW</TT> part of the statement will
look like this:</P>
<PRE><FONT COLOR="#0066FF">CREATE VIEW EXAMPLE (STATE, TOTAL_BILLS, TOTAL_AMOUNT) AS...
</FONT></PRE>
<P>Now you must determine what the <TT>SELECT</TT> query will look like. You know
that you want to select the <TT>STATE</TT> field first using <TT>the SELECT DISTINCT</TT>
syntax based on the requirement to show the states to which bills are being sent.
For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT STATE FROM COMPANY;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">STATE
GA
FL
CA
TX
SC
NJ

6 rows selected.
</FONT></PRE>
<P>In addition to selecting the <TT>STATE</TT> field, you need to total the number
of payments sent to that <TT>STATE</TT>. Therefore, you need to join the <TT>BILLS</TT>
table and the <TT>COMPANY</TT> table.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.*) FROM BILLS, COMPANY</B>
  2  <B> GROUP BY COMPANY.STATE</B>
  3   <B>HAVING BILLS.NAME = COMPANY.NAME;</B>

STATE     COUNT(BILLS.*)
GA        2
FL        3
CA        2
TX        1
SC        1
NJ        1

6 rows selected.
</FONT></PRE>
<P>Now that you have successfully returned two-thirds of the desired result, you
can add the final required return value. Use the <TT>SUM</TT> function to total the
amount of money sent to each state.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.NAME), SUM(BILLS.AMOUNT)</B>
  2   <B>FROM BILLS, COMPANY</B>
  3   <B>GROUP BY COMPANY.STATE</B>
  4   <B>HAVING BILLS.NAME = COMPANY.NAME;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">STATE     COUNT(BILLS.*)     SUM(BILLS.AMOUNT)
GA        2                  250
FL        3                  445
CA        2                  275
TX        1                  35
SC        1                  200
NJ        1                  35

6 rows selected.
</FONT></PRE>
<P>As the final step, you can combine this <TT>SELECT</TT> statement with the <TT>CREATE
VIEW</TT> statement you created at the beginning of this project:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW EXAMPLE (STATE, TOTAL_BILLS, TOTAL_AMOUNT) AS</B>
  2   <B>SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.NAME),SUM(BILLS.AMOUNT)</B>
  3   <B>FROM BILLS, COMPANY</B>
  4   <B>GROUP BY COMPANY.STATE</B>
  5   <B>HAVING BILLS.NAME = COMPANY.NAME;</B>

View created.
</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM EXAMPLE;</B>

STATE     TOTAL_BILLS     TOTAL_AMOUNT
GA        2               250
FL        3               445
CA        2               275
TX        1               35