Teradata Intermediate Quiz
1) USI Subtables are
- Hash distributed
- Amp Local
- Both A & B
- None
2) NUSI subtables are
- Hash distributed
- Amp Local
- Both A & B
- None
3) Which table will have UNIQUE records
- MULTISET table with NUPI
- MULTISET with UPI
- MULTISET with NUSI
- all of the above
4) Which will return at the most 1 Row
- UPI
- USI
- Both
- None
5) A uniqueness value is required to produce a unique Row ID because of
- NUPI duplicate
- Hash synonym
- none
- both A and B
6) Which statement is NOT true about hash indexes and single-table join indexes?
- Both are maintained by the system when their associated base table is updated.
- Both can be FALLBACK protected.
- Both receive their space from permanent space and are stored as separate tables.
- Both implicitly define a direct access path to base table rows.
7) What improves the performance of accessing and updating a join index?
- setting the OptimizeJoinIndex flag to TRUE in DBSControl
- defining the join index in the same database as the base table it references
- adding the BEFORE JOURNAL option to the join index definition
- collecting statistics on the primary index columns of the join index
8) Which statement is true about identity columns?
- The identity column used with the DEFAULT column attribute is beneficial for system-generated primary keys.
- System-generated primary keys always ensure row uniqueness.
- System-generated primary keys are useful when no quality natural key is available.
- You can add an identity column with the ALTER TABLE command.
9) When is NUSI Bitmapping done?
- When 2 or more weakly selective NUSIs are ANDed together
- When 2 or more weakly selective NUSIs are ORed together
- When 2 or more weakly selective NUSIs are NORed together
- all of the above
10) Which of the below are USI Considerations
- You want to ensure that a column’s values are unique
- You have a column with a high value access frequency and you want users to get to the data quickly
- You have a SET table that has a NUPI and you want to eliminate Duplicate row checking
- all of the above
11) Duplicate row checking of an NUPI table can be eliminated by
- changing PI to UPI
- creating a NUSI
- Creating a USI
- all of the above
12) Which of the following about Hash Indexes is true?
- Hash indexes can have secondary indexes
- Statistics cannot be collected on a Hash index
- Hash index can be compressed
- The column list can contain aggregate functions
13) _________ is a technique used to effectively link several weakly selective indexes
- Hash Collisions
- Multi table Join index
- Bit Mapping
- Hash synonyms
14) ________ removes the need to redistribute rows of a table for a frequently made join by redistributing the table on a vertical subset of data
- Single table Join index
- Secondary index
- Aggregate Join Index
- Primary Index
15) When rows are inserted into a table with an Identity Column, the Identity Column will always be consecutively numbered?
- TRUE
- False
16) Which of the following is true for a PPI –
- A PPI cannot be unique
- A PPI can contain an identity column
- A PPI cannot be unique unless all its partitioning columns are included in the index definition
- The result of the partitioning expression can be a character
17) The LOG/NO LOG option in the CREATE statement is applicable to –
- Permanent tables
- Secondary Indexes
- Join indexes
- Volatile tables
18) ________ returns the hexadecimal row hash value for an expression
- HASHAMP
- HASHBAKAMP
- HASHBUCKET
- HASHROW
19) _______ system view gives every explicit right for the priveleges on objects given to a user
- allrights
- allrolerights
- association
- authorizations
20) _______ system view gives CPU and I/O accounting information saved by the DBC
- dbcinfo
- collations
- databases
- ampusage
21) A user assigned to multiple roles can change his role using the ________ statement
- create role
- Set role
- Grant role
- Grant role with Admin option
22) Rows in an AMP are ordered by
- Table ID, Row hash values, uniqueness value,Partitions
- Row hash values, uniqueness value, table ID,partitions
- Table ID, partitions,Row hash values, uniqueness value
- None
23) Once the target AMP has been determined for a PI
search, the _______ ________ for that AMP must
be consulted
- Table ID
- data block
- cylinder index
- master Index
24) How does Teradata handle data distribution when performing system configuration changes
- Creates an output map to aid in redistributing data
- Data distribution is automatic
- Creates a reconfig map to help the DBA repartition data
- None of the above
25) Which of the following is not needed to attach Teradata to a mainframe?
- PE
- Escon Connection or BUS/TAG cables
- BYNET
- Host Channel Adapter PDE
26) At run time Macros are
- pre-optimized
- Re-optimized
- not-compiled
- none
27) Do privileges on Objects referenced in a Macro require permission to be granted to users that run them?
- yes
- no
- yes if objects on system tables
- yes if objects are on other user tables
28) What is not a characteristic of Aggregate Join Index?
- the result is stored as FLOAT
- only COUNT and SuM are permitted
- DISTINCT is not permitted
- The Join Index can be queried directly
29) Low confidence’ in EXPLAIN indicates
- Poor joining strategy
- statistics is not collected
- join access is bad
- value access is bad
30) TableA has a Secondary index defined on Col1. Which of the following SQLs results in a full table scan?
- sel * from tableA where substring(col1,1,1) = ‘2’;
- Sel distinct col2 from tableB where col1 in (‘1, ‘2’);
- Sel * from table a where col1 = ALL;
- Sel col1, col2 from tableA where col1 = ‘1’
31) Which of the following indexes can have aggregate functions in its DDL?
- Secondary index
- Primary Index
- Join Index
- Hash Index
32) What is the result of the following SQL :
select ROWID from tableB;
- Gives the rowid of each row in the table
- Gives the Rowid of the first row in the table
- Query returns an error since ROWID keyword cannot be used
- Query returns zero rows
33) The phrase “GENERATED DEFAULT AS IDENTITY” in the CREATE TABLE statement –
- always inserts unique system-generated values in the identity column
- inserts unique system-generated values for the identity column when no user value is provided
- always inserts user-provided values for the identity column
- inserts duplicate system-generated values in the identity column
34) A Foreign Key column in a referenced table is defined as NOT NULL. A row is inserted into the referencing table. What one thing does Referential Integrity check?
- If the row inserted is a Foreign Key row
- If NO CHECK OPTION has been defined on the referenced table
- If the row exists in the referenced table with the same values as that in the referencing columns
- If the Foreign Key in the referencing column is an Identity Column
35) Statistical sampling is –
- Collection of statistics on a small sample of rows on a single AMP
- Collection of statistics on all rows in a group of AMPs
- Collection of statistics on a small sample of rows from all AMPs
- Collection of statistics on the entire table
36) What does the following SQL do –
SELECT ADD_MONTHS (CURRENT_DATE, 60*6);
- Adds 360 days to the current date
- Adds 30 years to the current date
- Adds 60 months and 6 years to the current date
- Adds 60 years and 6 months to the current date
37) What does the following SQL do –
SELECT COUNT(Distinct DeptNo) FROM Employee;
- Gives a count of all Dept Numbers in the Employee table
- Distinct cannot be used with an aggregate function.
- Gives a count of unique Dept Numbers in the Employee table including nulls
- Gives a count of unique Dept Numbers in the Employee table excluding nulls
38) What does the following SQL do –
SELECT item, smonth, sales,
AVG(sales) OVER (PARTITION BY item
ORDER BY smonth
ROWS 3 PRECEDING)
FROM sales_tbl
ORDER BY item, smonth;
- Gives the sales of each item averaged over the preceeding 3 months
- Gives the average sales of the top 3 items in each month
- Gives the total average sales in the preceeding 3 months
- Gives the sales of each item averaged over the current month and the preceding 3 months
39) Select EmpName, EmpNo from Employee where (DeptNo, DeptStatus) NOT IN
(Select DeptNo, DeptStatus from Dept where DeptNo = 100).
If there are no rows in Dept table for DeptNo = 100, what will be the result of the above SQL?
- No rows will be returned
- All rows from Employee including those having DeptNo as null will be returned
- Rows from Employee having DeptStatus as null will be returned
- All rows from Employee except those having DeptNo as null will be returned
40) What is the result of the following SQL –
SELECT HASHROW (C1,C2) FROM T;
- The rowhash value of column C1
- The rowhash value of column C2
- The rowhash value of columns C1 and C2 combined
- Multiple columns cannot be specified in the Hashrow function
41) The following SQL is equivalent to –
SELECT city, kind, sales,
SUM(sales) OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM monthly
- Cumulative sum
- Group sum
- Moving sum
- Moving Difference
42) ______ is expressed as an approximate numeric ratio between 0.0 and 1.0
- RANK
- ROW_NUMBER
- QUANTILE
- PERCENT_RANK
43) What is the order of execution of the following –
table_A
LEFT OUTER JOIN table_B
RIGHT JOIN table_C ON join_condition
ON join_condition
- Table A is left outer joined with Table B and the result is right outer joined with table C
- Table B is right outer joined with Table C and the result is left outer joined with Table A
- Table A is right outer joined with Table C and the result is left outer joined with Table B
- The SQL returns an error
44) Another name for the EXCEPT operator is __________.
- INSTEAD OF
- NOT IN
- INTERSECT
- MINUS
45) SELECT CAST (‘bb12345bb’ AS Char(3)). (b = space). What is the answer set returned from the following query?
- 123
- 1
- bb1
- No answer set is returned because you can’t cast Integer data as Char.
46) Constant updating of rows in a table is achieved by
- Multiload
- Fastload
- Fastexport
- Tpump
47) Which utility loads to populated tables at block level?
- Multiload
- Fastload
- Fastexport
- Tpump
48) What is not a characteristic of TPump?
- can utilize stored procedures
- has no session limit
- supports conditional processing
- can change the rate at which statements are sent to be processed
49) Which utility should you use to populate an empty table with 100 million rows of data?
- Fastload
- Multiload
- TPUMP
- Fastexport
50) The _____ has a built in report formatting capability
- Multiload
- TPUMP
- Fastexport
- BTEQ
51) You have a table with 10,000,000,000 rows. Which utility would you use to update 8,000,000,000 of the table’s rows?
- Multiload
- TPUMP
- Fastexport
- BTEQ
52) The following operation requires the table to exist when moving data from archived files to the Teradata database –
- Restore
- Archive
- Copy
- Build
53) _______ uses performance and allocation groups to define different priorities for different categories of work
- TDQM
- Priority Scheduler
- Teradata Warehouse Builder
- Locking logger
54) Which of the following BTEQ commands submits an operating system command to the network-attached system
- TSO
- OS
- RUN
- COMPILE
55) The _______ option in Fastload is used to specify the number of hours that the utility tries to log on when the maximum number of load operations are alreast running in the database
- TENACITY
- SLEEP
- LOGON
- SESSIONS
56) Which of the following can be used to enhance input records to Fastload before sending them to the Database
- OUTMOD routine
- Notify exit routine
- INMOD routine
- DEFINE command
57) A Multiload job can be paused intentionally using the PAUSE command in the script
- True
- FALSE
58) Which of the following is NOT a valid command in a multiload job script?
- insert into t1 (col1, col2) values (:c1 (format ’hhmmss’),:c2);
- .Accept age, name from file info;
- Delete From Employee Where YrsExp < 5;
- .DML label COMPLEX Ignore duplicate insert rows Do insert for missing update rows;
59) The AMPCHECK APPLY option in multiload –
- Allows the multiload job to continue as long as no more than one AMP is down in a cluster
- Inhibits the Mulitload job from entering or exiting the Application phase when an AMP is down
- Pauses the multiload job when an AMP is down
- All of the above
60) What Does Session Control Component of PE Performs?
- Controlling number of sessions executed
- SQL Interpretation for session
- logon and logoff
- returns the result to the user
61) What are the actions performed by Parser Component of PE?
- Checks SQL Syntax
- ensure that all the objects and columns exist
- developing least expensive plan
- A&B
62) Which PE Component that enables teradata to handle multiple complex , ad hoc queries efficiently?
- Session Control
- Parser
- Dispatcher
- Optimizer
63) How Does Fault Tolerance feature in BYNET work?
- Automatically reconfigure that network so all messages avoid the unusable path.
- Automatically and dynamically distributes traffic between both BYNETs.
- Increases system size without loss of performance
- Message is routed to one specific node in the system
64) AMP is responsible for which duties listed below?
- Retrieve information from detentions and tables
- Insert, delete, or modify rows with in tables
- Lock databases and tables
- A,B & C
65) A clique is a set of Teradata nodes that share a common set of ?
- virtual processors
- BYNET
- Disk Arrays
- A&C
66) Create, modify, or delete definitions of tables are performed by?
- PE
- AMP
- Parser
- Optimizer
67) Which one is a software interface layer on top of the operating system that enables the RDBMS to operate in a parallel environment?
- AMP
- BYNET
- PDE
- PE
68) In the event of three out of four nodes failing, the remaining node would attempt to absorb all virtual processors from the failed nodes,in such situvation total number of virtual processor for the clique should not exceed ?
- 128
- 32
- 64
- 120
69) NUPI access always invovles how many AMP(s)?
- One or More
- More than One
- One
- Two or More
70) What is the Size of Row Hash?
- 24-bit
- 32-bit
- 16-bit
- 64-bit
71) Maximum Combination of Columns allowed for PI are?
- 32
- 16
- 64
- 128
72) Each Row can be uniquely identified by?
- Row Hash
- Uniqueness Value
- AMP ID
- A&B
73) Which Key word from explain plan confirms the Full Table scan
- Full Table Scan
- Product Join
- All Row scan
- Low Confidence
74) Which statement is TRUE about PERM Sapce for Database Objects
- Tables and Views Require PERM Space
- Stored Procedures and Macros Doesn’t Require PERM space
- Views and Macros Doesn’t require PERM Sapce
- UDF’s Doesn’t require PERM Sapce
75) A database or user with no PERM Space may not contain?
- Tables and Views
- Tables and Stored procedures
- views and Macros
- UDF’s
76) Which Space defines the upper limit for a user Query?
- Spool Space
- Perm Space
- Temp Space
- DB Space
77) PERM space utilized can be calculated using which DBC view/table?
- DiskSpace
- SpaceUtilization
- Space
- Disk
78) After Compression Where does the actual PI value will be stored?
- Header Block
- Logical Block
- PI Cannot be Compressed
- Table level Block
79) Choose the Correct DDL for creating Employee table with compression
- CREATE MULTISET TABLE Employee (
Column1 INTEGER COMPRESS 0,
Column2 INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,8 ),
Column3 DATE FORMAT ‘YYYY-MM-DD’ NOT NULL COMPRESS (DATE ‘0001-01-01’),
Column4 DECIMAL(18,3) COMPRESS 0.000 ,
Column5 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS (‘N’,’Y’),
) - CREATE MULTISET TABLE Employee (
Column1 INTEGER,
Column2 INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,8 ),
Column3 DATE FORMAT ‘YYYY-MM-DD’ NOT NULL COMPRESS (DATE ‘0001-01-01’),
Column4 DECIMAL(18,3) COMPRESS 0.000 ,
Column5 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS (‘N’,’Y’),
) - CREATE MULTISET TABLE Employee (
Column1 INTEGER,
Column2 INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,8 ),
Column3 DATE FORMAT ‘YYYY-MM-DD’ NOT NULL COMPRESS (DATE ‘0001-01-01’),
Column4 DECIMAL(18,3) COMPRESS 0.000 ,
Column5 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS (‘N’ AND ‘Y’),
) - CREATE MULTISET TABLE Employee (
Column1 INTEGER ,
Column2 INTEGER COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,8 ),
Column3 DATE FORMAT ‘YYYY-MM-DD’ NOT NULL COMPRESS (0001-01-01),
Column4 DECIMAL(18,3) COMPRESS 0.000 ,
Column5 CHAR(1) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS (‘N’,’Y’),
)
80) Between VARCHAR vs Compression VARCHAR is best when?
- when the difference of maximum and average field length is low and compressibility is high
- when the difference of maximum and average field length is average and compressibility is low
- when the difference of maximum and average field length is high and compressibility is high
- when the difference of maximum and average field length is high and compressibility is low
81) Between VARCHAR vs Compression Compression is best when?
- when the difference of maximum and average field length is low and compressibility is high
- when the difference of maximum and average field length is average and compressibility is low
- when the difference of maximum and average field length is high and compressibility is high
- when the difference of maximum and average field length is high and compressibility is low
82) Which Locking mechanism is never applied to rows?
- Access lock
- Write Lock
- Exclusive lock
- Read Lock
83) Which Locking mechanism is also referred as “stale read” lock
- Access lock
- Write Lock
- Exclusive lock
- Read Lock
84) Which Locking mechanism are used for DML statements that access by primary index or unique secondary index?
- Table level locks
- Database level locks
- Read locks
- Row Hash locks
85) Which locking modes are Placed in response to an INSERT, UPDATE, or DELETE request?
- Read and Write Locks
- Exclusive Lock
- Write Lock
- Access Lock
86) When There is a Read Lock on the table and a request needing Write lock is triggered what will be status of the second request?
- Lock Granted
- Request Queued
- Request Abort
- Error message
87) On Customer table Locks are triggered in following order
- User1 ACCESS
- User2 WRITE
- User3 EXCLUSIVE
- User4 ACCESS
which users get immediate access to the “Customer Table” and which users need to wait for the which other user.
- User1 READS the Object Customer Table IMMEDIATELY
- User2 is compatible and WRITES on the Object Customer Table
- User3 must wait on both User1 and User2
- User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock
- User1 READS the Object Customer Table IMMEDIATELY
- User2 is not compatible and does not WRITES on the Object Customer Table
- User3 must wait on both User1 and User2
- User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock
- User1 READS the Object Customer Table IMMEDIATELY
- User2 is compatible and WRITES on the Object Customer Table
- User3 must wait on both User1 and User2
- User4 can access parallel with user1
- User1 READS the Object Customer Table IMMEDIATELY
- User2 is compatible and WRITES on the Object Customer Table
- User3 takes priority over both User1 and User2
- User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock
88) when FALLBACK specifies that the system builds a duplicate copy of each row of the table and stores it on?
- Different AMP in the adjacent cluster.
- Different AMP within the cluster.
- Same AMP within the cluster.
- None
89) Which Statements are TRUE with respect to PPI
- UPI – partitioning column need not be part of PI
- NUPI – can be partitioned on any column, need not be part of PI
- UPI – partitioning column need not be part of PI
- NUPI – partioning column need to be part of PI
- UPI – partitioning column need to be part of PI
- NUPI – can be partitioned on any column in PI
- UPI – partitioning column need to be part of PI
- NUPI – can be partitioned on any column – need not be part of PI
90) Which is the correct DDL for PPI with RANGE_N partition?
- CREATE TABLE ORDER_MASTER(
ORDER_NUMBER INTEGER,
ORDER_DATE DATE
)
UNIQUE PRIMARY INDEX(ORDER_NUMBER, ORDER_DATE)
PARTITION BY RANGE_N
(
ORDER_DATE BETWEEN DATE ‘2008-01-01’ AND ‘2008-06-30’
EACH INTERVAL ‘1’ MONTH
); - CREATE TABLE ORDER_MASTER(
ORDER_NUMBER INTEGER,
ORDER_DATE DATE
)
UNIQUE PRIMARY INDEX(ORDER_NUMBER)
PARTITION BY RANGE_N
(
ORDER_DATE BETWEEN DATE ‘2008-01-01’ AND ‘2008-06-30’
EACH INTERVAL ‘1’ MONTH
); - CREATE TABLE ORDER_MASTER
(
ORDER_NUMBER INTEGER,
ORDER_DATE DATE
)
PRIMARY INDEX(ORDER_NUMBER)
PARTITION BY RANGE_N
(
ORDER_DATE BETWEEN DATE ‘2008-01-01’ AND ‘2008-06-30’
EACH INTERVAL ‘1’ MONTH
); - A&C
91) Which statements are true about (NO CASE or NO RANGE), UNKNOWN partitions in PPI
- NO CASE, UNKNOWN – two different partitions
- NO CASE OR UNKNOWN – only one partition for rows not meeting the criteria and also for rows with NULL values
- NO CASE or NO RANGE -partition to hold rows not part of CASE_ or RANGE_N , UNKNOWN – partition to hold rows with NULL values
- All the Above
92) What is the output if we execute below queries in given order
1.SELECT POSITION(‘e’ IN ‘vijay’);
2.SELECT POSITION(‘i’ IN ‘Ravi’);
3.SELECT POSITION (‘e’ IN ‘Teradata’);
- 0
- 2
- 4
- 0
- 4
- 2
- 0
- 4
- 4
- 0
- 2
- 2
93) Which statements are true with respect to below example subquery
SELECT name FROM Employee
WHERE emp_no IN (SELECT manager_emp_no FROM Dept);
- The outer query will get executed first and for every row of outer query, inner query will get executed.
- The inner query will get executed as many times as no. of rows in result of the outer query.
- The outer query output can use the inner query o
- The outer query will get executed first and for every row of outer query, inner query will get executed.
- The inner query is not dependent on outer query.
- The outer query output can use the inner query output for comparison. This means inner quer
- The inner query is executed only once
- The inner query will get executed first and the output of the inner query used by the outer query.
- The inner query is not dependent on outer query.
- The inner query is executed only once
- The inner query will get executed as many times as no. of rows in result of the outer query.
- The inner query is not dependent on outer query.
94) Which statements are true with respect to below example Correlated
SELECT name, dept_id FROM Employee e1 WHERE sal in (SELECT MAX(sal) FROM Employee e2
WHERE e1.dept_id=e2.dept_id);
- The outer query will get executed first and for every row of outer query, inner query will get executed.The inner query will get executed as many times as no. of rows in result of the outer query.The outer query output can use the inner query o
- The outer query will get executed first and for every row of outer query, inner query will get executed.The inner query is not dependent on outer query.The outer query output can use the inner query output for comparison. This means inner querThe inner query is executed only once
- The inner query will get executed first and the output of the inner query used by the outer query.The inner query is not dependent on outer query.The inner query is executed only once
- The inner query will get executed as many times as no. of rows in result of the outer query.The inner query is not dependent on outer query.
95) Which Statement(s) are true about use of Correlated Subquery?
- Faster than subquery when the column used for comparision between inner & outer query is a PI or SI
- Excellent technique to use when there is a need to determine which rows to SELECT based on an aggregate of another table.
- It only fetches aggregate when a particular account is read for processing, not for all accounts like a normal subquery.
- A,B & C
96) Which Statements are true about Global Temporary Table
- Data is active till the session ends, and definition will remain till it is dropped using Drop table statement
- When materialized data goes in temp space Multiple concurrent users can reference the same GTT, but the contents are not shareable between users
- When Global Temporary Table is created, table structure definition goes into Data DictionaryTable & its Data is not sharable across sessions.Data and table definition both are active only till the session ends (Dropped automatically when the session terminates)
- A&B
97) Which Statements are true about Volatile Table
- Data and table definition both are active only till the session ends (Dropped automatically when the session terminates)
- Table Definition is stored in System cache (Not included in the DBC data dictionary database )When materialized data goes in temp space
- Table & its Data is not sharable across sessions. Data is stored in spool space. Table & its Data is not sharable across sessions.
- 1&3
98) Which statements are true about work tables for TPump versus MultiLoad
- Multiload- Optional, 1per target table. Tpump- No work tables needed
- Multiload- Optional, 2 per target table. Tpump- Optional, 1 per target table
- Multiload- Optional, 1per target table. Tpump- Optional, 1 per target table
- None of the above
99) Which statements are true about “Allows RI, USI, Triggers”
for TPump versus MultiLoad
- MultiLoad- Yes, Tpump- No
- MultiLoad- Yes, Tpump- Yes
- MultiLoad- No, Tpump- Yes
- None of the above
100) Which statements are true about “Locks” for TPump versus MultiLoad
- Multiload- Row, Tpump – Table
- Multiload- Row & Table, Tpump – Table
- Multiload- Table, Tpump – Table
- Multiload- Table, Tpump – Row