NR0-016 NCR Teradata Application Development V2R5 Exam Set 4

What is an example of a valid multi-statement request in BTEQ?


Options are :

  • database db1 ;select * from tbla ;
  • create table tblA (col1 int) ;select * from tbla;
  • select * from tbla ; select * from tblb ;
  • select * from tbla ;select * from tblb ; (Correct)

Answer : select * from tbla ;select * from tblb ;

Which two restrict a user's access to database objects? (Choose two.)


Options are :

  • creator of user
  • access rights (Correct)
  • roles of user (Correct)
  • access logs
  • profile of user

Answer : access rights roles of user

Which two restrict a user's access to database objects? (Choose two.)


Options are :

  • access rights (Correct)
  • creator of user
  • roles of user (Correct)
  • access logs
  • profile of user

Answer : access rights roles of user

When can an identity column be useful in a table definition? (Choose three.)


Options are :

  • when a unique secondary index is desired for the table (Correct)
  • when a Partitioned Primary Index (PPI) is desired for the table
  • when a foreign key is desired for the table
  • when a primary index is desired for the table (Correct)
  • when a primary key is desired for the table (Correct)

Answer : when a unique secondary index is desired for the table when a primary index is desired for the table when a primary key is desired for the table

Which three statements about Teradata Warehouse Builder are true? (Choose three.)


Options are :

  • Loading and exporting data cannot be done in the same job stream.
  • Multiple tables may be loaded in the same job. (Correct)
  • It has automatic restart. (Correct)
  • It can read data from multiple sources in the same job. (Correct)
  • The target table must be empty.

Answer : Multiple tables may be loaded in the same job. It has automatic restart. It can read data from multiple sources in the same job.

A person submits a high volume of all-AMP requests. What two strategies can be used to increase parallelism? (Choose two.)


Options are :

  • use multi-statement requests (Correct)
  • use multi-request transactions
  • use NUSIs on the tables
  • use more sessions (Correct)

Answer : use multi-statement requests use more sessions

Which three describe the rows returned from the left and right table when using a Left Outer Join? (Choose three.)


Options are :

  • matched rows from the right table (Correct)
  • unmatched rows from the right table
  • unmatched rows from the left table (Correct)
  • matched rows from the left table (Correct)

Answer : matched rows from the right table unmatched rows from the left table matched rows from the left table

A report of sales totals by day-of-week (i.e., Saturday sales, Sunday sales, etc.) is requested. Which three techniques can be employed to design the query? (Choose three.)


Options are :

  • Use a minor entity table. (Correct)
  • Use the CASE statement. (Correct)
  • Use the SET SESSION DATEFORM statement.
  • Use the system calendar (Correct)

Answer : Use a minor entity table. Use the CASE statement. Use the system calendar

Which three factors does the Optimizer consider to choose the least costly access method and join path? (Choose three.)


Options are :

  • Priority Scheduler settings
  • row selection criteria (Correct)
  • index choices (Correct)
  • the workload of the system
  • the column and index demographics (Correct)

Answer : row selection criteria index choices the column and index demographics

What happens when you create a foreign key constraint on a populated table?


Options are :

  • An error is returned when creating a foreign key constraint on a populated table.
  • Any foreign key violations are deleted from the table.
  • Existing rows are not checked for violations.
  • Any foreign key violations are copied to an error table. (Correct)

Answer : Any foreign key violations are copied to an error table.

A sparse index _____. (Choose two.)


Options are :

  • is a type of join index (Correct)
  • references values that are rarely used in queries
  • reduces the storage requirements for an index (Correct)
  • is especially useful for joining to tables that have few rows

Answer : is a type of join index reduces the storage requirements for an index

Which type of processing workload is least likely to justify the cost of upgrading a system?


Options are :

  • nightly batch process
  • executive report
  • quarter end report (Correct)
  • daily sales report

Answer : quarter end report

Which three are join types that are available within queries? (Choose three.)


Options are :

  • Self (Correct)
  • Hash
  • Cross (Correct)
  • RowID
  • Outer (Correct)
  • Product

Answer : Self Cross Outer

What are two characteristics of multi-statement requests? (Choose two.)


Options are :

  • improve system performance by reducing processing overhead (Correct)
  • are atomic
  • can include a DDL statement
  • can have multiple USING row descriptors per request (Correct)

Answer : improve system performance by reducing processing overhead can have multiple USING row descriptors per request

What are two characteristics of an aggregate join index? (Choose two.)


Options are :

  • can be directly referenced by a query
  • COUNT is automatically present if SUM is used (Correct)
  • can be created using a Global Temporary table
  • must contain more than one table reference
  • automatically reflects changes in the underlying detail data (Correct)

Answer : COUNT is automatically present if SUM is used automatically reflects changes in the underlying detail data

Which three demographics are useful for index selection? (Choose three.)


Options are :

  • number of columns in a composite index
  • join access frequency (Correct)
  • number of concurrent users
  • distinct values (Correct)
  • value access frequency (Correct)

Answer : join access frequency distinct values value access frequency

Which three are valid considerations for optimal system performance? (Choose three.)


Options are :

  • Statistics are collected on all indexes. (Correct)
  • The disk space consumed by a secondary index negatively impacts OLTP applications.
  • Primary indexes are chosen based on access, uniqueness, and stable data values. (Correct)
  • The performance benefit of a secondary index for OLTP applications outweighs the performance cost for batch maintenance . (Correct)

Answer : Statistics are collected on all indexes. Primary indexes are chosen based on access, uniqueness, and stable data values. The performance benefit of a secondary index for OLTP applications outweighs the performance cost for batch maintenance .

Two tables prepared simultaneously for a join is an example of what kind of parallelism?


Options are :

  • multi-threading
  • multi-statement (Correct)
  • multi-session
  • multi-AMP
  • multi-step

Answer : multi-statement

When do you need to collect multi-column statistics? (Choose two.)


Options are :

  • when the columns frequently appear together in a GROUP BY clause of an hourly report
  • when the columns frequently appear together in join conditions (Correct)
  • if the combination of columns will be using multi-value compression
  • if a secondary index was defined to allow statistics collection in the past (Correct)

Answer : when the columns frequently appear together in join conditions if a secondary index was defined to allow statistics collection in the past

Soft referential integrity is defined with which DDL clause?


Options are :

  • REFERENCES WITH CHECK OPTION
  • REFERENCES WITHOUT CHECK OPTION
  • REFERENCES WITH SOFT CHECK OPTION
  • REFERENCES WITH NO CHECK OPTION (Correct)

Answer : REFERENCES WITH NO CHECK OPTION

The Optimizer can select a Nested Join only if which two conditions are true? (Choose two.)


Options are :

  • There is an equality condition on a unique index of one table. (Correct)
  • There is a join on a column of the row specified by the first table to any column of the second table.
  • There is a join on a column of the row specified by the first table to any primary or secondary index of the second table. (Correct)
  • There is an equality condition on a non-unique index of one table.

Answer : There is an equality condition on a unique index of one table. There is a join on a column of the row specified by the first table to any primary or secondary index of the second table.

What is the first stage where you make decisions to take full advantage of Teradata parallelism?


Options are :

  • design of the ELDM
  • design of the OLAP
  • design of the PDM (Correct)
  • design of the application code
  • design of the LDM

Answer : design of the PDM

A developer has written several SQL statements. Each statement generates the same report format for different departments and is run at different times. How could the developer simplify this process, retain the efficiency of the SQL, and keep the source secure?


Options are :

  • create a parameterized macro (Correct)
  • create a parameterized stored procedure
  • create a PP2 program using COBOL
  • create a global temporary table to hold the result

Answer : create a parameterized macro

Which three describe the rows returned from the left and right table when using a Left Outer Join? (Choose three.)


Options are :

  • unmatched rows from the right table
  • unmatched rows from the left table (Correct)
  • matched rows from the right table (Correct)
  • matched rows from the left table (Correct)

Answer : unmatched rows from the left table matched rows from the right table matched rows from the left table

What are three characteristics of third normal form? (Choose three.)


Options are :

  • Indexes are defined on the attributes.
  • Attributes must relate to the entire primary key. (Correct)
  • Attributes must relate to the primary key and not to each other. (Correct)
  • Attributes must relate to the entire foreign key.
  • There are no repeating groups (Correct)

Answer : Attributes must relate to the entire primary key. Attributes must relate to the primary key and not to each other. There are no repeating groups

You are creating an application to export some date values. You want to override the current default date form of INTEGERDATE with the statement SET SESSION DATEFORM=ANSIDATE; Which statement is true?


Options are :

  • Date values will continue to be returned in INTEGER form.
  • The statement is syntactically incorrect as written.
  • Date values will be returned in CHAR(10) form (Correct)
  • Date values will be returned in CHAR(8) form.

Answer : Date values will be returned in CHAR(10) form

What are three characteristics of Global Temporary tables? (Choose three.)


Options are :

  • They can utilize permanent journaling. (Correct)
  • They can be created using the WITH DATA option.
  • They can be referenced from a macro. (Correct)
  • They are materialized in the login user's temporary space (Correct)

Answer : They can utilize permanent journaling. They can be referenced from a macro. They are materialized in the login user's temporary space

In which two ways can you enforce transaction integrity in Teradata mode on a single platform? (Choose two.)


Options are :

  • Issue a COMMIT WORK statement after the last statement in the transaction.
  • Create a reference constraint to enforce the integrity.
  • Create an implicit transaction using a multi-statement request. (Correct)
  • Encapsulate statements between the Begin Transaction (BT) and End Transaction (ET) statement pair. (Correct)

Answer : Create an implicit transaction using a multi-statement request. Encapsulate statements between the Begin Transaction (BT) and End Transaction (ET) statement pair.

What is an advantage of using the WITH NO CHECK OPTION over the WITH CHECK OPTION ?


Options are :

  • There is no extra cost for inserting rows into the referencing table (Correct)
  • Join elimination is possible.
  • There is no extra cost for deleting rows from the referenced table.
  • No unique index is required on the referenced column list.

Answer : There is no extra cost for inserting rows into the referencing table

What can be used to provide column level security? (Choose two.)


Options are :

  • view or macro (Correct)
  • roles and profiles
  • sparse index
  • access rights (Correct)

Answer : view or macro access rights

Which three statements about the TPump utility are true? (Choose three.)


Options are :

  • SQL operations may be performed on multiple tables simultaneously. (Correct)
  • Target tables may have secondary indexes and referential integrity constraints. (Correct)
  • Near real-time updates may be achieved. (Correct)
  • It provides an alternative to MultiLoad for the high volume batch maintenance of large databases.
  • Data is loaded using stored procedures.

Answer : SQL operations may be performed on multiple tables simultaneously. Target tables may have secondary indexes and referential integrity constraints. Near real-time updates may be achieved.

What are three characteristics of tactical queries? (Choose three.)


Options are :

  • multiple tables scanned
  • defined response time requirements (Correct)
  • highly tuned (Correct)
  • use direct access path (Correct)

Answer : defined response time requirements highly tuned use direct access path

What is an advantage of using the WITH NO CHECK OPTION over the WITH CHECK OPTION ?


Options are :

  • Join elimination is possible.
  • There is no extra cost for deleting rows from the referenced table.
  • There is no extra cost for inserting rows into the referencing table. (Correct)
  • No unique index is required on the referenced column list.

Answer : There is no extra cost for inserting rows into the referencing table.

Assume the following stored procedure is defined and executed as follows: CREATE PROCEDURE test_set (IN V1 INT, INOUT V2 INT, OUT V3 INT) BEGIN SET V3 = V1 + 2; SET V2 = V2 * V2 END; CALL call_test1 (4, 5, V3); What are the output values for V2 and V3 ?


Options are :

  • 16, 5
  • 25, 7
  • 25, 6 (Correct)
  • 16, 7

Answer : 25, 6

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions