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 .. 88 89 90 91 92 93 < 94 > 95 96 97 98 99 100 .. 241 >> Next


The following statement selects all of the data (all columns and Example 2 rows) in the Employee table:

SELECT * FROM Employee;

The following statement selects the name, job title, and department Example 3 number for each employee in the Employee table (in this example,

Name, JobTitle, and DeptNo are expressions in the select expression list):

SELECT Name, JobTitle, DeptNo FROM Employee;

The following statement selects the name of, and calculates the Example 4 monthly salary for, each employee:

SELECT Name, Salary/12 AS MSalary FROM Employee;

The expression list of the preceding SELECT statement contains two expressions:

• Name

• Salary/12 AS MSalary

7-10

Teradata RDBMS for UNIX SQL Reference
Function

Syntax

Usage Notes

Use WHERE with AND and OR

Use WHERE to Search for a Character String

Queries: The SELECT Statement

WHERE Clause

WHERE Clause

The WHERE clause of the SELECT statement selects rows that satisfy a conditional expression. The WHERE clause can also be used in DELETE, UPDATE, INSERT-SELECT, and ROLLBACK/ABORT statements.

---WHERE search_cond---------

FF06A011

where:

Syntax Element . . . Is . . .
search_cond a search condition is a conditional expression as described in Chapter 6. The arguments can be individual values and or include subqueries, but the overall expression must be of a form that returns a single boolean (TRUE/FALSE) result. Note: Logical expressions include comparisons of numeric values, character strings, and partial string comparisons.

Except when used in the ROLLBACK statement, the WHERE conditional may not specify an aggregate operation. However, the conditional may be applied to an aggregate result returned by a subquery.

The WHERE clause can be used with the AND and OR operators. For example, the following statement selects employees who are in department 100 and who have either a college degree or at least 5 years of experience:

SELECT * FROM Employee WHERE Dept = 100

AND (EdLev >= 16 OR YrsExp >= 5);

The WHERE clause may be used to select data by searching for a character string. For example, the following statement is processed by searching the Employee table for every row that satisfies the condition: the JobTitle field contains the character string “analyst”. The Name and DeptNo fields for those rows are then listed.

SELECT Name, DeptNo FROM Employee

Teradata RDBMS for UNIX SQL Reference

7-11
Queries: The SELECT Statement

WHERE Clause

WHERE UPPER (JobTitle) LIKE '%ANALYST%' ;

WHERE in JOINS

The WHERE clause can define a condition for joining table rows; namely, that the values in a common column must match in both tables.

Example

The following statement, which requests the name of each employee from the Employee table and the location of each employee’s department from the Department table, is processed by joining the Employee and Department tables:

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

JOIN Efficiency and Indexes

The efficiency of a join operation depends on whether the WHERE condition uses values for columns on which primary or secondary indexes are defined.

If indexes are defined on the DeptNo columns in both the Employee Example and Department tables, specifying an equality condition between

the values in each indexed column, as in the preceding example, allows the rows in the two tables to be matched using the values in both indexed columns.

Efficiency is increased if a primary index is defined on one or both of these columns. For example, define DeptNo as the unique primary index for the Department table.

The EXISTS (logical $) predicate is supported as the predicate in a search condition.

The meaning of the exists predicate is to test the result of the subquery.

If execution of the subquery would return response rows then the where condition is considered satisfied. Use of the NOT qualifier for the EXISTS predicate reverses the sense of the test.

Execution of the subquery does not, in fact, return any response rows. It simply returns a boolean to indicate whether responses would or would not be returned.

The subquery may be correlated with an outer query.

Refer also to Chapter 6, “SQL Expressions,” “Logical Expressions: EXISTS” for more detail on using EXISTS.

7-12

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

WHERE Clause

Using Unconstrained Joins (No WHERE clause)

Caution:

Examples

Example 1

Example 2

Example 3

SQL allows unconstrained joins; that is, joins in which a WHERE is not specified for the tables that are joined.

The result of an unconstrained join is a Cartesian product, which is the product of the number of rows in each table that is joined.

An unconstrained join might produce a great many rows, returning a result that the user may not have intended. Also, it might place a heavy load on system resources. Consider carefully before specifying an unconstrained join.
Previous << 1 .. 88 89 90 91 92 93 < 94 > 95 96 97 98 99 100 .. 241 >> Next