NR0-013 Teradata SQL v2r5 Certification Practice Exam Set 3

Which column(s) does DISTINCT apply to in the following query? SELECT DISTINCT dept_num, job_code FROM employee;


Options are :

  • It does not apply to either column as the query needs parentheses.
  • Job_code
  • Dept_num
  • Dept_num and job_code

Answer : Dept_num and job_code

TE0-141 14 Teradata Basics Certified Practice Test Set 2

What is the result of SELECT CAST(100000.6 AS SMALLINT) ?


Options are :

  • 100000
  • 100000.6
  • 100001
  • Error: Numeric overflow occurred during computation.

Answer : Error: Numeric overflow occurred during computation.

What is the displayed result of SELECT 3 / 4 * 100.00 ?


Options are :

  • 0
  • 0.75
  • 75
  • 75

Answer : 0

Considering the following SQL, which statement is true assuming that there are no rows in Table_T where x is null? SELECT COUNT(*) FROM Table_T WHERE x = CASE WHEN y=2THEN 1 END ;


Options are :

  • The SQL statement is syntactically correct and a count of the number of rows from Table_T where y=2 and x=1 will be returned.
  • The SQL statement is syntactically correct and a count of the number of rows from Table_T where either y=2 or x=1 will be returned.
  • The SQL statement is not syntactically correct because a CASE statement cannot be used within a WHERE clause.
  • The SQL statement is not syntactically correct because no ELSE statement exists within the CASE statement.

Answer : The SQL statement is syntactically correct and a count of the number of rows from Table_T where y=2 and x=1 will be returned.

NR0-016 NCR Teradata Application Development V2R5 Exam Set 2

What is another way to write the following expression? CASE WHEN x = y THEN NULL ELSE x END


Options are :

  • NULLIF (x, y)
  • COALESCE (x, y)
  • ZEROIFNULL (x, y)
  • COMPRESS NULL (x, y)

Answer : NULLIF (x, y)

What type of table is recommended when users need to share a table definition but not share the data?


Options are :

  • Global Temporary
  • Volatile
  • Permanent
  • Derived

Answer : Global Temporary

Consider the expression: SELECT Name, COALESCE (HomePhone, OfficePhone, MessageService) FROM PhoneDir; If only the HomePhone is NULL , what is returned?


Options are :

  • Name with OfficePhone and MessageService
  • Name with OfficePhone
  • Name with '?'
  • An error message

Answer : Name with OfficePhone

TE0-121 Teradata Basics Practice Test Set 1

Which statement is true?


Options are :

  • You cannot add different data types, so must first use the CAST function to make them the same.
  • When an integer value is added to a float value, neither is converted.
  • When an integer value is added to a float value, the float is first converted to an integer.
  • When an integer value is added to a float value, the integer is first converted to a float.

Answer : When an integer value is added to a float value, the integer is first converted to a float.

What is the result of the following query where birthdate has the DATE data type ? SELECT (C URRENT_ DATE - birthdate ) FROM birthrecord;


Options are :

  • The day of birth
  • The month of birth
  • The days since birth
  • The years since birth
  • The months since birth

Answer : The days since birth

Which four statements are true about executing the following macro? (Choose four.) CREATE MACRO dept_budget (deptno INT, budgamt DEC(10,2))AS (MERGE INTO department USING (SELECT :deptno, dept_name, :budgamt FROM dept name_assignmentWHERE departme nt_number = :deptno) AS Dept(deptnum, deptname, budget) ON Dept.deptnum = department.department_number WHEN MATCHED THEN UPDATE SETbudget_amount = Dept.budget, department_name = Dept.deptname WHEN NOT MATCHED HEN INSERT VALUES (Dept.deptnum, Dept.deptname, Dept.budget, NULL););


Options are :

  • If zero rows are returned by the subquery, neither the update nor the insert occurs.
  • The USING subquery may return at most a single row.
  • The ON clause must contain the primary index of the target table.
  • The AS clause within the macro specifies the name of the source table.
  • The primary index of the target table must be unique.

Answer : If zero rows are returned by the subquery, neither the update nor the insert occurs. The USING subquery may return at most a single row. The ON clause must contain the primary index of the target table. The AS clause within the macro specifies the name of the source table.

TE0-124 Teradata 12 Database Administration Practice Exam Set 5

