Teradata Beginner Quiz
1) Which component is responsible for input conversion?
- AMP
- PE
- BYNET
- CLI
2) Explain command provides ______________
- an english translation of the AMP steps
- which indexes, if any will be used
- row count and time estimates for comparison only
- All options A,B &C
3) Which Operating System can run the teradata?
- Windows
- Linux
- Z
- ALL A,B&C
4) Which will return at the most one Row?
- UPI
- USI
- Both
- None
5) A uniqueness value is required to produce a
unique Row ID because of ___________
- duplicates values in NUPI
- Hash synonym
- both A and B
- None of the above
6) Non unique secondary index operation is always a ____________
- one AMP operation
- ALL AMPs operation
- five AMPs operation
- three AMPs Operation
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.
- I & III
- I & II
- I,II &III
- II & IV
8) Which statement is correct about Database?
- Require password
- Is an Active repository
- Not empty even though objects are not created
- None
9) Which table will have UNIQUE records
- MULTISET table with NUPI
- MULTISET with UPI
- MULTISET with NUSI
- all of the above
10) Which application interface is used by BTEQ?
- ODBC
- JDBC
- CLI
- NONE
11) Which command we can use to run BTEQ script?
- .run file = BTEQScript.btq
- Bteq < BTEQScript.btq
- Both A and B
- None
12) Which of the following option (s) is/are true?
- TPUMP can load up to 60 tables
- Mload can load up to 60 tables
- Fload can load up to 60 tables
- None
13) Which of the following option is true?
- Tpump transfers data in 64K blocks
- Mload transfers data in 64K blocks
- BTEQ transfers data in 64K blocks
- None
14) Which of the following columns are eligible for compression?
- Varchar
- Identity columns
- Date
- A and C
15) Choose the BTEQ command to change from BTET to ANSI transaction mode.
- .SET SESSION MODE ANSI;
- .SET SESSION TRANSACTION ANSI;
- .SET SESSION TXN MODE TO ANSI;
- None of the above
16) Which of the following utilities use Work table?
- FastLoad
- FastExport
- TPUMP
- Multiload
17) Which of the following jounal is deleted automatically once the transction is commited or rollbacked?
- Before Journal
- After journal
- Transient journal
- DARJ
18) Which of the following is/are called Virtual Processor?
- AMP
- PE
- Both AMP and PE
- BYNET
19) One node can handle maximum_______ many Virtual processors.
- 128
- 64
- 32
- 16
20) Which of the following is/are performed by bynet?
- communication between multiple SMP nodes
- checking syntax errors in sql queries
- session management
- sending results to client
21) Create table t1( col1 int, col2 int) primary index(col2); Above statement will create?
- UPI on col1
- NUPI on col2
- NUSI on col2
- None of the above
22) _______ function can change a column of TIMESTAMP data type to a DATE data type.
- EXTRACT
- CAST
- INTERVAL
- Add_month
23) Which function returns first not null argument’s value?
- COALESCE(X,Y)
- NULLIF(X,Y)
- ZEROIFNULL(X,Y)
- NULLIFZERO(X,Y)
24) Which command displays create statement of an object?
- show
- help
- Both A & B
- None of the above
25) Date Datatype is stored as _________
- Date format
- Integer format
- varchar(4)
- None
26) __________tables have DDL stored in data dictionary, but does not share the data?
- Global Temporary
- Permanent
- Derived
- Volatile
27) Select the correct order of execution of clauses.
- GROUP BY, HAVING, WHERE
- HAVING, WHERE, GROUP BY
- GROUP BY, WHERE, HAVING
- WHERE, GROUP BY, HAVING
28) Which of the following is not a task of the PE ?
- perform Input/Output and DB functions
- Checks SQL syntax for errors.
- Breaks SQL into steps for the AMPs.
- handles up to 120 user sessions.
29) Which of the following BTEQ commands submits an operating system command to the network-attached system
- TSO
- OS
- RUN
- COMPILE
30) Which of the following is NOT materialized in SPOOL
- Derived tables
- Volatile tables
- Both A and B
- Global temporary tables
31) Macro is _________
- ANSI SQL
- DDL
- Teradata extension to SQL
- Special type of trigger
32) What is used by AMPs and PEs for internal communication
- The Disk
- BYTNET
- A proprietary LAN
- An internode VAN
33) Which of the following can be created and dropped dynamically?
- UPI
- NUPI
- NUSI
- PI
34) Which of the following is not a DDL statement
- CREATE
- ALTER
- UPDATE
- DROP
35) Given the query, SELECT AVG(Column1) FROM t1; where t1 is an empty table, what is the result?
- 0
- Null
- Error
- No Record found
36) Which SQL clauses always perform the same type of operations?
- QUALIFY and HAVING
- DISTINCT and QUALIFY
- HAVING and GROUP BY
- HAVING and DISTINCT
37) If CHECKPOINT is not specified in Fast Load script than by default checkpoints will be taken at _________
- Beginning of Phase 1
- Every 1,00,000 input records
- End of Phase 1
- All of the above
38) Which index on the table to be loaded is supported by Multiload?
- USI
- JI
- HI
- NUSI
39) SQL protocols are used by
- BTEQ
- MULTILOAD
- FASTLOAD
- TPUMP
40) MULTISET TABLE is created by default in _________ transaction mode
- ANSI
- Teradata
- Both a and b
- None of the above
41) Predefined stored set of one or more SQL commands is a
- table
- macro
- view
- all of the above
42) If PERM space is zero
- we can create tables
- we cannot run queries
- we cannot create tables
- None of the above
43) If SPOOL space is zero
- we cannot create tables
- we cannot run queries
- we can run queries
- None of the above
44) To display null values as NULL in the report which BTEQ command is correct
- .set null NULL
- .set null ‘NULL’
- .set null “NULL”
- None of the above
45) Which commnad will display all the indexes on a table t1.
- sel * from t1;
- show index t1;
- help index t1;
- help table t1;
46) Which command we can use to run BTEQ script?
- .run file = BTEQScript.btq
- Bteq < BTEQScript.btq
- Both A and B
- None
47) Which of the following option(s) is/are true?
- Tpump transfers data in 64K blocks
- Mload transfers data in 64K blocks
- BTEQ transfers data in 64K blocks
- None
48) Which of the following journal is deleted automatically once the transaction is committed or rollbacked?
- Before Journal
- After journal
- Transient journal
- DARJ
49) Which of the following utilities use Work table
- FastLoad
- FastExport
- TPUMP
- Multiload
50) Which of the following jounal is deleted automatically once the transaction is committed or rollbacked?
- Before Journal
- After journal
- Transient journal
- DARJ
51) Create table t1( col1 int, col2 int) primary index(col2);
Above statement will create?
- UPI on col1
- NUPI on col2
- NUSI on col2
- None of the above
52) Create table t1( col1 int, col2 int, col3 int);
Above statement will create?
- UPI on col1
- NUPI on col1
- create table without PI
- None of the above
53) which command will display all the indexes on a table t1.
- sel * from t1;
- show index t1;
- help index t1;
- help table t1;
54) What is the main difference between a subquery and join?
- Subquery returns rows from both tables whereas Join returns from first table only
- Subquery returns rows from both tables whereas Join returns from second table only
- Subquery returns rows from first table only whereas Join returns from both tables
- Subquery returns rows from second table only whereas Join returns from both tables
55) When a session is processing in Teradata mode, which statements are used to close a transaction?
- END TRANSACTION statement
- BEGIN WORK or END WORK statements
- COMMIT TRANSACTION or ROLLBACK TRANSACTION statements
- COMMIT or ROLLBACK statements
56) The process of changing the Number of AMP in a system is called as ___
- Redistribution
- Reconfiguration
- Hash Mapping
- Partitioning
57) Which task do Teradata DBAs never have to do?
- Reorganize data
- Select Primary Index
- Restart the system
- Pre-Prepare data for loading.
58) Which of the following statements is false?
- Large number of non-unique values provides better distribution.
- The low-order 16 bits of the row hash are input to the hash map.
- Duplicate NUPI values will produce the same row hash.
- Uniform row distribution is accomplished using UPIs.
59) Which of the following statements is false about Primary Key and Primary Index?
- They can be the same column.
- They can contain unique data values.
- They can be defined in a “Create Table” statement.
- They are both optional in a Teradata table.
60) Which statement about the Transient Journal is true?
- Transient Journals are stored in a user’s spool space.
- Before Images are kept until manually purged.
- Transient Journal operation is automatic.
- It permits successful rollback to the last archive.
61) Which statement about Teradata Database is true?
- A database will always have a password.
- When a new database is created ,it gets perm space from its immediate parent.
- A database will always have table(s).
- Deleting a macro from a database reclaims Perm space for the database.
62) Cliques are physically composed of _____.
- gateways
- nodes
- PEs
- AMPs
63) In which phase of the Active Data Warehouse evolution do you use data to determine what will happen?
- pre-defined reports
- event based triggering
- continuous update
- analytical modeling
64) Which statement is true for Teradata data distribution?
- manual distribution of the data
- automatic distribution of the data
- based on hashing of the USI
- based on hashing of the Primary Key
65) Which feature allows Teradata data to process large amount of data quickly?
- High availability software and hardware components
- Parallelism
- Proven scalability
- High performance server from Intel
66) Which type of failure does a clique protect the system from?
- Loss of a disk drive
- Power loss
- Loss of a node
- Loss of a Vproc
67) Which statement is true about a foreign key?
- Foreign Keys provide a second level uniqueness.
- Foreign Keys cannot contain NULL or missing values.
- Foreign Keys can’t change values over time.
- Each Foreign Key must exist as a Primary Key.
68) What information a SHOW TABLE statement gives?
- The current DDL, creation, and last modification dates
- The current DDL based on the last modification of the table
- The current DDL for the table with any modifications noted as comments
- The current DDL with CREATE changed to REPLACE
69) What information a HELP TABLE statement gives?
- The current DDL based on the last modification of the table
- The current DDL for the table with any modifications noted as comments
- Displays the column information defined in that table.
- The current DDL with CREATE changed to REPLACE
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?
- The INSERTs succeed but the UPDATE fails.
- The CREATE TABLE fails.
- The INSERTs and the UPDATE succeed.
- The second INSERT fails.
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?
- 30
- NULL
- 45
- 50
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 ?
- It uses both PI and NUSI to retrieve the rows
- It uses PI only, to retrieve the rows
- It uses NUSI only, to retrieve the rows
- It uses neither of the indexes to retrieve the rows
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;
- Uses c1 as the unique primary index to retrieve the row
- Uses c2 as the unique index to retrieve the row
- Parser will give an error
- Uses FTS to retrieve the row
74) A Teradata database is created with no perm space. Which of the following objects can’t be created in it?
- Views
- Tables
- Macros
- Stored Procedures
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 –
- 70 GB
- 50 GB
- 30 GB
- 100 GB
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:
- Shows the total grant_amount for each department
- Shows the total grant_amount for each department and department name
- Gives a syntax error as GROUP BY should come before FROM clause
- Give as error as non-aggregates in the SELECT list must also appear in the GROUP BY clause.
77) Consider the following SQL:
SELECT * FROM Employee SAMPLE 0.1;
What it should return when executed:
- It gives an error because SAMPLE can’t be less than 1
- It outputs 10% of Employee table rows randomly
- It outputs 10 rows of Employee table randomly
- It outputs first 10% of Employee table rows
78) What the following SQL will output?
SELECT customer_number, zip_code FROM location WHERE zip_code MOD 10000 = 0;
- All rows from location table where zip codes end with only one zero.
- Throws an error because zip_code can’t be zero
- All rows from location table where zip codes end with four zeroes.
- Gives “Division By Zero” error
79) The following SQL –
SELECT employee_no, employee_name, hire_date as DOJ
FROM employee
WHERE (DATE – hire_date) / 365.25 > = 10;
will return –
- all employee data with 10 or more years of service
- 0 rows as DATE is not defined
- an error as arithmetic fuctions are not allowed for DATE datatypes
- all employee data who joined 10 or more days back
80) Global Temporary Tables get created in –
- Perm Space
- Spool Space
- Temp Space
- None of the above
81) Which statement is false on secondary index in Teradata?
- They can be created during table creation time
- They can be created after table creation
- They can be dropped after table creation
- Once created they can’t be changed unless table is dropped
82) Which one is not a valid type of Primary Index in Teradata?
- Unique Primary Index or UPI
- Partitioned Primary Index or PPI
- Full Text Primary Index or FTPI
- Non Unique Primary Index or NUPI
83) When two different primary index values hash identically, it is called –
- Hash Map
- Hash Synonym
- Destination Selection Word
- Row ID
84) Which is not an example of Teradata database object?
- Exclusive Lock
- Triggers
- Macros
- Stored Procedures
85) Which one is not a valid type of SQL subset?
- Data Manipulation Language or DML
- Stored Procedural Language or SPL
- Table Creation Language or TCL
- Data Control Language or DCL
86) GRANT and REVOKE are examples of –
- Teradata extensions to SQL
- Data Control Language or DCL
- Data Manipulation Language or DML
- Stored Procedural Language or SPL
87) The main difference between Macro and Stored Procedure(SP) is –
- Macro can’t contain SQL where as SP can have SQL
- Macro doesn’t allow DDLs where as SP allows DDL operations
- Macro doesn’t allow DML operations where as SP allows DML operations
- Macro contains set of SQLs where as SP contains both SQL and procedural statements
88) EXPLAIN plan is not useful for –
- Index Selection
- Performance Tuning
- Debugging
- Predicting row counts
89) BTEQ stands for –
- Basic Teradata Execution Query
- Basic Teradata Query
- Basic Teradata Execution Quality
- Basic Teradata Execution Quantity