Explore topic-wise InterviewSolutions in .

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.

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 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:

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.

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: 

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.

1. UNION

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

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

2. UNION ALL

Here is an example of a UNION ALL statement.

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

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.

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

4. MINUS/EXCEPT

Here is an example of a MINUS/EXCEPT statement.

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.

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

OR  

Example: CONSIDER the FOLLOWING student table.

DISTINCT

Here is an example of a DISTINCT statement.

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

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.

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. 

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. 

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. 

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.

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.

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: 

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.

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.

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.

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]