TE0-122 12 Teradata SQL Certification Practice Exam Set 4

A company needs to determine which employees have invalid job codes assigned to them. In order to write the query to determine this, a correlated subquery will be executed using the Employee and Job tables. What does the subquery require in order to make it a correlated subquery?


Options are :

  • It must contain a GROUP BY clause.
  • It will have a join condition.
  • It has a nested set of parenthesis.
  • It cannot use aliases.

Answer : It cannot use aliases.

A table contains the following column: Inv_TS TIMESTAMP(6) WITH TIME ZONE which contains the following values: 2009-04-18 22:47:30.000000+03:00 2009-04-18 10:30:22.000000+12:00 Which dates will be returned if CAST(Inv_TS AS DATE) is used on the table, given that the Local Time Zone for the session is defined as -'00:00'?


Options are :

  • 04/18/2009 04/17/2009
  • 04/18/2009 04/18/2009
  • 04/18/2009 04/19/2009
  • 04/19/2009 04/19/2009

Answer : 04/18/2009 04/18/2009

Which two are benefits of using a macro? (Choose two.)


Options are :

  • It is a short cut for executing frequently run SQL statements
  • It can simplify operations that are complex.
  • It enables procedural logic.
  • It allows parameterization of object names.

Answer : It is a short cut for executing frequently run SQL statements It allows parameterization of object names.

TE0-141 14 Teradata Basics Certified Practice Test Set 3

What causes the Teradata Database to treat each request as an implicit transaction?


Options are :

  • the use of the COMMIT statement
  • the absence of the COMMIT statement
  • the absence of the BT ET statements
  • the use of the BT ET statements

Answer : the use of the BT ET statements

What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?


Options are :

  • It will automatically upgrade the lock without warning.
  • It will automatically upgrade the lock and provide a warning.
  • It will send back an error message for the lock to be corrected.
  • It will provide a warning and put the query in the delay queue.

Answer : It will automatically upgrade the lock without warning.

NR0-014 Teradata Administration V2R5 Certified Practice Exam Set 1

Company Z wants to identify the highest salaried employee in each department for a possible mentoring program. Which query will generate the desired results?


Options are :

  • SELECT last_name ,department_number AS deptno ,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE ee.salary_amount = em.salary_amount);
  • SELECT last_name ,department_number AS deptno ,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE ee.employee = salary_amount);
  • SELECT last_name ,department_number AS deptno ,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em);
  • SELECT last_name ,department_number AS deptno ,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE ee.department_number = em.department_number);

Answer : SELECT last_name ,department_number AS deptno ,salary_amount FROM employee ee WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em WHERE ee.salary_amount = em.salary_amount);

Which two are primary benefits to using views? (Choose two.)


Options are :

  • It simplifies updates to the tables that make up the view
  • It requires less space to store the data
  • It simplifies end-user access.
  • It provides an easily maintained level of security and authorization

Answer : It simplifies end-user access. It provides an easily maintained level of security and authorization

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


Options are :

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

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

TE0-124 Teradata 12 Database Administration Practice Exam Set 5

Why would adding a debug variable be useful when writing a stored procedure?


Options are :

  • It simplifies usage of various levels of tracing.
  • It decreases the frequency of recompilation.
  • It provides information on the compiler.
  • It reduces the number of SET statements.

Answer : It decreases the frequency of recompilation.

