Databases

• Data is stored in a database on a server (on-site or in the cloud)
• Databases provide structured storage of information
• Relational databases are databases that consists of tables with rows and columns
• Each row in a table has a primary key. This primary key serves as a unique id that differentiates it from every other row in the table.

SQL – Structured Querying Language

The basic SQL statement is comprised of required and optional clauses:

Required:

SELECT – identifies the column(s) of data

FROM – identifies the table(s) from which data is being retrieved

; – every query should end with a semi-colon

Optional:

AS – used to rename columns

WHERE – specifies the search conditions or qualifiers that must be met for the data to be returned. If no qualifiers are used, then all rows of data from the view(s) specified are returned. This clause is also used to join multiple tables and create subqueries.

GROUP BY – specifies how aggregate functions are to summarize data by columns specified in the SELECT statement.

HAVING – allows filtering of the result set based on aggregate criteria

ORDER BY – used to sort one or multiple columns in a result set

Syntax Rules:

• Character columns should have single quotes
• Teradata is NOT case-sensitive
• Numeric column values have no quotes or commas but may have decimal points
• Date column values are enclosed in single quotes and follows one of two formats:
  • ‘yyyy-mm-dd’ is used for AEDW & EDL
  • ‘yyyymmdd’ is used for CRDW
• Use commas to separate column names in keyword lists, such as: name, address, city, etc.
• Labels for aliases require double quotes if the label has multiple words such as: “Label one”

Documenting your Code

As your queries become more and more complex, it becomes even more important that you document your code so you can not only remember why the code was written in a certain way but also so you can read the code more easily.

Two Methods:

1. Slash-Asterisk Method: /**/
2. Double-Hyphen Method: —

Creating a Table from Importing an Excel File in Teradata

Step One:

To Create Volatile Tables:

Volatile tables are temporary tables that will no longer exist once your session ends.

CREATE VOLATILE TABLE TableName, NO LOG
COLUMN DataType(),
COLUMN DataType() NOT NULL,
COLUMN DataType()
) WITH DATA
PRIMARY INDEX (COLUMN)
ON COMMIT PRESERVE ROWS;

To Create Permanent Tables:

CREATE TABLE DATABASENAME.TableName
(COLUMN DataType(),
COLUMN DataType() NOT NULL,
COLUMN DataType()
)
PRIMARY INDEX (COLUMN);

To insert data into the table you just created:

INSERT INTO TableName
(COLUMN,
COLUMN,
COLUMN)
VALUES (?,?,?);

The number of question marks will be the same number of columns that are listed within the parentheses.