NR0-013 Teradata SQL v2r5 Certification Practice Exam Set 1

Consider the following set of SQL statements: CREATE SET 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 b = 1; Which statement is true?


Options are :

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

Answer : The INSERTs succeed but the UPDATE fails because it would create a duplicate row.

TE0-121 Teradata Basics Practice Test Set 3

What are three security reasons for using a macro? (Choose three.)


Options are :

  • A parameterized macro can be designed to use the parameter to control the table or view to access in the macro.
  • The privilege to create the macro can be restricted to specified developers
  • It limits the need to grant privileges on tables and views used in the macro.
  • A parameterized macro can be designed to use the parameter to restrict access to a subset of the data for a given table or view used in the macro.

Answer : The privilege to create the macro can be restricted to specified developers It limits the need to grant privileges on tables and views used in the macro. A parameterized macro can be designed to use the parameter to restrict access to a subset of the data for a given table or view used in the macro.

Which three statements are true? (Choose three.)


Options are :

  • An implicit join is a sequence of table references enclosed in parentheses.
  • An outer join requires an ON clause.
  • For an implicit join, the join condition, if any, is specified in the WHERE clause.
  • An ON clause cannot be specified for a cross join.
  • A WHERE clause cannot be specified with a cross join.
  • A cross join requires a WHERE clause.

Answer : An outer join requires an ON clause. For an implicit join, the join condition, if any, is specified in the WHERE clause. An ON clause cannot be specified for a cross join.

Where can the join condition for an inner join be specified? (Choose two.)


Options are :

  • The ON clause
  • The WHERE clause
  • The FROM clause
  • The ANY clause

Answer : The ON clause The WHERE clause

TE0-141 14 Teradata Basics Certified Practice Test Set 2

What are three characteristics of views? (Choose three.)


Options are :

  • Views must change if columns are added to a table.
  • Views provide an additional level of security or authorization
  • Views cannot be used to UPDATE a table.
  • Views are not affected if columns are added to a table.
  • Views can contain a WHERE clause.

Answer : Views provide an additional level of security or authorization Views are not affected if columns are added to a table. Views can contain a WHERE clause.

Considering the following SQL statement: SELECT cust_name, cust_addr, SAMPLEID FROM customer_table SAMPLE WHEN state = 'CA' THEN .25, .20 ELSE .25, .35; How many distinct SAMPLEID values would be found in the answer set, assuming there are 10 rows from every state in customer_table ?


Options are :

  • 1
  • 4
  • 0
  • 2

Answer : 4

What two types of tables are available when users need to create a small temporary table but only have spool space allocated to them? (Choose two.)


Options are :

  • Global Temporary
  • Permanent
  • Derived
  • Volatile

Answer : Derived Volatile

TE0-141 14 Teradata Basics Certified Practice Test Set 1

Which two are correct uses of table aliasing to avoid Cartesian product joins? (Choose two.)


Options are :

  • SEL * FROM table_a A1, table a WHERE A1.acct1 = table_a.acct2
  • SEL * FROM table_a A1, table a A2 WHERE A1.acct1 = A2.acct2
  • SEL * FROM table_a A1, table a A2 WHERE A1.acct1 = table_a.acct2
  • SEL A1.c1, table_a.c2 FROM table_a A1, table_a A2 WHERE A1.acct1 = A2.acct2

Answer : SEL * FROM table_a A1, table a WHERE A1.acct1 = table_a.acct2 SEL * FROM table_a A1, table a A2 WHERE A1.acct1 = A2.acct2

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


Options are :

  • job_code
  • dept_num
  • It does not apply to either column as the query needs parentheses
  • dept_num and job_code

Answer : dept_num and job_code

Which four statements are true about the following MERGE INTO statement? (Choose four.) MERGE INTO department USING VALUES (105, 'Sales Dept', 770000, 1018) AS Dept (deptnum, deptname, budget, manager) ON Dept.deptnum = epartment.department_number WHEN MATCHED THEN UPDATE SET budget amount =Dept.budget WHEN NOT MATCHED THEN INSERT VALUES (Dept.deptnum, Dept.deptname,Dept.budget, Dept.manager);


Options are :

  • The AS clause specifies the name of the source table.
  • The USING clause defines the source table row.
  • The primary index of the target table must be unique.
  • The ON clause must contain the primary index of the target table.
  • The operation performed is an Upsert on the department table.

Answer : The AS clause specifies the name of the source table. The USING clause defines the source table row. The ON clause must contain the primary index of the target table. The operation performed is an Upsert on the department table.

TE0-125 Teradata 12 Solutions Development Practice Exam Set 3

Which three statements about view definitions are true? (Choose three.)


Options are :

  • You can use derived tables within a view
  • You can create an index on a view.
  • You can use an ORDER BY within a view.
  • You can assign new column names within a view.
  • You can perform aggregations within a view.

Answer : You can use derived tables within a view You can assign new column names within a view. You can perform aggregations within a view.

Which two statements about correlated subqueries are true? (Choose two.)


Options are :

  • They are one method for locating a maximum or minimum occurrence within groups.
  • They require temporary table space.
  • They allow the user to process data from the same table twice within the query.
  • They require special access rights to create.
  • They always join a table to a subset of the same table.

Answer : They are one method for locating a maximum or minimum occurrence within groups. They allow the user to process data from the same table twice within the query.

Consider the following SQL statement: SELECT city, state, SAMPLEID FROM stores SAMPLE WITH REPLACEMENT WHEN state = 'WI' THEN 4 ELSE 3 END ORDER BY 3; How many distinct SAMPLEID values are created, assuming 10 rows from every state in thestores table?


Options are :

  • 2
  • 7
  • 3
  • 4

Answer : 2

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 2

Consider the SQL statement: SELECT employee_number, department_number, salary_amount FROM personnel WITH SUM(salary_amount) ORDER BY department_number; Which results do you get from this SQL statement?


Options are :

  • A list of employee salaries with totals by department
  • No details, just a total of all salaries
  • A list of employee salaries with a grand total of all salaries
  • Total salaries for each department

Answer : A list of employee salaries with a grand total of all salaries

Which statement executes a parameterized macro named ABC ?


Options are :

  • EXEC abc VALUES(10, 'ABC');
  • EXEC abc (:10, :'ABC');
  • EXEC abc (10, 'ABC');
  • EXEC MACRO abc (10, 'ABC');

Answer : EXEC abc (10, 'ABC');

Which two types of tables would be considered when trying to avoid Data Dictionary access and Transaction locks? (Choose two.)


Options are :

  • Permanent
  • Global Temporary
  • Volatile
  • Derived

Answer : Volatile Derived

TE0-122 12 Teradata SQL Certification Practice Exam Set 4

What are two characteristics of UPDATE ? (Choose two .)


Options are :

  • It must include a WHERE clause.
  • It can be performed on a single table view.
  • It cannot include a subquery
  • It can include a self join.

Answer : It can be performed on a single table view. It can include a self join.

Which two statements about Teradata derived tables are true? (Choose two.)


Options are :

  • Derived tables can be used to compare an aggregated value from a table to the individual values from the rows of that table.
  • If users can create a derived table, they can also share it with other users.
  • Derived tables can be referenced by multiple SQL statements in a multi-statement request.
  • More than one derived table can be referenced in the same SQL statement.

Answer : Derived tables can be used to compare an aggregated value from a table to the individual values from the rows of that table. More than one derived table can be referenced in the same SQL statement.

The employee table contains emp_no, emp_name and dept_no . The department table contains dept_no and dept_name . Which query returns the employees who work in the Finance Department?


Options are :

  • SELECT emp_name FROM employee WHERE dept_name LIKE '%Finance%';
  • SELECT emp_name FROM employee WHERE dept_no IN (SELECT e.dept_no FROM employee e WHERE dept_name LIKE '%Finance%');
  • SELECT emp_name FROM employee WHERE dept_no IN (SELECT d.dept_no FROM department d WHERE dept_name LIKE '%Finance%');
  • SELECT emp_name FROM department WHERE dept_name LIKE '%Finance%' and dept_no IN (SELECT e.dept_no FROM employee e);

Answer : SELECT emp_name FROM employee WHERE dept_no IN (SELECT d.dept_no FROM department d WHERE dept_name LIKE '%Finance%');

TE0-121 12 Teradata Basics Certification Practical Exam Set 4

The employee table contains only empno and name columns. The department table contains only deptno and mgrno columns. Which query produces the names of all employees who are department managers?


Options are :

  • SELECT name FROM employee WHERE name IN (SELECT mgrno FROM department);
  • SELECT name FROM employee WHERE empno NOT IN (SELECT mgrno FROM department);
  • SELECT name FROM employee WHERE empno IN (SELECT mgrno FROM department);
  • SELECT name FROM employee WHERE mgrno IN (SELECT mgrno FROM department);

Answer : SELECT name FROM employee WHERE empno IN (SELECT mgrno FROM department);

