<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 anin-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 tablebut 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 justlike a table:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM DEBTS;</B>NAME                     AMOUNT  ACCOUNT_IDPhone Company            125     1Power Company             75     1Record Club               25     2Software Company         250     1Cable TV Company          35     3Joe's Car Palace         350     5S.C. Student Loan        200     6Florida Water Company     20     1U-O-Us Insurance Company 125     5Debtor's Credit Card      35     410 rows selected.</FONT></PRE><P>You can even create new views from existing views. Be careful when creating viewsof views. Although this practice is acceptable, it complicates maintenance. Supposeyou have a view three levels down from a table, such as a view of a view of a viewof 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 partof 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_IDDebtor's Credit Card       35    41 row selected.</FONT></PRE><P>The <TT>CREATE VIEW</TT> also enables you to select individual columns from atable 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                      STATEPhone Company                GAPower Company                FLRecord Club                  CASoftware Company             CACable TV Company             TXJoe's Car Palace             FLS.C. Student Loan            SCFlorida Water Company        FLU-O-Us Insurance Company     GADebtor's Credit Card         NJ10 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, theSQL syntax for the <TT>CREATE VIEW</TT> statement enables you to rename selectedcolumns. Consider the preceding example a little more closely. What if you wantedto 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 illustratesthis. This example uses the SQL <TT>+</TT> operator to combine the address fieldsinto 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_ADDRESSPhone Company                111 1st Street Atlanta, GAPower Company                222 2nd Street Jacksonville, FLRecord Club                  333 3rd Avenue Los Angeles, CASoftware Company             444 4th Drive San Francisco, CACable TV Company             555 5th Drive Austin, TXJoe's Car Palace             1000 Govt. Blvd Miami, FLS.C. Student Loan            25 College Blvd. Columbia, SCFlorida Water Company        1883 Hwy. 87 Navarre, FLU-O-Us Insurance Company     295 Beltline Hwy. Macon, GADebtor's Credit Card         115 2nd Avenue Newark, NJ10 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>The SQL syntax requires you to supply a virtual field name whenever the view'svirtual field is created using a calculation or SQL function. This pro- cedure makessense 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 whatactually exists in the database's table structure. Views can also be extremely convenientwhen performing several complex queries in a series (such as within a stored procedureor 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 againsta view. Suppose you have a query that occurs often, for example, you routinely jointhe <TT>BILLS</TT> table with the <TT>BANK_ACCOUNTS</TT> table to retrieve informationon 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 FederalPower Company	           75             500                     First FederalRecord Club	           25             1200                    First Investor'sSoftware Company	      250            500                     First  FederalCable TV Company	      35             90                      Credit UnionJoe's Car Palace	      350            2500                    Second MutualS.C. Student Loan	      200            4500                    FidelityFlorida Water Company     20             500                     First FederalU-O-Us Insurance Company  125            2500                    Second Mutual9 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 statementwould 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     BANKRecord Club                  25         1200             First Investor'sJoe's Car Palace             350        2500             Second MutualS.C. Student Loan            200        4500             FidelityU-O-Us Insurance Company     125        2500             Second Mutual4 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 systemyou are using) find out from a system table that <TT>BILLS_DUE</TT> is a view. Itwill 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. Alsodisplay the total amount of money and the total number of bills being sent to eachstate.</P><P>First of all, you know that the <TT>CREATE VIEW</TT> part of the statement willlook 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 knowthat 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">STATEGAFLCATXSCNJ6 rows selected.</FONT></PRE><P>In addition to selecting the <TT>STATE</TT> field, you need to total the numberof 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        2FL        3CA        2TX        1SC        1NJ        16 rows selected.</FONT></PRE><P>Now that you have successfully returned two-thirds of the desired result, youcan add the final required return value. Use the <TT>SUM</TT> function to total theamount 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                  250FL        3                  445CA        2                  275TX        1                  35SC        1                  200NJ        1                  356 rows selected.</FONT></PRE><P>As the final step, you can combine this <TT>SELECT</TT> statement with the <TT>CREATEVIEW</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_AMOUNTGA        2               250FL        3               445CA        2               275TX        1               35SC        1               200NJ        1               356 rows selected.</FONT></PRE><P>The preceding example shows you how to plan the <TT>CREATE VIEW</TT> statementand the <TT>SELECT</TT> statements. This code tests the <TT>SELECT</TT> statementsto see whether they will generate the proper results and then combines the statementsto 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 latecharge 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 usingviews. You can add the 10 percent service charge and present it as a field within