Teradata Interview Questions and answers for Experienced

Question.1   My table got locked during MLOAD due to a failed job. What do I do to perform other operations on it?
Answer:   Using RELEASE MLOAD. It removes access locks from the target tables in Teradata. It must be entered from BTEQ and not from MultiLoad.  To proceed, you can do RELEASE MLOAD.
Question.2  How to find duplicates in a table?
Answer:   Group by those fields and select id, count(*) from table group by id having count (*) > 1. 

Question.3   How to you verify a complicated SQL?
Answer:  I use explain statement to check if the query is doing what I wanted it to do.
Question.4  Did u ever use UPPER Function?
Answer:  UPPER Function is used to convert all characters in a column to the same characters in upper case.
Question.5  What does a LOWER Function do?
Answer:   LOWER function is used to convert all characters in a column to the lower case characters.
Question.6   How do you see a DDL for an existing table?
Answer:  By using show table command.
Question.7  Which is more efficient GROUP BY or DISTINCT to find duplicates?
Answer:   With more duplicates GROUP BY is more efficient, if only a few duplicates exist DISTINCT is more efficient.
Question.8  Syntax for CASE WHEN statement?
Answer:    CASE value_expression_1 WHEN value_expression_n THEN scalar_expression_n END;
Question.9  What’s the difference between TIMESTAMP (0) and TIMESTAMP (6)?
Answer:     TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26)
Everything is same except that TIMESTAMP (6) has microseconds too.
Question.10  How do you determine the number of sessions?
· Teradata performance and workload
· Client platform type, performance and workload
· Channel performance for channel attached systems
· Network topology and performance for network attached systems.
· Volume of data to be processed by the application.
Question.11  What is node? How many nodes and AMPs used in your previous project?
Answer:  Node is a database running in a server. We used 318 nodes and each node has 2 to 4 AMPS.
Question.12  What is a clique?
Answer:   Clique is a group of disk arrays physically cabled to a group of nodes.
Question.13  Interviewer explained about their project (Environment, nature of work)?
Answer:  Listen to them carefully so that at the end of the interview you can ask questions about the project when you are given a chance to ask questions.
of the project.
Question.14   What is the difference between FastLoad and MultiLoad?
Answer:  FastLoad uses multiple sessions to quickly load large amount of data on empty table.  MultiLoad is used for high-volume maintenance on tables and views. It works with non-empty tables also. Maximum 5 tables can be used in MultiLoad.
Question.15  Have you used procedures?
Answer:   No. I have not used procedures. But I have expertise knowledge writing procedures. My company have not encouraged me to write procedures because it becomes single AMP operation, as such uses lot of resources and expensive in terms of resource and time.
Question.16  What is the purpose of indexes?
Answer:   An index is a mechanism that can be used by the SQL query optimizer to make table access more performant. Indexes enhance data access by providing a more-or-less direct path to stored data and avoiding the necessity to perform full table scans to locate the small number of rows you typically want to retrieve or update.
Question.17  What is primary index and secondary index?
Answer:      Primary index is the mechanism for assigning a data row to an AMP and a location on the AMP’s disks. Indexes also used to access rows from a table without having to search the entire table.
Secondary indexes enhance set selection by specifying access paths less frequently used than the primary index path. Secondary indexes are also used to facilitate aggregate operations. If a secondary index covers a query, then the Optimizer determines that it would be less costly to accesses its rows directly rather than using it to access the base table rows it points to. Sometimes multiple secondary indexes with low individual selectivity can be overlapped and bit mapped to provide enhanced
Question.18  Why primary and secondary index is used?
Answer:   Refer answer from previous question.
Question.19  What are the things to considered while creating secondary index?
Answer:  Creating a secondary index causes Teradata to build a sub-table to contain its index rows, thus adding another set of rows that requires updating each time a table row is inserted, deleted, or updated. Secondary index sub-tables are also duplicated whenever a table is defined with FALLBACK, so the maintenance overhead is effectively doubled.