Chapter 18 – View Functions
"Be the change that you want to see in the world."
- Mahatma Gandhi
Creating a Simple View to Restrict Sensitive Columns
CREATE View Employee_V AS
SELECTEmployee_No
,First_Name
,Last_Name
,Dept_No
FROM Employee_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 the column salary.
Creating a Simple View to Restrict Rows
CREATE VIEW Employee_View
AS
SELECT First_Name
,Last_Name
,Dept_No
,Salary
FROM Employee_Table
WHERE Dept_No IN (300, 400) ;
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.
Creating a View to Join Tables Together
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. The view exists now in the database sql_views and accesses the tables in sql_class.
You Select From a View
Once the view is created, then users can query them with a SELECT statement. Above, we have queried the view we created to join the employee_table to the department_table (created on previous page). Users can select all columns with an asterisk, or they can choose individual columns (separated by a comma). Above, we selected all columns from the view.
Basic Rules for Views
1. All Aggregation needs to have an ALIAS
2. Any Derived columns (such as Math) needs an ALIAS
Above are the basic rules of Views with an excellent example. Matrix allows views to be created with an ORDER BY statement. In our example above, we did NOT include an ORDER BY statement to create the view. We allow the users to perform the ORDER BY when they SELECT from the view.
An ORDER BY Example Inside of a View
Matrix allows for an ORDER BY statement in the creation of a view. In the example above, notice that we have an ORDER BY statement inside the view creation. When the user selects from the view, the data comes back already sorted.
An ORDER BY Inside of a View that is Queried Differently
Matrix allows for an ORDER BY statement in the creation of a view. In the example above, notice that we have an ORDER BY statement inside the view creation. In our second example where the user selects from the view, they also put in a different Order By statement. The data comes back sorted by class_code. system.
Creating a View with Ordered Analytics
This view is used to create a Cumulative Sum, Moving Sum, Moving Average, and Moving Difference on the sales_table. Users will now be able to query this view with a simple SELECT statement. Views are designed to take the complexity out of querying for the majority of the user community. We are allowed to have an ORDER BY statement in the above creation of the view only because the ORDER BY statement is part of the ordered analytic.
Creating a View with the TOP Command
This view is used to find the top 3 salaried employees in the employee_table. Notice that the view creation has an ORDER BY statement. This is another exception to the rule that you can’t have an ORDER BY statement in a view creation. The reason is that the TOP command goes with ORDER BY like bread goes with butter. This view actually selects all the data from the employee_table. Then, the system sorts the data with the ORDER BY statement so that the rows show the largest to the smallest salaries. Then, only the top 3 salaried employees are selected.
Creating a View with the LIMIT Command
This view is used to find the top 3 students with the highest grade points. Notice that the view creation has an ORDER BY statement. This is another exception to the rule that you can’t have an ORDER BY statement in a view creation. The reason is that the LIMIT command goes with ORDER BY like bread goes with butter. This view actually selects all the data from the student_table. Then, the system sorts the data with the ORDER BY statement so that the rows show the highest to the lowest Grade_Pt' s. Then, only the top 3 students are selected.
Altering a Table
CREATE VIEW Emp_HR_v AS
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table ;
Altering the actual 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 ;
Altering the actual Table
This view runs after the table has added an additional column, but it won’t include Mgr_No in the view results even though 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 ;
Altering the actual Table
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 ;
Altering the actual Table
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;
Will the View still run?
SELECT *
FROM Employee_Table8
WHERE Employee_No = 2000000;
You can UPDATE a table through a View if you have the RIGHTS to do so.