"Be the change that you want to see in the world."
- Mahatma Gandhi
View Fundamentals
A view is a virtual table.
A view may define a subset of columns
A view can even define a subset of rows if it has a WHERE clause
A view never duplicates data or stores the data separately
Views provide security
View Advantages
An additional level of security is provided.
Helps the business user not miss join conditions.
Help control read and update privileges.
Unaffected when new columns are added to a table.
Unaffected when a column is dropped unless its referenced in the view.
The above is designed to introduce View fundamentals, View advantages and View recommendations.
The purposes of views is to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used). This view does not allow the user to see the column salary.
CREATE View Employee_V AS
SELECTEmployee_No
,First_Name
,Last_Name
,Dept_No
FROM Employee_Table ;
SELECT *
FROM Employee_V ;
You SELECT from a view exactly like you SELECT from a table. A user might not know whether or not they were querying a view or a table.
The purposes of views is to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used). This view does not allow the user to see information about rows unless the rows have a Dept_No of either 300 or 400.
CREATE VIEW Employee_View
AS
SELECT First_Name
,Last_Name
,Dept_No
,Salary
FROMEmployee_Table
WHEREDept_No IN (300, 400) ;
A view is usually associated with protecting sensitive columns, but view that utilize a WHERE clause also protect sensitive rows from being seen. Also, notice that we did not put an ORDER BY statement in the view creation, but instead you utilize the ORDER BY statement when you query the view.
No ORDER BY inside the View CREATE (exceptions exist)
All Aggregation needs to have an ALIAS
Any Derived columns (such as Math) needs an ALIAS
Above are the basic rules of Views with excellent examples.
The CREATE OR REPLACE Keywords will allow a user to change a view.
There are EXCEPTIONS to the ORDER BY rule. ANSI OLAP statements that use the ORDER BY statement also work inside a View.
CREATE VIEW OrderV
AS
SELECT
Order_Number
,Customer_Number
, TO_CHAR(Order_Date , 'MON, DD, YYYY') AS Order_Date
,TO_CHAR(Order_Total, 'L999,999.99') AS Total
FROM Order_Table ;
SELECT *
FROM OrderV ;
Views are designed to do many things. In the example above, this view formats some of the data.
This view is designed to join two tables together. By creating a view, we have now made it easier for the user community to join these tables by merely selecting the columns you want from the view or even the rows.
SELECT *
FROM E_View
ORDER BY Mnth_Sal ;
Will this View CREATE work or will it error? It works fine because it’s aliased above!
SELECT *
FROM Emp_v2
ORDER BY Sal_Monthly ;
The ALIAS for Salary / 12 that’ll be used in this example is Sal_Monthly and this form of aliasing is most often used.
SELECT *
FROM Emp_v3
ORDER BY 3 ;
The ALIAS for Salary / 12 that’ll be used in this example is Mnth_Sal. It came first at the top, even though it is aliased in the SELECT list also.
What happens when this query runs?
What will happen in the above query?
What happens when this query runs?
Error – Sal_Mnth is unrecognized
The query above errors because Sal_Mnth is an unrecognized alias. That is because we did our aliasing at the top so this makes the alias right after Salary/12 non-valid for use when querying the view.
CREATE VIEW Aggreg_Order_v AS
SELECT
Customer_Number
,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 ;
The examples above show how we put a SUM on the aggregate Order_Sum!
CREATE VIEW Emp_HR_v AS
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table3 ;
Altering the actual Table
This view will run after the table has added an additional column!
CREATE VIEW Emp_HR_v5 AS
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table5 ;
Altering the actual Table
ALTER TABLE Employee_Table5
Drop Dept_No;
ERROR [2BV01] ROLLBACK 3128: DROP failed due to
dependencies DETAIL: Projection
sql_class.Employee_Table5_super has column Dept_No as part of its segmentation expression HINT: Use DROP .. CASCADE to drop or modify the dependent objects ALTER TABLE Command Failed.
This table will error if you try to drop a column that exists in a view.