A score of 24 of the 30 is normally considered a passing score. Use this information to assess your readiness to take the Teradata Certification Exams. When you are satisfied with your answers, click on the SUBMIT button at the end of the test. You will receive immediate notification of your score. 1. What are three purposes of normalization? (Choose three.) to eliminate peculiar data sources to provide optimum flexibility to minimize data access spikes to minimize redundancy to eliminate update anomalies 2. Which two items represent derived data? (Choose two.) data derived from an input flat file data loaded into the physical model aggregate data data calculated from other attributes 3. Which statement is true of identity columns? System assigned primary keys are useful when no quality natural key is available. The identity column used with the DEFAULT column attribute is beneficial for system assigned primary keys . System assigned primary keys always ensure row uniqueness. You can add an identity column with the ALTER TABLE command. 4. Which three functions affect output only, not base table row selection? (Choose three.) WHERE UNION WITH... BY... EXISTS HAVING 5. Which two statements are true about a USI? (Choose two.) You can have up to 64 USIs per table. The requested USI row is accessed by hashing to its subtable. You can use the Teradata Index Wizard utility to help select a USI. All USI columns are created with a Unique constraint. 6. Which three statements are true about a NUSI? (Choose three.) A NUSI can be value-ordered or hash-ordered. NUSI access is always an all-AMP operation. The sort key in a value ordered NUSI cannot exceed four bytes. A value ordered NUSI may have up to sixteen numeric columns. 7. What are three ways that a join index can improve performance? (Choose three.) By referencing the join index, the SQL no longer needs to reference the base tables. By reducing the number of rows scanned, a sparse join index can reduce the full scan time of the join index. By pre-joining commonly joined base tables, a join index can eliminate the need to enforce Referential Integrity. By pre-aggregating an existing table, a join index can reduce aggregation overhead. By re-hashing an existing table, a join index can reduce re-hashing overhead. 8. Which two statements are true about how Teradata distributes and retrieves data? (Choose two.) A user may dictate the most efficient access method in the SQL statement. A simple select on a UPI requires the use of a spool file. Both partitioned and non-partitioned primary indexes are hash distributed. If a table is defined with a UPI, the Optimizer will always use it for distribution. 9. Which LOCKING modifier does not issue an all-AMP lock? LOCKING COLUMN columnname FOR ACCESS LOCKING TABLE tablename FOR ACCESS LOCKING VIEW viewname FOR ACCESS LOCKING ROW FOR READ 10. What should be coded when it is undesirable to have a requesting statement wait for resources, possibly tying up other resources while waiting? NOWAIT SUSPEND MODE ON WAIT SET SUSPEND_WAIT_MODE ABORT ON WAIT 11. When does a transaction lock timeout? forty seconds two minutes four minutes four hours never 12. How do multi-table join indexes reduce the number of join accesses to base tables in the database? They "pre-join" data defined in base tables. They give the Optimizer the ability to create a dynamic index. They provide pointers that go directly to the base tables to eliminate the need for a join. They allow the Optimizer to choose a NUSI instead of a NUPI. 13. Which operation must take place to prepare for a Product Join? The smaller table must be duplicated on every AMP. Both the larger and the smaller table must be duplicated on every AMP. Both the larger and the smaller table must be sorted on the joined value. The smaller table must be sorted on the join condition value. 14. Which three options may reduce the row redistributions often needed to prepare for Hash and Merge Joins? (Choose three.) define NUSIs on frequently joined columns define join indexes on frequently joined columns collect statistics on joined columns choose primary indexes on frequently joined columns 15. When joining Partitioned Primary Index (PPI) tables, which two factors determine the number of PPI table rows cached? (Choose two.) the size of the sliding window set in the DBSControl record the Optimizer chooses a sliding window size based on collected statistics the size of a cacheable table set in the DBSControl record the size of the partition(s) specified in the CREATE TABLE statement 16. Which two statements about EXPLAIN are correct? (Choose two.) It indicates why Optimizer makes certain choices. Times shown are accurate for determining how long a query will run. Takes into account the query load running on the system. It is useful for evaluating complex queries. Times shown are valuable for comparisons against other queries. 17. An EXPLAIN output contains the following phrase: group amps Which two statements are true?(Choose two.) The AMPs are being grouped in preparation of a vertical sort. The initial broadcast step will be sent to all AMPs. Subsequent steps will use a subset of AMPs instead of all AMPs. The AMPs are being grouped in preparation of a global aggregation step. 18. An EXPLAIN output contains the following phrase: "SORT to partition Spool 3 by rowkey" Which two statements are true? (Choose two.) Spool 3 is being sorted in preparation of joining to a non-partitioned table. Spool 3 is to be partitioned by partition number within row hash. Spool 3 is being sorted in preparation of joining to a partitioned table. Spool 3 is to be partitioned by row hash within partition number. 19. Which three join plans are available within Teradata? (Choose three.) Exclusion Nested Join Inclusion Merge Join Exclusion Product Join Exclusion Merge Join 20. Which statement is true concerning a RowID Join? A RowID Join is a Merge Join where the first right table row that matches the left row is joined. A RowID Join retrieves rows from two tables and then puts them on the same AMP based on the row hash of the columns involved in the join. A RowID Join is a special form of the Nested Join used when the first condition in the query specifies a literal for the first table. A RowID Join is a special Merge Join where only the rows that do not satisfy a NOT IN condition specified in the request are joined. 21. Table x has a UPI defined on integer col_a and a USI defined on integer col_b. Given the following query: SELECT col_c, col_b FROM x WHERE col_a = 5; Which index related phrase is seen in the EXPLAIN output? by way of row hash by way of spool # n by way of the unique secondary index by way of the unique primary index 22. When nested views are created, which privilege is required by the creator of the views to allow another user to retrieve data using a nested view? SELECT WITH GRANT option CREATE VIEW WITH GRANT option DROP VIEW SELECT 23. How can a reference index row be marked as invalid? by adding an RI constraint to an unpopulated table by adding an RI constraint to a populated table by archiving a table that has RI constraints defined by altering the definition of an existing RI constraint 24. What are three advantages of global temporary tables? (Choose three.) avoid denormalizations such as prejoins and summary tables reduce spool usage permanently materialized increase application code alternative to join indexes that require permanent space 25. Which two CREATE TABLE options produce subtables in Teradata? (Choose two.) INDEX FREESPACE PRIMARY KEY MULTISET FALLBACK 26. Which two traits are copied, by default, into the target table when using the CREATE TABLE AS (subquery); form of the CREATE TABLE command? (Choose two.) secondary indexes PRIMARY KEYS COMPRESS data types 27. Which two statements about Partitioned Primary Indexes (PPIs) are true? (Choose two.) PPI tables must include the partitioning column as part of a NUPI. PPIs must not be defined on Identity columns. PPIs must not be defined on global temporary or volatile tables. PPI tables must not include the partitioning column as part of a UPI. 28. Which two SQL applications might result in the user running out of spool space? (Choose two.) an insert select merging two or more tables into one a table create statement a report generated with an embedded join a multiload delete function a full table update of one or more data columns 29. Which operation is used to create a free cylinder for a maintenance operation that has run out of space? QRYCONFIG SHOWSPACE MINI-CYLPACK DEFRAG PACKDISK 30. Which three options are used in sizing a unique secondary index? (Choose three.) number of AMPs row count block overhead blocksize index value size