Monday, 9 April 2018

Most useful Teradata interview questions -- PART - IV



Most useful Teradata interview questions -- PART - IV



 151.How can we add new columns in a existing table after specific column ?
  No, you can’t. As It’s a relational database, Suppose, if we need the new column to be in a specific position as a 1st column or in middle, the you need to do like this,
  • 1) RENAME EXISTING TABLE TO BACKUP TABLE
  • 2) CREATE THE NEW TABLE WITH THE SPECIFIED COLUMN POSITION THAT YOU WANT
  • 3) INSERT INTO NEW TABLE SEL OLD COLUMNS FROM BACKUP TABLE (You should use SEL COLUMN LIST, Since New column will not be in the backup table)
  • 4) DROP THE BACKUP TABLE.
152.How to create a table with an existing structure of another table with or without data and also with stats defined in Teradata?
  • CREATE TABLE new_TABLE AS old_TABLE WITH DATA
  • CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA
  • CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS
153.How to find the duplicate rows in the table in Teradata?
  • SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name HAVING COUNT (*)>1; Also  DISTINCT will be useful. If both DISTINCT and COUNT(*) returns same number then there are no duplicates.
  • Method : 1
    • Create table tmp as ( select * from table qualify row_number() over (partition by pk_table order by pk_table) = 1 ) with data;
    • Delete from table all;
    • Insert into table select * from tmp;
    • Drop table tmp; ————
  • Method : 2
    • If we are having huge data in a file then simply load in temp table by using Fastload(only file to table) where it wont allow duplicates.
  • Method : 3
    • Load in a SET table and rename it into main table
Delete DML wont allow order by function in select query and also if you sub query it could be chance of deleting entire rows, so we can just read the duplicate record with the below query for deleting we need to follow above three methods.
sel * from table a qualify row_Number() over (partition by col1 col2 col3….coln order by col1 col2 col3….coln) > 1
154. How to calculate the tablesize , database size  and free space left in a database in teradata?
DBC.TABLESIZE and DBC.DISKSPACE are the systems tables used to find the space occupied. Below Query gives the table size of each tables in the database and it will be useful to find the big tables in case of any space recovery.
SELECT         DATABASENAME, TABLENAME,
SUM(CURRENTPERM/(1024*1024*1024)) AS “TABLE SIZE”
FROM DBC.TABLESIZE
WHERE DATABASENAME = <‘DATABASE_NAME’>
AND TABLENAME = <‘TABLE_NAME’> GROUP BY 1,2;
Below query gives the total space and free space available in a database.
SELECT DATABASENAME DATABASE_NAME,
SUM(MAXPERM)/(1024*1024*1024) TOTAL_PERM_SPACE, SUM(CURRENTPERM)/(1024*1024*1024) CURRENT_PERM_SPACE,
TOTAL_PERM_SPACE-CURRENT_PERM_SPACE as FREE_SPACE
FROM DBC.DISKSPACE
155. Query to  gives the skewness for all tables.
SELECT
ts.DatabaseName ,ts.TableName,td.CreateTimeStamp AS Created ,td.LastAlterTimeStamp AS LastAltered,td.AccessCount ,td.LastAccessTimeStamp AS LastAccess ,
SUM(ts.CurrentPerm) AS CurrentPerm ,
SUM(ts.PeakPerm) AS PeakPerm,
(100 – (AVG(ts.CurrentPerm)/MAX(ts.CurrentPerm)*100)) AS SkewFactor
FROM DBC.TableSize ts JOIN DBC.Tables td
ON ts.DatabaseName = td.DatabaseName
AND ts.TableName = td.TableName
GROUP BY 1,2,3,4,5,6;
156.Query to find the Primary key, Foreign key, primary Index,PPI for the Database?

Select DatabaseName, TableName ,columnName,
Case When IndexType=’K’ Then ‘Primary Key’
When IndexType=’S’ Then ‘Secondary Index’
When IndexType=’P’ Then ‘Primary Index’
When IndexType=’Q’ Then ‘PPIndex’
When IndexType=’J’ Then ‘Join Index’
End    as implimented _Index From DBC.Indices Where TableName in ( Select distinct TableName From DBC.Tablesize Where DatabaseName <>’DBC’ And CurrentPerm>0 ) Order by 1,2,3

157.How to find number of records present in Each AMP or a Node for a given Table through SQL?
Select HASHAMP(HASHBUCKET(HASHROW(PRIMARY_INDEX_COLUMNS))) AS AMP_NO,COUNT(*) 
from DATABASENAME.TABLE_NAME GROUP BY
158. How to select 5th row from a table?
SELECT * FROM TABLENAME
QUALIFY ROW_NUMBER() OVER (ORDER BY COLUMN) = 5; 
159.how do you list all the objects available in given database?
  • select * from dbc.tables where databasename='<DATABASENAME>’;
  • help database <DATABASENAME’;
160.How to rename columns using views?
Replace  view EmployeeV( number,fullname,addr,phno,depnum,sal, expr) as locking row for access sel EmpNo , Name  , Address, Phone   , DeptNo   , Salary    , YrsExp from employee;
When we run sel * form employeeV, Only alias columns are displayed
161.How to select first N Records in Teradata?
SELECT BOOK_NAME, BOOK_COUNT, RANK(BOOK_COUNT) A FROM LIBRARY QUALIFY A <= 10
162.How to view every column and the columns contained in indexes in Teradata?
  • SELECT * FROM DBC.TVFields;
  •  SELECT * FROM DBC.Indexes;
