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

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

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 29 30 31 32 33 34 < 35 > 36 37 38 39 40 41 .. 76 >> Next


SELECT Name,

YrsExp FROM Employee WHERE DeptNo = 600 ORDER BY YrsExp ;

This query produces the following results table.

Name YrsExp
Kemper R 7
Regan R 10
Aguilan J 11

You can group table data according to the values in one or more of the columns in the table. You can then use the Teradata SQL aggregate operators to provide summary information about the group in a results table.

The GROUP BY clause defines a group. When you use a GROUP BY clause in a SELECT statement, each item in the statement must be a unique property of the group.

Teradata SQL provides a HAVING clause, which has a similar function to the WHERE clause, to restrict the groups that appear in the results table.

Suppose you wanted to know the minimum and maximum salary for every department with a salary totaling more than $170,000.00. The query looks like this:

SELECT DeptNo,

MIN(Salary),

MAX(Salary)

FROM Employee

GROUP BY DeptNo

HAVING SUM(Salary) > 170000

5-12

Introduction to the Teradata RDBMS for UNIX
Data Manipulation

Simple SQL Queries: Using the SELECT Statement

Including Information from More Than One Table in a Query 5

The JOIN algebraic relational operator permits you to select data from more than one table using the Teradata SQL SELECT statement.

The WHERE clause specifies the join criteria. Suppose you wanted to report the names and locations of all employees. This requires a join of the Employee and Department tables on DeptNo. The query looks like this:

SELECT Name,

Loc

FROM Employee,

Department

WHERE Employee.DeptNo = Department.DeptNo

A join operator can be one of the following types:
Product Join A product join compares every qualifying row from one table to every qualifying row from the other table; those that match the WHERE condition are saved.
Merge Join A merge join retrieves rows from two tables, then puts them onto a common AMP, based on the row hash of the columns involved in the join.
Nested Join, local A local nested join is more typical than a remote nested join. It implies that no
and remote messages are sent during the execution of the nested join. A remote nested join implies the message will be sent to another AMP to get the rows from the right table.
Exclusion Join, An exclusion join is a product or merge join where only the rows that do not
merge and product satisfy (are NOT IN) any condition specified in the request are joined.
RowID Join A rowID join requires the following conditions: • The condition must match another column of the first table to a NUSI or USI of the second table. • Only a subset of the NUSI or USI values from the second table are qualified via the join condition, and a nested join is done between the two tables to retrieve the row IDs from the second table.
Self-Join A normal join establishes a relationship between the rows in different tables or views. You may also want to establish a relationship between different rows in the same table or view. To do this, you treat the table or view as two separate tables or views and join it to itself.
Hash Join Hash Join is an alternative join scheme that performs better than Merge Join under certain conditions. The performance gain comes mainly from eliminating the need for sorting the join tables before performing the actual join.

Introduction to the Teradata RDBMS for UNIX

5-13
Data Manipulation

Simple SQL Queries: Using the SELECT Statement

Nesting Subqueries

Suppose you wanted to know who the manager of employee Marston is. To determine this information, you could perform the following three SQL statements in order, taking the answer to the first query (500) and inserting it into the WHERE clause of the second, then taking the answer of that query (10008) and inserting it into the WHERE clause of the third. The SQL statements are:

SELECT DeptNo FROM Employee WHERE Name = 'Marston A' f

SELECT MgrNo FROM Department WHERE DeptNo = 50 0 t

SELECT Name FROM Employee WHERE EmpNo = 10 0 08 t

Teradata SQL provides facilities that allow you to determine this result with one query by using nested subqueries.

5-14

Introduction to the Teradata RDBMS for UNIX
Data Manipulation

Simple SQL Queries: Using the SELECT Statement

For example, the following query references the result of the first subquery in the WHERE clause of the second, then references the result of the second in the WHERE clause of the third.

SELECT Name FROM Employee WHERE EmpNo IN (SELECT Mgr No FROM Department WHERE DeptNo IN (SELECT DeptNo FROM Employee WHERE Name = 'Marston A'

)

)

t

You can obtain the same result by nesting only one level deep, for example:

SELECT Name FROM Employee WHERE EmpNo IN (SELECT MgrNo FROM Department,

Employee

WHERE Employee.Name = 'Marston A'

AND

Department.DeptNo = Employee.DeptNo

)

Introduction to the Teradata RDBMS for UNIX

5-15
Data Manipulation

Using the INSERT Statement

Introduction
Previous << 1 .. 29 30 31 32 33 34 < 35 > 36 37 38 39 40 41 .. 76 >> Next