This section provides techniques to specifically reference table and columns throughout all databases and to temporarily rename tables with an alias name. Both of these techniques are necessary to provide specific and unique names to the optimizer at SQL execution time.
Qualifying Column Names
Since column names within a table must be unique, the system knows which data to access simply by using the column name. However, when more that one table is referenced by the FROM in a single SELECT, this may not be the case. The potential exists for columns of the same domain to have the same name in more than one table. When this happens, the system does not guess which column to reference. The SQL must explicitly declare which table to use for accessing the column.
This declaration is called qualifying the column name. If the SQL does not qualify the column name appearing in more than one table, the system displays an error message that indicates too much ambiguity exists in the query. Correlated subqueries, addressed next, and join processing, both make use of more than one table at the same time. Therefore, many times it is important to make sure the system knows which table's columns to use for all portions of the SQL statement.
To qualify a column name, the table name and column name are connected using a period or sometimes referred to as a dot (.). The dot connects the names without a space to make the two names work as a single reference name. However, if the column has different names in the multiple tables, there is no confusion within the system and therefore, no need to qualify the name.
To illustrate this concept, lets consider people instead of tables. For instance, Mike is a common name. If two Mikes are in different rooms and someone uses the name in either room, there is no confusion. However, if both Mikes are in the same room and someone uses the name, both Mikes respond and therefore confusion exists. To eliminate the conflict, the use of the first and last names makes the identification unique.
The syntax for qualification levels follow:
Whenever all 3 levels are used, the first name is always the database, the second is the table and the last is the column. However, when two names appear in a 2-level qualification, the location of the names within the SQL must be examined to know for sure their meaning. Since the FROM names the tables, the first name of the qualified names is a database name and the second is a table. Since columns are referenced in the SELECT list and WHERE clause, the first name is a table name and the second is an * (all columns) or a single column.
In Teradata, the following is a valid statement, including the abbreviation for SELECT and missing FROM:
SEL DBC.TABLES.* ;
This technique is not ANSI standard, however, the PE has everything needed to get all columns and rows out of the TABLES table in the DBC database.
Creating an Alias for a Table
Since table names can be up to 30 characters long, to save typing when the name is used more than once, a commonly used technique is to provide a temporary name for the table within the SELECT. The new temporary name for a table is called an alias name.
Once the alias is created for the table, it is important to use the alias name throughout the request. Otherwise the system looks at the use of the full table name as another table and it causes undesirable results. To establish an alias for a table, in the FROM, simply follow the name of the table with an AS: FROM <table-name> AS <table-alias-name>.
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.