2, update the BILLS table to reflect that unfortunate 10 percent late charge.
INPUT/OUTPUT:
SQL> CREATE VIEW LATE_PAYMENT AS
2 SELECT * FROM BILLS;
View created.
SQL> UPDATE LATE_PAYMENT
2 SET AMOUNT = AMOUNT * 1.10;
1 row updated.
SQL> SELECT * FROM LATE_PAYMENT;
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.
To verify that the UPDATE actually updated the underlying table, BILLS, query the BILLS table:
INPUT/OUTPUT:
SQL> SELECT * FROM BILLS;
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.
Now delete a row from the view:
INPUT/OUTPUT:
SQL> DELETE FROM LATE_PAYMENT
2 WHERE ACCOUNT_ID = 4;
1 row deleted.
SQL> SELECT * FROM LATE_PAYMENT;
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.
The final step is to test the UPDATE function. For all bills that have a NEW_TOTAL greater than 100, add an additional 10.
INPUT/OUTPUT:
SQL> UPDATE LATE_PAYMENT
2 SET NEW_TOTAL = NEW_TOTAL + 10
3 WHERE NEW_TOTAL > 100;
9 rows updated.
SQL> SELECT * FROM LATE_PAYMENT;
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.
Problems with Modifying Data Using Views
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:
● You cannot use DELETE statements on multiple table views.
● You cannot use the INSERT statement unless all NOT NULL 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 NOT NULL columns.
● If you do insert or update records through a join view, all records that are
updated must belong to the same physical table.
● If you use the DISTINCT clause to create a view, you cannot update or insert records within that view.
● You cannot update a virtual column (a column that is the result of an expression
or function).
Common Applications of Views
Here are a few of the tasks that views can perform:
● Providing user security functions
● Converting between units
● Creating a new virtual table format
● Simplifying the construction of complex queries
Views and Security