the view. From that point on, you can select records from the view and already havethe 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_TYPEPhone Company                137.50        CheckingPower Company                82.50         CheckingRecord Club                  27.50         Money MarketSoftware Company             275           CheckingCable TV Company             38.50         CheckingJoe's Car Palace             385           CheckingS.C. Student Loan            220           BusinessFlorida Water Company        22            CheckingU-O-Us Insurance Company     137.50        BusinessDebtor's Credit Card         38.50         Savings10 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 formulatea 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 withina database, you can create a virtual table for use throughout an SQL script or adatabase 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;ManipulatingData.&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> tableto 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_IDPhone Company                137.50        1Power Company                82.50         1Record Club                  27.50         2Software Company             275           1Cable TV Company             38.50         3Joe's Car Palace             385           5S.C. Student Loan            220           6Florida Water Company        22            1U-O-Us Insurance Company     137.50        5Debtor's Credit Card         38.50         410 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_IDPhone Company                137.50        1Power Company                82.50         1Record Club                  27.50         2Software Company             275           1Cable TV Company             38.50         3Joe's Car Palace             385           5S.C. Student Loan            220           6Florida Water Company        22            1U-O-Us Insurance Company     137.50        5Debtor's Credit Card         38.50         410 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_IDPhone Company                137.50        1Power Company                82.50         1Record Club                  27.50         2Software Company             275           1Cable TV Company             38.50         3Joe's Car Palace             385           5S.C. Student Loan            220           6Florida Water Company        22            1U-O-Us Insurance Company     137.50        59 rows selected.</FONT></PRE><P>The final step is to test the <TT>UPDATE</TT> function. For all bills that havea <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_IDPhone Company                147.50        1Power Company                82.50         1Record Club                  27.50         2Software Company             285           1Cable TV Company             38.50         3Joe's Car Palace             395           5S.C. Student Loan            230           6Florida Water Company        22            1U-O-Us Insurance Company     147.50        59 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, modifyingthe data in the underlying tables is not always as straightforward as the previousexamples. Following is a list of the most common restrictions you will encounterwhile 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;DatabaseSecurity,&quot; we briefly touch on the topic now to explain how you can use viewsin performing security functions.</P><P>All relational database systems in use today include a full suite of built-insecurity features. Users of the database system are generally divided into groupsbased on their use of the database. Common group types are database administrators,database developers, data entry personnel, and public users. These groups of usershave varying degrees of privileges when using the database. The database administratorwill 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 begranted 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 thedatabase user has access to. For instance, if you wanted users to have access onlyto the <TT>NAME</TT> field of the <TT>BILLS</TT> table, you could simply create aview 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 nothave any privileges on the underlying <TT>BILLS</TT> table. As you might guess, SQLhas provided data security statements for your use also. Keep in mind that viewsare 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 withdata that is different from the data that actually exists within the database. Forinstance, if the <TT>AMOUNT</TT> field is actually stored in U.S. dollars and youdon'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_AMOUNTPhone Company                125Power Company                75Record Club                  25Software Company             250Cable TV Company             35Joe's Car Palace             350S.C. Student Loan            200Florida Water Company        20U-O-Us Insurance Company     1259 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>When converting units like this, keep in mind the possible problems inherent inmodifying the underlying data in a table when a calculation (such as the precedingexample) was used to create one of the columns of the view. As always, you shouldconsult your database system's documentation to determine exactly how the systemimplements 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 ofqueries to arrive at a result. The following example illustrates the use of a viewin this situation.</P><P>To give the name of all banks that sent bills to the state of Texas with an amountless 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 lessthan <TT>$50</TT>, you can now use the SQL <TT>IN</TT> clause to find which billsin <TT>BILLS1</TT> were sent to Texas. Use this information to create a new viewcalled <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.</FONT></PRE><P>Now combine the preceding query with the <TT>BANK_ACCOUNTS</TT> table to satisfythe original requirements of this example:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>CREATE VIEW BANKS_IN_TEXAS (BANK) AS</B>  2  <B>SELECT BANK_ACCOUNTS.BANK</B>  3  <B>FROM BANK_ACCOUNTS, BILLS3</B>  4  <B>WHERE BILLS3.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;</B>View created.SQL&gt; <B>SELECT * FROM BANK_IN_TEXAS;</B>BANKCredit Union1 row selected.</FONT></PRE><P><H5><FONT COLOR="#0066FF"></FONT></H5><H5>ANALYSIS:</H5><P>As you can see, after the queries were broken down into separate views, the finalquery was rather simple. Also, you can reuse the individual views as often as necessary.<H3><FONT COLOR="#000077">The DROP VIEW Statement</FONT></H3>