Before you go!

Take this "Teradata Interview Questions" interview guide with you

Welcome to Interviewbit, help us create the best experience for you!

Currently, You are a:

Few details about your education

College/University *
Enter the name of your college
Branch *
Year of completion *

Few details about your education

College/University *
Enter the name of your college
Branch *
Year of completion *

Few details about your career...

Current Company *
Enter company name
Experience *

You're all set!

Begin your success journey!

Sign Up using
Full name *
Email *
Password *

By creating an account, I acknowledge that I have read and agree to InterviewBit’s Terms and Privacy Policy .

Welcome back!

Log In using
Email *
Password *

Teradata Interview Questions

Download PDF


Your requested download is ready!
Click here to download.

Begin your success journey!

Sign Up using
Full name *
Email *
Password *

By creating an account, I acknowledge that I have read and agree to InterviewBit’s Terms and Privacy Policy .

Welcome back!

Log In using
Email *
Password *

As Big Data advances, companies are becoming increasingly data-driven with the goal of improving various aspects of their businesses. These companies handle a growing amount of data, at a rapid pace, and therefore need an effective management tool to store, manage, analyze, process, and discover value in an organization's data. Here, Teradata comes to the rescue. 

Business can improve their performance and outcomes by utilizing Teradata, a scalable and open-source database management system used to handle large data warehousing operations. 

What is Teradata? 

Teradata is an open-source RDBMS (relational database management system) that runs on different operating systems, including Windows, Unix, Mac OS, and Linux. A well-known tool Teradata enhances the performance of companies by managing large data warehouse operations and helps them achieve high-impact business goals. Hundreds of clients have chosen Teradata for Business Intelligence and Enterprise Data Warehousing. 

Parallelism (parallel execution) is at the heart of this technology. Using the parallelism concept, this tool allows the simultaneous execution of multiple data warehouse operations. As a result of its completely parallel architecture, a single task is broken into smaller chunks and processed in parallel or simultaneously to ensure that things are done as quickly as possible. Teradata serves as a single data store that can handle a large number of requests concurrently from multiple client applications. Teradata is developed by Teradata Corp, which is a firm headquartered in the United States.  A few of its capabilities include parallel processing, shared-nothing architecture, linear scalability, etc.

Throughout this article, you will learn about Teradata and the various important aspects of Teradata such as its features, architecture, and more. 

Crack your next tech interview with confidence!
Take a free mock interview, get instant⚡️ feedback and recommendation💡

Teradata Interview Questions for Freshers

1. What is the importance of using Teradata?

The following are reasons why Teradata is important:  

  • The system has the capability of handling (storing and processing) large volumes of data, more than 50 petabytes.  
  • You can integrate it with various business intelligence (BI) tools.  
  • This software supports OLAP (online analytical processing), enabling users to perform complex analytics on data.  
  • Teradata offers a comprehensive set of services (full) concerning data warehousing like cloud-based and hardware-based data warehousing, business analytics, etc.  
  • SQL (Structured Query Language) is supported by Teradata as a means of interacting with data stored in tables. The application consists of diverse queries that offer flexibility to users. 
  • Using the Teradata platform, companies can consolidate their core business objectives by organizing their analytical capabilities. 
  • Teradata is based on Massive Parallel Processing (MPP), which makes it possible to run multiple tasks simultaneously and efficiently, resulting in fast processing speeds. 

2. What are the newly developed features of Teradata?

