Teradata Quiz

Teradata Beginner Quiz




1) Which component is responsible for input conversion?

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

Answer : B

 
 
2) Explain command provides  ______________

  1. an english translation of the AMP steps
  2. which indexes, if any will be used
  3. row count and time estimates for comparison only
  4. All options A,B &C

Answer : D

 
 
3) Which Operating System can run the teradata?

  1. Windows
  2. Linux
  3. Z
  4. ALL A,B&C

Answer : D

 
 
4) Which will return at the most one 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. duplicates values in NUPI
  2. Hash synonym
  3. both A and B
  4. None of the above

Answer : C

 
 
6) Non unique secondary index operation is always a ____________

  1. one AMP operation
  2. ALL AMPs operation
  3. five AMPs operation
  4. three AMPs Operation

Answer : B

 
 
7) Which of the following are advantages to using a partitioned table as compared to a nonpartitioned table
I) There are fast deletes of all the rows in a partition
II)Fewer AMPs are involved when accessing data
III)Range queries can be executed without a secondary index
IV) There is faster access of a single row via a primary index access.

  1. I & III
  2. I & II
  3. I,II &III
  4. II & IV

Answer : A

 
 
8) Which statement is correct about Database?

  1. Require password
  2. Is an Active repository
  3. Not empty even though objects are not created
  4. None

Answer : D

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

 
 
10) Which application interface is used by BTEQ?

  1. ODBC
  2. JDBC
  3. CLI
  4. NONE

Answer : C




 
11) Which command we can use to run BTEQ script?

  1. .run file = BTEQScript.btq
  2. Bteq < BTEQScript.btq
  3. Both A and B
  4. None

Answer : C

 
 
12) Which of the following option (s) is/are true?

  1. TPUMP can load up to 60 tables
  2. Mload can load up to 60 tables
  3. Fload can load up to 60 tables
  4. None

Answer : A

 
 
13) Which of the following option is true?

  1. Tpump transfers data in 64K blocks
  2. Mload transfers data in 64K blocks
  3. BTEQ transfers data in 64K blocks
  4. None

Answer : B

 
 
14) Which of the following columns are eligible for compression?

  1. Varchar
  2. Identity columns
  3. Date
  4. A and C

Answer : D

 
 
15) Choose the BTEQ command to change from BTET to ANSI transaction mode.

  1. .SET SESSION MODE ANSI;
  2. .SET SESSION TRANSACTION ANSI;
  3. .SET SESSION TXN MODE TO ANSI;
  4. None of the above

Answer : B

 
 
16) Which of the following utilities use Work table?

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

Answer : D

 
 
17) Which of the following jounal is deleted automatically once the transction is commited or rollbacked?

  1. Before Journal
  2. After journal
  3. Transient journal
  4. DARJ

Answer : C

 
 
18) Which of the following is/are called Virtual Processor?

  1. AMP
  2. PE
  3. Both AMP and PE
  4. BYNET

Answer : C

 
 
19) One node can handle  maximum_______ many Virtual  processors.

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

Answer : A

 
 
20) Which of the following is/are performed  by bynet?

  1. communication between multiple SMP nodes
  2. checking syntax errors in sql queries
  3. session management
  4. sending results to client

Answer : A




 
21) Create table t1(  col1 int, col2 int) primary index(col2);                         Above statement will create?

  1. UPI on col1
  2. NUPI on col2
  3. NUSI on col2
  4. None of the above

Answer : B

 
 
22) _______ function  can change a column of TIMESTAMP data type to a DATE data type.

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

Answer : B

 
 
23) Which function returns first not null argument’s value?

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

Answer : A

 
 
24) Which command displays create statement of an object?

  1. show
  2. help
  3. Both A & B
  4. None of the above

Answer : A

 
 
25) Date Datatype is stored as _________

  1. Date format
  2. Integer format
  3. varchar(4)
  4. None

Answer : B

 
 
26) __________tables have DDL stored in data dictionary, but does not share the data?

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

Answer : A

 
 
27) Select the correct order of execution of clauses.

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

Answer : D

 
 
28) Which of the following is not a task of the PE ?

  1. perform Input/Output and DB functions
  2. Checks SQL syntax for errors.
  3. Breaks SQL into steps for the AMPs.
  4. handles up to 120 user sessions.

Answer : A

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

 
 
30) Which of the following is NOT materialized in SPOOL

  1. Derived tables
  2. Volatile tables
  3. Both A and B
  4. Global temporary tables

Answer : D




 
31) Macro is _________

  1. ANSI SQL
  2. DDL
  3. Teradata extension to SQL
  4. Special type of trigger

