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

##### Which two statements are true? (Choose two.)

Options are :

• CAST can be used to convert a byte data type to another data type.
• Conversion of numeric to integer rounds up any decimal portion.
• Conversion of numeric to integer truncates any decimal portion.
• CAST can be used to convert a non-byte data type to another non-byte data type.

Answer : Conversion of numeric to integer truncates any decimal portion. CAST can be used to convert a non-byte data type to another non-byte data type.

##### Which two statements have valid syntax and include an implicit join? (Choose two.)

Options are :

• SELECT * FROM t1 INNER JOIN t2 ON t1.a1=t2.a2, t3 WHERE t1.b1=t3.b3;
• SELECT * FROM t1 JOIN t2, t3 ON t1.t1=t2.a2 WHERE t1.b1=t3.b3;
• SELECT * FROM t1, t2, t3 WHERE t1.a1=t2.a2 AND t1.a1=t3.b3;
• SELECT * FROM t1, t2, t3 ON t1.a1=t2.a2 AND t2.b2=t3.b3;
• SELECT * FROM t1 INNER JOIN (t2, t3) ON t1.a1=.t2.a2 AND t2=b2=t3.b3;

Answer : SELECT * FROM t1 INNER JOIN t2 ON t1.a1=t2.a2, t3 WHERE t1.b1=t3.b3; SELECT * FROM t1, t2, t3 WHERE t1.a1=t2.a2 AND t1.a1=t3.b3;

##### Which SQL statement is a syntactically correct example of a Searched CASE expression?

Options are :

• SELECT CASE IF x=1 THEN 'Yes' ELSE NULL END FROM Table_A;
• SELECT CASE x WHEN 1 THEN 'Yes' ELSE 'No' END FROM Table_A;
• SELECT CASE WHEN x=1 THEN 'Yes' ELSE 'No' END FROM Table_A;
• SELECT CASE SEARCH x FOR 1 IF = THEN 'Yes' ELSE 'No' FROM Table_A;

Answer : SELECT CASE WHEN x=1 THEN 'Yes' ELSE 'No' END FROM Table_A;

##### Consider the following query: SELECT d.dept_no, d.name, e.name FROM department d INNER JOIN employee e ON e.dept_no = d.dept_no; Which is true?

Options are :

• The ON clause can be replaced with a WHERE clause to produce the same results.
• It returns information about employees who have no department number.
• It returns information about departments that have no employees assigned to them.
• It returns an output row for each successful match between employee and department.

Answer : It returns an output row for each successful match between employee and department.

Options are :

• 11
• 22
• 55
• 30
• 47

##### Which three statements about subqueries are true? (Choose three.)

Options are :

• They can use ORDER BY .
• They can be the object of an IN or NOT IN .
• They can be nested
• They generate a distinct list of values.

Answer : They can be the object of an IN or NOT IN . They can be nested They generate a distinct list of values.

Options are :

• 5
• 68
• 4
• 2

##### What is the result of the following query? SELECT last_name FROM employee ee WHERE ee.salary_amount &gt; (SELECT AVG (em.salary_amount) FROM employee em WHEREem.department_number = ee.department_number);

Options are :

• All employees whose salaries are greater than their departments' average salaries
• All employees whose salaries are greater than the average of the average department salary
• All employees whose salaries are greater than the average salary
• One employee per department whose salary is greater than the department's average salary

Answer : All employees whose salaries are greater than their departments' average salaries

Options are :

• GROUP BY
• SELECT
• WHERE
• ORDER BY

##### Which three are allowed within a view definition? (Choose three.)

Options are :

• HAVING
• ORDER BY
• UPDATE
• GROUP BY
• LEFT OUTER JOIN

Answer : HAVING GROUP BY LEFT OUTER JOIN

##### Consider the following data for column name where the column is non-case specific: Alison Dawn Susan Geoff Bob Gorm Paul What is the result set for the following query in Teradata mode? SELECT name FROM table_names WHERE name BETWEEN 'a' AND 'go';

Options are :

• Dawn, Bob, Geoff, Gorm
• Dawn, Bob, Geoff, Gorm, Paul
• Alison, Bob, Dawn, Geoff
• Alison, Bob, Dawn, Geoff, Gorm

Answer : Alison, Bob, Dawn, Geoff

##### What are two reasons to use macros? (Choose two.)

Options are :

• To have a query automatically ask for input values
• To have a simple way to create multiple tables in the same macro consistently
• To have the ability to pass parameter(s) to a query
• To have a simple way to execute a fixed series of SQL statements

Answer : To have the ability to pass parameter(s) to a query To have a simple way to execute a fixed series of SQL statements

Options are :

• 22
• 47
• 11
• 30
• 55

##### Which three statements about dates are true? (Choose three.)

Options are :

• DATE is a data type as well as a built-in function
• CURRENT_DATE is a data type as well as a built-in function
• The built-in functions DATE and CURRENT_DATE are equivalent.
• CURRENT_DATE is a column in SYS_CALENDAR.CALENDAR .
• DATE values are stored internally as integers.

Answer : DATE is a data type as well as a built-in function The built-in functions DATE and CURRENT_DATE are equivalent. DATE values are stored internally as integers.

##### What data type results from the following calculation? SELECT DATE '2003-02-15' - DATE '2003-01-15';

Options are :

• INTERVAL DAY
• INTEGER
• INTERVAL MONTH
• DATE

##### Con sider the following: SELECT e.name FROM department d RIGHT OUTER JOIN employee e ON d.dept_no = e.dept_no LEFT OUTER JOIN job j ON e.job_code = j.job_code; What could the result set contain? (Choose three.)

Options are :

• Departments with no employees
• Employees without departments
• All employees
• Jobs without employees
• Employees with invalid departments

Answer : Employees without departments All employees Employees with invalid departments

Options are :

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

##### What are three security reasons for using a macro? (Choose three.)

Options are :

• A parameterized macro can be designed to use the parameter to control the table or view to access in the macro.
• A parameterized macro can be designed to use the parameter to restrict access to a subset of the data for a given table or view used in the macro.
• The privilege to create the macro can be restricted to specified developers.
• It limits the need to grant privileges on tables and views used in the macro.

Answer : A parameterized macro can be designed to use the parameter to restrict access to a subset of the data for a given table or view used in the macro. The privilege to create the macro can be restricted to specified developers. It limits the need to grant privileges on tables and views used in the macro.

##### What does COUNT(*) return, if a single column table contains 100 rows with 37 of them being null ?

Options are :

• 63 with a warning message
• 63
• Null
• 100

##### What is the best description of this SQL statement? SELECT name, salary, avgsal FROM (SELECT AVG(salary) FROM employee) emp temp (avgsal) , employee WHERE salary &gt; avgsal ORDER BY salary DESC;

Options are :

• It uses a nested subquery with aggregate.
• It uses a correlated subquery with aggregate
• It is not valid.
• It uses a derived table with aggregate.

Answer : It uses a derived table with aggregate.

##### Which statement about data types is correct?

Options are :

• An INTERVAL times a number results in a number.
• A TIMESTAMP minus a TIMESTAMP results in an INTERVAL .
• A TIMESTAMP minus a TIMESTAMP results in a TIMESTAMP .
• A TIMESTAMP minus an INTERVAL results in an INTERVAL .

Answer : A TIMESTAMP minus a TIMESTAMP results in an INTERVAL .

Options are :

• NOT, AND, OR
• AND, OR, NOT
• NOT, OR, AND
• AND, NOT, OR

##### Which WHERE statement never returns any rows? SELECT EMPNO, DEPTNO, SALARY FROM RETAIL.EMPLOYEE

Options are :

• WHERE (DEPTNO = 2080 AND DEPTNO = 2200) AND (SALARY < 90000 AND SALARY > 40000)
• WHERE (DEPTNO = 2080 OR DEPTNO = 2200) OR (SALARY < 90000 AND SALARY > 40000)
• WHERE (DEPTNO = 2080 OR DEPTNO = 2200) AND (SALARY < 90000 AND SALARY > 40000)
• WHERE (DEPTNO = 2080 OR DEPTNO = 2200) AND (SALARY < 90000 OR SALARY > 40000)

Answer : WHERE (DEPTNO = 2080 AND DEPTNO = 2200) AND (SALARY < 90000 AND SALARY > 40000)

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

Options are :

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

Answer : IS NULL IS NOT NULL

##### What is the correct syntax for creating a parameterized macro, including the use of that parameter within the macro?

Options are :

• CREATE MACRO abc (x INTEGER) AS (SELECT Emp_Name FROM Table_1 WHERE Emp_No = x;);
• CREATE MACRO abc (x INTEGER) AS (SELECT Emp_Name FROM Table_1 WHERE Emp_No = :x;);
• CREATE MACRO abc (:x INTEGER) AS (SELECT Emp_Name FROM Table_1 WHERE Emp_No = x;);
• CREATE MACRO abc (INTEGER x) AS (SELECT Emp_Name FROM Table_1 WHERE Emp_No = :x;);

Answer : CREATE MACRO abc (x INTEGER) AS (SELECT Emp_Name FROM Table_1 WHERE Emp_No = :x;);

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions