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
    1. PERFORM
    2. DO LOOP
    3. OPEN FILE
    4. CLOSE FILE
    5. 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.

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

  1. 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.

2 comments:

  1. What is the difference between Restore and Copy:

    Thanks in advance

    ReplyDelete
  2. After reading this blog i very strong in this topics and this blog really helpful to all Big Data Hadoop Online Course

    ReplyDelete