A score of 24 of the 30 is normally considered a passing score. Use this information to assess your readiness to take the Teradata Certification Exams. When you are satisfied with your answers, click on the SUBMIT button at the end of the test. You will receive immediate notification of your score. 1. Which information is provided to a user when the SHOW TABLE statement is run? the current DDL based on the last modification of the table the current DDL with CREATE changed to REPLACE the current DDL, creation, and last modification dates the current DDL for the table with any modifications noted as comments 2. When a session is processing in ANSI mode, which statements are used to close a transaction? BEGIN WORK or END WORK statements BEGIN TRANSACTION or END TRANSACTION statements COMMIT TRANSACTION or ROLLBACK TRANSACTION statements COMMIT or ROLLBACK statements 3. What is true about the DROP TABLE statement? Columns to be dropped can be specified. Rows to be dropped can be specified. The statement removes any explicit privileges on the table. Permanent Journal space used for the table is released. 4. For the following table definition: CREATE TABLE t7 (d1 DATE, i1 INTEGER) PRIMARY INDEX (i1); Which statement creates an empty table with the same table definition? CREATE TABLE t9 AS t7 WITH NO DATA; CREATE TABLE t9 AS t7 WITHOUT DATA; CREATE TABLE t9 AS t7; CREATE TABLE t9 AS (SELECT * FROM t7) WITH NO DATA; 5. Consider the following set of SQL statements: 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 and the UPDATE succeed. The INSERTs succeed but the UPDATE fails. The second INSERT fails. The CREATE TABLE fails. 6. What are three advantages of using MERGE INTO instead of UPDATE ELSE INSERT for Upsert processing? (Choose three.) The UPDATE and INSERT portions need not reference the same row. The UPDATE operation may specify a different partition than the INSERT operation. The UPDATE operation may be a non-primary index operation. The primary index column may be an identity column. 7. Considering the following macro: CREATE MACRO Macro_1 (TBName char(30)) AS ( CREATE TABLE TBName (col_A INT, col_B INT); ); Which statement is true? CALL Macro_1 ('TableA') creates table TableA. EXECUTE Macro_1 ('TableA') creates table TBName. EXECUTE Macro_1 ('TableA') creates table TableA. You cannot have a CREATE TABLE statement within a macro. 8. Which three constructs can be used to secure data? (Choose three.) views macros locking modifiers privileges 9. Which three rules apply to the LIKE operator? (Choose three.) It implements pattern matching for strings of characters data. It requires a string expression to be searched and a string pattern to search for. It cannot be used with quantifiers. The searched for string pattern can contain specific characters and wildcards. It is case sensitive regardless of the session transaction mode. 10. 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? NULL 50 90 30 45 11. What is the result set for the following query? SELECT col_a FROM table_1 WHERE col_a NOT BETWEEN 8 AND 15; all rows for col_a excluding only 9 and 14 all rows for col_a excluding 9 through 14 all rows for col_a excluding only 8 and 15 all rows for col_a excluding 8 through 15 12. What is the displayed result of SELECT 3 * 100.00 / 4? .75 75 75.00 0 13. Table T1 contains one row as follows:  Birthdate   Yrs ----------   --- 01/18/1955    48 T2.Age is a SMALLINT T1.Yrs is CHARACTER(2) Considering the following SQL statement, which is true? UPDATE T2 SET Age = T1.Yrs + 5; The query succeeds because T1.Yrs is implicitly converted to a numeric. The query fails because the datatypes of T2.Age and T1.Yrs do not match. The query succeeds because T2.Age is altered to become a CHARACTER(2) data type. The query fails because there is no CAST statement to convert T1.Yrs to SMALLINT before the computation occurs. 14. Assuming the value of num_employees is null, which expression returns 0? NULLIF (num_employees, 0) ZEROIFNULL (num_employees, NULL) TRANSLATE (num_employees, 0) COALESCE (num_employees, 0) 15. Which function returns the first non-null value in an expression list? COALESCE NOT IFNULL FIRSTNOTNULL NULLIF 16. Consider the following SQL statement: SELECT region, state, store_number, total_sales WITH sum(total_sales) BY region WITH sum(total_sales) BY state; Which results do you get from this SELECT statement? total sales by store with subtotals by state and by region within state total sales by store with subtotals by state within region total sales by state within region total sales by region within state 17. The employee table contains columns emp_no, emp_name, dept_no. The department table contains columns dept_no, dept_name. Which SQL construct is used with IN to find the employees who work in the department named Finance? inner join derived table inclusion merge join subquery 18. In a join, when is the ON clause required? only when a WHERE clause is not used as part of the join all the time if the keyword INNER is specified if the join is a self join 19. Given the SQL statement: SELECT E.NAME, D.DEPARTMENT FROM EMPLOYEE E CROSS JOIN DEPARTMENT D; What does the result set contain? each department number shown with the employees in the department each employee name shown with all possible departments only employees with invalid departments each employee name and their department only employees with valid departments 20. Given the two tables and the query shown in the exhibit, what is the result set? NULL, 50000 5, NULL NULL, 30000 1, 20000 21. Which answer set is possible given this SELECT statement ? SELECT Name, ((Salary + (YrsExp * 200)) / 12) AS Projection FROM Employee WHERE DeptNo = 600 AND Projection < 2500;   Name              ((Salary + (YrsExp * 200)) / 12)    --------            --------------------------------    Carter J                   1300   Name                Projection    ---------            ----------    Carter J                17500   Name                Projection    ---------            ----------    Carter J                1300   Name              ((Salary + (YrsExp * 200)) / 12)    --------            --------------------------------    Carter J                   17500 22. What is the result of the following query? SELECT SUBSTRING (SUBSTRING ('RALPH JOHNSON' FROM 7 FOR 7) FROM 5 FOR 3); NSO JOH H J SON 23. Table_1 has only one column and contains the values (100, 200, 300, 400, 500). Table_2 has only one column and contains the values (100, 200, 250, 275, 500). If you use the INTERSECT operator between these tables, where Table_1 is the first table, what would the result set contain? 100, 200, 500 250, 275 300, 400 100, 200, 250, 275, 300, 400, 500 100, 100, 200, 200, 250, 275, 300, 400, 500, 500 250, 275, 300, 400 24. While using ordered analytic functions, which three SQL commands can by used within a window definition? (Choose three.) PARTITION BY ORDER BY ROWS GROUP BY 25. Which two types of tables could be considered when users need to collect statistics on a table? (Choose two.) Derived Volatile Permanent Global Temporary 26. Given a column with a timestamp data type, what function would you use to change it to a DATE data type? CAST INTERVAL SUBSTRING EXTRACT 27. Which three statements return a valid result? (Choose three.) SELECT DATE '1996-01-31' + INTERVAL '13' YEAR; SELECT DATE '1996-01-31' + INTERVAL '13' DAY; SELECT DATE '1996-01-31' + INTERVAL '1' DAY; SELECT DATE '1996-01-31' + INTERVAL '1' MONTH; 28. Considering the following SQL statement on customer_table which has a UPI of cust_id: SELECT cust_id, cust_name, cust_addr, SAMPLEID FROM customer_table SAMPLE 0.6, 0.25, 0.20; How many times will a particular cust_id appear with a different SAMPLEID? 2 It will not appear. The query's result is an error since the sample percentages add to greater than 1. 1 3 29. Considering the following SQL: SELECT DeptNo, SUM(Salary) FROM employee GROUP BY DeptNo; What type of clause needs to be added so that the answer set only contains departments with total salaries greater than $150,000? WITH SUM(Salary) BY DeptNo > 150000; HAVING SUM(Salary) > 150000; WHERE SUM(Salary) > 150000; WITH SUM(Salary) > 150000; 30. You have an EMPLOYEE table with columns name, dept, salary. Which two SQL statements produce a report that shows only employees having a salary greater than their departmental average salary? (Choose two.) SELECT name, dept, salary FROM EMPLOYEE e HAVING (SELECT AVG(salary) FROM EMPLOYEE f WHERE f.dept = e.dept GROUP BY dept) < salary; SELECT name, dept, salary FROM EMPLOYEE GROUP BY dept HAVING salary > AVG(salary); SELECT name, dept, salary FROM EMPLOYEE e WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE f GROUP BY dept) AND e.dept = f.dept; SELECT name, dept, salary FROM EMPLOYEE,(SELECT dept as avg_dept, AVG(salary) as avg_sal FROM EMPLOYEE GROUP BY 1) a WHERE dept = avg_dept AND salary > avg_sal; SELECT name, dept, salary FROM EMPLOYEE e WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE f WHERE e.dept = f.dept);