Teradata offers the following features:  

  • Unlimited Parallelism: Teradata is based on Massive Parallel Processing (MPP), which allows it to divide large tasks (related to data processing) into smaller tasks and run them in parallel.   
  • Shared Nothing Architecture: As this database is built on a shared-nothing architecture, the disks, Teradata nodes, and AMPs (Access Module Processors) are all independent and do not share resources with others, resulting in an optimized performance for the given task. 
  • Linear Scalability: Teradata Systems are linearly scalable and can handle large volumes of data in the most efficient manner.   
  • Connectivity: Teradata is best in terms of connectivity as it can connect with channel-attached systems, such as mainframes or networks. 
  • Mature Optimizer: Teradata offers a mature optimizer (which provides the most efficient method for retrieving the data required by a SQL query) that can handle up to 64 joins (combining data from different tables in a database) per SQL query.  
  • SQL (Structured Query Language): This standard language is supported by Teradata as a means of interacting with data stored in tables. It also offers its own extensions.  
  • Robust Utilities: As part of Teradata's robust utility suite, Teradata provides utilities for importing and exporting data from/to Teradata systems such as Fastload, Fastexport, Multiload, and TPT (Teradata Parallel Transporter), and many more.    
  • Load & Unload utilities: Teradata features load & unload utilities, which allow users to move data into and out of the Teradata system. 
  • Automatic Distribution: Teradata automatically distributes data evenly among the disks, requiring no manual intervention.  
  • Low TCO (Total cost of ownership): Due to its ease of setup, administration, and maintenance, it has a low TCO.  

3. Explain Teradata Architecture.

The following diagram illustrates Teradata's architecture:

Teradata's architecture is based on MPP (massively parallel processing). In general, it can be divided into two parts i.e., storage architectures and retrieval architectures. In total, the architecture consists of four components, namely, a parsing engine, BYNET, AMPs, and disks. The first two components make up the storage architecture, and the last two are retrieval architecture components. 

  • Parsing Engine (PE): The Parsing Engine receives client queries and prepares an efficient execution plan to execute/run SQL queries. As soon as a user executes a SQL query, it is first connected to the PE (Parsing Engine). The PE performs the following functions: 
    • Verifies whether the queries have syntax errors. 
    • Determines whether or not the objects utilized by the SQL query exist. 
    • Prepares execution plans for these queries and then sends them to BYNET. 
    • Get the results of the SQL query from the AMPs and send it to the client.
  • Access Module Processors (AMPs): It is a virtual processor that is connected to PE via BYNET. Each AMP contains its own disk allowing it to read and write data from disks.  As such, it is referred to as shared nothing architecture. After receiving the data and execution plan from Parsing Engine, AMPs perform any data type conversion, aggregation, filtering, and sorting, and further write (store) data to the corresponding disks. When the query is fired, all AMPs work together to give back the data. 
  • BYNETs: BYNET functions as a communication channel between PEs and AMPs. This component receives the execution plan from the parsing engine and passes it on to the AMPs. Teradata has two BYNETs, named BYNET 0 and BYNET 1, but we refer to them as a single system. Reason for having 2 BYNETs:   
    • The second BYNET can take over if the first BYNET fails.   
    • Both BYNETs can be made functional when data volume is large, which will enhance communication between PEs and AMPs, and therefore speed up the process. 
  • Disks: These are Virtual Disks offered by Teradata for each AMP. Vdisk, or Virtual Disk, is the storage area of each AMP. 
You can download a PDF version of Teradata Interview Questions.

Download PDF


Your requested download is ready!
Click here to download.

4. What do you mean by Teradata utilities and write different types of Teradata utilities?

Data can be loaded into Teradata databases as well as exported from Teradata databases to client applications using Teradata utilities. There are many Teradata utilities available, including: 

  • Basic Teradata Query (BTEQ): Teradata provides the BTEQ utility, which can be used in batch (executing a collection of statements as a script) or interactive mode (executing statements one by one). You can also use it to execute any DDL (Data Definition Language) or DML statement, macro, or stored procedure. With BTEQ, you can import data into Teradata tables as well as export data to files and reports.
  • Fastload: The FastLoad utility loads data into tables quickly. There will be no duplicate rows even if the target table is a Multiset table (stores duplicate records).
  • Multiload: MultiLoad is able to load data into multiple tables simultaneously and can perform different types of tasks such as INSERT, UPDATE, DELETE, and UPSERT. It is best suitable for operations such as bulk update, delete,  upsert and as well as complex interface manipulations.
  • Fastexport: This utility exports Teradata data into flat files. Alternatively, the utility can export the data as reports. Using joins, the utility can extract data from several tables at one time
  • Teradata Parallel Data Pump(TPump): It helps maintain Teradata Databases by updating, deleting, inserting, and upserting data in databases. Multiple changes can be made at the same time.
  • Teradata Parallel Transport (TPT): This is an all-in-one tool to load and export data into/from Teradata databases. Among existing utilities like fastload, fastexport, multiload, and TPUMP, Teradata recommends TPT.