Consider the SQL statement: SELECT name, lot, weight FROM participants WITH SUM(weight) BY lot WITH AVG(weight), SUM(weight) ORDER BY name, lot; Which answer set is returned from this SQL statement?


Options are :

  • A list of all participants with average weight for each lot and both the average and total weight for all lots
  • A list of all participants with average weight and subtotal of weight for each lot and a total weight for all lots
  • A list of all participants with their total weight and a subtotal, grand total and average weight for all lots
  • A list of all participants with a subtotal of weight for each lot and both the average and total weight for all lots

Answer : A list of all participants with a subtotal of weight for each lot and both the average and total weight for all lots

Which SQL statement is a syntactically correct example of a valued CASE expression?


Options are :

  • SELECT CASE x WHEN 1 THEN 'Yes' ELSE 'No' END FROM Table_A;
  • SELECT CASE WHEN x=1 THEN 'Yes' ELSE 'No' END FROM Table_A;
  • SELECT CASE SEARCH x FOR 1 IF = THEN 'Yes' ELSE 'No' FROM Table_A;
  • SELECT CASE IF x=1 THEN 'Yes' ELSE NULL END FROM Table_A;

Answer : SELECT CASE x WHEN 1 THEN 'Yes' ELSE 'No' END FROM Table_A;

Which three statements are true about Global and Volatile Temporary tables? (Choose three.)


Options are :

  • Volatile Temporary tables are materialized in spool space and their definition is maintained in cache.
  • Volatile Temporary table space requirements are charged against the user's allocation of temporary space.
  • Global Temporary table definitions are maintained in the Data Dictionary.
  • Volatile Temporary tables must be created before they can be materialized.

Answer : Volatile Temporary tables are materialized in spool space and their definition is maintained in cache. Global Temporary table definitions are maintained in the Data Dictionary. Volatile Temporary tables must be created before they can be materialized.

TE0-125 Teradata 12 Solutions Development Practice Exam Set 1

Consider the following set of SQL statements: CREATE MULTISET TABLE t1 (a INTEGER, b INTEGER) PRIMARY INDEX (a); INSERT INTO t1 VALUES (1,1); INSERT INTO t1 VALUES (1,2); UPDATE t1 SET b = b + 1 WHERE a = 1; Which statement is true?


Options are :

  • The INSERTs succeed but the UPDATE fails because it would create a duplicate row.
  • The INSERTs and the UPDATE succeed.
  • The second INSERT fails because it would create a duplicate primary index value.
  • The INSERTs succeed but the UPDATE fails because the syntax is incorrect

Answer : The INSERTs and the UPDATE succeed.

Consider the following statement: UPDATE T1 SET ColA = ColB WHERE ColB IS NOT NULL; Under which three conditions will the query succeed? (Choose three.)


Options are :

  • ColA is defined as INTEGER and ColB is defined as DATE
  • ColA is defined as INTEGER and ColB is defined as SMALLINT .
  • ColA is defined as CHAR(2) and ColB is defined as VARCHAR(2) .
  • ColA is defined as TIMESTAMP and ColB is defined as DATE

Answer : ColA is defined as INTEGER and ColB is defined as DATE ColA is defined as INTEGER and ColB is defined as SMALLINT . ColA is defined as CHAR(2) and ColB is defined as VARCHAR(2) .

Which statement has correct syntax?


Options are :

  • SELECT CASE WHEN table city.city IN (SELECT u.city FROM u) THEN 'Y' ELSE 'N' END FROM table_city;
  • SELECT CASE WHEN city = 'Boston' THEN city = 'Y' ELSE 'N' END FROM table_city;
  • SELECT city, CASE WHEN city = 'Boston' THEN 'Y' ELSE 'N' END FROM table_city;
  • SELECT CASE WHEN 'Boston' THEN 'Y' ELSE 'N' END FROM table_city;

Answer : SELECT city, CASE WHEN city = 'Boston' THEN 'Y' ELSE 'N' END FROM table_city;

NR0-016 NCR Teradata Application Development V2R5 Exam Set 3

Which two can be specified in the CREATE TABLE statement? (Choose two.)


Options are :

  • Maximum permanent space
  • Before and after journaling
  • Data block size
  • Join index

Answer : Before and after journaling Data block size

What data type results from the following calculation? SELECT DATE '2003-02-15' - DATE '2003-01-15';


