TE0-122 12 Teradata SQL Certification Practice Exam Set 1

Which statement correctly removes the DELETE privilege from all users who inherit rights from database Human_Resources?


Options are :

  • REVOKE DELETE FROM ALL Human_Resources ON Database1;
  • REVOKE DELETE ON Database1 FROM ALL Human_Resources;
  • REVOKE DELETE ON ALL Database1 FROM Human_Resources;
  • REVOKE DELETE FROM ALL Human_Resources ON ALL Database1;

Answer : REVOKE DELETE ON ALL Database1 FROM Human_Resources;

TE0-124 Teradata 12 Database Administration Practice Exam Set 5

Which two are reasons to use a view? (Choose two.)


Options are :

  • It enables renaming and formatting of columns.
  • It enables building of large and complex applications.
  • It simplifies updates to tables that make up the view
  • It simplifies selects from tables that make up the view.

Answer : It simplifies updates to tables that make up the view It simplifies selects from tables that make up the view.

Which statement is true regarding the use of the TYPE attribute function?


Options are :

  • It returns the name of the data type.
  • It cannot be used as a predicate.
  • It returns the default value for the expression.
  • It can be used in the partition expression for a partitioned primary index.

Answer : It can be used in the partition expression for a partitioned primary index.

A user is submitting additional requests via a BTEQ session as part of a multi-request transaction that is still open. Which session is in ANSI mode based on the additional requests being submitted?


Options are :

  • 1) A row INSERT statement is submitted that succeeds. 2) A row INSERT statement is submitted that violates a CHECK constraint. 3) A row INSERT statement is submitted that succeeds 4) A row INSERT statement is submitted that violates a CHECK constraint. 5) The user then sees, using a SELECT statement, that the row inserted in step 1 is not in the table and the row inserted in step 3 is in the table.
  • 1) A row INSERT statement is submitted that violates a CHECK constraint. 2) A second row INSERT statement is submitted that succeeds. 3) A Teradata system restart occurs. 4) After regaining access, the user sees, using a SELECT statement, that the row inserted in step2 is in the table.
  • 1) A row INSERT statement is submitted that succeeds. 2) A second row INSERT statement is submitted that violates a CHECK constraint. 3) The user then sees, using a SELECT statement, that the row inserted in step 1 is in the table.
  • 1) A row INSERT statement is submitted that succeeds. 2) An ET statement is submitted that succeeds. 3) The user then sees, using a SELECT statement, that the row in step 1 is in the table.

Answer : 1) A row INSERT statement is submitted that violates a CHECK constraint. 2) A second row INSERT statement is submitted that succeeds. 3) A Teradata system restart occurs. 4) After regaining access, the user sees, using a SELECT statement, that the row inserted in step2 is in the table.

TE0-121 12 Teradata Basics Certification Practical Exam Set 1

Company X is analyzing the average salary in each of its departments. It has asked the IT department to generate a report showing which employees have a salary greater than their department average. Which query will give this result?


Options are :

  • SELECT last_name ,salary_amount (FORMAT '$$$,$99.99') FROM employee ee WHERE salary_amount > (SELECT AVG (salary_amount) FROM employee em WHERE ee.department_number = em.department_number);
  • SELECT last_name ,salary_amount (FORMAT '$$$,$99.99') FROM employee ee WHERE salary_amount > (SELECT AVG (salary_amount) FROM employee em);
  • SELECT last_name ,salary_amount (FORMAT '$$$,$99.99') FROM employee ee WHERE salary_amount > (SELECT AVG (salary_amount) FROM department em WHERE ee.department_number = em.department_number);
  • SELECT last_name ,salary_amount (FORMAT '$$$,$99.99') FROM employee ee WHERE salary_amount > (SELECT AVG (department_number) FROM employee em WHERE ee.department_number = em.department_number);

Answer : SELECT last_name ,salary_amount (FORMAT '$$$,$99.99') FROM employee ee WHERE salary_amount > (SELECT AVG (salary_amount) FROM employee em);

