Most frequently asked Teradata Interview Questions and Answers:
This article will brief you about most of the common Teradata interview questions and answers, thereby enabling you to understand the concepts of Teradata in a much better way.
Teradata is a Relational Database Management System which is suitable to use with large-scale data warehousing applications. The main concept behind Teradata is parallelism.
The application was developed by a company called Teradata Corporation which is located in the United States. Hundreds of clients have chosen Teradata for the Enterprise Data Warehousing and Data analytical uses.
Most Important Teradata Interview Questions and Answers
Q #1) What is Teradata? Also, list out some of the primary characteristics of Teradata.
Ans: Teradata is a Relational Database Management System which is used to creation and maintenance of Data Warehouses, Online Analytical Processing (OLAP), online transaction processing (OLTP) and Decision Support Systems (DSS).
Following are the primary characteristics of Teradata:
- Single and multi-node running capabilities.
- Built on Parallelism.
- Compatible to American National Standards Institute (ANSI).
- Acts in the same way as the server.
- Open system.
Q #2) Briefly explain each of the following terms related to Relational Database Management System (RDBMS) – Database, Tables, Columns, Row, Primary Key and Foreign Key.
Ans: Database – A collection of logically related data is called database.
Tables – Table is a basic unit in Relational Database Management System and it is used to store data.
Example: Employee table contains details about the employees of a company.
Columns – Column is a unit that contains similar data.
Example: Date of birth column in the employee table contains only birth days.
Row – A Single instance of all the columns can be identified as a row.
Example: Details of one employee from the employee tab consists of one row and one instance of each column like the date of birth included in it.
Primary Key – Primary Key is the value that is used to uniquely identify a row in the table. No duplicate values or NULL values are permitted to insert. It is a mandatory value.
Foreign Key – The keys that are used to create a relationship between each table is identified as foreign keys. In the master-detail table design, the primary key of a parent table is designed as a foreign key of a child table.
Q #3) What are the different table types that are supported by Teradata?
Ans: There are four types of tables that are supported by Teradata.
Permanent Table – Permanent tables are the default table type and those tables are used to store the data permanently in the system.
Volatile Table – Volatile tables are used to store the data only during the user session. At the end of a user session, the table will drop with its data. These tables are important to store intermediate data during data transmission or during complex calculations.
Global Temporary Table – Global Temporary Table is used to store the globally used values throughout the application and the lifetime is limited to the user session. At the end of the user session, the table will drop with its data.
Derived Table – Derived tables have the smallest lifetime and it is with the execution time of a query. These tables are used to hold the intermediate results during the query execution.
Q #4) What are the commands to make a new table, change a table and remove a table in Teradata?
Ans: The commands are CREATE TABLE, ALTER TABLE, and DROP TABLE respectively.
Q #5) What are the main components of Teradata system?
Ans: There are four major components which are associated with the Teradata system.
They can be described as follows:
Node – Each individual server in a Teradata system is called as Node and it is the basic unit in a Teradata system. A node consists with its own copy of Teradata RDBMS & own operating system, CPU, memory and disk space. While considering the physical structure, a cabinet consists of one or more nodes.
Parsing Engine – This is the section with all the responsibilities of receiving a request from the client end (client queries) and creates an efficient execution plan.
The following key responsibilities were associated with Parsing Engine:
- Receives client-side SQL query and checks them for possible syntax errors.
- Checks the available user privileges to execute the SQL query.
- Checks the actual availability of the object associated with the SQL query.
- Prepares execution plan and executes SQL query.
- Receives the result and sends it to the client.
Message Passing Layer – In the Teradata system, Message Passing Layer is also called as BYNET and it is the networking layer. This facilitates the communication between Parsing Engine, Access Module Processor, and Nodes.
Access Module Processor – Access Module Processor is also known as Virtual Processors (vprocs) and it is the actual data store and retrieves agent in the Teradata system. Only Access Module Processor can read and write data to disks where physically the data gets stored.
Q #6) What are the frequently used data types in Teradata?
Ans: Teradata supports several data types and the following table describe a range of values and the length in bytes for frequently used data types.
Q #7) What is the basic syntax for Teradata select statement? Also, create a select statement to retrieve Employee No, First and Last names by considering the following table.
Ans: The basic Teradata syntax for SELECT statement is as follows:
SELECT column 1, column 2, …..
Q #8) List the logical and conditional operators that are used with Teradata along with their meanings?
Ans: Given below are the logical and conditional operators that are used with Teradata:
|>=||Greater than or equal to|
|<=||Less than or equal to|
|!ERROR! an unexpected error occurred||Equal to|
|BETWEEN||If values within range|
|IN||If values in |
|NOT IN||If values not in |
|IS NULL||If value is NULL|
|IS NOT NULL||If value is NOT NULL|
|AND||Combine multiple conditions. Evaluates to true only if all conditions are met|
|OR||Combine multiple conditions. Evaluates to true only if either of the conditions is met.|
|NOT||Reverses the meaning of the condition|
Q #9) Describe the BETWEEN keyword in Teradata and predict the outcome of the provided SELECT statement using the following table.
Ans: BETWEEN command is used to check any specified value in between the given range.
The outcome of the given select statement is employee no and the first name along with the Employee No between 101 and 103.
Q #10) Describe the set operators in Teradata.
Ans: Set operators are used to combine the result from multiple SELECT statements. This is different from joins because joins combine columns in multiple tables, but set operators combine multiple rows.
Given below are the four Set operators in Teradata:
- UNION ALL
Q #11) What is the difference between UNION and UNION ALL?
Ans: UNION combines the result sets by ignoring duplicates, but UNION ALL combines with duplicates.
Note: Question no 12 to 15 are based on the following two tables.
Q #12) Describe the outcome of the following SELECT statement in the given scenario.
Ans: The select statement has used the set operator as UNION and it combines the outcome of both the select statements and removes duplicates from the list.
The outcome is as follows:
Q #13) Describe the outcome of the following SELECT statement in the given scenario.
Ans: The select statement has used the set operator as UNION ALL and it combines the outcome of both the select statements, without removing duplicates from the list.
The outcome is as follows:
Q #14) Describe the outcome of the following SELECT statement in the given scenario.
Ans: The select statement has used the set operator as INTERSECT and it combines the common values for the both select statements. Since the employee no 105 is not available in Salary Table it is removed from the result.
The outcome is as follows:
Q #15) Describe the outcome of the following SELECT statement in the given scenario.
Ans: The select statement has used the set operator as MINUS and it considers both the select statements and displays the values that are in the first select statement but not available in the second select statement.
Since the employee no 101,102,103 and 104 are available in both the Tables only 105 is included in the end result.
The outcome is as follows:
Q #16) Describe Primary index in Teradata. And what are the available primary index types?
Ans: The mechanism to specify where the data resides in the Teradata is called primary index. Each table should have a primary index specified. If not Teradata will assign a primary index for the table. A primary index provides faster data access.
Given below are the two types of primary indexes in Teradata:
- Unique Primary Index(UPI)
- Non-Unique Primary Index(NUPI)
Q #17) What is the purpose of using CASE Expression in Teradata?
Ans: CASE Expression is used to evaluate each row against a specific condition and returns the result based on the first match. When there is no matching condition then else part will return.
The general syntax of a CASE is as follows:
CASE <expression> WHEN <expression> THEN result-1 WHEN <expression> THEN result-2 ELSE Result-n END
Q #18) What is the purpose of Joins in Teradata and what are the available join types?
Ans: Generally Joins are used to combine the records from more than one table using common columns or values.
Given below are the seven type of joins associated with Teradata:
- Inner Join: Combines the records from multiple tables and returns the value set that exist in both tables.
- Left Outer Join: Returns all the records in the left table and only matching records from the right table.
- Right Outer Join: Returns all the records in the right table and only matching records from the left table.
- Full Outer Join: It is a combination of Left Outer Join and Right Outer Join. Returns both matching and non-matching records from both the tables.
- Self-Join: Compares value in a column with the other values in a same column of the table.
- Cross Join: Joins every row from the left table to every row in the right table.
- Cartesian Production Join: Works same as Cross Join.
Q #19) What is called Partitioned Primary Index (PPI) and discuss the advantages of using it in a query?
Ans: Partitioned Primary Index (PPI) is an indexing mechanism that facilitates to improve the performance of certain queries. Within a table with Partitioned Primary Index (PPI) defined, rows are sorted according to their partition number.
Within each partition, records are arranged by their row hash. Also, rows are assigned to a partition based on the definition of a partition.
Advantages of Partitioned Primary Index (PPI):
- For several queries, PPI helps to avoid a full table scan and only required partitions are accessed.
- Prevents using the secondary index and it helps to avoid additional Input/output maintenance.
- Quick access to the subset of a large table.
- Easy to drop old data and add new data.
Q #20) Define Views in Teradata with general syntax.
Ans: Database objects that are built using queries on single or multiple tables are termed as views. The definition for view is permanently stored in the data definition and there is no save of the selected data. Data for the view is a dynamic process at the execution time.
CREATE/REPLACE VIEW <viewname> AS <select query>;
Q #21) What is the meaning of Caching in Teradata?
Ans: Caching is primarily working with the source that does not change frequently. The system keeps frequently using the data blocks in a separate memory in the application. Also caching is shared within several applications.
Q #22) What is the purpose of UPSERT command?
Ans: UPSERT command is used for the operation that needs to perform Update Else Insert natures. This is a specific option that is only available in Teradata.
Q #23) What is the difference between fast load and multi-load?
Ans: Fast load is using multiple sessions to quickly load a large amount of data to an empty table. Multi-load is used to maintain the activities of high volume tables and views. Multi loads work with non-empty tables also and only up to 5 tables can be used with multi-load.
Q #24) What are the String Manipulation operators and functions associated with Teradata?
Ans: || – Concatenate strings and create a single string.
SUBSTR – Extracting only a portion of the long string depends on Teradata extension.
SUBSTRING – Extracting only a portion of the long string depends on ANSI extension.
INDEX – Locating a specific position of a character string depends on Teradata extension.
POSITION – – Locating a specific position of a character string depends on ANSI extension.
TRIM – Trims blank from the specified string.
UPPER – Converts the string to Uppercase.
LOWER – Converts the string to Lowercase.
This article focused more on most of the specific features of Teradata. And I hope that the above questions based on Teradata concepts, Keywords, syntax’s and the application of the entire database management activities would have immensely helped you.
Also, this article can be used as a quick reference for experienced database programmers in their complex script creation process.
Are you ready to crack a Teradata Interview right now??