Options are :

  • INTEGER
  • INTERVAL DAY
  • INTERVAL MONTH
  • DATE

Answer : INTEGER

What is another way to write the following expression? CASE WHEN x IS NOT NULL THEN x WHEN y IS NOT NULL THEN y ELSE NULL END


Options are :

  • NULLIF (x, y)
  • COMPRESS NULL (x, y)
  • COALESCE (x, y)
  • ZEROIFNULL (x, y)

Answer : COALESCE (x, y)

TE0-122 12 Teradata SQL Certification Practice Exam Set 1

When is a table alias required?


Options are :

  • When joining a table to itself
  • To enhance join processing
  • When joining more than two tables
  • When processing subqueries

Answer : When joining a table to itself

Within a window, which SQL command determines the group or groups over which an ordered Analytical function executes?


Options are :

  • GROUP BY
  • ORDER BY
  • PARTITION BY
  • ROWS

Answer : PARTITION BY

Which three statements about dates are true? (Choose three.)


Options are :

  • CURRENT_DATE is a data type as well as a built-in function.
  • DATE is a data type as well as a built-in function.
  • The built-in functions DATE and CURRENT_DATE are equivalent.
  • DATE values are stored internally as integers.
  • CURRENT_DATE is a column in SYS_CALENDAR.CALENDAR .

Answer : DATE is a data type as well as a built-in function. The built-in functions DATE and CURRENT_DATE are equivalent. DATE values are stored internally as integers.

TE0-141 14 Teradata Basics Certified Practice Test Set 3

What is the result of SELECT CAST('bb12345' AS CHAR(3)) in Teradata mode? (Note: b =blank)


Options are :

  • bb1'
  • 123'
  • 123
  • 1
  • 345

Answer : bb1'

What is the expected result of the following SQL statement? SELECT SUM(CASE part_no WHEN '1' THEN ship_amt ELSE 0 END) / SUM(ship_amt) FROM Table_T;


Options are :

  • An error since you cannot place a CASE statement within a SUM statement
  • The total shipping cost attributed to all parts delivered
  • The total shipping cost attributed to part number 1 and the total shipping cost of all parts
  • The fraction of the shipping cost attributed to part number 1

Answer : The fraction of the shipping cost attributed to part number 1

What is the result of the following query? SELECT ADD_MONTHS (CURRENT_DATE, 40*3);


Options are :

  • The current date plus 40 months, three days
  • The current date plus 120 days
  • The current date plus 10 years
  • The current date plus 120 years

Answer : The current date plus 10 years

TE0-01A 12 Teradata Fundamentals Certification Practice Exam Set 1

Which statement about data types is correct?


Options are :

  • A TIMESTAMP minus an INTERVAL results in an INTERVAL .
  • An INTERVAL times a number results in a number.
  • A TIMESTAMP minus a TIMESTAMP results in an INTERVAL .
  • A TIMESTAMP minus a TIMESTAMP results in a TIMESTAMP .

Answer : A TIMESTAMP minus a TIMESTAMP results in an INTERVAL .

What is the join type in the following query? SELECT workers.name, workers.yrsexp, workers.deptno, managers.name, managers.yrsexp FROM employee workers, employeemanagers WHERE managers.deptno = workers.deptno;


Options are :

  • CARTESIAN join
  • Nested join
  • Cross join
  • Self join

Answer : Self join

What are two characteristics of the CHECK constraint? (Choose two.)


Options are :

  • Any Boolean comparison operator can be used.
  • Multiple columns can be checked at the table level.
  • It will not work on the primary index column
  • Multiple columns can be checked at the table level.

Answer : Any Boolean comparison operator can be used. Multiple columns can be checked at the table level.

TE0-141 14 Teradata Basics Certified Practice Test Set 4

Which statement creates a table that is initialized with the result of a query?


Options are :

  • CREATE TABLE t19 LIKE (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;
  • CREATE TABLE 19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH RESULTS;
  • CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;
  • CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2);

Answer : CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;

What are two characteristics of the FLOAT data type? (Choose two.)


Options are :

  • It is synonymous with REAL and DOUBLE PRECISION .
  • Has a minimum value of -32,768 and maximum value of 32,767.
  • The internal length is 4 bytes.
  • The internal length is 8 bytes.

Answer : It is synonymous with REAL and DOUBLE PRECISION . The internal length is 8 bytes.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions