TE0-122 12 Teradata SQL Certification Practice Exam Set 3

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.
  • INSERT portion must specify the same partition as the UPDATE portion.
  • All partitioning columns need to be in the UPDATE's WHERE clause.
  • The target table may be defined with multilevel partitioning.

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

TE0-121 Teradata Basics Practice Test Set 1

The date '2009-04-10' is represented by StartDate and the date '2008-03-07' is represented by EndDate. Which calculation produces the number of days between the two dates?


Options are :

  • EndDate - StartDate
  • EXTRACT(DAY FROM EndDate) - EXTRACT(DAY FROM StartDate)
  • DIFF(EndDate, StartDate)
  • DAYS(StartDate, EndDate)

Answer : EndDate - StartDate

Which expression will return what hour it will be in 3 hours 30 minutes?


Options are :

  • EXTRACT(HOUR FROM CURRENT_TIME + INTERVAL ('3' HOUR + '30' MINUTE))
  • EXTRACT(HOUR FROM CURRENT_TIME + ('3' HOUR + '30' MINUTE) INTERVAL)
  • EXTRACT(HOUR FROM CURRENT_TIME + INTERVAL '3:30' HOUR TO MINUTE)
  • EXTRACT(HOUR FROM CURRENT_TIME + '3:30' HOUR TO SECOND INTERVAL)
  • EXTRACT(HOUR FROM CURRENT_TIME + '03:30')

Answer : EXTRACT(HOUR FROM CURRENT_TIME + ('3' HOUR + '30' MINUTE) INTERVAL)

Which statement is written correctly?


Options are :

  • . DELETE FROM Userlist WHERE UserDate < DATE '2000-01-01';
  • DELETE UserList WHERE UserDate < DATE '2000-01-01' ALL;
  • DELETE Userlist FROM ALL;
  • DELETE WHERE UserDate < DATE '2000-01-01';

Answer : . DELETE FROM Userlist WHERE UserDate < DATE '2000-01-01';

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 1

A company wants to award a 3% increase in salary to all employees who meet these two criteria: - They have a valid department assignment. - Their current salary is less than $50,000. Which two statements will accomplish this objective? (Choose two.)


Options are :

  • UPDATE emp FROM department AS dept, employee AS emp SET salary_amount = salary_amount * 1.03 WHERE emp.department_number = dept.department_number AND salary_amount < 50000;
  • UPDATE employee SET salary_amount = salary_amount * 1.03 WHERE employee.department_number IN (SELECT department_number FROM department AND salary_amount < 50000);
  • UPDATE employee SET salary_amount = salary_amount * 1.03 WHERE employee.department_number IN (SELECT department_number FROM department) AND salary_amount < 50000;
  • UPDATE employee AS emp FROM department AS dept SET salary_amount = salary_amount * 1.03 WHERE emp.department_number = dept.department_number AND salary_amount < 50000;

Answer : UPDATE employee SET salary_amount = salary_amount * 1.03 WHERE employee.department_number IN (SELECT department_number FROM department) AND salary_amount < 50000; UPDATE employee AS emp FROM department AS dept SET salary_amount = salary_amount * 1.03 WHERE emp.department_number = dept.department_number AND salary_amount < 50000;

What is a restriction on a macro?


Options are :

  • A DDL statement in a macro must be the only statement in the macro
  • A DDL statement in a macro must be followed by an END TRANSACTION or COMMIT statement.
  • A DDL statement in a Multi-statement macro must be the last statement in the macro.
  • A DDL statement in a macro must be the first statement in the macro.

Answer : A DDL statement in a Multi-statement macro must be the last statement in the macro.

A company wants a sequential ranking of all employee salaries. They do not want employees with the same salary to be given the same rank number. Which ordered analytical function should be used?


Options are :

  • PERCENT_RANK
  • ROW_NUMBER
  • RANK
  • QUANTILE

Answer : QUANTILE

NCR NR0-017 Teradata Masters Update V2R5 Practice Exam Set 5

A table contains the following column: Inv_TS TIMESTAMP(6) WITH TIME ZONE Which expression returns the normalized hour (for the current session time zone) of column Inv_TS?


Options are :

  • HOUR(CAST(Inv_TS AS TIMESTAMP(6)))
  • EXTRACT(HOUR FROM Inv_TS)
  • HOUR(Inv_TS AS TIMESTAMP(6))
  • EXTRACT(HOUR FROM CAST(Inv_TS AS TIMESTAMP(6)))

Answer : EXTRACT(HOUR FROM Inv_TS)

Which DCL statement is written correctly?


Options are :

  • GRANT SELECT, EXECUTE, DELETE TO User1 ON User1;
  • GRANT SELECT, EXECUTE, DELETE ON DATABASE Database1 TO User1;
  • GRANT SELECT, EXECUTE, DELETE TO User1 ON DATABASE Database1;
  • GRANT SELECT, EXECUTE, DELETE ON Database1 TO Database1;

Answer : GRANT SELECT, EXECUTE, DELETE ON DATABASE Database1 TO User1;

Given the following: CREATE TABLE table1_ppi ( column1 INTEGER NOT NULL ,column2 INTEGER ,column3 DATE FORMAT 'yyyy-mm-dd') PRIMARY INDEX (column1) PARTITION BY RANGE_N(column3 BETWEEN DATE '2002-01-01' AND DATE '2012-12-31' EACH INTERVAL '1' YEAR, NO RANGE); ALTER TABLE table1_ppi MODIFY PRIMARY INDEX DROP RANGE BETWEEN DATE '2002-01-01' AND DATE '2002-12-31' EACH INTERVAL '1' YEAR; What is an effect of the ALTER TABLE statement?


Options are :

  • Rows are moved from the dropped partition to the save table.
  • Rows are moved from the dropped partition to the NO RANGE partition.
  • Rows of the dropped partition are deleted.
  • Rows of the NO RANGE partition are deleted.

Answer : Rows are moved from the dropped partition to the NO RANGE partition.

TE0-125 Teradata 12 Solutions Development Practice Exam Set 2

Given the following query: SELECT * FROM Tbl_1 WHERE Col_1 NOT IN (SELECT Col_2 FROM Tbl_2); If one of the Col2 values is NULL, what is the impact on the Tbl_1 rows returned?


Options are :

  • The Tbl_1 rows that contain a NULL in Col_1 are returned.
  • Tbl_1 will return all of the non-NULL rows.
  • No Tbl_1 rows are returned for the entire query.
  • Tbl_1 will return all rows not in Tbl_2.

Answer : Tbl_1 will return all of the non-NULL rows.

A company wants to extract statistical salary information from a sample employee population. It would like to create 10 salary partitions, with $0 as the lower bound and $1,000,000 as the upper bound. Which ordered analytical function should be used?


Options are :

  • WIDTH_BUCKET
  • QUANTILE
  • RANK
  • SAMPLE_ID

Answer : SAMPLE_ID

Given the following reporting requirements: salary totals of each department a grand total of all departments Which grouping function is designed to handle these requirements?


Options are :

  • GROUP BY GROUPING SETS
  • ROLLUP
  • CUBE
  • PARTITION BY

Answer : PARTITION BY

TE0-124 Teradata 12 Database Administration Practice Exam Set 2

A mobile provider wants to increase the price on a specific feature included in several of their service offers. The feature name contains the string "walkabout". Which query modifies the correct rows from the Phone_Packages table?


Options are :

  • UPDATE Phone_Packages FROM Features SET price = price * 1.25 WHERE Features.feature_name LIKE '%walkabout%';
  • UPDATE Phone_Packages SET price = price * 1.25 WHERE Features.feature_name LIKE '%walkabout%';
  • UPDATE Phone_Packages SET price = price * 1.25 WHERE feature_code IN (SELECT feature_code FROM Features WHERE feature_name LIKE '%walkabout%');
  • UPDATE Phone_Packages FROM Features SET price = price * 1.25 WHERE feature_code IN (SELECT feature_code FROM Features WHERE feature_name LIKE '%walkabout%');

Answer : UPDATE Phone_Packages FROM Features SET price = price * 1.25 WHERE feature_code IN (SELECT feature_code FROM Features WHERE feature_name LIKE '%walkabout%');

Which three statements are true regarding stored procedure input and output parameters? (Choose three.)


Options are :

  • IN, INOUT and OUT arguments in a CALL request must correspond to an IN, INOUT or OUT parameter in the called procedure.
  • CALL arguments cannot be specified if a called procedure has no parameters.
  • NULL can be assigned to corresponding IN, INOUT and OUT parameters in the called procedure.
  • The number of arguments in a CALL request must equal the number of parameters in the called procedure.

Answer : CALL arguments cannot be specified if a called procedure has no parameters. NULL can be assigned to corresponding IN, INOUT and OUT parameters in the called procedure. The number of arguments in a CALL request must equal the number of parameters in the called procedure.

NR0-015 Teradata Design Architecture V2R5 Practice Test Set 3

Which expression will return the month it will be 300 days from today's date?


Options are :

  • (CURRENT_DATE + 300) MOD 12 + 1
  • MONTH(CURRENT_DATE + 300)
  • EXTRACT(MONTH FROM (CURRENT_DATE + 300))
  • CAST((CURRENT_DATE + 300) AS INTERVAL MONTH)

Answer : (CURRENT_DATE + 300) MOD 12 + 1

Column c1 has SMALLINT data type and column c2 has CHARACTER data type. What is the resulting data type when multiplying c1 and c2?


Options are :

  • INTEGER
  • FLOAT
  • SMALLINT
  • CHARACTER

Answer : SMALLINT

As part of a query, the average age of employees in the company needs to be compared to the age of each employee in the company. Which construct can be used to allow the writing of the query as a single SELECT statement?


Options are :

  • Virtual Table
  • recursive subquery
  • Derived Table
  • attribute function

Answer : Virtual Table

TE0-126 Teradata 12 Enterprise Architecture Practice Exam Set 3

Which statement is written correctly?


Options are :

  • DELETE employee FROM WHERE dept_no IN (SELECT dept_no FROM department WHERE salary_pool < 50000);
  • DELETE FROM employee WHERE dept_no IN (SELECT dept_no FROM department WHERE salary_pool < 50000);
  • DELETE FROM employee emp WHERE dept_no.emp IN (SELECT dept_no FROM department WHERE salary_pool < 50000);
  • DELETE FROM employee emp WHERE emp IN (SELECT dept_no FROM department WHERE salary_pool < 50000);

Answer : DELETE FROM employee emp WHERE emp IN (SELECT dept_no FROM department WHERE salary_pool < 50000);

Given the following PPI table: CREATE TABLE SalesHistory_Store1( Product_Code CHAR(8) ,Store_Number INTEGER ,Transaction_Date DATE ,Quantity_Sold INTEGER ,Notes CHAR(50)) PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY); Which three conditions are necessary to join to this table using a rowkey-based merge join? (Choose three.)


Options are :

  • all primary index and partitioning columns are specified as equality join terms
  • identical partitioning expressions
  • condition specified with partitioning column
  • same primary index

Answer : all primary index and partitioning columns are specified as equality join terms condition specified with partitioning column same primary index

What are two reasons to use the SAMPLE clause in an SQL statement? (Choose two.)


Options are :

  • to obtain a portion of data from an ordered set
  • to obtain a percentage of data rows from the SELECT query
  • to obtain identical sample sets from multiple executions of a single SQL statement
  • to obtain a specific number of data rows in the result set

Answer : to obtain a portion of data from an ordered set to obtain a specific number of data rows in the result set

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

A development team has decided that recursive queries are required to create a set of inventory reports. Which two facts must be considered when using derived tables for this project? (Choose two.)


Options are :

  • The temporary data will be available for other reports after the SQL request completes.
  • Derived tables are valid when coded as part of a seed statement of a recursive query.
  • The derived tables will be defined in the Data Dictionary for reuse.
  • Data distribution will be controlled by defining the primary index.
  • The derived tables can act like a viewed table.

Answer : The temporary data will be available for other reports after the SQL request completes. Data distribution will be controlled by defining the primary index.

A retailer needs to identify manufacturers that are no longer providing shoes to their shops so the manufacturer can be removed from their active Shoe_Manufacturer table. Which DELETE statement removes the correct rows from the Shoe_Manufacturer table?


Options are :

  • DELETE FROM Shoe_Manufacturer WHERE Shoe_Manufacturer.Shoe_style NOT IN (SELECT Shoes.Shoe_style FROM Shoes ,Shoe_Manufacturer WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);
  • DELETE FROM Shoes WHERE 0 = (SELECT COUNT(*) FROM Shoes ,Shoe_Manufacturer WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);
  • DELETE FROM Shoes WHERE Shoes.Shoe_style IN (SELECT Shoes.Shoe_style FROM Shoes ,Shoe_Manufacturer WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);
  • DELETE FROM Shoe_Manufacturer WHERE 0 = (SELECT COUNT(*) FROM Shoes ,Shoe_Manufacturer WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);

Answer : DELETE FROM Shoes WHERE Shoes.Shoe_style IN (SELECT Shoes.Shoe_style FROM Shoes ,Shoe_Manufacturer WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);

Which describes the functionality of QUANTILE?


Options are :

  • A function which allows a user to specify a sample of results returned by a specified SELECT statement.
  • A function which divides a set of rows into a set of partitions such as percentiles and tertiles.
  • A function which assigns partition numbers to values based on a specified range and the number of desired partitions.
  • A function which permits a column to be ranked, either based on a high or low order, against other rows in the answer set.

Answer : A function which allows a user to specify a sample of results returned by a specified SELECT statement.

TE0-124 Teradata 12 Database Administration Practice Exam Set 1

Which statement is true about the use of NO RANGE for a partition expression?


Options are :

  • UNKNOWN and NO RANGE are mutually exclusive.
  • When dropping a partition, no rows in that partition will be moved to the NO RANGE partition.
  • When adding a partition, no rows from the NO RANGE partition are moved to the added partition.
  • Its use may allow unwanted data to be inserted to the table.

Answer : When adding a partition, no rows from the NO RANGE partition are moved to the added partition.

Which three requirements result in choosing stored procedures for implementing an application? (Choose three)


Options are :

  • Large object (LOB) processing
  • CASE statement logic
  • execution of macros
  • dynamic SQL

Answer : CASE statement logic execution of macros dynamic SQL

Given the following business question: Display the employee number, department, and salary for full time employees who either earn less than $50,000 or work in department 610. Which WHERE clause will answer the business question?


Options are :

  • (department_number = 610 OR salary_amount < 50000.00) AND job_status = 'full time'
  • (department_number = 610 AND salary_amount < 50000.00) AND job_status = 'full time'
  • department_number = 610 OR (salary_amount < 50000.00 AND job_status = 'full time')
  • department_number = 610 OR salary_amount < 50000.00 OR job_status = 'full time'

Answer : (department_number = 610 OR salary_amount < 50000.00) AND job_status = 'full time'

TE0-122 12 Teradata SQL Certification Practice Exam Set 4

What is the evaluation order when using a HAVING, WHERE, and GROUP BY clause together in the same SELECT statement?


Options are :

  • WHERE, GROUP BY, HAVING
  • GROUP BY, WHERE, HAVING
  • WHERE, HAVING, GROUP BY
  • GROUP BY, HAVING, WHERE

Answer : GROUP BY, WHERE, HAVING

What information could be examined in DBC.DBQLogTbl to identify report queries submitted by a business intelligence tool to identify potential performance issues?


Options are :

  • number of active PEs
  • number of active AMPs
  • number of query bands used
  • number of rows returned

Answer : number of active PEs

What information can be reviewed to indicate CPU skewed processing for a query?


Options are :

  • the number of active AMPs in DBC.AccLogTbl
  • the response time from DBC.DBQLogTbl
  • the results of the execution of the SHOWCPUSKEW command before the query
  • the ratio of maximum AMP CPU to the average AMP CPU from DBC.DBQLogTbl

Answer : the results of the execution of the SHOWCPUSKEW command before the query

TE0-124 Teradata 12 Database Administration Practice Exam Set 5

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions