Teradata Expert Quiz

Posted on: | By: – Comments Off

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : When the application uses LOCKING FOR ACCESS[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : A PPI requires current statistics[/bg_collapse]
 
3
A global temporary table cannot have a unique secondary index.

  1. TRUE
  2. False

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : False[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : There can be more than one row with the same Primary Index value.[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Performance[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : To speed up query access and joins.[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : 2[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : To speed up well-known queries by increasing the response time.[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Yes, with the All-AMP Index.[/bg_collapse]



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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : An Exclusion Join.[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Left Outer Join.[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : You can create the GTT with a ‘Partition by’ clause[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Volatile table can be populated using Fast Load and Multi Load utilities[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : A PPI is best used for queries that specify range constraints[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Access and distribution[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : They are logical constructs that guarantee uniqueness[/bg_collapse]
 
17
All roles assigned to a user can be enabled using the SET ROLE ALL statement

  1. True
  2. FALSE

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : True[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Modify user[/bg_collapse]
 
19
Single column NUSIs are stored in –

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : DBC.Tvfields[/bg_collapse]



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

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : DBC.SysSecDefaults[/bg_collapse]
 
21
What table would you access to see all the failed logon attempts?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : DBC.LogonOff[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : The hash value is calculated using a mathematical algorithm[/bg_collapse]
 
23
Which API controls Teradata connectivity?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : CLI[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : A single logical data store is acessed by all Unit of Parallelisms[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : WHERE, GROUP BY, HAVING[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : CREATE TABLE t9 AS (SELECT * FROM t7) WITH DATA;[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : If the keyword INNER is specified.[/bg_collapse]
 
28
Which type of table provides the DDL, but does not share the data?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Global Temporary[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : HAVING and QUALIFY[/bg_collapse]



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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : No Confidence[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : SELECT[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : The statement remves any explicit privileges on the table[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : COMMIT or ROLLBACK statements[/bg_collapse]
 
34
Which one can’t be used to secure data?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Locking Modifiers[/bg_collapse]
 
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)

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : COALESCE(num_employees, 0)[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : SON[/bg_collapse]
 
37
Which function can change a column with TIMESTAMP data type to a DATE data type?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : CAST[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Global temporary tables[/bg_collapse]
 
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;

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : CREATE TABLE t9 AS (SEL * FROM t7) WITH DATA;[/bg_collapse]



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)

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : NULLIF(X,Y)[/bg_collapse]
 
41
What will be the result if you convert (100000.6) to SMALLINT?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Error numeric overflow[/bg_collapse]
 
42
SELECT DATE+60 returns: (Assume DATE = 7/31/2001)

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Error[/bg_collapse]
 
43
The date 10th October, 2011 will be internally stored in TD as an integer:

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : 1111010[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : The primary index value in the INSERT row must be the same as the primary value in the targeted UPDATE row[/bg_collapse]
 
45
Fast Export uses the support environment, can export from multiple locations and uses multiple sessions to complete its tasks.

  1. True
  2. FALSE

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : True[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : A tool which imports data demographics and system characteristics from one system to another[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Looks at table demographics and makes recommendations[/bg_collapse]
 
48
Which utility allows the constant updating of rows in a table?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : TPump[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Drop the Join Index, Use MultiLoad to update the table and then re-build the Join Index.[/bg_collapse]



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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Teradata Index Wizard[/bg_collapse]
 
51
Which tool stops queries from running if they violate predefined rules?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : TDQM[/bg_collapse]
 
52
Sessions are obtained in what phase of MultiLoad?

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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Preliminary[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : By changing the Rate Parameter.[/bg_collapse]
 
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.

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Set up Query Capture Database (QCD)[/bg_collapse]
 
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

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Profile Builder[/bg_collapse]
 
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)

[bg_collapse view=”button-green” color=”#FFF” icon=”arrow” expand_text=”Show Answer” collapse_text=”Hide Answer” ]Answer : Windows Data Dictionary (WinDDI)[/bg_collapse]



 
 
 

Related Articles

Advanced Manufacturing Process Quiz

Jenkins Quiz

Jenkins Quiz