Chapter 9 - View Functions
“It is easier to go down a hill than up it, but the view is much better at the top.”
Creating a Simple View
CREATE View Employee_V AS
| SELECT | Employee_No |
| ,First_Name | |
| ,Last_Name | |
| ,Dept_No |
FROM Employee_Table;
The purposes of views are to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used).
Basic Rules for Views
- No ORDER BY inside the View CREATE (some exceptions exist)
- All Aggregation needs to have an ALIAS
- Any Derived columns (such as Math) needs an ALIAS
CREATE View Department_Salaries AS
SELECT Dept_No
,SUM(Salary) as SumSal
,SUM(Salary) / 12 as MonthSal
FROM Employee_Table
GROUP BY 1;
SEL *
FROM Department_Salaries
Order By 1 ;
| Dept_No | SumSal | MonthSal |
| ? | 32800.50 | 2733.38 |
| 10 | 64300.00 | 5358.33 |
| 100 | 48850.00 | 4070.83 |
| 200 | 89888.88 | 7490.74 |
| 300 | 40200.00 | 3350.00 |
| 400 | 145000.00 | 12083.33 |
Above are the basic rules of Views with excellent examples.
How to Modify a View
/* CREATE the View */
CREATE View Employee_V2 AS
SELECT Employee_No
,First_Name
,Last_Name
,Dept_No
,Salary
FROM Employee_Table ;
The REPLACE Keyword will allow a user to change a view.
Exceptions to the ORDER BY Rule inside a View
There are EXCEPTIONS to the ORDER BY rule. The TOP command allows a view to work with an ORDER BY inside. ANSI OLAP statements also work inside a View.
How to Get HELP with a View
HELP View Command
HELP View Emp_View ;
The Help View command does little but show you the columns.
Views sometimes CREATED for Formatting or Row Security
CREATE VIEW empl_200_v AS
SELECT Employee_No AS Emp_No
,Last_Name AS Last
,salary/12 (format '$$$$,$$9.99') AS Monthly_Salary
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY Monthly_Salary ;
| Emp_No | Last_Name | Monthly_Salary |
| 1324657 | Coffing | $3,490.74 |
| 1333454 | Smith | $4,000.00 |
Views are designed to do many things. In the example above, this view formats and derives data, limits columns, and also limits the rows coming back with a WHERE.
Another Way to Alias Columns in a View CREATE
SELECT *
FROM Empl_200_v
ORDER BY Monthly_Salary;
| Emp_No | Last_Name | Monthly_Salary |
| 1324657 | Coffing | $3,490.74 |
| 1333454 | Smith | $4,000.00 |
Will this View CREATE Error? No! It won't error because it's Aliased above!
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_Salary) 
AS SELECT Employee_No
,Last_Name
,Salary/12 (format '$$$$,$$9.99') as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECT *
FROM Empl_200_v
ORDER BY 3;
The ALIAS for Salary / 12 that'll be used in this example is MONTHLY_SALARY. It came first at the top even though it is aliased in the SELECT list also.
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_Salary)
AS SELECT Employee_No
,Last_Name
,Salary/12 (format '$$$$,$$9.99') as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200;
SELECT *
FROM Empl_200_v
ORDER BY Sal_Monthly;
What will happen in the above query?
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_Salary)
As SELECT Employee_No
,Last_Name
,salary/12 (format '$$$$,$$9.99') as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
ERROR
If you ALIAS at the top, then that is the only ALIAS that the query can recognize. So, it is a good idea to alias at the top or the bottom but not do both.
CREATING Views for Complex SQL such as Joins
CREATE VIEW Customer_Order_v AS
| SELECT | Customer_Name AS Customer |
| ,Order_Number | |
| ,Order_Total (FORMAT '$$$,$$9.99' ) AS Total_Amount | |
| FROM | Customer_Table AS Cust |
| ,Order_Table AS Ord | |
| WHERE | Cust.Customer_Number = Ord.Customer_Number ; |
SELECT * FROM Customer_Order_v
ORDER BY 1 ;
| Customer | Order_Number | Total_Amount |
| Ace Consulting | 123552 | $5,111.47 |
| Billy's Best Choice | 123456 | $12,346.53 |
| Billy's Best Choice | 123512 | $8,005.91 |
| Databases N-U | 123585 | $15,231.62 |
| XYZ Plumbing | 123777 | $23,454.84 |
A huge reason for Views other than security is to also make Complex SQL easy for users. This view already has the Inner Join built into it, but users just SELECT.
WHY certain columns need Aliasing in a View
CREATE VIEW Aggreg_Order_v AS
| SELECT | Customer_Number |
| ,Order_Date/100+190000 (format ‘9999-99’) AS Yr_Mth_Orders | |
| ,COUNT(Order_Total) AS Order_Cnt | |
| ,SUM(Order_Total) AS Order_Sum | |
| ,AVG(Order_Total) AS Order_Avg | |
| FROM | Order_Table |
GROUP BY Customer_Number, Yr_Mth_Orders ;
SELECT Customer_Number
,Order_Sum
FROM Aggreg_Order_v ;
| Customer_Number | Order_Sum |
| 31323134 | 5111.47 |
| 87323456 | 15231.62 |
| 11111111 | 8005.91 |
| 11111111 | 12347.53 |
| 57896883 | 23454.84 |
When you CREATE a view, you have to ALIAS any aggregation or derived data (such as math). Why? So you can SELECT it later, without having to do a SELECT *. Here, we only chose two columns and used their ALIAS to retrieve them.
Aggregates on View Aggregates
CREATE VIEW Aggreg_Order_v AS
| SELECT | Customer_Number |
| ,Order_Date/100+190000 (format ‘9999-99’) AS Yr_Mth_Orders | |
| ,COUNT(Order_Total) AS Order_Cnt | |
| ,SUM(Order_Total) AS Order_Sum | |
| ,AVG(Order_Total) AS Order_Avg | |
| FROM | Order_Table |
GROUP BY Customer_Number, Yr_Mth_Orders ;
SELECT Customer_Number
,Order_Sum
FROM Aggreg_Order_v ;
SELECT SUM (Order_Sum)
FROM Aggreg_Order_v ;
| Customer_Number | Order_Sum |
| 31323134 | 5111.47 |
| 87323456 | 15231.62 |
| 11111111 | 8005.91 |
| 11111111 | 12347.53 |
| 57896883 | 23454.84 |
SUM(Order_Sum)
64151.37
The examples above show how we put a SUM on the aggregate Order_Sum.
Locking Row for Access
SELECT * FROM Emp_HR_v;
The Employee_Table used above will automatically use an ACCESS Lock, which allows ACCESS during UPDATES or table loads.
Most views utilize the Locking row for ACCESS command. This is because they want to be able to read while a table is being updated and loaded into. If the user knows a dirty read won't have a huge effect on their job, why not make a view lock with an ACCESS Lock, thus preventing unnecessary waiting?
Altering a Table
CREATE VIEW Emp_HR_v AS
| SELECT | Employee_No |
| ,Dept_No | |
| ,Last_Name | |
| ,First_Name | |
| FROM | Employee_Table ; |
This view will run after the table has added an additional column!
Altering a Table after a View has been created
CREATE VIEW Emp_HR_v4 AS
| SELECT | * |
| FROM | Employee_Table4 ; |
This view runs after the table has added an additional column, but it won't include Mgr_No in the view results although there is a SELECT * in the view. The View includes only the columns present when the view was CREATED.
A View that errors After an ALTER
CREATE VIEW Emp_HR_v5 AS
| SELECT | Employee_No |
| ,Dept_No | |
| ,Last_Name | |
| ,First_Name | |
| FROM | Employee_Table5 ; |
This view will NOT run after the table has dropped a column referenced in the view.
Troubleshooting a View
CREATE VIEW Emp_HR_v6 AS
SELECT *
FROM Employee_Table6 ;
This view will NOT run after the table has dropped a column referenced in the view, even though the View was CREATED with a SELECT *. At View CREATE Time, the columns present were the only ones the view considered responsible for and Dept_No was one of those columns. Once Dept_No was dropped, the view no longer works.
Updating Data in a Table through a View
CREATE VIEW Emp_HR_v8 AS
SELECT *
FROM Employee_Table8 ;
--Updating the table through the View
UPDATE Emp_HR_V8
SET Salary = 88888.88
WHERE Employee_No = 2000000;
--SELECT from the actual Table
SELECT *
FROM Employee_Table8 ;
WHERE Employee_No = 2000000;
You can UPDATE a table through a View if you have the RIGHTS to do so.
Maintenance Restrictions on a Table through a View
There are a few restrictions that disallow maintenance activity on a view with an INSERT, UPDATE or DELETE request. A view cannot be used for maintenance if it:
- Performs a join operation – more than one table
- Selects the same column twice – wouldn't know which one to use
- Derives data – because it does not undo the math or calculation
- Performs aggregation – because this eliminates detail data
- Uses OLAP functions – because OLAP data is calculated
- Uses a DISTINCT or GROUP BY – eliminates duplicate rows
Perform maintenance on a table through a view, but see the restrictions above first.