Teradata Expert Quiz

 



1) A table is having an INSERT/SELECT performed on it. When is it possible for an application to read a Primary Index Value on that table?

1. When the INSERT/SELECT is on a volatile Table
2. When the application uses NO WAIT
3. When the INSERT/SELECT is from another application
4. When the application uses LOCKING FOR ACCESS

Show Answer

 
 
2) Which statement is FALSE about Partitioned Primary Indexes?

1. A PPI can act take the place of a Secondary Index.
2. If there are a lot of Partitions a PPI could slow Joins
3. A PPI requires current statistics
4. A PPI can assist in the deletion of rows

Show Answer

 
 
3) A global temporary table cannot have a unique secondary index.

1. TRUE
2. False

Show Answer

 
 
4) What is true about Non-Unique Primary Indexes (NUPIs)?

1. Primary Indexes cannot be non unique
2. There cannot be more than one row with the same Primary Index value.
3. Rows in a NUPI will always have a Uniqueness value of one.
4. There can be more than one row with the same Primary Index value.

Show Answer

 
 
5) Why would you pick a Primary Index that is different than the Primary Key?

1. Performance.
2. To avoid full table scans.
3. Unknown Access Paths.
4. You can’t pick a Primary Index that is different than the Primary Key because they are the same thing.

Show Answer

 
 
6) When would you choose a NUPI over a UPI?

1. You should never choose a NUPI over a UPI.
2. To speed up query access and joins.
3. To assist in NULL processing.
4. To decrease overhead created by the UPI subtable.

Show Answer

 
 
7) A USI typically accesses how many AMPs?

1. 1
2. 2
3. 3
4. A USI will always access every AMP via a Full Table Scan

Show Answer

 
 
8) When would you want to use a Join Index on tables?

1. When a Cover Query is not a viable alternative.
2. To speed up well-known queries by increasing the response time.
3. When you are joining two tables in a View.
4. As a result of poor Primary Index selection and uneven distribution

Show Answer

 
 
9) Is it possible to archive Secondary Indexes?

1. No
2. Yes, but only if the Indexes are Unique.
3. Yes, with a DD/D Index.
4. Yes, with the All-AMP Index.

Show Answer

 
 
10) When you usE NOT IN what type of Join are you likely to have?

1. An Inclusion Join.
2. An Exclusion Join.
3. A Cartesian Product Join.
4. A Hash Join.

Show Answer

 
11) Matched rows from the left and right table and unmatched rows from the left table are returned to the User in which type of Join?

1. Right Outer Join.
2. Inner Join.
3. Left Outer Join.
4. Full Outer Join.

Show Answer

 
 
12) Identify the false statement specific to Global Temporary Table(GTT) .

1. The definition for a Global Temporary table is persistent and stored in the data
2. You can create the GTT with a ‘Partition by’ clause
3. To create the base definition for a GTT, use the CREATETABLE statement and specify the keywords GLOBAL TEMPORARY to describe the table type.
4. There are only two occasions when an empty GTT is materialized:
   • A CREATE INDEX statement is issued on the table.
   • A COLLECT STATISTICS statement is issued on the table.

Show Answer

 
 
13) Identify the false statement specific to Volatile Table(VT) .

1. Volatile tables are private to a session. This means that you can log on multiple
2. sessions and create volatile tables with the same name in each session.
3. General table creation options namely like Permanent Journaling, Referential Integrity constraints are not permitted.
4. Volatile table can be populated using Fast Load and Multi Load utilities
5. To create a volatile table, you do not need any privileges.

Show Answer

 
 
14) Which is true about a PPI(Partitioned Primary Index)?

1. A PPI distributes data on the AMPs
2. A PPI is best used for queries that specify range constraints
3. A PPI causes the AMPs to create a PPI subtable
4. PPI is mandatory for all tables because it better distributes the data.

Show Answer

 
 
15) What is the tradeoff that must be considered in making primary index choices?

1. Row size
2. Block size
3. Column datatype
4. Access and distribution

Show Answer

 
 
16) Which is true about Primary keys?

1. NULL data values are allowed
2. Must be mentioned during table creation
3. They are logical constructs that guarantee uniqueness
4. They are physical mechanisms that distribute data

Show Answer

 
 
17) All roles assigned to a user can be enabled using the SET ROLE ALL statement

1. True
2. FALSE

Show Answer

 
 
18) To remove a profile from a member, the following statement is used –

1. Drop Profile
2. Modify profile
3. Modify user
4. Drop user

Show Answer

 
 
19) Single column NUSIs are stored in –

1. TVM
2. Tables
3. Tables2
4. DBC.Tvfields

Show Answer

 
 
20) When a DBA changes password features what table in DBC is updated?

1. Acctg
2. SecSysDefaults
3. Security
4. DBC.SysSecDefaults

Show Answer

 
21) What table would you access to see all the failed logon attempts?

1. Logon
2. LogonLog
3. LogonFail
4. DBC.LogonOff

Show Answer

 
 
22) What is true about Teradata’s handling of Data Distribution?

1. Partitioned Primary Indexes also distribute data.
2. AMPs determine how data is distributed.
3. PE distributes data
4. The hash value is calculated using a mathematical algorithm

Show Answer

 
 
23) Which API controls Teradata connectivity?

1. C Preprocessor 2
2. CLI
3. PM/API
4. BTEQWin

Show Answer

 
 
24) Find the false statement about the Shared Nothing architecture

1. Each Unit of Parallelism is assigned a data portion
2. Locks, buffers, etc not shared
3. Highly Scalable with high Data Volumes
4. A single logical data store is acessed by all Unit of Parallelisms

Show Answer

 
 
25) Select the correct order of clauses.

1. GROUP BY, HAVING, WHERE
2. HAVING, WHERE, GROUP BY
3. GROUP BY, WHERE, HAVING
4. WHERE, GROUP BY, HAVING

Show Answer

 
 
26) Which statement creates a table that is created with SQL and includes Data?

1. CREATE TABLE t9 AS (SELECT * FROM t7) WITH NO DATA;
2. CREATE TABLE t9 AS (SELECT * FROM t7) WITH DATA;
3. CREATE TABLE t9 AS t7;
4. CREATE TABLE t9 AS t7 WITH NO DATA

Show Answer

 
 
27) In a Join, when is the ON clause required?

1. If the Join is a SELF JOIN.
2. Only if the WHERE clause is not used.
3. If the keyword INNER is specified.
4. all the time.

Show Answer

 
 
28) Which type of table provides the DDL, but does not share the data?

1. Global Temporary
2. Permanent
3. Derived
4. Volatile

Show Answer

 
 
29) Which commands do the same thing?

1. HAVING and GROUP BY
2. HAVING and QUALIFY
3. QUALIFY and QUANTIFY
4. QUALIFY and ORDER BY

Show Answer

 
 
30) What words in the EXPLAIN of an SQL statement tell you statistics have not been collected

1. No Sampling Available
2. No Statistics Collected
3. No Confidence
4. Statistic Not Available

Show Answer

 
31) Which clause will limit the columns returned to Users in the CREATE VIEW statement?

1. WHERE
2. SELECT
3. WITH CHECK OPTION
4. WITH NO CHECK OPTION

Show Answer

 
 
32) What is true about the DROP TABLE statement?

1. The statement remves any explicit privileges on the table
2. Columns to be dropped can be specified
3. Permanent Journal space used for the table is released
4. Rows to be dropped can be specified

Show Answer

 
 
33) In ANSI mode, which statements are used to close a transaction?

1. BT or ET statements
2. COMMIT or ROLLBACK statements
3. COMMIT TRASACTION and ROLLBACK TRANSACTION statements
4. BEGIN WORK or END WORK statements

Show Answer

 
 
34) Which one can’t be used to secure data?

1. Privileges
2. Views
3. Macros
4. Locking Modifiers

Show Answer

 
 
35) Assuming the value of num_employees is null, which expression returns 0?

1. TRANSLATE(num_employees, 0)
2. COALESCE(num_employees, 0)
3. NULLIF(num_employees, 0)
4. ZEROIFNULL(num_employees, 0)

Show Answer

 
 
36) What is the result of the following query? SELECT SUBSTR(SUBSTR(‘RALPH JOHNSON’ FROM 7 FOR 7) FROM 5 FOR 3);

1. H J
2. JOH
3. NSO
4. SON

Show Answer

 
 
37) Which function can change a column with TIMESTAMP data type to a DATE data type?

1. EXTRACT
2. SUBSTR
3. INTERVAL
4. CAST

Show Answer

 
 
38) Which type of TD objects could be considered when users need to collect statistics?

1. Global temporary tables
2. Volatile tables
3. Derived tables
4. All three of A, B & C

Show Answer

 
 
39) Which SQL statement creates a table similar to an existing table and includes data:

1. CREATE TABLE t9 AS (SEL * FROM t7) WITH ALL DATA;
2. CREATE TABLE t9 AS (SEL * FROM t7) WITH DATA;
3. CREATE TABLE t9 AS t7;
4. CREATE TABLE t9 WITH DATA as t7;

Show Answer

 
40) Which function return NULL when X=Y and X when it does not?

1. NULLIF(X,Y)
2. COALESCE(X,Y)
3. ZEROIFNULL(X,Y)
4. NULLIFZERO(X,Y)

Show Answer

 
 
41) What will be the result if you convert (100000.6) to SMALLINT?

1. 6
2. 100000
3. 100001
4. Error numeric overflow

Show Answer

 
 
42) SELECT DATE+60 returns: (Assume DATE = 7/31/2001)

1. 30-09-2001
2. Error
3. 9/31/2001
4. 02-08-2001

Show Answer

 
 
43) The date 10th October, 2011 will be internally stored in TD as an integer:

1. 1011010
2. 1111010
3. 1001110
4. 1001011

Show Answer

 
 
44) Which of the following is true about an Atomic Upsert in Tpump –

1. The UPDATE should partially specify the primary index
2. The INSERT should use a subquery to specify any of the inserted values.
3. The primary index value in the INSERT row must be the same as the primary value in the targeted UPDATE row
4. All of the above

Show Answer

 
 
45) Fast Export uses the support environment, can export from multiple locations and uses multiple sessions to complete its tasks.

1. True
2. FALSE

Show Answer

 
 
46) TSET is –

1. A tool which gives an administrator the ability to monitor access requests in the system
2. A tool which imports data demographics and system characteristics from one system to another
3. A tool which is helpful in recommending Secondary Indexes
4. A tool which provides a graphical representation of performance data

Show Answer

 
 
47) The statistics wizard –

1. Controls when queries are executed based on whether or not statistics have been collected
2. Looks at table demographics and makes recommendations
3. Schedules ResUsage collections
4. Will set ResUsage rates to optimize RDBMS performance

Show Answer

 
 
48) Which utility allows the constant updating of rows in a table?

1. Multiload
2. FastLoad
3. TPump
4. FastExport

Show Answer

 
 
49) How do you update a table using MultiLoad with a Join Index?

1. Update the table with MultiLoad as MultiLoad will now rebuild Join Indexes automatically.
2. Drop the Join Index and then run MultiLoad to update the table with the REBUILD INDEXES command.
3. You would use FastLoad to update a table with a Join Index, not MultiLoad.
4. Drop the Join Index, Use MultiLoad to update the table and then re-build the Join Index.

Show Answer

 
 
50) Which tool will show you the best possible set of secondary indexes?

1. Teradata Dynamic Query Manager (TDQM)
2. Teradata Index Wizard
3. Teradata Secondary Index Optimizer (TSIO)
4. PMON

Show Answer

 
51) Which tool stops queries from running if they violate predefined rules?

1. DBQL
2. TDQM
3. PMON
4. MOSI

Show Answer

 
 
52) Sessions are obtained in what phase of MultiLoad?

1. Acquisition
2. Preliminary Final
3. AGR-Init
4. AGR-Detail

Show Answer

 
 
53) How can you lessen TPump’s affect on system resources?

1. By changing the Rate Parameter.
2. By dropping all Secondary Indexes, Triggers and Referential Integrity.
3. By using TPump only in an OLTP environment.
4. By changing Priority Scheduler settings.

Show Answer

 
 
54) What is the prelimenary step to perform or setup to be done, before running Visual Explain tool?

1. Execute Collect Statistics
2. Set up Query Capture Database (QCD)
3. Preperation of the complex Query
4. Install Visual Explain Tool in Customized Mode.

Show Answer

 
 
55) The ability of the Teradata Manager to create a view to configure a specific set of Teradata Applications for a specific monitoring is called..

1. Profiler
2. Profile Maker
3. Profile Builder
4. View Profiler

Show Answer

 
 
56) The Teradata Manager application which perform database administration tasks on the associated Teradata RDBMS computer is called ..

1. Unix Data Dictionary (UniDDI)
2. Windows Data Dictionary (WinDDI)
3. Legacy Data Dictionary(LgcyDDI)
4. AS400 Data Dictionary(AS400DDI)

Show Answer