Teradata - Views
Views are database objects that are built by the query. Views can be built using a single table or multiple tables by way of join. Their definition is stored permanently in data dictionary but they don't store copy of the data. Data for the view is built dynamically.
A view may contain a subset of rows of the table or a subset of columns of the table.
Create a View
Views are created using CREATE VIEW statement.
Following is the syntax for creating a view.
CREATE/REPLACE VIEW <viewname> AS <select query>;
Consider the following Employee table.
The following example creates a view on Employee table.
CREATE VIEW Employee_View AS SELECT EmployeeNo, FirstName, LastName, FROM Employee;
You can use regular SELECT statement to retrieve data from Views.
The following example retrieves the records from Employee_View;
SELECT EmployeeNo, FirstName, LastName FROM Employee_View;
When the above query is executed, it produces the following output.
*** Query completed. 5 rows found. 3 columns returned. *** Total elapsed time was 1 second. EmployeeNo FirstName LastName ----------- ------------------------------ --------------------------- 101 Mike James 104 Alex Stuart 102 Robert Williams 105 Robert James 103 Peter Paul
An existing view can be modified using REPLACE VIEW statement.
Following is the syntax to modify a view.
REPLACE VIEW <viewname> AS <select query>;
The following example modifies the view Employee_View for adding additional columns.
REPLACE VIEW Employee_View AS SELECT EmployeeNo, FirstName, BirthDate, JoinedDate DepartmentNo FROM Employee;
An existing view can be dropped using DROP VIEW statement.
Following is the syntax of DROP VIEW.
DROP VIEW <viewname>;
Following is an example to drop the view Employee_View.
DROP VIEW Employee_View;
Advantages of Views
Views provide additional level of security by restricting the rows or columns of a table.
Users can be given access only to views instead of base tables.
Simplifies the use of multiple tables by pre-joining them using Views.