TE0-122 12 Teradata SQL Certification Practice Exam Set 2

Given the following definition of a table: CREATE TABLE trans ( trans_id INTEGER NOT NULL ,unit_price DECIMAL(10,2) ,qnty INTEGER ,t_time TIMESTAMP(6) NOT NULL) PRIMARY INDEX (trans_id); The table has a row that was inserted as follows: INSERT trans VALUES (45912, 12.99, NULL, CURRENT_TIMESTAMP); The following statement is submitted: SELECT COALESCE(unit_price,0)*qnty FROM trans WHERE trans_id=45912 AND unit_price IS NOT NULL; What is the result?


Options are :

  • NULL
  • 0
  • 12.99
  • no rows returned

Answer : no rows returned

Which statement will reduce the price of books by 10% for all books published by any division of the Blackwell group?


Options are :

  • UPDATE Books SET Book_list_price = Book_list_price * .90 WHERE Publisher_Id IN (SELECT Publisher_Id FROM Publisher WHERE Publisher_name = Blackwell);
  • UPDATE Books SET Book_list_price = Book_list_price * .90 WHERE Books.Publisher_Id = Publisher.Publisher_Id AND WHERE Publisher_name LIKE 'Blackwell%';
  • UPDATE Books SET Book_list_price = Book_list_price * .90 WHERE Publisher_Id IN (SELECT Publisher_Id FROM Publisher WHERE Publisher_name LIKE 'Blackwell%');
  • UPDATE Books SET Book_list_price = Book_list_price * .90 WHERE Books.Publisher_Id = Publisher.Publisher_Id AND Publisher_name = Blackwell;

Answer : UPDATE Books SET Book_list_price = Book_list_price * .90 WHERE Books.Publisher_Id = Publisher.Publisher_Id AND WHERE Publisher_name LIKE 'Blackwell%';

What is an appropriate use of a macro?


Options are :

  • start load utilities
  • create multiple users
  • insert new rows
  • drop databases

Answer : drop databases

TE0-123 Teradata 12 Physical Design & Implementation Exam Set 3

Which function is needed when converting a character string to another server character set?


Options are :

  • INTERPRET
  • CONVERT
  • CAST
  • TRANSLATE

Answer : CAST

What information would be reviewed to indicate I/O skewed processing for a query?


Options are :

  • the amount of temporary space consumed by the query
  • . the results of the execution of the SHOWIOSKEW command before the query
  • the results of the execution of the EXPLAIN command before the query
  • the ratio of maximum I/O to average I/O from DBC.DBQLogTbl

Answer : . the results of the execution of the SHOWIOSKEW command before the query

TE0-121 12 Teradata Basics Certification Practical Exam Set 5

Which three statements are true about the functionality of queue tables? (Choose three.)


Options are :

  • A SELECT AND CONSUME reads a row and deletes it from the table and queue.
  • A queue table may or may not contain data.
  • The definition of a queue table is not stored in the data dictionary.
  • A normal SELECT may not be used to read rows in the table.
  • The first column must be a timestamp data type.

Answer : A SELECT AND CONSUME reads a row and deletes it from the table and queue. The definition of a queue table is not stored in the data dictionary. The first column must be a timestamp data type.

The account balance column in the Customer table is defined as the following: acctbal DECIMAL(8,2) NOT NULL In the Customer table, the column values are the following: The following SQL statement is submitted: SELECT CAST(acctbal AS INTEGER) balance FROM Customer WHERE CAST(acctbal AS DECIMAL(6,0)) > acctbal; What is the result?


Options are :

  • balance 227639 229562
  • balance 227638 229561
  • balance 220794 228324 227498
  • balance 227639 220795 228325 227499 229562

Answer : balance 227639 220795 228325 227499 229562

Which data type is supported for Multi-Value Compression (MVC)?


Options are :

  • VARCHAR
  • TIME
  • FLOAT
  • INTERVAL

Answer : TIME

TE0-122 12 Teradata SQL Certification Practice Exam Set 4

Which expression adds 10 hours 20 minutes 15 seconds to CURRENT_TIME?