Given the following expressions: SUM(CASE WHEN maxAWT EQ 0 THEN 1 ELSE 0 END) AS AWT_0 SUM(CASE WHEN maxAWT BETWEEN 1 AND 10 THEN 1 ELSE 0 END) AS AWT1_10 SUM(CASE WHEN maxAWT BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS AWT11_20 SUM(CASE WHEN maxAWT BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS AWT21_30 SUM(CASE WHEN maxAWT BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS AWT31_40 SUM(CASE WHEN maxAWT GE 41 THEN 1 ELSE 0 END) AS AWT_41Plus What is the common purpose of these expressions?


Options are :

  • SUM the maxAWT value that are NOT IN the BETWEEN range.
  • Count the total occurrences of the BETWEEN range being FALSE.
  • Count the total occurrences of the BETWEEN range being TRUE.
  • SUM the maxAWT value that are IN the BETWEEN range.

Answer : SUM the maxAWT value that are IN the BETWEEN range.

Given the following table definition: CREATE TABLE employee ( employee_id INTEGER NOT NULL ,employee_name VARCHAR(50) ,employee_job VARCHAR(50)) PRIMARY INDEX (employee_id); Which statement is a correct use of a comparison operator?


Options are :

  • SELECT * FROM employee WHERE employee_job IS 'governance' OR 'quality';
  • SELECT * FROM employee WHERE employee_job IN ANY (maintenance, management, engineer);
  • SELECT * FROM employee WHERE employee_job IS NULL;
  • SELECT * FROM employee WHERE employee_job BETWEEN POSITIONS;

Answer : SELECT * FROM employee WHERE employee_job IN ANY (maintenance, management, engineer);

NR0-016 NCR Teradata Application Development V2R5 Exam Set 1

Which DCL statement is written correctly?


Options are :

  • GRANT ALL ON Database1 TO User1 WITH GRANT OPTION;
  • GRANT ALL ON Database1 TO User1 WITH ADMIN OPTION;
  • GRANT ALL ON Database1 TO User1 WITH GRANT;
  • GRANT ALL ON Database1 TO User1 WITH ADMIN;

Answer : GRANT ALL ON Database1 TO User1 WITH ADMIN OPTION;

Which data type can be converted to any other data type in the Day-Time interval family?


Options are :

  • INTERVAL TIME
  • INTERVAL DAY
  • INTERVAL MONTH
  • INTERVAL DATE

Answer : INTERVAL DATE

The stores table contains the following data: Given the following SQL statement: SELECT store_id ,city ,state ,SAMPLEID FROM stores SAMPLE 0.50, 0.25, 0.25 ORDER BY 4; How many rows are returned?


Options are :

  • 15
  • 1
  • 5
  • 4

Answer : 4

TE0-141 14 Teradata Basics Certified Practice Test Set 4

A user wants to create two copies of the following table: CREATE TABLE table_1( column1 INTEGER NOT NULL ,column2 INTEGER NOT NULL ,column3 DATE FORMAT 'YYYY-MM-DD' ,column4 INTEGER DEFAULT 1000 ,FOREIGN KEY (column2) REFERENCES WITH NO CHECK OPTION Parent_table1(column1)) PRIMARY INDEX (column1) PARTITION BY RANGE_N(column3 BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY); COLLECT STATISTICS ON table_1 INDEX (column1); COLLECT STATISTICS ON table_1 COLUMN PARTITION; CREATE TABLE table_2 AS table_1 WITH DATA AND STATISTICS; CREATE TABLE table_3 AS (SELECT * FROM table_1) WITH DATA AND STATISTICS; Which two statements are true about these two CREATE TABLE AS statements? (Choose two.)


Options are :

  • table_2 will have the referential integrity column from table_1
  • table_3 will have the DEFAULT column attribute from table_1.
  • table_3 will not be partitioned.
  • table_2 will have statistics on the system-derived PARTITION column from table_1.

Answer : table_2 will have the referential integrity column from table_1 table_3 will not be partitioned.

An SQL developer is asked to develop a set of performance monitoring reports. The development team has decided to code the reports using derived tables to aggregate the system performance reports. Which three are benefits of using a derived table in the report coding process? (Choose three.)


Options are :

  • SQL coding will take fewer steps than using temporary table solutions.
  • The temporary data will be available after the SQL request has completed.
  • Data distribution will be controlled by defining the primary index.
  • Column data types will be automatically defined except for casting requirements.
  • The derived tables will not require the use of temporary space for materialization.
  • The derived tables will be defined in the Data Dictionary for reuse.

Answer : SQL coding will take fewer steps than using temporary table solutions. The derived tables will not require the use of temporary space for materialization. The derived tables will be defined in the Data Dictionary for reuse.

Which three requirements result in choosing stored procedures for implementing an application? (Choose three)


Options are :

  • warning condition handlers
  • re-scrolling of answer sets
  • USING request modifiers
  • recursive processing

Answer : warning condition handlers USING request modifiers recursive processing

TE0-121 Teradata Basics Practice Test Set 4

A table is created with the following DDL: CREATE TABLE Test ( Column1 CHAR(2) NOT NULL) UNIQUE PRIMARY INDEX(Column1); The data inserted into Column1 looks like the following: Which query will have optimal performance, given the data inserted into Column1?


Options are :

  • SELECT Column1 FROM Test WHERE CAST(Column1 AS INTEGER)=35;
  • SELECT Column1 FROM Test WHERE Column1=35;
  • SELECT Column1 FROM Test WHERE Column1='35';
  • SELECT Column1 FROM Test WHERE Column1(INTEGER)=35;

Answer : SELECT Column1 FROM Test WHERE Column1(INTEGER)=35;

Which statement is true regarding session transaction mode in Teradata?


Options are :

  • All Teradata features work in ANSI mode but not all ANSI features work in Teradata mode.
  • ANSI mode can be set after logon.
  • The same query might return different results in each mode.
  • All ANSI features work in Teradata mode but not all Teradata features work in ANSI mode.

Answer : All Teradata features work in ANSI mode but not all ANSI features work in Teradata mode.

Which attribute function will return information about how a named expression will display?


Options are :

  • DEFAULT
  • FORMAT
  • TYPE
  • DISPLAY

Answer : DEFAULT

TE0-121 Teradata Basics Practice Test Set 5

Assume columns c1 and c2 have DATE data type. What is the resulting data type when subtracting c1 from c2?


Options are :

  • DATE
  • INTERVAL DAY
  • INTEGER
  • SMALLINT

Answer : DATE

Which two parameters should be included as OUT parameters to assist with debugging in a stored procedure? (Choose two.)


Options are :

  • informative error message
  • boundary conditions
  • activity count
  • return code
  • check SQL argument result

Answer : return code check SQL argument result

Given the following table: CREATE GLOBAL TEMPORARY TABLE database1.temp1( column1 INTEGER NOT NULL, column2 INTEGER DEFAULT 100 COMPRESS , column3 CHAR(80)) PRIMARY INDEX (column1) ON COMMIT PRESERVE ROWS; Which statement is true about this table?


Options are :

  • Rows will be preserved after a session that inserted rows into this table is logged off.
  • Multiple sessions can use this definition to insert rows but not share rows.
  • Rows will be deleted after a transaction that inserted rows into this table is complete
  • Inserted rows will use spool space.

Answer : Rows will be deleted after a transaction that inserted rows into this table is complete

TE0-124 Teradata 12 Database Administration Practice Exam Set 4

Which two are compatible with DISTINCT in a SELECT statement? (Choose two.)


Options are :

  • recursive queries
  • TOP N operator
  • CLOB cast as VARCHAR
  • WITH clause

Answer : recursive queries CLOB cast as VARCHAR

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions