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 .. 9 10 11 12 13 14 < 15 > 16 17 18 19 20 21 .. 241 >> Next


Structure of a SQL Statement

Structure of a SQL Statement

Introduction

The general structure of an SQL statement is:.

StatementKeyword(s)

I________I

^-expressions —I —keywords —

----clauses —

----phrases —

HH01A082

where

This syntax element . . . Specifies . . .
StatementKeyword the function of the statement and introduces the primary clause.
expressions constants, name references, or operations using names and constants.
keywords functions or special values; introduce clauses or phrases. Most keywords are reserved words and may not be used in names.
clauses subordinate statement qualifiers.
phrases data attribute phrases.

A request defined in the body of a macro must be terminated. Refer Requests in Macros 2 to “Macros”, for a discussion of macros and their use.

2-12

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Data Manipulation Language (DML)

Selecting Columns

Selecting Rows

Data Manipulation Language (DML)

Use the SELECT statement to obtain information from the tables in a database. The SELECT statement specifies the table columns from which to obtain the data, the corresponding database (if you have not already established a default database), and the table (or tables) that you need within that database.

For example, to request the data from the name, salary, and jobtitle columns of the employee table, enter:

SELECT name, salary, jobtitle FROM employee ;

and receive:
Name Salary JobTitle
Newman P 28,600.00 Test Tech
Chin M 38,000.00 Controller
Aguilar J 45,000.00 Manager
Russell S 65,000.00 President
Clements D 38,000.00 Salesperson

Note: The left-to-right order of the columns in a result is determined by the order in which the column names are entered in the statement.

As long as a statement is otherwise constructed properly, the spacing between statement components may vary. For example, the statement above could be:

SELECT name , salary,jobtitle FROM employee;

To select all the data in the employee table, you could enter:

SEL * FROM employee ;

The asterisk specifies that the data in all columns of the table be returned.

The SELECT statement retrieves stored data from a table. All rows, specified rows, or specific fields of all or specified rows can be retrieved. The FROM, WHERE, ORDER BY, DISTINCT, WITH, GROUP BY, and HAVING clauses provide for a fine detail of selection criteria.

To obtain data from specific rows of a table, use the WHERE clause of the SELECT statement. That portion of the clause following the keyword WHERE causes a search for rows that satisfy the condition specified.

For example, to get the name, salary, and title of each employee in Department 100, use the WHERE clause:

Teradata RDBMS for UNIX SQL Reference

2-13
Data Handling Fundamentals

Data Manipulation Language (DML)

Adding Rows

Modifying Rows

SELECT name, salary, jobtitle FROM employee WHERE deptno = 100 ;

The response is:

Name Salary JobTitle

Chin M Greene W Moffit H Peterson J

38.000.00 32,500.00

35.000.00

25.000.00

Controller Payroll Ck Recruiter Payroll Ck

The INSERT statement is used to add rows to a table.

One statement is required for each new row, except in the case of an INSERT...SELECT statement. For more detail on this, refer to Chapter 8, “Teradata SQL Syntax Guide,” (INSERT-SELECT).

Values for all the fields (columns) or one or more selected fields of the row may be specified in any order. A NULL, or the column’s default value, is stored in any field for which a value is not specified. One statement is required for each new row except in the case of INSERT ... SELECT.

The CREATE TABLE statement affects an insert operation in the following ways:

WHEN an INSERT statement . . . THEN . . .
attempts to add a duplicate row or rows for at least one unique index, either primary or secondary, or the table is defined not to allow duplicate rows an error returns.
omits a value for a field for which a default value is defined the default value is supplied for that field.
omits a value for a field for which NOT NULL is specified, and no default is specified the operation is rejected and an error message is returned.
supplies a value that does not satisfy the constraints specified for a field, or violates some defined constraint on a field or fields the operation is rejected and an error message is returned.

The UPDATE statement allows you to modify data in one or more rows of a table. In the UPDATE statement, the column name of the data to be modified is specified along with the new value. A WHERE clause is used to qualify the row(s) whose data is changed.

Attributes specified in the CREATE TABLE statement affect an update operation in the following ways:

2-14

Teradata RDBMS for UNIX SQL Reference
Data Handling Fundamentals

Data Manipulation Language (DML)

Removing Rows

• When an update supplies a value that does not satisfy the constraints specified for a field, or violates some defined constraint on a field or fields, the update operation is rejected and an error message is returned.
Previous << 1 .. 9 10 11 12 13 14 < 15 > 16 17 18 19 20 21 .. 241 >> Next