Back to Research Page

Tao Guo & Qingxiu Luo




Data definition and data manipulation with Teradata SQL 

Database management systems based on the hierarchical, network, and object_oriented models use different languages to define and manipulate the database. But SQL is the only language that Teradata can understand. Teradata SQL is a data language designed for manipulation data in relational database. It is the ANSI standard language for relation database management. Since SQL is the only language the Teradata RDBMS understand, all application programming facilities ultimately make their queries against the database using SQL. You can use Teradata SQL statement to define data; select data; manipulate data; create Teradata SQL macro; control data and users; (Using Teradata Structured Query Language, Relational tables and their contents can be accessed.) SQL can be incorporated into an application program, or typed directly by the user. Unlike many procedural languages used for database management, Teradata SQL specifies the desired result rather than the procedure for deriving the result. Teradata SQL functions can be divided into the following general areas of activity:

a. Data definition Language (DDL) which is used to create, rename, alter, modify, drop, replace, and delete tables, Indexes, Views, and macros; show data; comment on database objects; and establish a default database.

b. Data manipulation Language (DML) which is used to add, delete, and revise data in existing tables or views.

c. Data retrieval, which is used to query relational tables to extract data, uses joins, unions, and intersects.

d. Data Control Language (DCL) which is used to control database security; establish privileges and revoke privileges.

e. Macros which are used to simplify repetitive operations.

f. Statement modifiers, such as EXPLAIN, LOCKING , and USING

g. Informative statements, such as HELP, SHOW.

h. Embedded SQL, which can be embedded in an application program.

3. Details about Data Definition Language (DDL) and Data Manipulation 

Language (DML)

SQL language is a combination of DDL, DML and the select statement. The following details will specify the data definition and manipulation capabilities of Teradata SQL and query facilities.

a). The DDL provides statements for the definition and description of entities. It enable you to perform the following

operations:

· CREATE Define a new database, user, table, trigger, index, macro, view, depending on the object of the CREATE

statement.

· DROP Remove a table, trigger, index, macro, view definition, depending on the object of the DROP statement

Other data definitions allow you to perform the following function:

· ALTER Change a table, trigger or protection definition

· RENAME table triggers views and macros

· REPLACE macros, triggers or views

· SET seddions and time zones

· DELETE database or users

· COLLECT statistics on a column or index

· MODIFY databases or users

· COMMENT on database objects

· GRANT/REVOKE access rights, logon rights

· GIVE another database object to another database or user

· HELP on database objects

· SHOW database objects

· ABORT, ROLLBACK, COMMIT, BEGIN/END TRANSACTION to manage transactions

· CHECKPOINT a journal

· DATABASE to set default

· ECHO a string or command to client

A DDL statement may be entered as a single statement, the solitary statement, and the solitary statement in a macro. When a table is created, using a data type must be specified for each column. Teradata SQL contains Numeric, Character, DateTime, Interval, Byte, numeric character and byte data types. Teradata also support ANSI-compliant DateTimes and Intervals, including DATE, TIME,TIMESTAMP,TIME ZONE, and INTERVAL data types.

b. DML supports statements for manipulating and processing database values. It enable you to perform followingoperations:

· BEGIN and END which is a uaer_defined transaction in Teradata mode.

· COMMENT which is a transaction in ANSI mode.

· INSERT insert new rows into a table

· UPDATE modifies data in one or more rows of a table.

· DELETE removes a row from a table.

c. Date retrieval is used to Select data from Teradata RDBMS. A query statement retrieve data without affecting its

content or location. The following variations can be used with the SELECT statement:

· UNION option

· INTERSECT option

· EXCEPSET option

· DISTINCT option

· FROM list

· WHERE clause, including subqueries

· SAMPLE clause

· GROUP BY clause

· HAVING clause

· ORDER BY clause-casespecific option. International sort orders

· WITH clause

The SELECT statement can be performed in several ways:

· Select all rows, specific rows, or specific columns from a single table or view, and obtain the results in a specified order

· Perform arithmetic and logical operations and generate new from a single table or view

· Select ordered values or operational results from two or more joined tables or view.