| Team Fly | |
rows and columns from this view. The second example selects only the name and total columns for customers whose sales are greater than 20,000. Keep in mind, this is still only for Utah customers.
SQL> select *
2 from utah_sales;
ID NAME CITY STATE TOTAL
---------- -------------------- -------------------- ----- ----------
118 Kuehler Farmington UT 23258.4
392 Eubank Farmington UT 21297.49
411 Vankirk Farmington UT 19279.94
462 Nielley Farmington UT 64509.91
599 Robbinette Farmington UT 11167.65
7003 Bane Farmington UT 62605.42
100207 Campbell Farmington UT 11.99
100267 Desai Farmington UT 240.95
100308 Wilbur Farmington UT 190.96
9 rows selected.
SQL> select name, total
2 from utah_sales
3 where total > 20000;
NAME TOTAL
-------------------- ----------
Kuehler 23258.4
Eubank 21297.49
Nielley 64509.91
Bane 62605.42
It's easy to see how we could keep certain users in our company from accessing sales information from more than the states they are granted access to. If this sample database had sale representatives with assigned territories, one could imagine how the use of territory-based views could keep one salesperson from viewing the sales and commissions of another territory representative.
We have demonstrated here that views contain no data. All the data for our view example in this section resides in the underlying tables. In Chapter 9, we will introduce you to materialized views, which is a physical implementation of a view.
Quite often, primary keys in tables are simply generated numeric values that are sequential. In the sample database that we've used throughout this chapter, cust_id and prod_id in the CUSTOMERS and PRODUCTS tables are likely candidates for creation using a sequence.
| Team Fly | |