Answer : C

 
 
32) What is used by AMPs and PEs for internal communication

  1. The Disk
  2. BYTNET
  3. A proprietary LAN
  4. An internode VAN

Answer : B

 
 
33) Which of the following can be created and dropped dynamically?

  1. UPI
  2. NUPI
  3. NUSI
  4. PI

Answer : C

 
 
34) Which of the following is not a DDL statement

  1. CREATE
  2. ALTER
  3. UPDATE
  4. DROP

Answer : C

 
 
35) Given the query, SELECT AVG(Column1) FROM t1; where t1 is an empty table, what is the result?

  1. 0
  2. Null
  3. Error
  4. No Record found

Answer : B

 
 
36) Which SQL clauses always perform the same type of operations?

  1. QUALIFY and HAVING
  2. DISTINCT and QUALIFY
  3. HAVING and GROUP BY
  4. HAVING and DISTINCT

Answer : A

 
 
37) If CHECKPOINT is not specified in Fast Load script than by default checkpoints will be taken at  _________

  1. Beginning of Phase 1
  2. Every 1,00,000 input records
  3. End of Phase 1
  4. All of the above

Answer : D

 
 
38) Which index on the table to be loaded is supported by Multiload?

  1. USI
  2. JI
  3. HI
  4. NUSI

Answer : D

 
 
39) SQL protocols are used by

  1. BTEQ
  2. MULTILOAD
  3. FASTLOAD
  4. TPUMP

Answer : A

 
 
40) MULTISET TABLE is created by default in _________ transaction mode

  1. ANSI
  2. Teradata
  3. Both a and b
  4. None of the above

Answer : A




 
41) Predefined stored set of one or more SQL commands is a

  1. table
  2. macro
  3. view
  4. all of the above

Answer : B

 
 
42) If PERM space is zero

  1. we can create tables
  2. we cannot run queries
  3. we cannot create tables
  4. None of the above

Answer : C

 
 
43) If SPOOL space is zero

  1. we cannot create tables
  2. we cannot run queries
  3. we can run queries
  4. None of the above

Answer : B

 
 
44) To display null values as NULL in the report which BTEQ command is correct

  1. .set null NULL
  2. .set null ‘NULL’
  3. .set null “NULL”
  4. None of the above

Answer : B

 
 
45) Which commnad will display all the indexes on a table t1.

  1. sel * from t1;
  2. show index t1;
  3. help index t1;
  4. help table t1;

Answer : C

 
 
46) Which command we can use to run BTEQ script?

  1. .run file = BTEQScript.btq
  2. Bteq < BTEQScript.btq
  3. Both A and B
  4. None

Answer : C

 
 
47) Which of the following option(s) is/are true?

  1. Tpump transfers data in 64K blocks
  2. Mload transfers data in 64K blocks
  3. BTEQ transfers data in 64K blocks
  4. None

Answer : B

 
 
48) Which of the following journal is deleted automatically once the transaction is committed or rollbacked?

  1. Before Journal
  2. After journal
  3. Transient journal
  4. DARJ

Answer : C

 
 
49) Which of the following utilities use Work table

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

Answer : D

 
 
50) Which of the following jounal is deleted automatically once the transaction is committed or rollbacked?

  1. Before Journal
  2. After journal
  3. Transient journal
  4. DARJ

Answer : C

 
 
51) Create table t1(  col1 int, col2 int) primary index(col2);
Above statement will create?

  1. UPI on col1
  2. NUPI on col2
  3. NUSI on col2
  4. None of the above

Answer : B

 
 
52) Create table t1(  col1 int, col2 int, col3 int);
Above statement will create?

  1. UPI on col1
  2. NUPI on col1
  3. create table without PI
  4. None of the above

Answer : B

 
 
53) which command will display all the indexes on a table t1.

  1. sel * from t1;
  2. show index t1;
  3. help index t1;
  4. help table t1;

Answer : C

 
 
54) What is the main difference between a subquery and join?

  1. Subquery returns rows from both tables whereas Join returns from first table only
  2. Subquery returns rows from both tables whereas Join returns from second table only
  3. Subquery returns rows from first table only whereas Join returns from both tables
  4. Subquery returns rows from second table only whereas Join returns from both tables

Answer : C

 
 
55) When a session is processing in Teradata mode, which statements are used to close a transaction?

  1. END TRANSACTION statement
  2. BEGIN WORK or END WORK statements
  3. COMMIT TRANSACTION or ROLLBACK TRANSACTION statements
  4. COMMIT or ROLLBACK statements

Answer : A

 
 
56) The process of changing the Number of AMP in a system is called as ___

  1. Redistribution
  2. Reconfiguration
  3. Hash Mapping
  4. Partitioning

Answer : B

 
 
57) Which task do Teradata DBAs never have to do?

  1. Reorganize data
  2. Select Primary Index
  3. Restart the system
  4. Pre-Prepare data for loading.

Answer : A

 
 
58) Which of the following statements is false?

  1. Large number of non-unique values provides better distribution.
  2. The low-order 16 bits of the row hash are input to the hash map.
  3. Duplicate NUPI values will produce the same row hash.
  4. Uniform row distribution is accomplished using UPIs.

Answer : A

 
 
59) Which of the following statements is false about Primary Key and Primary Index?

  1. They can be the same column.
  2. They can contain unique data values.
  3. They can be defined in a “Create Table” statement.
  4. They are both optional in a Teradata table.

Answer : D

 
 
60) Which statement about the Transient Journal is true?

  1. Transient Journals are stored in a user’s spool space.
  2. Before Images are kept until manually purged.
  3. Transient Journal operation is automatic.
  4. It permits successful rollback to the last archive.

Answer : C




 
61) Which statement about Teradata Database is true?

  1. A database will always have a password.
  2. When a new database is created ,it gets perm space from its immediate parent.
  3. A database will always have table(s).
  4. Deleting a macro from a database reclaims Perm space for the database.

Answer : B

 
 
62) Cliques are physically composed of _____.

  1. gateways
  2. nodes
  3. PEs
  4. AMPs

Answer : B

 
 
63) In which phase of the Active Data Warehouse evolution do you use data to determine what will happen?

  1. pre-defined reports
  2. event based triggering
  3. continuous update
  4. analytical modeling

Answer : D

 
 
64) Which statement is true for Teradata data distribution?

  1. manual distribution of the data
  2. automatic distribution of the data
  3. based on hashing of the USI
  4. based on hashing of the Primary Key

Answer : B

 
 
65) Which feature allows Teradata data to process large amount of data quickly?

  1. High availability software and hardware components
  2. Parallelism
  3. Proven scalability
  4. High performance server from Intel

Answer : B

 
 
66) Which type of failure does a clique protect the system from?

  1. Loss of a disk drive
  2. Power loss
  3. Loss of a node
  4. Loss of a Vproc

Answer : C

 
 
67) Which statement is true about a foreign key?

  1. Foreign Keys provide a second level uniqueness.
  2. Foreign Keys cannot contain NULL or missing values.
  3. Foreign Keys can’t change values over time.
  4. Each Foreign Key must exist as a Primary Key.

Answer : D

 
 
68) What information a SHOW TABLE statement gives?

  1. The current DDL, creation, and last modification dates
  2. The current DDL based on the last modification of the table
  3. The current DDL for the table with any modifications noted as comments
  4. The current DDL with CREATE changed to REPLACE

Answer : B

 
 
69) What information a HELP TABLE statement gives?

  1. The current DDL based on the last modification of the table
  2. The current DDL for the table with any modifications noted as comments
  3. Displays the column information defined in that table.
  4. The current DDL with CREATE changed to REPLACE

Answer : C

 
 
70) CREATE MULTISET TABLE t1 (a INTEGER, b INTEGER)
UNIQUE PRIMARY INDEX (a);
INSERT INTO t1 VALUES (1,1);
INSERT INTO t1 VALUES (1,2);
UPDATE t1 SET b = b + 1 WHERE b = 1;
Which statement is true?

  1. The INSERTs succeed but the UPDATE fails.
  2. The CREATE TABLE fails.
  3. The INSERTs and the UPDATE succeed.
  4. The second INSERT fails.

Answer : D




 
71) Dept_No    Dept_Name     Internal_Cost
1                      Research             50
2                      Development    40
3                      Marketing           NULL
SELECT AVG(Internal_Cost) FROM department;
Given the table and the query above, what is the result set?
 

  1. 30
  2. NULL
  3. 45
  4. 50

Answer : C

 
 
72) CREATE TABLE Employee (
Emp_Number Integer
,Emp_Name Varchar(30)
,Emp_Salary Decimal(18,2)
)
Unique Primary Index (Emp_Number)
Index (Emp_Name );
SELECT * FROM Employee
WHERE Emp_Number = 1234 OR Emp_Name =’James’
Which of the following statement is true ?
 

  1. It uses both PI and NUSI to retrieve the rows
  2. It uses PI only, to retrieve the rows
  3. It uses NUSI only, to retrieve the rows
  4. It uses neither of the indexes to retrieve the rows

Answer : B

 
 
73) Consider the following SQL :
CREATE TABLE table_1 (
c1 char(5)
,c2 int
);
INSERT INTO table_1 ( ‘3’ , 2);
SELECT * FROM table_1 WHERE c1 = 3;
 

  1. Uses c1 as the unique primary index to retrieve the row
  2. Uses c2 as the unique index to retrieve the row
  3. Parser will give an error
  4. Uses FTS to retrieve the row

Answer : D

 
 
74) A Teradata database is created with no perm space. Which of the following objects can’t be created in it?

  1. Views
  2. Tables
  3. Macros
  4. Stored Procedures

Answer : B

 
 
75) A database is created with 100 GB Perm space. 50 GB is allotted as Spool space. A user uses 30 GB to store objects. Space available for spool is –

  1. 70 GB
  2. 50 GB
  3. 30 GB
  4. 100 GB

Answer : A

 
 
76) Consider the following SQL:
SELECT dept_no, dept_name,SUM (grant_amount) FROM department GROUP BY dept_no;
What it should return when executed:

  1. Shows the total grant_amount for each department
  2. Shows the total grant_amount for each department and department name
  3. Gives a syntax error as GROUP BY should come before FROM clause
  4. Give as error as non-aggregates in the SELECT list must also appear in the GROUP BY clause.

Answer : D

 
 
77) Consider the following SQL:
SELECT * FROM Employee SAMPLE 0.1;
What it should return when executed:

  1. It gives an error because SAMPLE can’t be less than 1
  2. It outputs 10% of Employee table rows randomly
  3. It outputs 10 rows of Employee table randomly
  4. It outputs first 10% of Employee table rows

Answer : B

 
 
78) What the following SQL will output?
SELECT customer_number, zip_code FROM location WHERE zip_code MOD 10000 = 0;

  1. All rows from location table where zip codes end with only one zero.
  2. Throws an error because zip_code can’t be zero
  3. All rows from location table where zip codes end with four zeroes.
  4. Gives “Division By Zero” error

Answer : C

 
 
79) The following SQL –
SELECT employee_no, employee_name, hire_date as DOJ
FROM employee
WHERE (DATE – hire_date) / 365.25 > = 10;
will return –

  1. all employee data with 10 or more years of service
  2. 0 rows as DATE is not defined
  3. an error as arithmetic fuctions are not allowed for DATE datatypes
  4. all employee data who joined 10 or more days back

Answer : A

 
 
80) Global Temporary Tables get created in –

  1. Perm Space
  2. Spool Space
  3. Temp Space
  4. None of the above

Answer : C




 
81) Which statement is false on secondary index in Teradata?

  1. They can be created during table creation time
  2. They can be created after table creation
  3. They can be dropped after table creation
  4. Once created they can’t be changed unless table is dropped

Answer : D

 
 
82) Which one is not a valid type of Primary Index in Teradata?

  1. Unique Primary Index or UPI
  2. Partitioned Primary Index or PPI
  3. Full Text Primary Index or FTPI
  4. Non Unique Primary Index or NUPI

Answer : C

 
 
83) When two different primary index values hash identically, it is called –

  1. Hash Map
  2. Hash Synonym
  3. Destination Selection Word
  4. Row ID

Answer : B

 
 
84) Which is not an example of Teradata database object?

  1. Exclusive Lock
  2. Triggers
  3. Macros
  4. Stored Procedures

Answer : A

 
 
85) Which one is not a valid type of SQL subset?

  1. Data Manipulation Language or DML
  2. Stored Procedural Language or SPL
  3. Table Creation Language or TCL
  4. Data Control Language or DCL

Answer : A

 
 
86) GRANT and REVOKE are examples of –

  1. Teradata extensions to SQL
  2. Data Control Language or DCL
  3. Data Manipulation Language or DML
  4. Stored Procedural Language or SPL

Answer : B

 
 
87) The main difference between Macro and Stored Procedure(SP) is –

  1. Macro can’t contain SQL where as SP can have SQL
  2. Macro doesn’t allow DDLs where as SP allows DDL operations
  3. Macro doesn’t allow DML operations where as SP allows DML operations
  4. Macro contains set of SQLs where as SP contains both SQL and procedural statements

Answer : D

 
 
88) EXPLAIN plan is not useful for –

  1. Index Selection
  2. Performance Tuning
  3. Debugging
  4. Predicting row counts

Answer : A

 
 
89) BTEQ stands for –

  1. Basic Teradata Execution Query
  2. Basic Teradata Query
  3. Basic Teradata Execution Quality
  4. Basic Teradata Execution Quantity

Answer : B