ch10.htm

21精通SQL

SC        1               200
NJ        1               35

6 rows selected.
</FONT></PRE>
<P>The preceding example shows you how to plan the <TT>CREATE VIEW</TT> statement
and the <TT>SELECT</TT> statements. This code tests the <TT>SELECT</TT> statements
to see whether they will generate the proper results and then combines the statements
to create the view.
<H4><FONT COLOR="#000077">Example 10.2</FONT></H4>
<P>Assume that your creditors charge a 10 percent service charge for all late payments,
and unfortunately you are late on everything this month. You want to see this late
charge along with the type of accounts the payments are coming from.</P>
<P>This join is straightforward. (You don't need to use anything like <TT>COUNT</TT>
or <TT>SUM.</TT>) However, you will discover one of the primary benefits of using
views. You can add the 10 percent service charge and present it as a field within
the view. From that point on, you can select records from the view and already have
the total amount calculated for you. The statement would look like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW LATE_PAYMENT (NAME, NEW_TOTAL, ACCOUNT_TYPE) AS</B>
  2  <B>SELECT BILLS.NAME, BILLS.AMOUNT * 1.10, BANK_ACCOUNTS.TYPE</B>
  3  <B>FROM BILLS, BANK_ACCOUNTS</B>
  4  <B>WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">View created.</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM LATE_PAYMENT;</B>

NAME                         NEW_TOTAL     ACCOUNT_TYPE
Phone Company                137.50        Checking
Power Company                82.50         Checking
Record Club                  27.50         Money Market
Software Company             275           Checking
Cable TV Company             38.50         Checking
Joe's Car Palace             385           Checking
S.C. Student Loan            220           Business
Florida Water Company        22            Checking
U-O-Us Insurance Company     137.50        Business
Debtor's Credit Card         38.50         Savings

10 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">Restrictions on Using SELECT</FONT></H3>
<P>SQL places certain restrictions on using the <TT>SELECT</TT> statement to formulate
a view. The following two rules apply when using the <TT>SELECT</TT> statement:

<UL>
	<LI>You cannot use the <TT>UNION</TT> operator.
</UL>


<UL>
	<LI>You cannot use the <TT>ORDER BY</TT> clause. However, you can use the <TT>GROUP
	BY</TT> clause in a view to perform the same functions as the <TT>ORDER BY</TT> clause.
</UL>

<H3><FONT COLOR="#000077">Modifying Data in a View</FONT></H3>
<P>As you have learned, by creating a view on one or more physical tables within
a database, you can create a virtual table for use throughout an SQL script or a
database application. After the view has been created using the <TT>CREATE VIEW...SELECT</TT>
statement, you can update, insert, or delete view data using the <TT>UPDATE</TT>,
<TT>INSERT</TT>, and <TT>DELETE</TT> commands you learned about on Day 8, &quot;Manipulating
Data.&quot;</P>
<P>We discuss the limitations on modifying a view's data in greater detail later.
The next group of examples illustrates how to manipulate data that is in a view.</P>
<P>To continue on the work you did in Example 10.2, update the <TT>BILLS</TT> table
to reflect that unfortunate 10 percent late charge.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW LATE_PAYMENT AS</B>
  2  <B>SELECT * FROM BILLS;</B>

View created.

SQL&gt; <B>UPDATE LATE_PAYMENT</B>
  2  <B>SET AMOUNT = AMOUNT * 1.10;
</B>
1 row updated.

SQL&gt;  <B>SELECT * FROM LATE_PAYMENT;</B>

NAME                         NEW_TOTAL     ACCOUNT_ID
Phone Company                137.50        1
Power Company                82.50         1
Record Club                  27.50         2
Software Company             275           1
Cable TV Company             38.50         3
Joe's Car Palace             385           5
S.C. Student Loan            220           6
Florida Water Company        22            1
U-O-Us Insurance Company     137.50        5
Debtor's Credit Card         38.50         4

10 rows selected.
</FONT></PRE>
<P>To verify that the <TT>UPDATE</TT> actually updated the underlying table, <TT>BILLS</TT>,
query the <TT>BILLS</TT> table:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM BILLS;</B>

NAME                         NEW_TOTAL     ACCOUNT_ID
Phone Company                137.50        1
Power Company                82.50         1
Record Club                  27.50         2
Software Company             275           1
Cable TV Company             38.50         3
Joe's Car Palace             385           5
S.C. Student Loan            220           6
Florida Water Company        22            1
U-O-Us Insurance Company     137.50        5
Debtor's Credit Card         38.50         4

10 rows selected.
</FONT></PRE>
<P>Now delete a row from the view:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>DELETE FROM LATE_PAYMENT</B>
  2  <B> WHERE ACCOUNT_ID = 4;</B>

1 row deleted.

SQL&gt; <B>SELECT * FROM LATE_PAYMENT;</B>

NAME                         NEW_TOTAL     ACCOUNT_ID
Phone Company                137.50        1
Power Company                82.50         1
Record Club                  27.50         2
Software Company             275           1
Cable TV Company             38.50         3
Joe's Car Palace             385           5
S.C. Student Loan            220           6
Florida Water Company        22            1
U-O-Us Insurance Company     137.50        5

9 rows selected.
</FONT></PRE>
<P>The final step is to test the <TT>UPDATE</TT> function. For all bills that have
a <TT>NEW_TOTAL</TT> greater than <TT>100</TT>, add an additional <TT>10</TT>.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>UPDATE LATE_PAYMENT</B>
  2  <B>SET NEW_TOTAL = NEW_TOTAL + 10</B>
  3  <B>WHERE NEW_TOTAL &gt; 100;</B>

9 rows updated.

SQL&gt; <B>SELECT * FROM LATE_PAYMENT;</B>

NAME                         NEW_TOTAL     ACCOUNT_ID
Phone Company                147.50        1
Power Company                82.50         1
Record Club                  27.50         2
Software Company             285           1
Cable TV Company             38.50         3
Joe's Car Palace             395           5
S.C. Student Loan            230           6
Florida Water Company        22            1
U-O-Us Insurance Company     147.50        5

9 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">Problems with Modifying Data Using Views</FONT></H3>
<P>Because what you see through a view can be some set of a group of tables, modifying
the data in the underlying tables is not always as straightforward as the previous
examples. Following is a list of the most common restrictions you will encounter
while working with views:

<UL>
	<LI>You cannot use <TT>DELETE</TT> statements on multiple table views.
	<P>
	<LI>You cannot use the <TT>INSERT</TT> statement unless all <TT>NOT NULL</TT> columns
	used in the underlying table are included in the view. This restriction applies because
	the SQL processor does not know which values to insert into the <TT>NOT NULL</TT>
	columns.
	<P>
	<LI>If you do insert or update records through a join view, all records that are
	updated must belong to the same physical table.
	<P>
	<LI>If you use the <TT>DISTINCT</TT> clause to create a view, you cannot update or
	insert records within that view.
	<P>
	<LI>You cannot update a virtual column (a column that is the result of an expression
	or function).
</UL>

<H3><FONT COLOR="#000077">Common Applications of Views</FONT></H3>
<P>Here are a few of the tasks that views can perform:

<UL>
	<LI>Providing user security functions
	<P>
	<LI>Converting between units
	<P>
	<LI>Creating a new virtual table format
	<P>
	<LI>Simplifying the construction of complex queries
</UL>

