Views are created using a special form of Data Definition Language (DDL). The CREATE requests a new VIEW, provides the name and the SELECT for the view. It is suggested that the name of the view either start with "v_" or end with "_v" to identify it as a view name (check your site standards). That way, it is visibly obvious to people that this is a view and not a table. The name of the view must be unique from the names of other objects in the database. The CREATE VIEW verifies that the name does not already exist and return an error if it does.
The rest of the statement is the SELECT statement required to return the desired columns and rows. The syntax for creating a view follows:
A view, if created with "SELECT * FROM <table-name>" reflects the column definitions at the time the CREATE VIEW is executed. If the data table is altered afterwards, it will not be seen when using the view definition. Therefore, using this technique is discouraged.
The Employee table is used to demonstrate the use of views:
The following creates a view to return the employees (above table) in department 200. It limits the view to an employee's number, last name, and salary. This view restricts both columns (i.e. first_name) and rows not for department number 200:
The next SELECT can be used to return all the columns and rows from this VIEW:SELECT * FROM empl_200_v ;
This above view contains alias names for all the columns using the AS to define the alias in the SELECT. The view was created using them and they become the column names of the view. However, if the original SELECT does not contain alias names and they are needed in the view, alias names may be assigned as part of the CREATE VIEW.
The following creates the same view as above, however, it establishes the alias using this other technique:
Using this second technique, if one alias is established in the parentheses, all columns must be represented with an alias column name. The order of the name needs to respectively match the sequence of the columns in the SELECT list. If the SELECT contains an alias, this technique over-rides it using the name in parentheses.
It is a common practice to use views for the purpose of accomplishing a join. The lengthy and sometimes complicated join code and conditions are made easier and automatic when they are stored in a view. By simply selecting from the view name, the join is automatically performed and the appropriate columns and rows are returned. A view does not store the data separately; only the SELECT is stored.
These two tables are used in the following examples:
The following view performs the join as part of its processing whenever a user does a SELECT from the view:5 Rows Returned
Notice that all alias names and formatting defined in the view become the default for the virtual table. Now that there is a view, it can be involved in another join as a table. As a result, there may be a 3-table join executed as two 2-table joins using the view. Virtually, a view is a table.
Another common use for views is to summarize data. Instead of creating an actual table and storing the data twice, many times Teradata is powerful enough to do all the aggregation within a view.
The following creates a view to perform the aggregation of all orders placed by every customer for each month:
The view can then be used to aggregate the columns that are created as a result of an aggregate, as seen next:SELECT SUM(Order_Sum) FROM Aggreg_Order_v ; 1 Row Returned
Notice that the view contains a WHERE and so does the SELECT from the view. All conditions within the view and the user SELECT must be satisfied for the rows to be returned. Together, the conditions are compared using the AND logical operation. This can be seen using an EXPLAIN on the SELECT from the view. Also seen in the EXPLAIN is the use of actual table names, never the view name. Therefore, there is no additional processing overhead compared to selecting the rows directly out of the table. At the same time, this allows companies the option of additional security by not revealing to users if the rows are retrieved directly from a table or through a view.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.