163.How to find out list of indexes in Teradata?
IndexTypeDescription
PNonpartitioned Primary
QPartitioned Primary
SSecondary
Jjoin index
Nhash index
Kprimary key
Uunique constraint
Vvalue ordered secondary
Hhash ordered ALL covering secondary
Ovalued ordered ALL covering secondary
Iordering column of a composite secondary index
MMulti column statistics
DDerived column partition statistics
1field1 column of a join or hash index
2field2 column of a join or hash index
SELECT databasename, tablename, columnname, indextype, indexnumber, indexname FROM   dbc.indices ORDER BY databasename,  tablename, indexnumber;
164.How do you execute the given SQL statement repeatedly in BTEQ?
Select top 1* from database.table1; =n
Here “=n” is to run the previous sql statement, “n” number of times.
165.What is the command in BTEQ to check for session settings ?
The BTEQ .SHOW CONTROL command displays BTEQ settings.
166.How can you track Login Parameters of users in Teradata?
SELECT LOGDATE, LOGTIME, USERNAME, EVENT FROM DBC.LOGONOFF;
 167..How can you determine I/O and CPU usage at a user level in Teradata?
SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME) AS CPU, SUM(DISKIO) AS DISKIO FROM DBC.AMPUSAGEGROUP BY 1,2 ORDER BY 3 DESC;
168.BTEQ script to fetch the DDL’s of table/view/macro ?
/* This BTEQ script fetches the table DDL’s for the current DB */ /* Make sure you set the database before running the script */ /* Copy contents and paste it in a text file (eg getDDL.scp) */ /* in the prompt # bteq < getDDL.scp (to execute) */ .SET SIDETITLES OFF .SET WIDTH 254 .SET QUIET ON .os IF EXIST showDDL.tmp del showDDL.tmp.EXPORT REPORT file=showDDL.tmp SELECT ‘.SET TITLEDASHES OFF’ (TITLE ”); /*
Generate Table DDL’s */ SELECT ‘SHOW TABLE ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘T’ ORDER BY 1; /*
  • Generate Views DDL’s */ SELECT ‘SHOW VIEW ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘V’ ORDER BY 1; /*
  • Generate Macro DDL’s */ SELECT ‘SHOW MACRO ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘M’ ORDER BY 1; /*
  • Generate Stored Procedure DDL’s */ SELECT ‘SHOW PROCEDURE ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘P’ ORDER BY 1;
SELECT ‘.DEFAULTS’ (TITLE ”); SELECT ‘.EXPORT RESET’ (TITLE ”); .EXPORT RESET .OS IF EXIST DDLS.txt del DDLS.txt .EXPORT REPORT file=DDLS.txt .run file = showDDL.tmp .OS IF EXIST showDDL.tmp del showDDL.tmp/q
 169..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’
170.Examples for the INDEX function.
  • SELECT INDEX(‘Write’, ‘i’); Displays Result as ‘3’
  • SELECT INDEX(‘Write’, ‘te’); Displays Result as ‘4’

DATA WAREHOUSING

171. can we have an unconnected lkp to lookup a DB2 record against a Teradata record?
we can use an unconnected lookup to lookup on any DB because we specify the connection string details in the lookup transformation !
but some times we need to check the value which lookup is returning …..like it happens in teradata if we are having lookup on varchar column and record is coming from source system it never works because when informatica creates cache from there it adds space to the values.so we always have to use ltrim(rtrim)) function in lookup query.
173. How can we build data marts from FSLDM. is there any standard approach for this?Is FSLDM supports place ? .
Teradata is like all other DBMS, we can create as many database as required. Data Mart are basically subject oriented and mainly based on business KPIs which end-users generally like to measure. You have to refer business requirement document for designing data mart.
174. Does SDLC changes when you use Teradata instead of Oracle?
SDLC doesn’t change but physical data model changes. Physical data model(i.e table structure) needs to be re- designed according to the teradata database.
175. What is a common data source for the central enterprise data warehouse?
operational data stores (ODS)
176. What are two examples of an OLTP environment?
On Line Banking On Line Reservation(Transportation like Rail, Air etc.)
 1- ATM. 2- POS.
177. In which phase of the Active Data Warehouse evolution do you use data to determine what will happen?
In predicting phase. Analysts utilize the system to leverage information to predict what will happen next in the business to proactively manage the organization’s strategy.This stage requires data mining tools and building predictive models using historical detail. As an example, users can model customer demographics for target marketing.
178. what is the difference between start schema and Fsldm?
A star schema has one Fact tables and many dimensional tables to store the related. FSLDM (Financial services logical data model) is a model proposed and designed by NCR to cater the needs of financial sectors, specially the Banking domain. It has many Fact tables and dimension tbls. The major fact tbls include Party (Customers of the bank), Product (Services offered by the bank), Event (An event occurred ex: An ATM transaction), Agreement (A deal between the party and the bank for a product)etc… The FSLDM can be customized to cater to bank’s specific needs.
FSLDM -> 3rd normal form Star Schema –> Denormalized for query performance
178.What is a pseudo key? What is its use?
 If a table in a database either has no natural key or the natural key is so long that using it as the key would be inefficient then we need to consider using a pseudokey instead. Pseudokeys are usually given the same name as the table they belong to with _id added to the end of the name. They usually are defined to contain a number and use auto increment in mySQL or the equivalent in other versions of SQL to dynamically allocate a value when new rows are created.
179.What is the difference between filter and router transmissions?
A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition.
However, a Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output group.
at

No comments:

Post a Comment