<H3><FONT COLOR="#000077">Views and Security</FONT></H3>
<P>Although a complete discussion of database security appears in Day 12, &quot;Database
Security,&quot; we briefly touch on the topic now to explain how you can use views
in performing security functions.</P>
<P>All relational database systems in use today include a full suite of built-in
security features. Users of the database system are generally divided into groups
based on their use of the database. Common group types are database administrators,
database developers, data entry personnel, and public users. These groups of users
have varying degrees of privileges when using the database. The database administrator
will probably have complete control of the system, including <TT>UPDATE</TT>, <TT>INSERT</TT>,
<TT>DELETE</TT>, and <TT>ALTER</TT> database privileges. The public group may be
granted only <TT>SELECT</TT> privileges--and perhaps may be allowed to <TT>SELECT</TT>
only from certain tables within certain databases.</P>
<P>Views are commonly used in this situation to control the information that the
database user has access to. For instance, if you wanted users to have access only
to the <TT>NAME</TT> field of the <TT>BILLS</TT> table, you could simply create a
view called <TT>BILLS_NAME</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>CREATE VIEW BILLS_NAME AS</B>
  2   SELECT NAME FROM BILLS;

View created.
</FONT></PRE>
<P>Someone with system administrator-level privileges could grant the public group
<TT>SELECT</TT> privileges on the <TT>BILLS_NAME</TT> view. This group would not
have any privileges on the underlying <TT>BILLS</TT> table. As you might guess, SQL
has provided data security statements for your use also. Keep in mind that views
are very useful for implementing database security.
<H3><FONT COLOR="#000077">Using Views to Convert Units</FONT></H3>
<P>Views are also useful in situations in which you need to present the user with
data that is different from the data that actually exists within the database. For
instance, if the <TT>AMOUNT</TT> field is actually stored in U.S. dollars and you
don't want Canadian users to have to continually do mental calculations to see the
<TT>AMOUNT</TT> total in Canadian dollars, you could create a simple view called
<TT>CANADIAN_BILLS</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;  <B>CREATE VIEW CANADIAN_BILLS (NAME, CAN_AMOUNT) AS</B>
  2   <B>SELECT NAME, AMOUNT / 1.10</B>
  3   <B>FROM BILLS;</B>

View Created.

SQL&gt; <B>SELECT * FROM CANADIAN_BILLS;</B>

NAME                         CAN_AMOUNT
Phone Company                125
Power Company                75
Record Club                  25
Software Company             250
Cable TV Company             35
Joe's Car Palace             350
S.C. Student Loan            200
Florida Water Company        20
U-O-Us Insurance Company     125

9 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>When converting units like this, keep in mind the possible problems inherent in
modifying the underlying data in a table when a calculation (such as the preceding
example) was used to create one of the columns of the view. As always, you should
consult your database system's documentation to determine exactly how the system
implements the <TT>CREATE VIEW</TT> command.
<H3><FONT COLOR="#000077">Simplifying Complex Queries Using Views</FONT></H3>
<P>Views are also useful in situations that require you to perform a sequence of
queries to arrive at a result. The following example illustrates the use of a view
in this situation.</P>
<P>To give the name of all banks that sent bills to the state of Texas with an amount
less than <TT>$50</TT>, you would break the problem into two separate problems:

<UL>
	<LI>Retrieve all bills that were sent to Texas
</UL>


<UL>
	<LI>Retrieve all bills less than <TT>$50</TT>
</UL>

<P>Let's solve this problem using two separate views: <TT>BILLS_1</TT> and <TT>BILLS_2</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE TABLE BILLS1 AS</B>
  2  <B>SELECT * FROM BILLS</B>
  3  <B>WHERE AMOUNT &lt; 50;</B>

Table created.

SQL&gt; <B>CREATE TABLE BILLS2 (NAME, AMOUNT, ACCOUNT_ID) AS</B>
  2  <B>SELECT BILLS.* FROM BILLS, COMPANY</B>
  3  <B>WHERE BILLS.NAME = COMPANY.NAME AND COMPANY.STATE = &quot;TX&quot;;</B>

Table created.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Because you want to find all bills sent to Texas and all bills that were less
than <TT>$50</TT>, you can now use the SQL <TT>IN</TT> clause to find which bills
in <TT>BILLS1</TT> were sent to Texas. Use this information to create a new view
called <TT>BILLS3</TT>:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> CREATE VIEW BILLS3 AS</B>
  2  <B>SELECT * FROM BILLS2 WHERE NAME IN</B>
  3 <B> (SELECT * FROM BILLS1);</B>

View created.