Options are :

  • CURRENT_TIME + INTERVAL ('10' HOUR + '20' MINUTE + '15' SECOND)
  • CURRENT_TIME + ('10' HOUR + '20' MINUTE + '15' SECOND) INTERVAL
  • CURRENT_TIME + '10:20:15'
  • CURRENT_TIME + ('10:20:15' HOUR TO SECOND) INTERVAL
  • CURRENT_TIME + INTERVAL '10:20:15' HOUR TO SECOND

Answer : CURRENT_TIME + ('10:20:15' HOUR TO SECOND) INTERVAL

Given the following table: CREATE TABLE Division_Sales_2008 (Division_id INTEGER, Sales_Rep CHAR(10) NOT NULL PRIMARY KEY, Sales_Amt DEC(10,2)); Each row in the table represents the 2008 total sales for an agent in a specific division. Given the following query: SELECT Division_id ,Sales_Rep, RANK ( ) OVER (PARTITION BY Division_id ORDER BY SALES DESC) AS "Ranking" FROM Division_Sales_2008 QUALIFY Ranking <= 3; What describes the output from this query?


Options are :

  • the top three divisions and their total sales
  • the bottom three divisions and their total sales
  • the top three sales reps in the each of the divisions
  • the bottom three sales reps and their total sales

Answer : the bottom three divisions and their total sales

Table 'logtable' is defined as the following: CREATE TABLE logtable (col1 INT); Which three stored procedures will compile successfully? (Choose three.)


Options are :

  • CREATE PROCEDURE sp_ins BEGIN DECLARE vcount INTEGER DEFAULT 0; INSERT logtable (:vcount); END;
  • CREATE PROCEDURE sp_ins BEGIN DECLARE col1 INTEGER DEFAULT 0; INSERT logtable (col1); END;
  • CREATE PROCEDURE sp_ins BEGIN DECLARE col1 INTEGER DEFAULT 0; INSERT logtable (:col1); END;
  • CREATE PROCEDURE sp_ins BEGIN DECLARE vcount INTEGER DEFAULT 0; INSERT logtable (vcount); END;

Answer : CREATE PROCEDURE sp_ins BEGIN DECLARE vcount INTEGER DEFAULT 0; INSERT logtable (:vcount); END; CREATE PROCEDURE sp_ins BEGIN DECLARE col1 INTEGER DEFAULT 0; INSERT logtable (col1); END; CREATE PROCEDURE sp_ins BEGIN DECLARE col1 INTEGER DEFAULT 0; INSERT logtable (:col1); END;

TE0-124 Teradata 12 Database Administration Practice Exam Set 2

A user is writing a complex query that needs to aggregate values from multiple tables and join those results. Which construct can be used in the query to allow the writing of the query as a single SELECT statement?


Options are :

  • Derived Table
  • Volatile Table
  • Temporary Table
  • Virtual Table

Answer : Temporary Table

Given the following statement: SHOW MACRO Customer; Which is a valid output of this statement?


Options are :

  • DROP MACRO Customer ( First name CHAR(20) ,Last name CHAR(25) ,Cellphone_number INTEGER ,Business_phone_number INTEGER) AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz) );
  • UPDATE MACRO Customer ( First name CHAR(20) ,Last name CHAR(25) ,Cellphone_number INTEGER ,Business_phone_number INTEGER) AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz) );
  • DEFINE MACRO Customer ( First name CHAR(20) ,Last name CHAR(25) ,Cellphone_number INTEGER ,Business_phone_number INTEGER) AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz) );
  • REPLACE MACRO Customer ( First name CHAR(20) ,Last name CHAR(25) ,Cellphone_number INTEGER ,Business_phone_number INTEGER) AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz) );

Answer : UPDATE MACRO Customer ( First name CHAR(20) ,Last name CHAR(25) ,Cellphone_number INTEGER ,Business_phone_number INTEGER) AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz) );

A company thinks there are some employees who earn more than their managers. They have asked for a report showing the employee number, department number, and salary of all department managers who have the highest salary in their department. Which query will produce the desired report?


Options are :

  • SELECT d.man_employee_num, d.department_num, e.salary_amount FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE d.department_num = em.department_num);
  • SELECT d.man_employee_num, d.department_num, e.salary_amount FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount = (SELECT MAX (salary_amount) FROM employee em);
  • . SELECT d.man_employee_num, d.department_num, e.salary_amount FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount = (SELECT MAX (salary_amount) FROM department d WHERE d.department_num = em.department_num);
  • SELECT d.man_employee_num, d.department_num, e.salary_amount FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount = (SELECT MAX (department_num) FROM department d WHERE d.department_num = em.department_num);

Answer : SELECT d.man_employee_num, d.department_num, e.salary_amount FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount = (SELECT MAX (department_num) FROM department d WHERE d.department_num = em.department_num);

TE0-121 12 Teradata Basics Certification Practical Exam Set 3

A database administrator has initiated charge-back accounting for an entire session and wants to determine whether the current account string is correct. Which built-in function should be issued?


Options are :

  • ACCOUNT
  • USER
  • PROFILE
  • SESSION

Answer : PROFILE

Given the following two PPI tables: CREATE TABLE orders (o_orderkey INTEGER NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_shipdate DATE FORMAT 'yyyymm-dd' NOT NULL, o_shippriority INTEGER, o_comment VARCHAR(80)) PRIMARY INDEX (o_orderkey) PARTITION BY ( RANGE_N (o_orderdate BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH), RANGE_N (o_shipdate BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH)); CREATE TABLE lineitem (l_orderkey INTEGER NOT NULL, l_partkey INTEGER NOT NULL, l_suppkey INTEGER, l_linenumber INTEGER, l_quantity INTEGER NOT NULL) PRIMARY INDEX (l_orderkey) PARTITION BY RANGE_N (l_suppkey BETWEEN 0 and 4999 EACH 10)); Given the query: SELECT * FROM orders, lineitem WHERE o_orderkey=l_orderkey AND .... Which three additional conditions will perform partition elimination when added after AND? (Choose three.)


Options are :

  • l_suppkey in (3000, 4000)
  • l_suppkey < 3000
  • o_orderdate < o_shipdate - 30
  • o_orderdate < CURRENT_DATE - 30

Answer : l_suppkey < 3000 o_orderdate < o_shipdate - 30 o_orderdate < CURRENT_DATE - 30

Which two pieces of information can be reviewed in the trace output to assist in debugging a stored procedure?(Choose two.)


Options are :

  • identify compilation parameters
  • set severity warnings in ERRORLEVEL
  • removal of database error handling
  • set severity warnings in ERRORLEVEL
  • isolate the failure point

Answer : set severity warnings in ERRORLEVEL set severity warnings in ERRORLEVEL

TE0-141 14 Teradata Basics Certified Practice Test Set 2

Which function can be used to aid in searching for a carriage return character in a string?


Options are :

  • INTERPRET
  • CHAR2HEXINT
  • TRANSLATE
  • CAST

Answer : CAST

Given the following columns and their data types: Inv_Date DATE Inv_Time TIME(0) IntFld INTEGER Which expression will execute without an error?


Options are :

  • SELECT IntFld - Inv_date
  • SELECT Inv_Time - IntFld
  • SELECT IntFld - Inv_Time
  • SELECT Inv_Date - IntFld

Answer : SELECT IntFld - Inv_date

Which two statements describe the functionality of a UDF? (Choose two.)


Options are :

  • It allows SQL to be extended by writing functions and installing them on the database, and then using them like standard SQL functions.
  • It is a combination of SQL and control and condition handling statements that provide an interface to Teradata.
  • It is globally accessible to those with the proper access rights.
  • It is an automatically driven database event.

Answer : It allows SQL to be extended by writing functions and installing them on the database, and then using them like standard SQL functions. It is an automatically driven database event.

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 2

What are two uses of the GROUP BY clause? (Choose two.)


Options are :

  • aggregating data groups
  • ranking
  • setting the sort order of data groups
  • qualifying
  • alternative method to SELECT DISTINCT

Answer : aggregating data groups ranking

When creating a new macro, the name of the macro must be different from which objects?


Options are :

  • all other objects within the same database as the new macro
  • all other macros within the system
  • only other macros within the same database as the new macro
  • the name of the database containing the macro

Answer : the name of the database containing the macro

Given the following reporting requirements: - a breakdown of salaries by department, by manager, by manager/department - a grand total of all salaries Which grouping function is designed to handle these requirements?


Options are :

  • ROLLUP
  • CUBE
  • GROUP BY GROUPING SETS
  • PARTITION BY

Answer : ROLLUP

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 4

Which statement will update the customer's order priority to Rush (R) and change the delivery city to Atlanta?


Options are :

  • UPDATE Orders SET Order_priority = 'R' ,Order_destination = 'Atlanta' WHERE Order_id = 5001;
  • UPDATE Orders WHERE Order_id = 5001 SET Order_priority = 'R' ,Order_destination = 'Atlanta';
  • UPDATE Orders SET Order_priority = 'R' Order_destination = 'Atlanta' WHERE Order_id = 5001;
  • UPDATE Orders SET Order_priority = R ,Order_destination = Atlanta WHERE Order_id = 5001;

Answer : UPDATE Orders WHERE Order_id = 5001 SET Order_priority = 'R' ,Order_destination = 'Atlanta';

Which two are reasons to use a view? (Choose two.)


Options are :

  • It simplifies selects from tables that make up the view.
  • It simplifies updates to tables that make up the view.
  • It enables building of large and complex applications.
  • It enables renaming and formatting of columns.

Answer : It simplifies selects from tables that make up the view. It simplifies updates to tables that make up the view.

TE0-141 14 Teradata Basics Certified Practice Test Set 3

Which statement is written correctly?


Options are :

  • SELECT table2.logdate, table2.logtime, db1.* FROM DBC.ResUsageSPMA db1 WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;
  • SELECT table2.logdate, table2.logtime, * FROM DBC.ResUsageSPMA db1 WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;
  • SELECT table2.logdate, table2.logtime, (*) FROM DBC.ResUsageSPMA db1 WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;
  • SELECT *, table2.logdate, table2.logtime FROM DBC.ResUsageSPMA db1 WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;

Answer : SELECT table2.logdate, table2.logtime, * FROM DBC.ResUsageSPMA db1 WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;

Given the following PPI table: CREATE TABLE SalesHistory_Store1( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY); Which table, when joined with the given table, may result in a rowkey-based merge join?


Options are :

  • CREATE TABLE SalesHistory_Store2( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY);
  • CREATE TABLE SalesHistory_Store2( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date)
  • CREATE TABLE SalesHistory_Store2( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY);
  • CREATE TABLE SalesHistory_Store2( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH);

Answer : CREATE TABLE SalesHistory_Store2( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH);

A SELECT query uses the RANK() OVER (ORDER BY batting_avg DESC) syntax to produce a ranking of baseball players. The top ten baseball players are being ranked based on their batting averages. In the resulting output of the query there will be ten rows. There is a twoway tie for the second position and a three-way tie for the last three positions. What will be the ranking number of the player in the final row?


Options are :

  • 10
  • 9
  • 7
  • 8

Answer : 10

NR0-014 Teradata Administration V2R5 Certified Practice Exam Set 1

Which two will happen during a transaction when no rows are available in a queue table? (Choose two.)


Options are :

  • The transaction aborts either by an ABORT statement, or a DROP TABLE statement on the queue table.
  • The transaction aborts automatically.
  • The transaction enters a delay state until a row is inserted into the queue table.
  • The SELECT AND CONSUME statement will generate new rows in the table.

Answer : The transaction aborts automatically. The SELECT AND CONSUME statement will generate new rows in the table.

Which three types of user-defined functions does Teradata support? (Choose three.)


Options are :

  • Scalar
  • Local
  • Global
  • Table
  • Aggregate

Answer : Local Global Aggregate

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions