# Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

**Download**(direct link)

**:**

**33**> 34 35 36 37 38 39 .. 76 >> Next

Operator Definition

+ Addition

- Subtraction

* Multiplication

/ Division

MOD Modulus

** Exponentiation

() Evaluated first

The data type of the result of an arithmetic expression is a function of the data types of the two operands. Appropriate conversions are made before the operations are carried out. For example, before an INTEGER value is added to a DECIMAL(5,2) value, the INTEGER value is converted to DECIMAL(5,2), and that is the data type of the result.

Introduction to the Teradata RDBMS for UNIX

5-3

Data Manipulation

The SELECT Statement

Aggregate Operators

Comparison Operators

Aggregate operators are used to group data to define a query result. The following table lists and describes the Teradata SQL aggregate operators.

Operator Definition

AVERAGE Computes the average of a set of values.

COUNT Returns the number of members in a set of values.

MAXIMUM Returns the maximum value in a set of values.

MINIMUM Returns the minimum value in a set of values.

SUM Computes the sum of a set of values.

The set on which the aggregate operator functions can consist either of all values in a particular column or a subset of column values.

Comparison operators compare numeric or character values to produce a logical (TRUE or FALSE) result. The following table lists and describes the Teradata SQL comparison operators.

Operator Definition

= Equal

EQ

> Greater than

GT

< Less than

LT

<= Less than or equal

LE

>= Greater than or equal

GE

BETWEEN...AND Range

5-4

Introduction to the Teradata RDBMS for UNIX

Data Manipulation

The SELECT Statement

Logical Operators

Partial String Matching Operator

Logical operators combine logical expressions and generate compound conditions. They can be used in the WHERE clause of a SELECT statement. The following table lists and describes the Teradata SQL logical operators.

Operator Definition

AND Specifies that both conditions must be true for the condition to evaluate true.

OR Specifies that either (or both) conditions must be true for the condition to evaluate true.

NOT Specifies logical exclusion. The condition evaluates true if

Ë and only if the value is not true.

( ) Delimits precedence

Partial string matching operators locate character strings that match portions or complete strings of characters. The following table lists and describes the Teradata SQL partial string matching LIKE

operator.

Operator Definition

[NOT] LIKE charstring% String begins with partial string charstring, but can contain any other characters trailing charstring.

[NOT] LIKE %charstring String ends with partial string charstring, but can contain any other characters leading charstring.

[NOT] LIKE %charstring% String has partial string charstring embedded within it, but can lead or trail with any other characters.

Introduction to the Teradata RDBMS for UNIX

5-5

Data Manipulation

The SELECT Statement

Set Operators

Other Operators

Teradata RDBMS SQL set operators are used in conditional expressions. The operators test whether one or more values are within a defined set of values. You can express a set as a list of constants or as a single column table. The following table lists and describes the Teradata SQL set operators.

Operator Definition

INTERSECT Find the set of all results rows in tables A and B that belong to both A and B.

MINUS Find the set of all results rows in tables A and B that are not in both.

UNION Find the set of all results rows in tables A and B that appear in either or both A and B.

Suppose we have a parts database where table Suppliers defines suppliers, table Parts defines parts, and table SupplierParts relates the two. You want to find part numbers for parts that either weigh more than 50 pounds or are supplied by supplier Western Widgets. Use the UNION set operator in a conditional expression like one of the following:

SELECT PartNumber FROM Parts WHERE Weight > 50 UNION

SELECT PartNumber FROM SupplierParts

WHERE SupplierNumber = 'Western Widgets'

;

The result of this query is a list of all parts that weigh more than 50 pounds or are supplied by Western Widgets.

Teradata RDBMS SQL also provides a concatenation operator and string functions for working with character data.

5-6

Introduction to the Teradata RDBMS for UNIX

Arithmetic Functions

Data Manipulation

The SELECT Statement

Teradata RDBMS SQL provides arithmetic functions that require numeric arguments. The following table lists the arguments, their result, and the data type of the result.

Function Result Result Data Type

ABS (arg) Presents absolute value of arg. Same as arg.

EXP (arg) Raise e to the power of arg. FLOAT

LOG (arg) Derives log10 of arg. FLOAT

LN (arg) Derives loge of arg. FLOAT

SQRT (arg) Derives square root of arg. FLOAT

NULLIFZERO Returns NULL if arg is 0. Same as arg.

ZEROIFNULL Returns 0 if arg is NULL. Same as arg.

Introduction to the Teradata RDBMS for UNIX

**33**> 34 35 36 37 38 39 .. 76 >> Next