## NR0-013 Teradata SQL v2r5 Certification Practice Exam Set 2

##### Which statement returned the following answer set? TITLE(employee_number) ----------------- ----- employee#

Options are :

• SELECT TITLE(employee#) FROM Employee;
• SELECT DISTINCT(TITLE(employee#)) FROM Employee;
• SELECT DISTINCT TITLE(employee_number) FROM Employee;
• SELECT DISTINCT TITLE(employee#) FROM Employee;

Answer : SELECT DISTINCT TITLE(employee_number) FROM Employee;

##### Within ordered analytic functions, what is signified by the syntax element UNBOUNDED PRECEDING ?

Options are :

• The entire partition before and after the current row
• The one row prior to the current row
• The number of rows before the current row
• The entire partition before the current row

Answer : The entire partition before the current row

Options are :

• EXCEPT
• MINUS
• UNION
• INTERSECT

##### The choice of ANSI vs. Teradata mode affects which three items? (Choose three.)

Options are :

• Case sensitivity defaults
• Transaction protocol behavior
• Data conversions
• Rounding behavior

Answer : Case sensitivity defaults Transaction protocol behavior Data conversions

##### Which statement creates a table that is initialized with the result of a query?

Options are :

• CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;
• CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2);
• CREATE TABLE 19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH RESULTS;
• CREATE TABLE t19 LIKE (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;

Answer : CREATE TABLE t19 AS (SELECT a1, c2 FROM t1, t2 WHERE q1=r2) WITH DATA;

##### Which command will display the account you are currently using?

Options are :

• SELECT 'ACCOUNT';
• HELP SESSION;
• HELP USER;
• HELP ACCOUNT;
• EXPLAIN ACCOUNT;

Options are :

• 1
• 2
• 3
• 4

##### Which two can be specified in the CREATE TABLE statement? (Choose two.)

Options are :

• Maximum permanent space
• Data block size
• Before and after journaling
• Join index

Answer : Data block size Before and after journaling

##### What are two characteristics of COMPRESS ? (Choose two.)

Options are :

• It can be defined so a specific value is not stored in each row.
• It can be used on character data types only.
• It supports multiple values per column
• It will compress repeating 1s and 0s.

Answer : It can be defined so a specific value is not stored in each row. It supports multiple values per column

##### Which SQL clauses always perform the same type of operations?

Options are :

• DISTINCT and QUALIFY
• QUALIFY and HAVING
• HAVING and GROUP BY
• HAVING and DISTINCT

Options are :

• NOT IN
• INTERSECT
• EXCEPT
• SUBTRACT
• LESS

##### Which information does HELP TABLE &lt;tablename&gt; provide about the specified table?

Options are :

• Column name, table id, data type, and comment
• Column name, constraint, data type, and comment
• Column name, index, data type, and comment
• Column name, data type, and comment

Answer : Column name, data type, and comment

##### Which formatting string applied to a timestamp will produce the following output? Monday, November 04,2002 03:24:22 +00:00

Options are :

• FORMAT ' E3,BM2BD2,Y4BHH:MI:SSBZ '
• FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBT '
• FORMAT ' E3,BM2BD2,Y4BHH:MI:SSBT '
• FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBZ '

Answer : FORMAT ' E4,BM4BD2,Y4BHH:MI:SSBZ '

##### When processing a SELECT statement that includes WHERE , GROUP BY, and HAVING clauses, what is the order of evaluation during execution?

Options are :

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

Answer : WHERE, GROUP BY, HAVING

##### What does the EXPLAIN output provide to assist the user in query analysis? (Choose two.)

Options are :

• Details of which indexes, if any, will be used to process the query
• Recommendations to improve the query based on timing and sizing
• Which steps may be processed in parallel
• Accurate time measurements for query processing
• What statistics should be collected on a column

Answer : Details of which indexes, if any, will be used to process the query Which steps may be processed in parallel

##### Within ordered analytic functions, what is signified by the syntax element UNBOUNDED PRECEDING ?

Options are :

• The entire partition before the current row
• The number of rows before the current row
• The entire partition before and after the current row
• The one row prior to the current row

Answer : The entire partition before the current row

##### Which result do you get from the SELECT SESSION statement ?

Options are :

• The number of sessions running for the user
• The session transaction mode for the user
• The SQLFLAG value for the user
• The session number for the user

Answer : The session number for the user

##### Which statement returned the following answer set? TITLE(employee_number) ----------------- ----- employee#

Options are :

• SELECT DISTINCT(TITLE(employee#)) FROM Employee;
• SELECT TITLE(employee#) FROM Employee;
• SELECT DISTINCT TITLE(employee#) FROM Employee;
• SELECT DISTINCT TITLE(employee number) FROM Employee;

Answer : SELECT DISTINCT TITLE(employee number) FROM Employee;

##### Table_1 has only one column and contains these values (100, 200, 300, 400, 500). Table_2 has only one column and contains these values (100, 200, 250, 275, 500). If you use the EXCEPT operator between these tables, where Table 1 is the first table, what would the result set contain?

Options are :

• 250, 275
• 300, 400
• 100, 200, 500
• 250, 275, 300, 400
• 100, 200, 250, 275, 300, 400, 500

Options are :

• & and %
• \$ and %
• _ and \$
• _ and %

##### Consider the following SQL statement: SELECT colA, colB, SUM(colX) FROM table1 WHERE colA &gt; 45 GROUP BY colA HAVING SUM(colX) = 20; Which statement is true ?

Options are :

• The query fails because of the HAVING clause.
• The query fails because of the WHERE clause.
• The query fails because of the GROUP BY clause.
• The query succeeds.

Answer : The query fails because of the GROUP BY clause.

##### Which logical comparison is equivalent to " x BETWEEN y AND z" ?

Options are :

• ((x >= y) AND (x <=z))
• ((x > y) AND (x <= z))
• ( (x > y) AND (x < z))
• ((x >= y) AND (x < z))

Answer : ((x >= y) AND (x <=z))

##### Which two expressions are used to distinguish null data from n on- null data? (Choose two.)

Options are :

• IS NOT NULL
• < > NULL
• NULL ONLY
• IS NULL

Answer : IS NOT NULL IS NULL

##### How does a null sort during an ascending sort?

Options are :

• It sorts before negative values and before blank character values.
• It sorts after negative values and after blank character values.
• It sorts before negative values and after blank character values
• It sorts after negative values and before blank character values

Answer : It sorts before negative values and before blank character values.

##### Table tmp_tbl contains following four rows: c1 c2 ----------- ------- 9135 60. 9135 50. 9235 60. 9235 50. Which answer set is returned with the following SELECT statement ? SELECT * FROM tmp_tbl WHERE c1 = 9135 OR c1 = 9235 AND c2 &lt; 51.00 ORDER BY 1;

Options are :

• c1 c2 ----------- ------- 9135 60. 9135 50.
• c1 c2 ----------- ------- 9135 50. 9235 50.
• c1 c2 ----------- ------- 9135 50. 9235 50. 9235 60.
• c1 c2 ----------- ------- 9135 50. 9135 60. 9235 50.
• c1 c2 ------------------ 9135 60. 9135 50. 9235 60.

Answer : c1 c2 ----------- ------- 9135 50. 9135 60. 9235 50.

##### The FULL OUTER JOIN command is designed to produce which type of results?

Options are :

• All unmatched data from both tables, with the matched data eliminated
• All rows from the table listed before the JOIN command and the unmatched rows from the other table
• All matched data from both tables, and the unmatched rows from the second table listed in the JOIN clause
• All matched data from both tables, combined with all unmatched data from both tables

Answer : All matched data from both tables, combined with all unmatched data from both tables

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

Options are :

• No record found
• Null
• 0
• Error

Options are :

• CHARPOS
• INDEX
• SUBINDEX
• POS
• SUBSTRING

##### Consider this request in Teradata mode: DELETE t1; Which request performs equivalently in ANSI mode?

Options are :

• DELETE FROM t1 ALL;
• DELETE FROM t1;
• DELETE FROM t1; COMMIT;
• DROP TABLE t1;

Answer : DELETE FROM t1; COMMIT;

##### You have an EMPLOYEE table with columns name, dept, salary . Which statement produces a report of employees with the lowest salary in their department?

Options are :

• SELECT name, dept, salary FROM EMPLOYEE e WHERE salary = (SELECT MIN(salary) FROM EMPLOYEE f GROUP BY dept) AND e.dept = f.dept;
• SELECT name, dept, salary FROM EMPLOYEE GROUP BY dept HAVING salary = MIN(salary) ;
• SELECT name, dept, salary FROM EMPLOYEE ,(SELECT dept as min_dept, MIN(salary) as min_sal FROM EMPLOYEE GROUP BY 1) a WHERE dept = a.min_dept AND salary = a.min_sal ;
• SELECT name, dept, salary FROM EMPLOYEE e HAVING (SEL MIN(salary) FROM EMPLOYEE f WHERE f.dept = e.dept GROUP BY dep) = salary;

Answer : SELECT name, dept, salary FROM EMPLOYEE ,(SELECT dept as min_dept, MIN(salary) as min_sal FROM EMPLOYEE GROUP BY 1) a WHERE dept = a.min_dept AND salary = a.min_sal ;

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions