Teradata Learning (in process)

Introduction to Teradata:

In  RDBMS Data is organized in to tables.

Rows in the table represent instances of an entity.

Columns represent the data fields which comprise the rows.

SQL:

• Structured Query Language (SQL) is the industry standard language for communicating with Relational Database Management Systems.

• SQL is a non-procedural language, meaning it contains no procedural-type statements such as those listed here:
1. GO TO
2. PERFORM
3. DO LOOP
4. OPEN FILE
5. CLOSE FILE
6. END OF FILE

• SQL commands are divided into 3 categories:

Data Definition Language(DDL): Used to define and create database objects such as tables, views, macros, databases, and users.

Data Manipulation Language (DML) - Used to work with the data, including such tasks as inserting data rows into a table, updating an existing row, or performing queries on the data.

Data Control Language(DCL)- Used for administrative tasks such as granting and revoking privileges to database objects or controlling ownership of those objects.

Data Definition Language (DDL) Examples:

SQL statement	Function
CREATE	Define a table, view, macro, index, trigger or stored procedure.
DROP	Remove a table, view, macro, index, trigger or stored procedure.
ALTER	Change table structure or protection definition.

Data Manipulation Language (DML):

SQL statement	Function
SELECT	Select data from one or more tables.
INSERT	Place a new row into a table.
UPDATE	Change data values in one or more existing rows.
DELETE	Remove one or more rows from a table.

Data Control Language (DCL):

SQL statement	Function
GRANT	Give user privileges.
REVOKE	Remove user privileges.
GIVE	Transfer database ownership.

Relational Concepts:

1. Intersection of row and column is called a data value.
    
2. Each data value comes from a particular domain. A Domain is a pool of legal values for the column.

3. The purpose of the Primary Key is to uniquely identify each record. No two values in a primary key column can be identical

4. A Foreign Key represents the Primary Key of another table. Relationships between tables are formed through the use of Foreign Keys.

Module 2 : Teradata SQL

SELECT statement:

• The SELECT statement allows you to retrieve data from one or more tables

• Example:

SELECT * FROM employee WHERE department_number = 401; 

The asterisk, "*", indicates that we wish to see all of the columns in the table.
 

The FROM clause specifies from which table in our database to retrieve the rows.
 

The WHERE clause acts as a filter which passes only rows that meet the specified condition

• Note that SQL does not require a trailing semicolon to end a statement. We can run a query in SQL assistant without a semicolon.

However it’s the requirement of the BTEQ(Basic Teradata Utility) to use and enter SQL commands that require.

Example:

 *** Logon successfully completed.                                

 *** Teradata Database Release is 13.10.04.08                     

 *** Teradata Database Version is 13.10.04.08                     

 *** Transaction semantics are BTET.                              

 *** Session Character Set Name is 'EBCDIC'.                      

                                                                  

 *** Total elapsed time was 0.26 seconds.                         

                                                                  

 BTEQ -- Enter your SQL request or BTEQ command:  select date     

                                                                  

                                                                  

If we keep hitting enter it will keep on going to next line unless you put a semicolon as then hit enter.

• Instead of using the asterisk symbol to specify all columns, we could name specific columns separated by commas

Example:

SELECT	employee_number  ,hire_date  ,last_name  ,first_name
FROM	employee
WHERE	department_number = 401;

ORDER BY Clause:

• Use the ORDER BY clause to have your results displayed in a sorted order. Without the ORDER BY clause, resulting output rows are displayed in a random sequence.

• Ascending order is the default sort sequence for an ORDER BY clause.
  
  To explicitly specify ascending or descending order, add ASC or DESC, to the end of the ORDER BY clause.

Example:

SELECT	employee_number  ,last_name  ,first_name  ,hire_date
FROM	employee
WHERE	department_number = 401
ORDER BY	hire_date;

• You may indicate the sort column by naming it directly (e.g., hire_date) or by specifying its position within the SELECT statement.
  
  Since hire_date is the fourth column in the SELECT statement, the following ORDER BY clause is equivalent to saying ORDER BY hire_date..

ORDER BY

4;

Note that it’s the number in the select list that can be used . Its not the sequence of column in table.

• An ORDER BY clause may specify multiple columns. No single column in an ORDER BY clause should exceed a length of 4096 bytes, otherwise it will be truncated for sorting purposes. 

The order in which columns are listed in the ORDER BY clause is significant.
The column named first is the major sort column.
The second and subsequent are minor sort columns.

Example:

SELECT	employee_number  ,department_number  ,job_code
FROM	employee
WHERE	department_number < 302
ORDER BY	department_number  ,job_code;

• Each column specified in the ORDER BY clause can have its own sort order, either ascending or descending.

SELECT	employee_number  ,department_number  ,job_code
FROM	employee
WHERE	department_number < 302
ORDER BY	department_number ASC ,job_code DESC;

DISTINCT clause:

• The DISTINCT operator will consolidate duplicate output rows to a single occurrence.

Example:

SELECT	DISTINCT department_number ,job_code
FROM	employee
WHERE	department_number = 501;

• DISTINCT appears directly after SELECT, and before the first named column. It may appear to apply only to the first column, but in fact, DISTINCT applies to all columns named in the query.

The combination of department_number and job_code are distinct since the job codes differ.

Naming Database Objects:

• Teradata Names are not case sensitive

• Teradata Names are limited to 30 characters. If we create a Table name greater than 30 bytes we get following error:

CREATE TABLE Failed. 3737:  Name requires more than 30 bytes in LATIN internal form

Names are composed of:	a-z A-Z 0-9 _ (underscore) $ #
Names must start with: Basically names cannot start with a number. If we create a table with a number as 1st character we get following error message CREATE TABLE Failed. 3707:  Syntax error, expected something like '(' between the word 'EDW_RESTORE_TABLES' and the number '.9'.	a-z A-Z _(underscore) # $

• Database names and User names must be unique within the Teradata RDBMS.

• Table, view, macro, trigger, index and stored procedure names must be unique within a Database or User

• Column names must be unique within a Table.

• The syntax for fully qualifying a column name is:      databasename.tablename.columnname 

Example:

COLUMN1 --> unqualified

TABLENAME.COLUMN1 --> Partially qualified

DATABASE.TABLENAME.COLUMN1 --> Fully Qualified.

Default Database:

The user name you logon with is usually your default database. However this can also depend on how user is created.

We can see the current database by using the query:

SELECT DATABASE;

The DATABASE command is used to change your default database.

For example: DATABASE payroll;

sets your default database to payroll. Subsequent queries (assuming the proper privileges are held) are made against the payroll database.