Given that the Part table is defined as the following: partid INTEGER NOT NULL CHECK(partid>0) partnm VARCHAR(50) asmblyid INTEGER NOT NULL CHECK(asmblyid>=0) moddate DATE NOT NULL If asmblyid for partid is 0, the part is not a subpart of an assembly of parts. If asmblyid is greater than 0, the part is a subpart of an assembly of parts. Which query lists each partnm whose moddate is greater than moddate for its asmblyid?


Options are :

  • SELECT partnm FROM part p LEFT OUTER JOIN part pa WHERE p.asmblyid = pa.partid AND p.moddate > pa.moddate;
  • SELECT partnm FROM part p ,part pa ON pa.asmblyid = p.partid AND p.moddate > pa.moddate;
  • SELECT partnm FROM part p JOIN part pa WHERE p.asmblyid = pa.partid AND p.moddate > pa.moddate;
  • SELECT partnm FROM part p ,part pa WHERE p.asmblyid = pa.partid AND p.moddate > pa.moddate;

Answer : SELECT partnm FROM part p LEFT OUTER JOIN part pa WHERE p.asmblyid = pa.partid AND p.moddate > pa.moddate;

Which three statements are true about the functionality of stored procedures? (Choose three.)


Options are :

  • They allow better application security by restricting user access to procedures rather than accessing data tables directly.
  • They allow extensions to be written to Teradata for functions that don't exist.
  • They provide control and condition handling statements.
  • They provide security for automatically driven database events.
  • They provide better application maintenance because business rules are encapsulated and enforced on the server.

Answer : They allow better application security by restricting user access to procedures rather than accessing data tables directly. They allow extensions to be written to Teradata for functions that don't exist. They provide control and condition handling statements.

TE0-122 12 Teradata SQL Certification Practice Exam Set 1

Given the following query: SELECT * FROM trans t LEFT JOIN item i JOIN detailitem d ON i.itemid=d.itemid ON t.itemid=d.itemid; Which two statements provide the same result as the statement above? (Choose two.)


Options are :

  • SELECT * FROM (trans t LEFT JOIN item i ON t.itemid = i.itemid) JOIN detailitem d ON t.itemid = d.itemid;
  • SELECT t.* ,i.* ,d.* FROM detailitem d JOIN item i ON d.itemid = i.itemid RIGHT OUTER JOIN trans t ON t.itemid = d.itemid;
  • SELECT * FROM trans LEFT OUTER JOIN (item INNER JOIN detailitem ON item.itemid = detailitem.itemid) ON trans.itemid = item.itemid;
  • SELECT t.* ,i.* ,d.* FROM detailitem d JOIN item i ON i.itemid = d.itemid LEFT JOIN trans t ON t.itemid = d.itemid;

Answer : SELECT * FROM trans LEFT OUTER JOIN (item INNER JOIN detailitem ON item.itemid = detailitem.itemid) ON trans.itemid = item.itemid; SELECT t.* ,i.* ,d.* FROM detailitem d JOIN item i ON i.itemid = d.itemid LEFT JOIN trans t ON t.itemid = d.itemid;

A company has a policy to grant all privileges at role level. Given the following: Account_User Role GRANT SELECT ON Database Account_Records GRANT EXECUTE ON Database Account_Records Account_Admin Role GRANT UPDATE ON Database Account_Records GRANT DELETE ON Database Account_Records What is the impact of changing user A's default role from Account_User to Account_Admin?


Options are :

  • User A's current privileges do not change.
  • User A's current privileges are UPDATE and DELETE on Account_Records.
  • User A is no longer a member of the Account_User role.
  • User A's current privileges are SELECT, EXECUTE, UPDATE, and DELETE on Account_Records.

Answer : User A is no longer a member of the Account_User role.

Assume that all formatting symbols are set to default and that column salary_amount is defined as DECIMAL (10,2). Given the following statement: SELECT salary_amount(FORMAT 'G-9(I)BN') FROM ........ What is a valid result of this statement?


Options are :

  • 100000 US Dollars
  • 100,000 US Dollars
  • 00,100,000 US Dollars
  • 00100000 US Dollars

Answer : 100,000 US Dollars

TE0-123 Teradata 12 Physical Design & Implementation Exam Set 3

Which DCL statement is written correctly, if Macro1 is a macro?


Options are :

  • GRANT EXECUTE, DROP ON Macro1 TO User1 AND User2;
  • GRANT EXECUTE, DROP MACRO ON Macro1 TO User1 AND User2;
  • GRANT EXECUTE, DROP ON Macro1 TO User1, User2;
  • GRANT EXECUTE, DROP MACRO ON Macro1 TO User1, User2;

Answer : GRANT EXECUTE, DROP ON Macro1 TO User1 AND User2;

Given the following SQL statement modifier: LOCKING TABLE table1 FOR ACCESS Which statement is true when this statement is used?


Options are :

  • It is the most efficient approach to achieve precise results.
  • The query has less concern for data consistency.
  • Other users cannot access the table.
  • The lock will be delayed for an object being held for write.

Answer : It is the most efficient approach to achieve precise results.

A requirement exists to perform upsert processing against a partitioned table. You choose to execute a MERGE INTO instead of a UPDATE ELSE INSERT statement. Which three restrictions on the UPDATE ELSE INSERT statement make the MERGE INTO statement a better choice? (Choose three.)


Options are :

  • UPDATE portion may not modify any of the partitioning columns.
  • The target table may be defined with multilevel partitioning.
  • All partitioning columns need to be in the UPDATE's WHERE clause.
  • INSERT portion must specify the same partition as the UPDATE portion.

Answer : UPDATE portion may not modify any of the partitioning columns. The target table may be defined with multilevel partitioning. All partitioning columns need to be in the UPDATE's WHERE clause.

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 4

Which key words in an EXPLAIN plan might indicate a missing join condition?


Options are :

  • all-AMP join
  • all-row scan
  • missing join condition
  • join condition of ("1=1")

Answer : missing join condition

A manager walks by a Teradata workstation and sees someone has left for the day but did not log out of Teradata. Which built-in function could be used to determine which login name is assigned to this session?


Options are :

  • USER
  • ACCOUNT
  • DATABASE
  • SESSION

Answer : USER

TE0-123 Teradata 12 Physical Design & Implementation Exam Set 3

A manager asks a data analyst to produce a one time aggregated monthly summary report from multiple tables for a meeting in an hour. Which option would require fewer steps to complete the task?


Options are :

  • create multiple Derived Tables for the query report
  • create multiple permanent tables to load data for the query report
  • create multiple Volatile Tables for the query report
  • create multiple Global Temporary Tables for the query report

Answer : create multiple Volatile Tables for the query report

A company has a policy to grant all privileges at role level. Given the following: Account_User Role GRANT SELECT ON Database Account_Records GRANT EXECUTE ON Database Account_Records Account_Admin Role GRANT UPDATE ON Database Account_Records GRANT DELETE ON Database Account_Records Orders_User Role GRANT SELECT ON Database Account_Orders GRANT INSERT ON Database Account_Orders User A is a member of Account_User and Orders_User roles. What is the impact of changing user A's default role from Account_User to ALL?


Options are :

  • User A can EXECUTE and DELETE on the Account_Records database.
  • User A can INSERT on the Account_Orders database and EXECUTE on the Account_Records database.
  • User A can EXECUTE on the Account_Orders database and EXECUTE on the Account_Records database.
  • User A can UPDATE on the Account_Records database.

Answer : User A can EXECUTE and DELETE on the Account_Records database.

Given the following two very large PPI tables with current statistics: CREATE TABLE ppi_table1 ( column1 INTEGER NOT NULL ,column2 DATE) PRIMARY INDEX (column1) PARTITION BY RANGE_N(column2 BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' YEAR); CREATE TABLE ppi_table2 ( column1 INTEGER NOT NULL ,column2 INTEGER) PRIMARY INDEX (column1) PARTITION BY RANGE_N(column2 BETWEEN 1 AND 6 EACH 1); SELECT * FROM ppi_table1 t1, ppi_table2 t2 WHERE t1.column1=t2 .column1; Which statement is true about the step that can be taken by joining these tables?


Options are :

  • rowkey-based merge join
  • product join without dynamic partition elimination
  • single-window merge join
  • product join with dynamic partition elimination

Answer : product join without dynamic partition elimination

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 4

Given the following statement: SELECT * FROM sales s LEFT JOIN prod p ON s.prdid = p.prdid FULL JOIN customer c ON s.custid = c.custid; Which two statements provide the same result as the statement above? (Choose two.)


Options are :

  • SELECT * FROM sales s LEFT JOIN prod p ON s.prdid = p.prdid JOIN customer c ON s.custid = c.custid;
  • SELECT s.* ,p.* ,c.* FROM customer c FULL JOIN prod p RIGHT OUTER JOIN sales s ON p.prdid = s.prdid ON c.custid = s.custid;
  • SELECT * FROM (sales s LEFT JOIN prod p ON s.prdid = p.prdid) FULL OUTER JOIN customer c ON s.custid = c.custid;
  • SELECT s.* ,p.* ,c.* FROM prod p LEFT JOIN sales s ON p.prdid = s.prdid FULL JOIN customer c ON s.custid = c.custid;

Answer : SELECT * FROM sales s LEFT JOIN prod p ON s.prdid = p.prdid JOIN customer c ON s.custid = c.custid; SELECT s.* ,p.* ,c.* FROM prod p LEFT JOIN sales s ON p.prdid = s.prdid FULL JOIN customer c ON s.custid = c.custid;

Which DCL statement is written correctly to remove the UPDATE privilege?


Options are :

  • REVOKE UPDATE FROM Role1 ON Database1 WITH GRANT OPTION;
  • REVOKE UPDATE FROM Role1 ON Database1;
  • REVOKE UPDATE ON Database1 FROM Role1;
  • REVOKE UPDATE ON Database1 FROM Role1 WITH ADMIN OPTION;

Answer : REVOKE UPDATE ON Database1 FROM Role1 WITH ADMIN OPTION;

Which data type is supported for Multi-Value Compression (MVC)?


Options are :

  • FLOAT
  • TIME
  • VARCHAR
  • INTERVAL

Answer : TIME

TE0-125 Teradata 12 Solutions Development Practice Exam Set 2

Which is an example of a properly coded correlated subquery?


Options are :

  • SELECT * FROM employee emp WHERE EXISTS (SELECT * FROM Department dept WHERE deptnum = deptnum);
  • SELECT * FROM Employee emp WHERE EXISTS SELECT * FROM Department dept WHERE emp.deptnum = dept.deptnum;
  • SELECT * FROM Employee emp WHERE deptnum IN (SELECT deptnum FROM department dept);
  • SELECT * FROM Employee emp WHERE EXISTS (SELECT * FROM Department dept WHERE emp.deptnum = dept.deptnum);

Answer : SELECT * FROM Employee emp WHERE deptnum IN (SELECT deptnum FROM department dept);

Assume that all formatting symbols are set to default and that the column salary_amount is defined as DECIMAL (10,2). Given the following statement: SELECT salary_amount * (-1) (FORMAT '-Z(I)BN') FROM ........ What is a valid result of this statement?


Options are :

  • -100000 USD
  • -100000USD
  • -100000 US Dollars
  • -100000US Dollars

Answer : -100000 USD

Given the following table: CREATE SET TABLE emp_table ( emp_no INTEGER ,last_name CHAR(20) ,first_name CHAR(20) ,hire_date DATE FORMAT 'yyyy-mm-dd') UNIQUE PRIMARY INDEX (emp_no); Given the following statement: INSERT INTO emp_table VALUES (1011, 'Krishnaswamyn-Laavanya', ' Rajagopala', '2009-04-27'); Which two will occur? (Choose two.)


Options are :

  • In Teradata mode, the system will return an error.
  • In Teradata mode, a string is truncated and the row is inserted.
  • In ANSI mode, the system will return an error.
  • In ANSI mode, a string is truncated and the row is inserted

Answer : In Teradata mode, the system will return an error. In Teradata mode, a string is truncated and the row is inserted.

TE0-125 Teradata 12 Solutions Development Practice Exam Set 1

Which statement will correctly remove all granted privileges except the SELECT privilege on Database1 from Role1?


Options are :

  • REVOKE ALL BUT SELECT FROM Role1 ON Database1;
  • REVOKE EACH BUT SELECT ON Database1 FROM Role1;
  • REVOKE EACH BUT SELECT FROM Role1 ON Database1;
  • REVOKE ALL BUT SELECT ON Database1 FROM Role1;

Answer : REVOKE EACH BUT SELECT ON Database1 FROM Role1;

Given the following command: HELP STATISTICS sales.orders What is a valid output and what information is provided?


Options are :

  • Date Time Column Name Unique Values 10/15/09 17:41:54 Order_Number 150986 Displays the attributes for the columns making up the Hash Index.
  • Date Time Unique values Column names 10/15/09 22:39:08 156798 Order Id Displays summary attributes for statistics that have been collected in the Data Dictionary for the specified data table.
  • Date Time Table/View/JI/HI Kind Unique Values 10/15/09 02:19:22 Product T 2500 10/15/09 02:20:56 Active Orders V 29000 10/15/09 02:23:47 Product_Id JI 17000 10/15/09 02:19:22 Order_Id HI 11409 Displays attributes for statistics collected for all tables, views, Join Indexes and Hash Indexes.
  • Primary or Date Time Secondary Unique Values 10/15/09 22:39:08 P 16780 Displays the attributes for an index column including whether it is a primary or secondary index and the number of unique values within the column.

Answer : Date Time Column Name Unique Values 10/15/09 17:41:54 Order_Number 150986 Displays the attributes for the columns making up the Hash Index.

Which statement is true regarding the use of the DEFAULT attribute function?


Options are :

  • It can be used in the partitioning expression for defining partitioned primary indexes.
  • It can be used in an insert statement.
  • It can return a null value if the argument is 0
  • It cannot be used as a predicate.

Answer : It can return a null value if the argument is 0

NR0-015 Teradata Design Architecture V2R5 Practice Exam Set 1

Given the following: SELECT Department ,Employee_No ,SUM(Sales_Amt) FROM SALES WHERE Department IN ('Westcoast','Central','Eastcoast') What is needed to make this query run without error?


Options are :

  • GROUP BY 1,2
  • GROUP BY 1 ORDER BY 1,2
  • ORDER BY 1,2
  • modify the WHERE clause to include Employee_No

Answer : GROUP BY 1 ORDER BY 1,2

A user is submitting additional requests via a BTEQ session as part of a multirequest transaction that is still open. Which two sessions are in ANSI mode based on the additional requests being submitted? (Choose two.)


Options are :

  • 1) An UPDATE statement is submitted that succeeds. 2) An UPDATE statement is submitted that violates a CHECK constraint. 3) The user then sees, using a SELECT statement, that the UPDATE statement in step 1 is reflected in the table.
  • 1) An UPDATE statement is submitted that succeeds. 2) An ET statement is submitted that succeeds. 3) The user then sees, using a SELECT statement, that the UPDATE statement in step 1 is reflected in the table.
  • 1) An UPDATE statement is submitted that violates a CHECK constraint. 2) An UPDATE statement is submitted that succeeds. 3) A ROLLBACK statement is submitted. 4) The user then sees, using a SELECT statement, that the UPDATE statement in step 2 is reflected in the table.
  • 1) An UPDATE statement is submitted that succeeds. 2) An UPDATE statement is submitted that violates a CHECK constraint. 3) An UPDATE statement is submitted that succeeds 4) A ROLLBACK statement is submitted. 5) The user then sees, using a SELECT statement, that the UPDATE statements in step 1 and 3 are not reflected in the table.

Answer : 1) An UPDATE statement is submitted that succeeds. 2) An ET statement is submitted that succeeds. 3) The user then sees, using a SELECT statement, that the UPDATE statement in step 1 is reflected in the table. 1) An UPDATE statement is submitted that violates a CHECK constraint. 2) An UPDATE statement is submitted that succeeds. 3) A ROLLBACK statement is submitted. 4) The user then sees, using a SELECT statement, that the UPDATE statement in step 2 is reflected in the table.

Which ANSI SQL-2003 compliant function should be used when determining the length of a string?


Options are :

  • CHARACTER_LENGTH
  • CHAR_COUNT
  • COUNT
  • CHARACTERS

Answer : CHARACTER_LENGTH

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 3

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions