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 CREATE TABLE 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 sessions and create volatile tables with the same name in each session.
2. General table creation options namely like Permanent Journaling, Referential Integrity constraints are not permitted.
3. Volatile table can be populated using Fast Load and Multi Load utilities
4. 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. 100000.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
3. Final
4. AGR-Init, 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