Q1. - (Topic 2) 

View the Exhibit and examine the structure and data in the INVOICE table. 

Which statements are true regarding data type conversion in expressions used in queries? (Choose all that apply.) 

A. inv_amt ='0255982' : requires explicit conversion 

B. inv_date > '01-02-2008' : uses implicit conversion 

C. CONCAT(inv_amt,inv_date) : requires explicit conversion 

D. inv_date = '15-february-2008' : uses implicit conversion 

E. inv_no BETWEEN '101' AND '110' : uses implicit conversion 

View Answer

Q2. - (Topic 2) 

The EMPLOYEES table contains these columns: 

EMPLOYEE_ID NUMBER(4) 

LAST_NAME VARCHAR2 (25) 

JOB_ID VARCHAR2(10) 

You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use? 

A. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_' ESCAPE '\' 

B. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' 

C. SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\"; 

D. SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_' 

View Answer

Q3. - (Topic 1) 

View the Exhibit and examine the description for the CUSTOMERS table. 

You want to update the CUST_INCOME_LEVEL and CUST_CREDIT_LIMIT columns for the customer with the CUST_ID 2360. You want the value for the CUST_INCOME_LEVEL to have the same value as that of the customer with the CUST_ID 2560 and the CUST_CREDIT_LIMIT to have the same value as that of the customer with CUST_ID 2566. 

Which UPDATE statement will accomplish the task? 

A. 

UPDATE customers SET cust_income_level = (SELECT cust_income_level FROM customers WHERE cust_id = 2560), cust_credit_limit = (SELECT cust_credit_limit FROM customers WHERE cust_id = 2566) WHERE cust_id=2360; 

B. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 OR cust_id=2566) WHERE cust_id=2360; 

C. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id IN(2560, 2566) WHERE cust_id=2360; 

D. 

UPDATE customers SET (cust_income_level,cust_credit_limit) = (SELECT cust_income_level, cust_credit_limit FROM customers WHERE cust_id=2560 AND cust_id=2566) WHERE cust_id=2360; 

View Answer

Q4. - (Topic 2) 

Which two statements complete a transaction? (Choose two) 

A. DELETE employees; 

B. DESCRIBE employees; 

C. ROLLBACK TO SAVEPOINT C; 

D. GRANT SELECT ON employees TO SCOTT; 

E. ALTER TABLE employeesSET UNUSED COLUMN sal; 

F. Select MAX(sal)FROM employeesWHERE department_id = 20; 

View Answer

Q5. - (Topic 1) 

Evaluate the following SQL commands: 

The command to create a table fails. Identify the reason for the SQL statement failure? 

(Choose all that apply.) 

A. You cannot use SYSDATE in the condition of a CHECK constraint. 

B. You cannot use the BETWEEN clause in the condition of a CHECK constraint. 

C. You cannot use the NEXTVAL sequence value as a DEFAULT value for a column. 

D. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD NO is also the FOREIGN KEY. 

View Answer

Q6. - (Topic 1) 

View the Exhibit and examine the structure of the PROMOTIONS table. Evaluate the following SQL statement: 

The above query generates an error on execution. 

Which clause in the above SQL statement causes the error? 

A. WHERE 

B. SELECT 

C. GROUP BY 

D. ORDER BY 

View Answer

Q7. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. 

Exhibit: 

You issue the following SQL statement: 

Which statement is true regarding the execution of the above query? 

A. It produces an error because the AMT_SPENT column contains a null value. 

B. It displays a bonus of 1000 for all customers whose AMT_SPENT is less than CREDIT_LIMIT. 

C. It displays a bonus of 1000 for all customers whose AMT_SPENT equals CREDIT_LIMIT, or AMT_SPENT is null. 

D. It produces an error because the TO_NUMBER function must be used to convert the result of the NULLIF function before it can be used by the NVL2 function. 

View Answer

Q8. - (Topic 1) 

User Mary has a view called EMP_DEPT_LOC_VU that was created based on the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. She has the privilege to create a public synonym, and would like to create a synonym for this view that can be used by all users of the database. 

Which SQL statement can Mary use to accomplish that task? 

A. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu; 

B. CREATE PUBLIC SYNONYM EDL:VU FOR mary (emp_dept_loc_vu); 

C. CREATE PUBLIC SYNONYM EDL_VU FOR emp_dept_loc_vu; 

D. CREATE SYNONYM EDL_VU ON emp_dept_loc_vu FOR EACH USER; 

E. CREATE SYNONYM EDL_VU FOR EACH USER ON emp_dept_loc_vu; 

F. CREATE PUBLIC SYNONYM EDL_VU ON emp_dept_loc_vu FOR ALL USERS; 

View Answer

Q9. - (Topic 1) 

A SELECT statement can be used to perform these three functions: 

Choose rows from a table. 

Choose columns from a table 

Bring together data that is stored in different tables by creating a link between 

them. 

Which set of keywords describes these capabilities? 

A. difference, projection, join 

B. selection, projection, join 

C. selection, intersection, join 

D. intersection, projection, join 

E. difference, projection, product 

View Answer

Q10. - (Topic 2) 

Which two statements are true regarding the DELETE and TRUNCATE commands? (Choose two.) 

A. DELETE can be used to remove only rows from only one table at a time. 

B. DELETE can be used to remove only rows from multiple tables at a time. 

C. DELETE can be used only on a table that is a parent of a referential integrity constraint. 

D. DELETE can be used to remove data from specific columns as well as complete rows. 

E. DELETE and TRUNCATE can be used on a table that is a parent of a referential integrity constraint having ON DELETE rule. 

View Answer