Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 89 90 91 92 93 94 < 95 > 96 97 98 99 100 101 .. 241 >> Next


If a SELECT uses both alias and real names (for example, alias names in a WHERE clause and real names in the SELECT), the Teradata RDBMS does an unconstrained join.

The following examples illustrate the use of the WHERE clause:

The following statement may be used to select the name and job title of every employee in Department 100. In this statement, “DeptNo = 100" is the conditional expression.

SELECT Name, JobTitle FROM Employee WHERE DeptNo = 100;

he following statement returns only those rows from the EMployee table whose values in the EmpNo column match the values in the MgrNo column of the Department table.

Note the use of tname.* form of the SELECT list.

SELECT Employee.* FROM Employee, Department WHERE Employee.EmpNo=Departmentt.MgrNo;

If EmpNo is defined as CHAR(5), then EmpNo values should be compared with character values in the WHERE condition, as:

WHERE EmpNo = '12345'

Teradata RDBMS for UNIX SQL Reference

7-13
Queries: The SELECT Statement

Subqueries in Search Conditions

Subqueries in Search Conditions

Introduction

Subqueries can also be used in search conditions.

Syntax

- expr-

—( -l-expr-L ) —

- comparison_operator ----------IN-----------

NOT IN-

-ANY — -SOME — -ALL-

- (query )-

HH01A064

The following is the syntax for a special case of using subqueries in logical expressions:

-----EXISTS-----(query )-----

HH01A065

The following examples illustrate the use of subqueries in search

Examples conditions:

The following request can be used to select the names and Example 1 7 department locations of those employees who report to manager

Aguilar (whose EmpNo is 10007):

SELECT Name, Loc FROM Employee, Department WHERE Employee.DeptNo = Department.DeptNo AND Employee.DeptNo IN

( SELECT DeptNo FROM Department WHERE MgrNo = 10007 ) ;

The following statement finds every employee in the Employee Example 2 7 table with a salary that is greater than the average salary of all

employees in the table:

SELECT Name, DeptNo, JobTitle, Salary FROM Employee WHERE Salary > ( SELECT AVG(Salary) FROM Employee)

ORDER BY Name ;

The statement returns:

Name DeptNo JobTitle Salary
Aguilar J 600 Manager 45, . 000.00
Carter J 500 Engineer 44, . 000.00
Omura H 500 Programmer 40, . 000.00
Phan A 300 Vice Pres 55, . 000.00
Regan R 600 Purchaser 44, . 000.00
Russell S 300 President 65, . 000.00
Smith T 500 Engineer 42, . 000.00
Smith T 700 Manager 45, . 000.00
Watson L 500 Vice Pres 56, . 000.00

7-14

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

Subqueries in Search Conditions

The following statement retrieves the employee who has the highest Example 3 salary and the employee who has the most years of experience:

SELECT EmpNo,Name,JobTitle,Salary,YrsExp FROM Employee

WHERE (Salary,YrsExp) >= ALL

(SELECT Salary,YrsExp FROM Employee) ;

The result shows that a single employee has both the highest salary and the most years of experience:

EmpNo Name JobTitle Salary YrsExp

10018 Russell S President 65,000.00 25

Teradata RDBMS for UNIX SQL Reference

7-15
Queries: The SELECT Statement

FROM Clause

FROM Clause

The FROM clause of the SELECT statement lists the tables or views that are referenced by the select operation, as illustrated by the preceding SELECT examples

The FROM clause also may be used to define a temporary alias name for a table or view. An alias name must be used when a table is to be joined with itself for a self-referencing operation, or to simplify the references to columns.

If the FROM clause refers to a table name or view name that is used Usage NotGs in more than one database, you must use the fully-qualified form of

the name, thus:

databasename.tablename

If a SELECT statement is a subquery, it must contain a FROM list and every field referenced in the SELECT statement must be in a table referenced either in the FROM list for the subquery, or some FROM list in an outer query containing the subquery.

ANSI SQL also requires that all fields referenced in an outer query be in tables listed in the FROM list. Teradata RDBMS does not require this, but it is recommended.

Syntax

-FROM-

tname

Las-I

-joined_table

-INNER- LEFT — -RIGHT-lFULL-I

I—OUTER—I

¦JOIN-joined_table-ON-search_cond ¦

-CROSS JOIN—single_table --( subquery ——p derived_tname -

}

Single

Tables

-(¦—col_name -L)—

Joined

Tables

J

( Derived Tables

FF06A013

aname

7-16

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement FROM Clause
where:
Syntax Element . . . Is . . .
Single Tables
Note that a FROM list may include a sequence of “single table” references. In this case, an implicit join is created. This is distinguished from explicit joins where the keyword JOIN is part of the syntax.
tname the name of a table, view or macro. If database is omitted, it is inferred from context.
Previous << 1 .. 89 90 91 92 93 94 < 95 > 96 97 98 99 100 101 .. 241 >> Next