![]() |
Teradata is a relational database management system (RDBMS) that drives a company's data warehouse. The Teradata database is an open-source, compliant with industry ANGI standards. It runs on these industries standard operating systems like Microsoft Windows, enterprise servers, and Novell SUSE Linux. For this reason, Teradata is considered an Open Architecture. This Teradata database is one of the popular database servers that enable users to access Client data applications available from multiple data sources and also help them to solve the inquiries related to data applications.
Become a Teradata Certified professional by learning Teradata Training from hkrtrainings!
Now I am listing all the major milestones of Teradata,
Teradata is designed completely based on massively parallel processing architecture (MPP). Let me explain a few major components of Teradata Architecture,
The following are the key components of Teradata;
Nodes: Nodes are the basic unit of Teradata units. An individual server in Teradata architecture is called a Node. Each node consists of its own OS operating system, CPU, memory, RDBMS's copy of data software, and disk space.
Parsing Engine: Parsing engine is used to receive the Queries from different client sources and prepare those Queries to make an efficient execution plan. Now see the main functions of Parsing Engine;
As I said earlier, this Teradata has been designed to store and manage a large amount of complex data set in the system. It makes it easier to work with data warehousing in the RDBMS system. I would like to mention a few important advantages of using Teradata,
Teradata is a fully operational database server virtual machine adopted on the base of VMWARE software.
Requirements: Virtual machine 64 bits and CPU of 64 bits.
Steps:
1) Download the latest version of VM ware
2) Extract the execution file and save it in any folder.
3) You need to download the VMWare workstation player
It is available for both Linux operating systems and Windows OS.
4) Once you finish the download and install the software.
5) After the installation is done, run the VMWare client-server.
6) Select-> Open a virtual Machine-> it will navigate through the already extracted executed folder -> now choose the file with ext.VMDK.
7) Teradata VMware is now added to the VMWare Client-> click on the added Teradata VMware-> Click on the ‘Play Virtual Machine’.
8) If you see any popup on software updates->select ‘Remind me later’
9) Now enter the username (or root name) -> then press on tab -> enter the user password -> finally press enter.
10) Double click on ‘root’s name’-> again double click on ‘Genome’s terminal’-> it will open the shell,
11) Now enter the command /etc/init.d/tpa start-> this will start the Teradata server.
The relational Database management system is database management software that interacts with the database from multiple servers. They usually use the sequential query language (SQL) to store the data in RDBMS.
Database: Database is a collection of relatively logical database sets. Many users can access these data for multiple purposes.
For example, a sales and marketing database contains information about sales and customers that is stored in many database tables.
Tables: Table is the main component in the Relational database management system (RDBMS) which is used to store the data. These tables are consist of Columns and Rows as shown in the figure,
Columns: Columns contain similar data as same as table database. For example, if you add employee salary details, that will be stored like this,
Rows: Row is just an instance of the entire column database. For example, one row contains single employee details like,
Primary Key: The primary key is used for the unique identification of row in a table. The main thing is that no duplicate values are allowed in a table row. They never accept the NULL values in the row. The primary key is the compulsory field in the table database.
Foreign key: Foreign keys are mainly used to establish a relationship between the table databases. In the tree hierarchy of the table, a foreign key in the child table can be considered as a primary key in the parental table. A table can consist of multiple foreign key values. It allows duplicate values and also you can add null values. These keys are the optional ones in the table database creation.
That’s it about Teradata relational concepts.
Check out here for frequently asked Teradata interview questions & answers
Teradata datatypes help the user to add what kind of data values that can be stored in Column. Teradata supports several data types associated with the columns.
Table to be continued
Tables in Teradata nothing but the collection of data. We can create a table with the help of the Number of rows and columns.
Types of Teradata Tables:
1) Permanent table: This permanent table is a default table and it contains the user-created data and stored them permanently.
2) Volatile Table: The data added to the volatile table can be retained only during the user session. This table data is dropped only at the end of the session. These tables are used to hold the intermediate data during the data transformation.
3) Global Temporary table: In the Global temporary table, table data is deleted at the end of the user session.
4) Derived Table: This Derived table can be used to store the intermediate results in the queries. The life span of this table within the query in which the query is created, used and dropped.
Table data is classified into SET and MULTISET, the SET data set doesn't hold the duplicate records, where the MULTISET table can hold the duplicate records.
a) Create Table: Create table command is used to create tables in the Teradata. The syntax is as follows,
CREATE
Table option: this table option specifies the physical attributes that contains journal and fallback.
Column definition: Column Definition specifies the list of columns, data types, and their attributes.
Index Definition: this consists of a primary index, secondary index, and partitioned primary index.
b) ALTER Table: Alter table command is used to add or drop the columns from an already existing table. Users can use the Alter table command to modify the attributes in the existing columns.
Syntax:
ALTER TABLE
ADD
DROP
c) DROP Table: DROP table is used to drop the table. When you will get an issue in table drop, you need to delete the table data, and then drop the table.
Syntax:
DROP TABLE;
There are two types of operators available; they are Logical and conditional operators. These operators perform the comparison and combining multiple data conditions tasks in the database management.
Table to be continued
For example:
BETWEEN: This BETWEEN commands are used to check if a value is within a range of values.
SELECT EmployeeNo, FirstName FROM
Employee
WHERE EmployeeNo BETWEEN … AND …;
IN: this command is used to check the value against a given list of values.
For example:
SELECT EmployeeNo, FirstName FROM
Employee
WHERE EmployeeNo in (…,…,…);
NOT IN: This command reverses the result of IN command. It fetches records with values that don’t match with the given list.
For example:
SELECT * From
Employee
Where EmployeeNo not in (…,..., …);
The SET operator is used to combine the result from multiple SELECT statements. Where SET operators combine the rows from multiple rows.
Rules you should follow while working with SET operators:
UNION: Union statement is used to combine results from multiple SELECT statements. It doesn’t calculate the duplicate value.
Syntax:
SELECT col1, col2, col3…
FROM
[Where condition]
UNION
SELECT col1, col2, col3..
FROM
[WHERE condition];
UNION ALL:
UNION ALL combines the results from multiple tables including duplicate rows.
Syntax:
SELECT col1, col2, col3..
FROM
[WHERE condition]
UNION ALL
SELECT col1, col2, col3…
FROM
[WHERE condition]
Related Article:CSUM in Teradata
INTERSECT:
INTERSECT command is used to combine results from multiple SELECT statements. It returns the rows from the first SELECT statement that has a corresponding match in the second SELECT statement.
Syntax:
SELECT col1, col2, col3,
FROM
[WHERE condition]
INTERSECT
SELECT col1, col2, col3..
FROM
[WHERE condition];
MINUS/EXCEPT: This command combines the rows from multiple tables and returns the rows which are in the first SELECT but not in the second SELECT.
Syntax:
SELECT col1, col2, col3,
FROM
[WHERE condition]
MINUS
SELECT col1, col2, col3,
FROM
[WHERE condition];
Teradata supports many built-in functions to perform specific tasks or operations.
Count – this counts the rows
Sum – Sums up the values of the specified columns
Max- this returns the large value of the specified columns
Min- this returns the minimum value of the specified column
AVG – this returns the average value of the specific columns.
Let me explain one by one,
COUNT:
Syntax:
SELECT count (*) from row name;
………..
MAX:
Syntax:
SELECT max (NetPay) from row name;
………
MIN:
Syntax:
SELECT min (NetPay) from row name;
………..
AVG:
Syntax:
SELECT avg (NetPay) from row name;
…………..
SUM:
Syntax: SELECT sum (Net Pay) from row name;
……………
The following are the few examples for built-in functions and they are the extensions to SQL query language.
1) SELECT;
2) SELECT DATE;
3) SELECT CURRENT_DATE;
4) SELECT TIME;
5) SELECT CURRENT_TIME;
6) SELECT CURRENT_TIMESAMP;
7) SELECT DATABASE;
I will explain one by one with an example,
CASE function: This CASE expression check each row again the condition (WHEN clause) and it will return the result of the first match. If no condition matches, it will abort the loop.
Syntax:
CASE
WHEN
WHEN
ELSE
Result-n
END
COALESCE function:
This COALESCE function first returns the non-null value. If all the arguments are NULL value, it returns the whole expression as a NULL,
Syntax:
COALESCE (expression 1, expression 2, ….)
Advantages of using Teradata over another database system:
Here I would like to mention a few advantages of using Teradata over another database system,
In this article, I have tried to explain the basic concepts of Teradata and its uses in a relational database management system. As I have explained, Teradata is one the easiest database management tools that supports user to create a large amount of data set and also help users to perform data warehousing operations. Even no-programmer or non-technical people can learn this technology without having any programming experience. I hope this blog may help a lot of people who have a desire to learn this tool and also for Teradata Community Forums.
Other Related Articles :
As a senior Technical Content Writer for HKR Trainings, Gayathri has a good comprehension of the present technical innovations, which incorporates perspectives like Business Intelligence and Analytics. She conveys advanced technical ideas precisely and vividly, as conceivable to the target group, guaranteeing that the content is available to clients. She writes qualitative content in the field of Data Warehousing & ETL, Big Data Analytics, and ERP Tools. Connect me on LinkedIn.
Batch starts on 12th Dec 2023 |
|
||
Batch starts on 16th Dec 2023 |
|
||
Batch starts on 20th Dec 2023 |
|