5. Explain different string manipulation operators and functions associated with Teradata.

The Teradata String functions are used to manipulate strings and are also compatible with the ANSI standard. Additionally, it supports some standard string functions as well as the Teradata extensions to those functions.

  • SUBSTRING: It extracts a selective portion of the long string (ANSI standard).

Example:  Consider a string “InterviewBit” from a table.

SELECT SUBSTRING('Interviewbit' FROM 1 FOR 5);

Output: 

Inter
  • POSITION: An individual character in a string (ANSI standard) can be located.

Example:

SELECT POSITION("r" IN "InterviewBit");  

Output:

5
  • TRIM: Removes (trims) blank space from a specified string.

Example: 

SELECT TRIM("  InterviewBit  ");  

Output:

InterviewBit
  • UPPER: The string is converted to uppercase.

Example:

SELECT UPPER("InterviewBit");  

Output:

INTERVIEWBIT
  • LOWER: The string is converted to lowercase.

Example:

SELECT LOWER("INTERVIEWBIT");  

Output:

interviewbit

6. Why Multi-load doesn't support USI (Unique Secondary Index) instead of NUSI (Non-Unique Secondary Index)?

Teradata allows all AMP (Access Module Processors) to operate independently. With USI, the index subtable would have to be present on multiple AMPs, which would require communication between AMPs. But with NUSI, the index subtable would be present on the same AMP as the data row, which would allow that AMP to be handled independently. This is why NUSI is supported by multi-load.

7. What is the process of restarting MLOAD Client System after its failure?

Teradata MultiLoad jobs that failed or were aborted because of client system failure can be restarted depending on whether they stopped during the application phase (apply all DML (Data Manipulation Language) operations). 

  • When the Teradata MultiLoad job was stopped prior to or after the application phase, you should restart the job as it is, without making any changes to the script. Terradata MultiLoad uses the entries from the restart log table to determine its stopping point and begins processing at that point. '
  • When a Teradata MultiLoad job is aborted or the client system fails during the application stage, resolve the issue associated with failure and then restart the job again. 

8. What is the process of restarting MLOAD Teradata Server after execution?

In general, the process begins from the last known checkpoint, and after the MLOAD script is executed, the server is restarted.

9. What is performance tuning and why is it important?

Specifically, Teradata Performance tuning involves identifying all bottlenecks in the database and resolving them. The bottleneck does not cause errors, but it certainly causes delays in data retrieval from the database. A company without performance tuning for its database could suffer from imperfect responses to queries, causing unnecessary difficulties in accessing its data and other issues. 

The reasons for consistent performance tuning are as follows:  

  • Increasing the speed of data retrieval: A database that isn't optimized can cause retrieval of data to be slower if you have a lot of data. Performance tuning allows you to build indexes and fix problems that could delay the retrieval of your data.
  • Avoiding coding loops: Coding loops (repeating a certain block of code repeatedly until a certain condition is met) can increase the load on your database. SQL queries are executed multiple times if present in a loop, however if you move them out of the loop, performance can be improved since they are executed once instead of many times.
  • Optimize SQL query performance: In order to enhance query performance, it is best to avoid correlated subqueries, to avoid overusing select (and instead declare each column individually), and to avoid temporary tables if possible. The purpose of performance tuning is generally to reduce the response time for the end user or the latency.

10. What is Skewness in Teradata?

As a statistical concept, "skewness" refers to the row distribution on AMPs (Access Module Processors). In data distribution, the Skew Factor refers to the distribution of table data among AMPs. Skewed factor of 0 indicate that the data is evenly distributed among the AMP's. In the case of highly skewed data, it means that some AMPs have more rows and some have very few, i.e., the distribution is not even. The Skew Factor is high in this case (unequal distribution of data), affecting performance and Teradata's parallelism.  

Choosing the right index can control the skewness of the data distribution. Ideally, you should choose a Primary Index that contains as many unique values as possible so as to avoid skewness. 

11. What do you mean by Spool space in Teradata? Write its usage.

Spool space refers to the unused space in the system in which intermediate results from a SQL query are stored. Query execution is not possible for users without spool space. Amount of spool space is divided based on the number of AMPs, but each AMP has only a fraction of the space available. In the event that the per AMP limit is exceeded, the user will receive a warning that they have run out of spool space. 

Example: Let's say the user was assigned a spool space of 200000000 bytes. This is the maximum space the user is allowed to use and it is distributed evenly across all AMPs as shown below. 

12. Explain nodes in Teradata.

The Teradata System consists of nodes, which are the basic units. Nodes are individual servers in a Teradata system. Each node comprises a separate operating system, CPU, memory, Teradata RDBMS software, and disk space.

13. Mention a few ETL (Extract, Transform and Load) Tools that come under Teradata.

There are several ETL (Extract, Transform, and Load) tools that are commonly used in Teradata as follows: 

  • DataStage 
  • Informatica
  • SSIS (SQL Server Integration Services)

14. Is Teradata an ETL tool or a database?

Teradata is not an ETL (Extract, Transform and Load) tool. Teradata is an open-source RDBMS (relational database management system) that runs on different operating systems, including Windows, Unix, and Linux. Teradata is a relational database management system capable of handling data loads in terabytes. The system is capable of handling large-scale data warehouse applications.

15. What are the benefits of using ETL tools over Teradata?

Extract, Transform, Load (ETL) means three distinct tasks for managing databases.  ETL tools offer some advantages over Teradata, including: 

  • Support for multiple heterogeneous destinations and sources of data.  
  • The ETL tools provide a full-featured GUI that simplifies the debugging process for managing databases. 
  • ETL tools offer the advantage of being able to reuse components. Thus, if the main server is updated, all corresponding applications connected to the server are automatically updated.  
  • ETL tools can be used to pivot (transform rows into columns) and de-pivot (transform columns into rows).

16. What do you mean by caching in Teradata?

In simplest terms, caching is a benefit of using Teradata that involves storing frequently used data and information in cache memory so that, when the next time the data is needed, it can be retrieved directly from memory instead of requiring the application to generate it again. In Teradata, caching remains in the same order, which means that it does not change very often. In fact, caches are typically shared among several applications.

17. Explain channel driver.

As the name implies, a channel driver acts as a means of communication between PEs and the applications that run on channels connected to clients. The Teradata Gateway acts in much the same way as a channel driver, acting as a conduit between the Parse Engine and applications connected to network clients. 

Teradata Interview Questions for Experienced

18. Mention the procedure via which we can run Teradata jobs in a UNIX environment.

In order to run Teradata utilities under UNIX, you simply need to execute them in the following manner:

  • BTEQ -    $Sh> BTEQ< [ Script path]/TEE< LOGFILE PATH > (OR) BTEQ -    $Sh> BTEQ< [ Script path > [LOG FILE PATH]
  • FLOAD -    Sh> FASTLOAD< [ Script path]/TEE< LOGFILE PATH > (OR) FLOAD -    $Sh> FASTLOAD < [ Script path > [LOG FILE PATH]
  • MLOAD -    $Sh> Mload < [ Script path]/TEE< LOGFILE PATH > (OR) MLOAD -    $Sh> Mload < [ Script path > [LOG FILE PATH]
  • TPUMP -   $Sh> TPUMP < [ Script path]/TEE< LOGFILE PATH > (OR) TPUMP -    $Sh> TPUMP < [ Script path > [LOG FILE PATH]
  • FEXP -    $Sh> FEXP < [ Script path]/TEE< LOGFILE PATH > (OR) FEXP -    $Sh> FEXP < [ Script path > [LOG FILE PATH]

19. State difference between Teradata and Oracle.

Among the most popular RDBMS systems are Teradata and Oracle:

  • Teradata: Teradata is an open-source RDBMS (relational database management system) that runs on different operating systems, including Windows, Unix, and Linux. The Teradata architecture is based on Massive Parallel Processing (MPP), and is therefore widely used for large-scale data warehouse applications.   
  • Oracle: Oracle is a well-known Relational Database Management System (RDBMS), also referred to as Oracle database or OracleDB. OracleDB runs seamlessly on various platforms such as Windows, Unix, Mac, etc. Oracle was the first database designed exclusively for business and enterprise grid computing. As a result of Real Application Clustering and Portability features, Oracle databases are scalable to meet the demands of workloads. Enterprises choose it as a cost-effective solution to their application and data management problems.
Teradata  Oracle 
As this database is built on a shared-nothing architecture, the disks, Teradata nodes, and AMPs (Access Module Processors) are all independent and do not share resources with others. Oracle architecture is based on Shared Everything architecture.  
In most cases, Oracle is used as an online backend application. It handles inserts, updates, and deletions in transactions in the database. In most cases, Oracle is used as an online backend application. It handles inserts, updates, and deletions in transactions in the database.
The architecture of Oracle allows any machine to access any data. Oracle is therefore a good fit for OLTP (Online Transaction Processing).  The architecture of Oracle allows any machine to access any data. Oracle is therefore a good fit for OLTP (Online Transaction Processing). 
It is complex to set up, execute, and maintain.  It is complex to set up, execute, and maintain. 

20. What steps will you take if the Fast Load Script does not run in a reliable manner?

In the event the Fast Load Script does not work for you, and only the error tables are available to you, then there are two ways to restart: 

  • Getting the old file to run again: Be sure not to completely remove the error tables. Instead, fix the errors in the script or file, and then execute it again. 
  • Running a new file: Alternatively, you can restart by using the ending loading and beginning statements. As a consequence, the lock that was put on the target table can be removed and the record can also be removed from the fast-log table. If that works, you can run the whole script again. Or, you might drop the table and recreate it.

21. What's the best way to check the current version of Teradata?

For determining the current version and release of software, there are several methods. You can view the current Teradata Database version and release level via any console or client session by running the following query: 

SELECT * FROM DBC.DBCInfoV;  

In Teradata, the DBC.DBCINFO table contains information relevant to the Teradata release and version. Here, 

  • DBC is a Database computer.
  • DBCInfoV is the table where database information is saved.

22. Explain various table types supported by Teradata.

The following types of tables are supported by Teradata:  

  • Permanent Table: This table contains all the data inserted by the user and stores the data permanently after it has been entered in the table. The content of permanent tables can be shared between different sessions and users. It is the default table. 
  • Volatile Table: When data is added to a volatile table, it is only retained during the current session, and the table is automatically dropped after the user session ends. They are generally used to store data that is intermediate during data transformation. 
  • Global Temporary Table: Other types of permanent tables include global temporary tables. This type of table stores the globally used values for the entire application, and its lifetime is dependent on the session of the user. Upon the end of the session, the table is deleted/dropped. 
  • Derived Table: Among all the tables, derived tables have the shortest lifetime. During query execution, intermediate results of queries are stored in these tables. Tables are created, used, and then dropped in the course of a query.

23. List some of the most commonly used BTEQ scripts.

The following are some common BTEQ scripts:    

  • LOGON: This allows you to log in to the Teradata system.   
  • ACTIVITYCOUNT: It specifies how many rows were affected by the last query performed.   
  • ERRORCODE: This returns the status code of the last query performed.  
  • DATABASE: This sets the default database.   
  • LABEL: It specifies a label for a set of SQL commands.   
  • RUN FILE: This command executes the query contained in a file.   
  • GOTO: Turns the control over to a label.   
  • LOGOFF: This terminates all sessions and logs you off from the database.   
  • IMPORT: It specifies the path to the input file and initiates import to the input file.   
  • EXPORT: It specifies a path to the output file and initiates export to the output file.

24. What is PDE (Parallel Data Extension)?

Between the operating system and Teradata Database lies a software layer called Parallel Database Extensions (PDE). This enhances the speed and scalability of Teradata Database by supporting parallelism across system nodes. Through PDE, Teradata Database is capable of: 

  • Parallel processing 
  • Prioritize and manage Teradata Database workloads. 
  • Manage memory, I/O (Input/Output), and messaging system interfaces consistently across multiple OS platforms, etc. 

25. What is PPI (Partitioned Primary Index)?

As its name suggests, Partitioned Primary Indexes (PPI) are one of Teradata's powerful features that allow users to access a specific portion of a table instead of the whole table. Essentially, PPI is an indexing mechanism that will help improve query performance. When used for data distribution, PPI works the same way as Primary Index, and partitions are created based on range or case as specified in the table. In partitioned primary indexes (PPIs), rows are sorted according to the partition number. 

  • By using PPI, you can avoid a full table scan and only access required partitions. 
  • Using PPI prevents the use of secondary indexes, as well as additional I/O maintenance. 
  • It enables quick access to a subset of a large table. 
  • PPI enables removing old data and adding new data to a table with ease. 

26. List out the different forms of locks available in Teradata.

Every object in the database is shared between multiple users who access the data simultaneously. If, for example, a user was updating a table and another user tried to view it simultaneously, the second user would receive inaccurate and inconsistent information. Locking mechanisms have been invented to avoid this kind of data inconsistency or data corruption. Having a lock prevents multiple users from changing the same data at the same time, reducing the possibility of data corruption/data inconsistency. In general, Teradata comprises four types of locks as follows: 

  • Exclusive: Teradata applies an exclusive lock whenever anyone attempts to modify the structure of any objects ( like a table or view). In other words, Teradata holds the lock on that object and no other user is permitted to access or manipulate that object until the lock is released from the object. 
  • Write: Table that is secured with a write lock can only be modified by the lock owner. If other users try to insert, delete, or update a table, a write lock will be applied. With a write lock, other users cannot modify the same table.  
  • Read: When a user submits a SELECT query, a read lock is applied.  Multiple users can hold READ locks on an object, during which the system does not permit changes to that object. Hence, data integrity is thus maintained since the data in the tables cannot be altered when read locks are applied.  
  • Access: It only prevents exclusive access. ACCESS locks do not restrict access to another user except for when an EXCLUSIVE lock is required to prevent others from accessing. 

27. Explain fallback in Teradata.

As the name implies, the fallback feature stores the second copy of rows from a table on a different AMP, which is called the Fallback AMP. In the event that one of the AMPs fails, the fallback rows are accessed. As a result, even if one of the AMPs fails, data can still be accessed since a fallback AMP is available.  

In the below diagram, you can see how a duplicate (fallback) copy of each row is stored in another AMPs. In AMP 0, a duplicate copy of the primary row of AMP1 (3) and AMP2 (6) is stored in fallback rows. Similarly, in AMP 1, a duplicate copy of the primary row of AMP0 (1) and AMP2 (5) is stored in the fallback row. All the AMPs stored fallback values in the same way.

28. How to find duplicate records in a table?

The DISTINCT statement or GROUP BY statement can be used to identify duplicate records in a table. 

SELECT DISTINCT column 1, column 2... FROM tablename;  

OR  

SELECT column 1, column 2,... FROM tablename GROUP BY column 1, column 2....;

Example: Consider the following student table.

Roll_No First_Name Last_Name
101 Asha Bisht
102 Rahul Patidar
103 Vishal Bairagi
104 Girja Shankar
105 Asha Dange

DISTINCT

Here is an example of a DISTINCT statement.

SELECT DISTINCT First_Name FROM tablename;  

Output: Following is the output of the above query when executed. With a distinct statement, duplicate values can be eliminated.

First_Name
Asha
Rahul
Vishal
Girja

29. Explain the set operators in Teradata.

Operators such as SET combine the results from multiple SELECT statements. It may seem similar to Joins, but Joins combine columns from different tables, whereas SET operators combine rows from different tables. The following set operators are supported by Teradata SQL: 

  • UNION: It combines results from several SELECT statements. Duplicate values are ignored.
  • UNION ALL: It combines results from several SELECT statements, including duplicate rows.
  • INTERSECT: It combines results from several SELECT statements. The function can be used to return those rows in the first SELECT statement that match those in the second SELECT statement. Basically, it returns the rows which exist in both SELECT statements.
  • MINUS/EXCEPT: It combines results from several SELECT statements. The function can be used to return those rows in the first SELECT statement that are not matched with those in the second SELECT statement.

Example: Assume the student table below is Table1 and the attendance table is Table2.

Roll_No First_Name Last_Name DateofBirth
101 Asha Bisht 18/1/1996
102 Rahul Patidar 1/1/1997
103 Vishal Bairagi 25/9/1997
104 Girja Shankar 14/6/1994
105 Sonal Dange 20/8/1997
       
       
       
       
       

1. UNION

Following is a UNION query that combines the Roll_No values from both Table1 and Table2.

SELECT Roll_No FROM Table1  
UNION  
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. It does not includes duplicate values.

Roll_No
101
102
103
104
105

2. UNION ALL

Here is an example of a UNION ALL statement.

SELECT Roll_No FROM Table1  
UNION ALL   
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. It includes duplicate values as well.

Roll_No
101
102
103
104
105
101
102
103
104

3. INTERSECT

Here is an example of an INTERSECT statement. This command returns the Roll_No value that exists or present in both tables i.e., Table1 and Table2.

SELECT Roll_No FROM Table1  
INTERSECT   
SELECT Roll_No FROM Table2;  

Output: Following is the output of the above query when executed. The Roll_No105 is excluded because there is no such record in Table2.

Roll_No
101
102
103
104

4. MINUS/EXCEPT

Here is an example of a MINUS/EXCEPT statement.

SELECT Roll_No FROM Table1  
MINUS
SELECT Roll_No FROM Table2;

Output: Following is the output of the above query when executed. Only Roll_No105 is included because it is present in Table1 but there is no such record in Table2.

Roll_No
105

30. In Teradata, what is the purpose of using CASE Expression?

When a CASE expression is used, each row is compared with a condition or WHEN clause, and the result of the first match is returned. Else, the result from the ELSE clause will be returned if there are no matches.

Syntax: 

CASE <expression>  
WHEN <expression> THEN result-1  
WHEN <expression> THEN result-2  
ELSE  
  Result-n  
END 

31. What is the importance of UPSERT command in Teradata?

Teradata enables update and insert operations to be performed simultaneously on a table from another table using UPSERT Command. Updates are made if the update condition matches in another table and unmatched rows are inserted into the table if the update condition does not match.  

UPDATE-ELSE-INSERT syntax:

UPDATE department SET budget_amount = 60000 WHERE department_number = 600 ELSE INSERT INTO department(department_number, department_name, budget_amount, manager_employee_number) VALUES(600, 'Test Dept', 60000, NULL);

This statement updates the row where department_number matches 600 to set budget_amount to 60000. In the event that no match is found, a new row will be inserted.

32. What's the purpose of this below query?

SELECT HASHMAP (HASHBUCKET(HASHROW(emp_id))), COUNT(*) FROM Employee GROUP BY 1;

With the above query, you can determine the number of rows in each AMP for a given database table. HASHAMP, HASHBUCKET, and HASHROW indicate the number of rows in the AMPs when used together.

Conclusion 

Among the most popular database management systems are Teradata. Employers are seeking candidates with extensive knowledge of its architecture and ability to use it efficiently. It is important to prepare for Teradata Interview Questions if you are seeking employment related to Teradata. 

It is true that every interview is different depending on the position. In this article, we have provided the important Teradata Interview Questions and Answers for freshers and experienced, which will help you excel in your interview. There are questions on architectural basics, utilities related to Fastload, Multiload, BTEQ, SQL, and Stored Procedures. 

Ready to ace your Teradata interview now? 

Teradata MCQ Questions

1.

Among the following components, which one performs input conversion?

2.

Can Teradata be run on any of the following operating systems?

3.

In the event that spool space is zero, _________.

4.

Teradata has been described as a _____ architecture. 

5.

What kind of lock allows other users to read data concurrently, but not modify it?

6.

Which of the following Teradata utilities load into multiple tables simultaneously?

7.

Which of the following set operators combines results from several SELECT statements, including duplicate rows? 

8.

What are the table types supported by Teradata?

9.

Which of the following types has the shortest lifetime?

10.

BTEQ stands for ___.

11.

Teradata is an ETL Tool. True or False.

12.

Parsing Engine receives client queries and prepares an efficient execution plan.

Get Placed at Top Product Companies with Scaler Know More 
Get Placed at Top Product Companies with Scaler
Sat transparent 640a34d454880bf68e3bfdf33f2389f2214043f59ad18b4c7f7b114e834fb257.svg

Point markers b3add1cc88e4996b2df6e0aedb9f0d1b65fa73c51b7ada8fbee3895a2aa11802.svg Personalised feedback report with solutions
Point markers b3add1cc88e4996b2df6e0aedb9f0d1b65fa73c51b7ada8fbee3895a2aa11802.svg Real life Interview Questions
Point markers b3add1cc88e4996b2df6e0aedb9f0d1b65fa73c51b7ada8fbee3895a2aa11802.svg Identify exact topics to improve

Your feedback is important to help us improve.
Free Mock Assessment
Help us know you better for the best experience
Current Employer *
Enter company name
College you graduated from *
Enter university name
Phone Number *
OTP will be sent to this number for verification
+91
+91
+247
+376
+971
+93
+1
+1
+355
+374
+244
+54
+1
+43
+61
+297
+358
+994
+387
+1
+880
+32
+226
+359
+973
+257
+229
+590
+1
+673
+591
+599
+55
+1
+975
+267
+375
+501
+1
+61
+243
+236
+242
+41
+225
+682
+56
+237
+86
+57
+506
+53
+238
+599
+61
+357
+420
+49
+253
+45
+1
+1
+213
+593
+372
+20
+212
+291
+34
+251
+358
+679
+500
+691
+298
+33
+241
+44
+1
+995
+594
+44
+233
+350
+299
+220
+224
+590
+240
+30
+502
+1
+245
+592
+852
+504
+385
+509
+36
+62
+353
+972
+44
+246
+964
+98
+354
+39
+44
+1
+962
+81
+254
+996
+855
+686
+269
+1
+850
+82
+965
+1
+7
+856
+961
+1
+423
+94
+231
+266
+370
+352
+371
+218
+212
+377
+373
+382
+590
+261
+692
+389
+223
+95
+976
+853
+1
+596
+222
+1
+356
+230
+960
+265
+52
+60
+258
+264
+687
+227
+672
+234
+505
+31
+47
+977
+674
+683
+64
+968
+507
+51
+689
+675
+63
+92
+48
+508
+1
+970
+351
+680
+595
+974
+262
+40
+381
+7
+250
+966
+677
+248
+249
+46
+65
+290
+386
+47
+421
+232
+378
+221
+252
+597
+211
+239
+503
+1
+963
+268
+290
+1
+235
+228
+66
+992
+690
+670
+993
+216
+676
+90
+1
+688
+886
+255
+380
+256
+1
+598
+998
+39
+1
+58
+1
+1
+84
+678
+681
+685
+383
+967
+262
+27
+260
+263
+979
Change Number
Edit
Resend OTP
By Continuing I agree to be contacted by Scaler in the future.