Monday, 9 April 2018

Most useful Teradata interview questions -- PART - III



Most useful Teradata interview questions -- PART - III


141.  What’s the difference between TIMESTAMP (0) and TIMESTAMP (6)?
     TIMESTAMP (0) is CHAR (19) and TIMESTAMP (6) is CHAR (26) Everything is same except that                    TIMESTAMP (6) has microseconds too.
142. How can we check the version of Teradata that we are using currently?
Just give the command .SHOW VERSION.
143. Mention the procedure via which, we can run Teradata jobs in a UNIX environment.
$Sh > BTEQ < [Script Path] > [Logfile Path] or
$Sh > BTEQ < [Script Path] TEE [Logfile Path]

 144. In BTEQ, how are the session-mode parameters being set?
.set session transaction BTET -> Teradata transaction mode
.set session transaction ANSI -> ANSI mode
145. While retrieving COUNT(*) in your SQL Query, it is giving numeric overflow error. How will you get the count now ?
  •  Try to cast count(*) to some other bigger datatype in order to avoid numeric overflow error:
  • SEL CAST(COUNT(*) AS DECIMAL(18,0)) FROM TABLE_NAME;
146. Will the count be same if we use COUNT(*), COUNT(20), COUNT(COLUMN_NAME) from some table ?
COUNT(*) and COUNT(20) will give same number of records however COUNT(COLUMN_NAME) will return number of records excluding NULL values. So if all the records are NOT NULL , then all three will result in same number of records.
147. Date column is not shown properly in TSA. How to overcome this issue?
CAST the date column into varchar, now the date column will be displayed in proper format.

 148.How to view recent query executed in teradata ?
 To view recent queries in Teradata there are two simple ways : First one is you can enable history if you are using some utility tool for running SQL’s like
  • Teradata SQL Assistant. To enable history in SQL Assistant go to view –> Show History. It will list down all the SQL executed on the system . The disadvantage is it will display only those queries which were executed from that system and not all the queries which were executed on Teradata server.
  • Second way of viewing recent queries in Teradata is to use DBC.QRYLOG
  • SEL * FROM DBC.QRYLOG;
  • This will display all the queries executed on the Teradata Server. Along with the queries it will also show which user executed the query and at what time . You can get much more information from this query. As the query will return huge number of rows if your Teradata Server is mature , you can add some filters in order to minimize the output rows.
HELPFUL DBA SQL
149. How to use analytical function as row number with qualify ?
  • We can use Teradata Qualify Row Number to have an alternate solution to Group by clause or using distinct.Qualify works on the final set of result rows.Qualify row number we can use in order to have a specific record or number of records.
  • SELECT EMP_NAME,EMP_LOCATION FROM EMP QUALIFY ROW_NUMBER() OVER ( PARTITION BY EMP_NAME ORDER BY DATE_EFFECTIVE DESC) = 1;
  • This query will result in getting all the EMPLOYEE NAMES with their latest location.ROW NUMBER will fetch the first record and we are ordering on the basis of date in descending order so latest record will be fetched for each employee.
  • Using Qualify Row Number is also very useful when we have very complex queries consisting of left , right joins involving several tables.In that case we can implement all our required rules and at the end we can refine the result set using Qualify Row Number.The Qualify ROW_NUMBER() can be very helpful in removing Unique Primary Key Violation error while loading data into your Target Table.
150. How to find Teradata Release and Version details?
  • SELECT * FROM DBC.DBCINFO;
at

No comments:

Post a Comment