Teradata Quiz

Teradata Intermediate Quiz

 



1) USI Subtables are

  1. Hash distributed
  2. Amp Local
  3. Both A & B
  4. None

Answer : A

 
 
2) NUSI subtables are

  1. Hash distributed
  2. Amp Local
  3. Both A & B
  4. None

Answer : B

 
 
3) Which table will have UNIQUE records

  1. MULTISET table with NUPI
  2. MULTISET with UPI
  3. MULTISET with NUSI
  4. all of the above

Answer : B

 
 
4) Which will return at the most 1 Row

  1. UPI
  2. USI
  3. Both
  4. None

Answer : C

 
 
5) A uniqueness value is required to produce a unique Row ID because of

  1. NUPI duplicate
  2. Hash synonym
  3. none
  4. both A and B

Answer : D

 
 
6) Which statement is NOT true about hash indexes and single-table join indexes?

  1. Both are maintained by the system when their associated base table is updated.
  2. Both can be FALLBACK protected.
  3. Both receive their space from permanent space and are stored as separate tables.
  4. Both implicitly define a direct access path to base table rows.

Answer : D

 
 
7) What improves the performance of accessing and updating a join index?

  1. setting the OptimizeJoinIndex flag to TRUE in DBSControl
  2. defining the join index in the same database as the base table it references
  3. adding the BEFORE JOURNAL option to the join index definition
  4. collecting statistics on the primary index columns of the join index

Answer : D

 
 
8) Which statement is true about identity columns?

  1. The identity column used with the DEFAULT column attribute is beneficial for system-generated primary keys.
  2. System-generated primary keys always ensure row uniqueness.
  3. System-generated primary keys are useful when no quality natural key is available.
  4. You can add an identity column with the ALTER TABLE command.

Answer : C

 
 
9) When is NUSI Bitmapping done?

  1. When 2 or more weakly selective NUSIs are ANDed together
  2. When 2 or more weakly selective NUSIs are ORed together
  3. When 2 or more weakly selective NUSIs are NORed together
  4. all of the above

Answer : A

 
 
10) Which of the below are USI Considerations

  1. You want to ensure that a column’s values are unique
  2. You have a column with a high value access frequency and you want users to get to the data quickly
  3. You have a SET table that has a NUPI and you want to eliminate Duplicate row checking
  4. all of the above

Answer : D




 
11) Duplicate row checking of an NUPI table can be eliminated by

  1. changing PI to UPI
  2. creating a NUSI
  3. Creating a USI
  4. all of the above

Answer : C

 
 
12) Which of the following about Hash Indexes is true?

  1. Hash indexes can have secondary indexes
  2. Statistics cannot be collected on a Hash index
  3. Hash index can be compressed
  4. The column list can contain aggregate functions

Answer : D

 
 
13) _________ is a technique used to effectively link several weakly selective indexes

  1. Hash Collisions
  2. Multi table Join index
  3. Bit Mapping
  4. Hash synonyms

Answer : C

 
 
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

  1. Single table Join index
  2. Secondary index
  3. Aggregate Join Index
  4. Primary Index

Answer : A

 
 
15) When rows are inserted into a table with an Identity Column, the Identity Column will always be consecutively numbered?

  1. TRUE
  2. False

Answer : B

 
 
16) Which of the following is true for a PPI –

  1. A PPI cannot be unique
  2. A PPI can contain an identity column
  3. A PPI cannot be unique unless all its partitioning columns are included in the index definition
  4. The result of the partitioning expression can be a character

Answer : C

 
 
17) The LOG/NO LOG option in the CREATE statement is applicable to –

  1. Permanent tables
  2. Secondary Indexes
  3. Join indexes
  4. Volatile tables

Answer : D

 
 
18) ________ returns the hexadecimal row hash value for an expression

  1. HASHAMP
  2. HASHBAKAMP
  3. HASHBUCKET
  4. HASHROW

Answer : D

 
 
19) _______ system view gives every explicit right for the priveleges on objects given to a user

  1. allrights
  2. allrolerights
  3. association
  4. authorizations

Answer : A

 
 
20) _______ system view gives CPU and I/O accounting information saved by the DBC

  1. dbcinfo
  2. collations
  3. databases
  4. ampusage

Answer : D




 
21) A user assigned to multiple roles can change his role using the ________ statement

  1. create role
  2. Set role
  3. Grant role
  4. Grant role with Admin option

Answer : B

 
 
22) Rows in an AMP are ordered by

  1. Table ID, Row hash values, uniqueness value,Partitions
  2. Row hash values, uniqueness value, table ID,partitions
  3. Table ID, partitions,Row hash values, uniqueness value
  4. None

Answer : C

 
 
23) Once the target AMP has been determined for a PI
search, the _______ ________ for that AMP must
be consulted

  1. Table ID
  2. data block
  3. cylinder index
  4. master Index

Answer : D

 
 
24) How does Teradata handle data distribution when performing system configuration changes

  1. Creates an output map to aid in redistributing data
  2. Data distribution is automatic
  3. Creates a reconfig map to help the DBA repartition data
  4. None of the above

Answer : B

 
 
25) Which of the following is not needed to attach Teradata to a mainframe?

  1. PE
  2. Escon Connection or BUS/TAG cables
  3. BYNET
  4. Host Channel Adapter PDE

Answer : C

 
 
26) At run time Macros are

  1. pre-optimized
  2. Re-optimized
  3. not-compiled
  4. none

Answer : B

 
 
27) Do privileges on Objects referenced in a Macro require permission to be granted to users that run them?

  1. yes
  2. no
  3. yes if objects on system tables
  4. yes if objects are on other user tables

Answer : B

 
 
28) What is not a characteristic of Aggregate Join Index?

  1. the result is stored as FLOAT
  2. only COUNT and SuM are permitted
  3. DISTINCT is not permitted
  4. The Join Index can be queried directly

Answer : D

 
 
29) Low confidence’ in EXPLAIN indicates

  1. Poor joining strategy
  2. statistics is not collected
  3. join access is bad
  4. value access is bad

Answer : B

 
 
30) TableA has a Secondary index defined on Col1. Which of the following SQLs results in a full table scan?

  1. sel * from tableA where substring(col1,1,1) = ‘2’;
  2. Sel distinct col2 from tableB where col1 in (‘1, ‘2’);
  3. Sel * from table a where col1 = ALL;
  4. Sel col1, col2 from tableA where col1 = ‘1’

Answer : A




 
31) Which of the following indexes can have aggregate functions in its DDL?

  1. Secondary index
  2. Primary Index
  3. Join Index
  4. Hash Index

Answer : C

 
 
32) What is the result of the following SQL :
select ROWID from tableB;

  1. Gives the rowid of each row in the table
  2. Gives the Rowid of the first row in the table
  3. Query returns an error since ROWID keyword cannot be used
  4. Query returns zero rows

Answer : C

 
 
33) The phrase “GENERATED DEFAULT AS IDENTITY” in the CREATE TABLE statement –

  1. always inserts unique system-generated values in the identity column
  2. inserts unique system-generated values for the identity column when no user value is provided
  3. always inserts user-provided values for the identity column
  4. inserts duplicate system-generated values in the identity column

Answer : B

 
 
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?

  1. If the row inserted is a Foreign Key row
  2. If NO CHECK OPTION has been defined on the referenced table
  3. If the row exists in the referenced table with the same values as that in the referencing columns
  4. If the Foreign Key in the referencing column is an Identity Column

Answer : C

 
 
35) Statistical sampling is –

  1. Collection of statistics on a small sample of rows on a single AMP
  2. Collection of statistics on all rows in a group of AMPs
  3. Collection of statistics on a small sample of rows from all AMPs
  4. Collection of statistics on the entire table

Answer : C

 
 
36) What does the following SQL do –
SELECT ADD_MONTHS (CURRENT_DATE, 60*6);

  1. Adds 360 days to the current date
  2. Adds 30 years to the current date
  3. Adds 60 months and 6 years to the current date
  4. Adds 60 years and 6 months to the current date

Answer : B

 
 
37) What does the following SQL do –
SELECT COUNT(Distinct DeptNo) FROM Employee;

  1. Gives a count of all Dept Numbers in the Employee table
  2. Distinct cannot be used with an aggregate function.
  3. Gives a count of unique Dept Numbers in the Employee table including nulls
  4. Gives a count of unique Dept Numbers in the Employee table excluding nulls

Answer : D

 
 
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;

  1. Gives the sales of each item averaged over the preceeding 3 months
  2. Gives the average sales of the top 3 items in each month
  3. Gives the total average sales in the preceeding 3 months
  4. Gives the sales of each item averaged over the current month and the preceding 3 months

Answer : D

 
 
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?

  1. No rows will be returned
  2. All rows from Employee including those having DeptNo as null will be returned
  3. Rows from Employee having DeptStatus as null will be returned
  4. All rows from Employee except those having DeptNo as null will be returned

Answer : B




 
40) What is the result of the following SQL –
SELECT HASHROW (C1,C2) FROM T;

  1. The rowhash value of column C1
  2. The rowhash value of column C2
  3. The rowhash value of columns C1 and C2 combined
  4. Multiple columns cannot be specified in the Hashrow function

Answer : C

 
 
41) The following SQL is equivalent to –
SELECT city, kind, sales,
SUM(sales) OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) FROM monthly

  1. Cumulative sum
  2. Group sum
  3. Moving sum
  4. Moving Difference

Answer : B

 
 
42) ______ is expressed as an approximate numeric ratio between 0.0 and 1.0

  1. RANK
  2. ROW_NUMBER
  3. QUANTILE
  4. PERCENT_RANK

Answer : D

 
 
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

  1. Table A is left outer joined with Table B and the result is right outer joined with table C
  2. Table B is right outer joined with Table C and the result is left outer joined with Table A
  3. Table A is right outer joined with Table C and the result is left outer joined with Table B
  4. The SQL returns an error

Answer : B

 
 
44) Another name for the EXCEPT operator is __________.

  1. INSTEAD OF
  2. NOT IN
  3. INTERSECT
  4. MINUS

Answer : D

 
 
45) SELECT CAST (‘bb12345bb’ AS Char(3)). (b = space). What is the answer set returned from the following query?

  1. 123
  2. 1
  3. bb1
  4. No answer set is returned because you can’t cast Integer data as Char.

Answer : C

 
 
46) Constant updating of rows in a table is achieved by

  1. Multiload
  2. Fastload
  3. Fastexport
  4. Tpump

Answer : D

 
 
47) Which utility loads to populated tables at block level?

  1. Multiload
  2. Fastload
  3. Fastexport
  4. Tpump

Answer : A

 
 
48) What is not a characteristic of TPump?

  1. can utilize stored procedures
  2. has no session limit
  3. supports conditional processing
  4. can change the rate at which statements are sent to be processed

Answer : A

 
 
49) Which utility should you use to populate an empty table with 100 million rows of data?

  1. Fastload
  2. Multiload
  3. TPUMP
  4. Fastexport

Answer : A




 
50) The _____ has a built in report formatting capability
 

  1. Multiload
  2. TPUMP
  3. Fastexport
  4. BTEQ

Answer : D

 
 
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?

  1. Multiload
  2. TPUMP
  3. Fastexport
  4. BTEQ

Answer : A

 
 
52) The following operation requires the table to exist when moving data from archived files to the Teradata database –

  1. Restore
  2. Archive
  3. Copy
  4. Build

Answer : A

 
 
53) _______ uses performance and allocation groups to define different priorities for different categories of work

  1. TDQM
  2. Priority Scheduler
  3. Teradata Warehouse Builder
  4. Locking logger

Answer : B

 
 
54) Which of the following BTEQ commands submits an operating system command to the network-attached system

  1. TSO
  2. OS
  3. RUN
  4. COMPILE

Answer : B

 
 
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

  1. TENACITY
  2. SLEEP
  3. LOGON
  4. SESSIONS

Answer : A

 
 
56) Which of the following can be used to enhance input records to Fastload before sending them to the Database

  1. OUTMOD routine
  2. Notify exit routine
  3. INMOD routine
  4. DEFINE command

Answer : C

 
 
57) A Multiload job can be paused intentionally using the PAUSE command in the script

  1. True
  2. FALSE

Answer : A

 
 
58) Which of the following is NOT a valid command in a multiload job script?

  1. insert into t1 (col1, col2) values (:c1 (format ’hhmmss’),:c2);
  2. .Accept age, name from file info;
  3. Delete From Employee Where YrsExp < 5;
  4. .DML label COMPLEX Ignore duplicate insert rows Do insert for missing update rows;

Answer : A

 
 
59) The AMPCHECK APPLY option in multiload –

  1. Allows the multiload job to continue as long as no more than one AMP is down in a cluster
  2. Inhibits the Mulitload job from entering or exiting the Application phase when an AMP is down
  3. Pauses the multiload job when an AMP is down
  4. All of the above

Answer : B




 
60) What Does Session Control Component of PE Performs?

  1. Controlling number of sessions executed
  2. SQL Interpretation for session
  3. logon and logoff
  4. returns the result to the user

Answer : C

 
 
61) What are the actions performed by Parser Component of PE?

  1. Checks SQL Syntax
  2. ensure that all the objects and columns exist
  3. developing least expensive plan
  4. A&B

Answer : D

 
 
62) Which PE Component that enables teradata to handle multiple complex , ad hoc queries efficiently?

  1. Session Control
  2. Parser
  3. Dispatcher
  4. Optimizer

Answer : D

 
 
63) How Does Fault Tolerance feature in BYNET work?

  1. Automatically reconfigure that network so all messages avoid the unusable path.
  2. Automatically and dynamically distributes traffic between both BYNETs.
  3. Increases system size without loss of performance
  4. Message is routed to one specific node in the system

Answer : A

 
 
64) AMP is responsible for which duties listed below?

  1. Retrieve information from detentions and tables
  2. Insert, delete, or modify rows with in tables
  3. Lock databases and tables
  4. A,B & C

Answer : D

 
 
65) A clique is a set of Teradata nodes that share a common set of ?

  1. virtual processors
  2. BYNET
  3. Disk Arrays
  4. A&C

Answer : C

 
 
66) Create, modify, or delete definitions of  tables are performed by?
 

  1. PE
  2. AMP
  3. Parser
  4. Optimizer

Answer : B

 
 
67) Which one is a software interface layer on top of the operating system that enables the RDBMS to operate in a parallel environment?

  1. AMP
  2. BYNET
  3. PDE
  4. PE

Answer : C

 
 
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 ?

  1. 128
  2. 32
  3. 64
  4. 120

Answer : A

 
 
69) NUPI access always invovles how many AMP(s)?

  1. One or More
  2. More than One
  3. One
  4. Two or More

Answer : C

 
 
70) What is the Size of Row Hash?

  1. 24-bit
  2. 32-bit
  3. 16-bit
  4. 64-bit

Answer : B

 
 
71) Maximum Combination of Columns allowed for PI are?

  1. 32
  2. 16
  3. 64
  4. 128

Answer : C

 
 
72) Each Row can be uniquely identified by?

  1. Row Hash
  2. Uniqueness Value
  3. AMP ID
  4. A&B

Answer : D

 
 
73) Which Key word from explain plan confirms the Full Table scan

  1. Full Table Scan
  2. Product Join
  3. All Row scan
  4. Low Confidence

Answer : C

 
 
74) Which statement is TRUE about PERM Sapce for Database Objects

  1. Tables and Views Require PERM Space
  2. Stored Procedures and Macros Doesn’t Require PERM space
  3. Views and Macros Doesn’t require PERM Sapce
  4. UDF’s Doesn’t require PERM Sapce

Answer : C

 
 
75) A database or user with no PERM Space may not contain?

  1. Tables and Views
  2. Tables and Stored procedures
  3. views and Macros
  4. UDF’s

Answer : B

 
 
76) Which Space defines the upper limit for a user Query?

  1. Spool Space
  2. Perm Space
  3. Temp Space
  4. DB Space

Answer : A

 
 
77) PERM space utilized can be calculated using which DBC view/table?

  1. DiskSpace
  2. SpaceUtilization
  3. Space
  4. Disk

Answer : A

 
 
78) After Compression Where does the actual PI value will be stored?

  1. Header Block
  2. Logical Block
  3. PI Cannot be Compressed
  4. Table level Block

Answer : C

 
 
79) Choose the Correct DDL for creating Employee table with compression

  1. 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’),
    )
  2. 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’),
    )
  3. 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’),
    )
  4. 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’),
    )

Answer : B




 
80) Between VARCHAR vs Compression VARCHAR is best when?

  1. when the difference of maximum and average field length is low and compressibility is high
  2. when the difference of maximum and average field length is average and compressibility is low
  3. when the difference of maximum and average field length is high and compressibility is high
  4. when the difference of maximum and average field length is high and compressibility is low

Answer : D

 
 
81) Between VARCHAR vs Compression Compression is best when?

  1. when the difference of maximum and average field length is low and compressibility is high
  2. when the difference of maximum and average field length is average and compressibility is low
  3. when the difference of maximum and average field length is high and compressibility is high
  4. when the difference of maximum and average field length is high and compressibility is low

Answer : A

 
 
82) Which Locking mechanism is never applied to rows?

  1. Access lock
  2. Write Lock
  3. Exclusive lock
  4. Read Lock

Answer : C

 
 
83) Which Locking mechanism is also referred as “stale read” lock

  1. Access lock
  2. Write Lock
  3. Exclusive lock
  4. Read Lock

Answer : A

 
 
84) Which Locking mechanism are used for DML statements that access by primary index or unique secondary index?

  1. Table level locks
  2. Database level locks
  3. Read locks
  4. Row Hash locks

Answer : D

 
 
85) Which locking modes are Placed in response to an INSERT, UPDATE, or DELETE request?

  1. Read and Write Locks
  2. Exclusive Lock
  3. Write Lock
  4. Access Lock

Answer : C

 
 
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?

  1. Lock Granted
  2. Request Queued
  3. Request Abort
  4. Error message

Answer : B

 
 
87) On Customer table Locks are triggered in following order

  1. User1 ACCESS
  2. User2 WRITE
  3. User3 EXCLUSIVE
  4. User4 ACCESS

which users get immediate access to the “Customer Table”  and which users need to wait for the which other user.
 

  1. User1 READS the Object Customer Table IMMEDIATELY
  2. User2 is compatible and WRITES on the Object Customer Table
  3. User3 must wait on both User1 and User2
  4. User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock

 

  1. User1 READS the Object Customer Table IMMEDIATELY
  2. User2 is not compatible and does not WRITES on the Object Customer Table
  3. User3 must wait on both User1 and User2
  4. User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock

 

  1. User1 READS the Object Customer Table IMMEDIATELY
  2. User2 is compatible and WRITES on the Object Customer Table
  3. User3 must wait on both User1 and User2
  4. User4 can access parallel with user1

 

  1. User1 READS the Object Customer Table IMMEDIATELY
  2. User2 is compatible and WRITES on the Object Customer Table
  3. User3 takes priority over both User1 and User2
  4. User4 must wait until User3 is done. It is not compatible with EXCLUSIVE lock

Answer : A

 
 
88) when FALLBACK specifies that the system builds a duplicate copy of each row of the table and stores it on?
 

  1. Different AMP in the adjacent cluster.
  2. Different AMP within the cluster.
  3. Same AMP within the cluster.
  4. None

Answer : B

 
 
89) Which Statements are TRUE with respect to PPI

  1. UPI – partitioning column need not be part of PI
  2. NUPI – can be partitioned on any column, need not be part of PI

 

  1. UPI – partitioning column need not be part of PI
  2. NUPI – partioning column need to be part of PI

 

  1. UPI – partitioning column need to be part of PI
  2. NUPI – can be partitioned on any column in PI

 

  1. UPI – partitioning column need to be part of PI
  2. NUPI – can be partitioned on any column – need not be part of PI

Answer : D

 
 
90) Which is the correct DDL for PPI with RANGE_N partition?

  1. 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
    );
  2. 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
    );
  3. 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
    );
  4. A&C

Answer : D




 
91) Which statements are true about (NO CASE or NO RANGE),  UNKNOWN partitions in PPI

  1. NO CASE, UNKNOWN – two different partitions
  2. NO CASE OR UNKNOWN – only one partition for rows not meeting the criteria and also for rows with NULL values
  3. NO CASE or NO RANGE -partition to hold rows not part of CASE_ or RANGE_N , UNKNOWN – partition to hold rows with NULL values
  4. All the Above

Answer : D

 
 
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’);

  1. 0
  2. 2
  3. 4

 

  1. 0
  2. 4
  3. 2

 

  1. 0
  2. 4
  3. 4

 

  1. 0
  2. 2
  3. 2

Answer : B

 
 
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);
 

  1. The outer query will get executed first and for every row of outer query, inner query will get executed.
  2. The inner query will get executed as many times as no. of rows in result of the outer query.
  3. The outer query output can use the inner query o
  4. The outer query will get executed first and for every row of outer query, inner query will get executed.
  5. The inner query is not dependent on outer query.
  6. The outer query output can use the inner query output for comparison. This means inner quer
  7. The inner query is executed only once
  8. The inner query will get executed first and the output of the inner query used by the outer query.
  9. The inner query is not dependent on outer query.
  10. The inner query is executed only once
  11. The inner query will get executed as many times as no. of rows in result of the outer query.
  12. The inner query is not dependent on outer query.

Answer : C

 
 
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);
 

  1. 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
  2. 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
  3. 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
  4. 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.

Answer : A

 
 
95) Which Statement(s) are true about use of Correlated Subquery?

  1. Faster than subquery when the column used for comparision between inner & outer query is a PI or SI
  2. Excellent technique to use when there is a need to determine which rows to SELECT based on an aggregate of another table.
  3. It only fetches aggregate when a particular account is read for processing, not for all accounts like a normal subquery.
  4. A,B & C

Answer : D

 
 
96) Which Statements are true about Global Temporary Table

  1. Data is active till the session ends, and definition will remain till it is dropped using Drop table statement
  2. When materialized data goes in temp space Multiple concurrent users can reference the same GTT, but the contents are not shareable between users
  3. 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)
  4. A&B

Answer : D

 
 
97) Which Statements are true about Volatile Table

  1. Data and table definition both are active only till the session ends (Dropped automatically when the session terminates)
  2. Table Definition is stored in System cache (Not included in the DBC data dictionary database )When materialized data goes in temp space
  3. Table & its Data is not sharable across sessions. Data is stored in spool space. Table & its Data is not sharable across sessions.
  4. 1&3

Answer : D

 
 
98) Which statements are true about work tables for TPump versus MultiLoad
 

  1. Multiload- Optional, 1per target table. Tpump- No work tables needed
  2. Multiload- Optional, 2 per target table. Tpump- Optional, 1 per target table
  3. Multiload- Optional, 1per target table. Tpump- Optional, 1 per target table
  4. None of the above

Answer : A

 
 
99) Which statements are true about “Allows RI, USI, Triggers”
for TPump versus MultiLoad

  1. MultiLoad- Yes, Tpump- No
  2. MultiLoad- Yes, Tpump- Yes
  3. MultiLoad- No, Tpump- Yes
  4. None of the above

Answer : C

 
 
100) Which statements are true about “Locks” for TPump versus MultiLoad

  1. Multiload- Row, Tpump – Table
  2. Multiload- Row & Table, Tpump – Table
  3. Multiload- Table, Tpump – Table
  4. Multiload- Table, Tpump – Row

Answer : D