Wednesday 13 June 2012

TERADATA INTERVIEW QUESTIONS

Recent collection of questions




1.How do you find the list of employees named "Madhu" in an Party table without using Like operator??

Above question looks like confused

Yes ,another way by which we can find names/patterns without using like operator.

By using "BETWEEN" , we can find the list of customer named Madhu

sel * from party where name between 'M' and 'N';

But at times usage of between is tricky, if there are other customer Names starts with M also will be listed.

2.What is the acceptable range for skew factor in a table?
There is no particular range for skew factor.  In case of production systems, it is suggested to keep skew factor between 5-10.  

There are various considerations for skew factor
- Number of AMPS
- Size of row in a table
- number of records in a table
- PI of a table
- Frequent access of table (Performance consideration)
- whether table getting loaded daily /monthly or how frequently data is being refreshed.

3.How do you list all the objects available in given database?

 select * from dbc.tables where databasename='<DATABASENAME>';

 By running a normal help command on that database as follows.  
help database <DATABASENAME';

4.How to rename columns using views?

Create a view which is a subset of the Party table as follows.
Renaming columns in views will help increase security of sensitive tables and hiding columns under alias names

Replace  view Partyview ( number,fullname,addr,phono,codenum) as 
locking row for access  
sel
partyNo ,                        
Name  ,                        
Address,                       
Phone   ,                      
CodeNo                   
from Party;                

If we Run this below query
 sel * form party.view, Only alias columns are displayed .

5.What are Restrictions on Views in Teradata?
An index cannot be Created on a view.
It cannot contain an ORDER BY clause.
All the derived columns and aggregate columns used in the view  must have an AS clause (alias defined).
A view cannot be used to UPDATE if it contains the following :
        – Data from more than one table (JOIN VIEW)
        – The same column twice
        – Derived columns
        – A DISTINCT clause
        – A GROUP BY clause

6. Built-in functions used in teradata ?

The main functionality of built in functions is  that they do not need any arguments or paramaters and can be used directly with select to return system values.

• SESSION: – Returns a number for the session for current user .
• TIME: – this function provides the current time based on a 24-hour day
• USER: –  This one gives the user name of the current user.
• ACCOUNT: – display's your Teradata Account information 
• CURRENT_DATE: – Returns the current system date 
• CURRENT_TIME: - This function returns the current system time and current session ‘Time Zone’ displacement. 
• CURRENT_TIMESTAMP: - Returns the current system timestamp  with TimeZone
• DATABASE: –  It  returns the name of the default database for the current user.
• DATE: – same as Current_DATE   and is teradata built in .

7.How do you know whether table is locked or not?

Simple just run this query on the table.

Lock Table DBNAME.TABLENAME write nowait
Select * from DBNAME.TABLENAME;

If this table is locked currently then , then the above statement would fail as Nowait will not wait for locks to be released on that  table .

one more way


You can find it by using Show locks utility in Teradata Manager Tool.

You can use Teradata Manager -> Administrator -> Database Console Utility -> Show locks

8.what are the  different date formats available in Teradata system?

Two different Date formats are

 The Teradata default format is: 
YY/MM/DD

The ANSI display format is: 
YYYY-MM-DD


9.How to handle nulls in Teradata?How many columns can be there in a table???

1. Use zeroifnull, nullifzero in select and NULL in insert directly.
2. 256 columns max per table.

10.What are the scenarios in which Full Table Scans occurs?

1. The where clause in SELECT statement does not use either primary index or secondary index
2. SQL Statement which uses a partial value (like or not like), in the WHERE statement.
3. SQL Statement which does not contain where clause.
4. SQL statement using range in where clause. Ex. (col1 > 40 or col1 < =10000)

11.What is a role? Profile?

A role is a set of access rights which can be assigned to the users.   They indirectly help in performance by reducing the number of rows entered in DBC.accessrights

A profile contains set of user parameters  like accounts, default database, spool
space, and temporary space for a group of users

To assign the profile to a user, use the AS PROFILE modified in a CREATE USER or
MODIFY USER statement:
MODIFY USER username AS PROFILE=profilename ;
To remove a profile from a member but retain the profile itself:
MODIFY USER username AS PROFILE=NULL ;

12.How can you track Login Parameters of users in Teradata?

You can view all these parameters in this Data Dictionary Table DBC.LOGONOFF
SELECT LOGDATE, LOGTIME, USERNAME, EVENT FROM DBC.LOGONOFF;

13.Why are AMPs and PEs called as vprocs ?

AMPs and PEs are implemented as “virtual processors - vprocs”. 
They run under the control of PDE and their number is software configurable.
AMPs are associated with “virtual disks – vdisks” which are associated with logical units (LUNs) within a disk array.

Vprocs:Virtual process From PE to AMP (This is the network root via MSP(message passing layer),The processing data will store in Disks(These are Physical disks),Each Amp have too many P.disks,to migrate these P.disks The Bynet Network maintains Virtual disks.These V.disks will responsible for data migration.hence they are called as Virtual Process(VPROCS).

14.How does Hashing happens in Teradata?

  Hashing is the mechanism through which data is distributed and retrieved to/from AMPs.
 Primary Index (PI) value of a row is the input to the Hashing Algorithm.
 Row Hash (32-bit number) value is the output from this Algorithm.
 Table Id + Row Hash is used to locate Cylinder and Data block.
 Same Primary Index value and data type will always produce same hash value.
 Rows with the same hash value will go to the same AMP.

So data distribution depends directly on the Row Hash uniqueness; be careful while Choosing Indexes in Teradata.


15.Define POSITION Function?

The POSITION function Returns the actual position of the character which occurs first. POSITION function is ANSI standard.

Teradata has an equivalent function called INDEX.

Both the POSITION and INDEX functions returns position of character's first occurrence in a string.

Examples for the POSITION function

SELECT POSITION( 'e' IN 'Read');  Displays Result as '2' 
SELECT POSITION( 'ad' IN 'Read'); Displays Result as '3' 
SELECT POSITION( 's' IN 'Read');  Displays Result as '0'  

Examples for the INDEX function.

SELECT INDEX('Write', 'i');    Displays Result as '3'   
SELECT INDEX('Write', 'te');   Displays Result as '4'   

16.Explain LOB?

It is possible to create functions which operate on large object data types.
 The following library functions exists to provide the ability use LOB's as function input or output:

• FNC_GetLobLength - acquire Lob length
• FNC_LobAppend - add to Lob in memory
• FNC_LobClose - close a context to a Lob
• FNC_LobOpen - open a context to a Lob
• FNC_LobRead - read some or all
• FNC_LobRef2Loc - convert a Lob Reference to a Locator 
• FNC_LobLoc2Ref - convert a Locator to a Lob Reference

Example :
The CREATE FUNCTION syntax permits BLOB's or CLOB's to be the data type of an argument or a return value

CREATE FUNCTION JPEG_CROP
(
Image BLOB AS LOCATOR 
Width INTEGER, 
Height INTEGER, 
X_Offset INTEGER, 
Y_Offset INTEGER
RETURNS BLOB AS LOCATOR
LANGUAGE C
NO SQL
EXTERNAL;