Most Frequently Asked Teradata Interview Questions and Answers:
This tutorial enlists some common Teradata Interview questions and answers for a better understanding of the concept.
Teradata is a Relational Database Management System that is suitable to use with large-scale data warehousing applications. The main concept behind this technology is parallelism.
The application was developed by a company called Teradata Corporation, United States. Hundreds of clients have chosen Teradata for Enterprise Data Warehousing and Data analytical uses.
Table of Contents:
Most Important Teradata Interview Questions And Answers
Q #1) What is Teradata? List out some of its primary characteristics of it.
Answer: Teradata is a Relational Database Management System that is used for the creation and maintenance of Data Warehouses, Online Analytical Processing (OLAP), Online Transaction Processing (OLTP), and Decision Support Systems (DSS).
Following are the primary characteristics:
- Single and multi-node running capabilities.
- Built on parallelism.
- Compatible with 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 the Relational Database Management System (RDBMS) –
- Database
- Tables
- Columns
- Row
- Primary Key
- Foreign Key
Answer:
- Database: A collection of logically related data is called a database.
- Tables: A table is defined as a basic unit in the Relational Database Management System and it is used to store data. For Example, The Employee table contains details about the employees of the company.
- Columns: A column is a unit that contains similar data. For Example, the Date of birth column in the employee table contains only birthdays.
- Row: A single instance of all the columns can be identified as a row. For Example, the details of one employee from the employee table consist of one row and one instance of each column.
- 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. It is a mandatory value.
- Foreign Key: The keys that are used to create a relationship between each table are 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?
Answer: There are four types of tables that are supported by Teradata. These are:
- Permanent table: Permanent table is the default table type and these 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 the 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 its validity is for the current user session only. At the end of the user session, the table will drop with its data.
- Derived table: Derived tables have the smallest lifetime until the execution time of a query. These tables are used to hold the intermediate results during query execution.
Q #4) What are the commands to make a new table, change a table and remove a table in Teradata?
Answer: The commands are CREATE TABLE, ALTER TABLE, and DROP TABLE respectively.
Q #5) What are the main components of Teradata system?
Answer: There are four major components that are associated with this system. They can be described as follows:
- Node: Each individual server in this system is called a node and it is the basic unit in a Teradata system. A node consists of its own copy of Teradata RDBMS & own operating system, CPU, memory, and disk space. In the case of a physical structure, a cabinet consists of one or more nodes.
- Parsing Engine: Here, the main responsibility lies in receiving a request from the client end (client queries) and creating 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 the execution plan and executes SQL queries.
- Receives the result and sends it to the client.
- Message Passing Layer: In this system, the Message Passing Layer, also called BYNET 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 the data gets stored physically.
Q #6) What are the frequently used data types in Teradata?
Answer: It supports several data types. The following table describes the range of values and the length in bytes for frequently used data types.
Q #7) What is the basic syntax for the Teradata SELECT statement? Also, create a SELECT statement to retrieve Employee No, First, and Last names by considering the following table.
Answer: The basic syntax for the SELECT statement is as follows:
SELECT column 1, column 2, …..
FROM tablename;
SELECT EmployeeNo, FirstName, LastName FROM Employee;
Q #8) List the logical and conditional operators with meanings, that are used with Teradata?
Answer: Logical and conditional operators are listed below:
| Syntax | Meaning |
|---|---|
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| = | 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 and predict the outcome of the provided SELECT statement using the following table.
SELECT EmployeeNo, FirstName FROM Employee WHERE EmployeeNo BETWEEN 101 AND 103;
Answer: BETWEEN command is used to inspect any specified value in between the given range.
The result of the given SELECT statement is EmployeeNo and the FirstName of EmployeeNo between 101 and 103.
Q #10) Describe the set operators in Teradata.
Answer: Set operators are used for combining the results from multiple SELECT statements. This is different from the join operator because join combines the column in multiple tables, but set operators are used to combining multiple rows.
Given below are the four Set operators:
- UNION
- UNION ALL
- INTERSECT
- MINUS/EXCEPT
Q #11) What is the difference between UNION and UNION ALL?
Answer: UNION combines the result set by ignoring duplicates, but UNION ALL combines with duplicates.
Note: Question no 12 to 15 are based on the following two tables.
Employee Table
Salary Table
Q #12) Describe the result of the following SELECT statement in the given scenario.
SELECT EmployeeNo FROM Employee UNION SELECT EmployeeNo FROM Salary;
Answer: The SELECT statement has used the UNION set operator to combine the results of both the SELECT statements and remove duplicates from the list.
Result:
Q #13) Describe the result of the following SELECT statement in the given scenario.
SELECT EmployeeNo FROM Employee UNION ALL SELECT EmployeeNo FROM Salary;
Answer: The SELECT statement has used the UNION ALL set operator to combine the result of both the SELECT statements, without removing duplicates from the list.
Result:
Q #14) Describe the result of the following SELECT statement in the given scenario.
SELECT EmployeeNo FROM Employee INTERSECT SELECT EmployeeNo FROM Salary;
Answer: The SELECT statement has used the INTERSECT set operator to combine the common values for both SELECT statements. Since employee no 105 is not available in Salary Table, it is removed from the result.
Result:
Q #15) Describe the result of the following SELECT statement in the given scenario.
SELECT EmployeeNo FROM Employee MINUS SELECT EmployeeNo FROM Salary;
Answer: The SELECT statement has used the MINUS set operator to display the result values from the first SELECT statement excluding the result values from the second SELECT statement.
Since the EmployeeNo 101,102,103 and 104 are available in both the Tables only 105 are included in the end result.
Result:
Q #16) Describe the primary index in Teradata. And what are the available primary index types?
Answer: The mechanism to specify where the data resides in the Teradata is called the primary index. Each table should have a primary index specified. If not, it will assign a primary index for the table. A primary index provides faster data access.
There are two types of primary indexes:
- Unique Primary Index(UPI)
- Non-Unique Primary Index(NUPI)
Q #17) What is the purpose of using CASE Expression in Teradata?
Answer: CASE Expression is used to evaluate each row against a specific condition that returns the result based on the first match. In case of no matching condition, else part result 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 and what are the available join types?
Answer: Generally Joins are used to combine the records from more than one table using common columns or values.
Given below are the seven types of joins associated with Teradata:
- Inner Join: Combine the records from multiple tables and returns the value set that exists in both tables.
- Left Outer Join: Return all the records in the left table and only match records from the right table.
- Right Outer Join: Return 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. Return both matching and non-matching records from both tables.
- Self-Join: Compare the value in a column with the other values in the same column of the table.
- Cross Join: Joins every row from the left table to every row on the right table.
- Cartesian Production Join: Works the same as Cross Join.
Q #19) What is called Partitioned Primary Index (PPI) and discuss the advantages of using it in a query?
Answer: Partitioned Primary Index (PPI) is an indexing mechanism that facilitates to improvement of 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 the large table.
- Easy to drop old data and add new data.
Q #20) Define views with general syntax.
Answer: Database objects that are built using queries on single or multiple tables are termed as views. The definition for the 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.
General syntax:
CREATE/REPLACE VIEW <viewname> AS <select query>;
Q #21) What is the meaning of caching in Teradata?
Answer: Caching primarily works with a source that does not change frequently. The system keeps using the data blocks frequently in a separate memory in the application. Also, caching is shared within several applications.
Q #22) What is the purpose of UPSERT command?
Answer: UPSERT command is used for the operation 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?
Answer: 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?
Answer: The string manipulation operators and functions are as follows:
- ||: Concatenate strings together and create a single string.
- SUBSTR: Extracts only a portion of the long string, depends on Teradata extension.
- SUBSTRING: Extracts only a portion of the long string, depends on ANSI extension.
- INDEX: Locate a specific position of a character string, depends on the Teradata extension.
- POSITION: Locate 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.
Conclusion
This article focus on the specific features of Teradata. And I hope that the above questions based on the concepts, keywords, syntax, and the application of the entire database management activities will definitely help 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??