You have an EMPLOYEE table with columns name, salary . Which two SQL statements produce a total salary, for only employees having salary greater than the average salary? (Choose two.)


Options are :

  • SEL SUM(salary) FROM EMPLOYEE e WHERE salary > (SEL AVG(salary) FROM EMPLOYEE);
  • SEL SUM(salary) FROM EMPLOYEE HAVING salary > AVG(salary);
  • SEL SUM(salary) FROM EMPLOYEE HAVING salary > (SEL AVG(salary) FROM EMPLOYEE);
  • SEL SUM(salary) FROM EMPLOYEE ,(SEL AVG(salary) as avg_sal FROM EMPLOYEE ) e WHERE salary > avg_sal;
  • SEL SUM(avg_sal) FROM EMPLOYEE ,(SEL AVG(salary) as avg_sal FROM EMPLOYEE HAVING salary > avg_sal) e;

Answer : SEL SUM(salary) FROM EMPLOYEE e WHERE salary > (SEL AVG(salary) FROM EMPLOYEE); SEL SUM(salary) FROM EMPLOYEE ,(SEL AVG(salary) as avg_sal FROM EMPLOYEE ) e WHERE salary > avg_sal;

NR0-016 NCR Teradata Application Development V2R5 Exam Set 1

What does the EXPLAIN output provide to assist the user in query analysis? (Choose two.)


Options are :

  • Accurate time measurements for query processing
  • What statistics should be collected on a column
  • Details of which indexes, if any, will be used to process the query
  • Which steps may be processed in parallel
  • Recommendations to improve the query based on timing and sizing

Answer : Details of which indexes, if any, will be used to process the query Which steps may be processed in parallel

Which two statements about the ACCOUNT function are true? (Choose two.)


Options are :

  • It has no arguments.
  • Value is provided by the parser
  • It must be coded between quotes.
  • It cannot be used in a macro.

Answer : It has no arguments. Value is provided by the parser

Employee_ID Name Salary Dept_No 1 Miller 10 2 2 Miller 20 2 3 Hurd 40 3 4 Stevens 10 2 SELECT COUNT(*) FROM employee GROUP BY name, dept no HAVING COUNT(*) > 1; Given the table and the query shown above, how many rows are in the result set?


Options are :

  • 1
  • 30
  • 3
  • 2

Answer : 1

TE0-125 Teradata 12 Solutions Development Practice Exam Set 3

Which formatting string applied to a timestamp will produce the following output? Monday, November 04,2002 03:24:22 PM


Options are :

  • FORMAT ' E3,BM2BD2,Y4BHH:MI:SSBT '
  • FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBZ '
  • FORMAT ' E3,BM2BD2,Y4BHH:MI:SSBZ '
  • FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBT '

Answer : FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBT '

Which option is available with Global Temporary tables?


Options are :

  • Identity Column
  • Permanent Journaling
  • Collect Statistics
  • Referential Constraints

Answer : Collect Statistics

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


Options are :

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

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

TE0-121 12 Teradata Basics Certification Practical Exam Set 7

Which condition is always true?


Options are :

  • SUM(COALESCE(a,0))+SUM(COALESCE(b,0)) = SUM(a+b)
  • SUM(a)+SUM(b) = SUM(a+b)
  • SUM(COALESCE(a,0))+SUM(COALESCE(b,0)) = SUM(COALESCE(a,0)+COALESCE(b,0))
  • SUM(a)+SUM(b) = SUM(COALESCE(a,0)+COALESCE(b,0))

Answer : SUM(COALESCE(a,0))+SUM(COALESCE(b,0)) = SUM(COALESCE(a,0)+COALESCE(b,0))

Which rows will this UNION operation return to the useWhile using ordered analytic functions, which SQL command specifies the window or subset of data?r? SELECT col_1 FROM table_a UNION SELECT col_6 FROM table_b;


Options are :

  • IN ()
  • OVER ()
  • GROUP BY ()
  • WINDOW ()

Answer : OVER ()

Which rows will this UNION operation return to the user? SELECT col_1 FROM table_a UNION SELECT col_6 FROM table_b;


Options are :

  • All rows from table_a and table_b, including duplicate rows
  • All rows in table_a that have matching rows in table_b
  • All rows from table_a and table_b, with output formatted by table_b
  • All rows from table_a and table_b, with duplicate rows eliminated

Answer : All rows from table_a and table_b, with duplicate rows eliminated

TE0-125 Teradata 12 Solutions Development Practice Exam Set 3

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions