Q1. Evaluate the following statement: INSERT ALL WHEN order_total < 10000 THEN INTO small_orders WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, customer_id FROM orders; 

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? 

A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. 

C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

View Answer

Q2. Which three statements are true regarding single-row functions? (Choose three.) 

A. They can accept only one argument. 

B. They can be nested up to only two levels. 

C. They can return multiple values of more than one data type. 

D. They can be used in SELECT, WHERE, and ORDER BY clauses. 

E. They can modify the data type of the argument that is referenced. 

F. They can accept a column name, expression, variable name, or a user-supplied constant as arguments. 

View Answer

Q3. View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: 

SELECT product_name, list_price 

FROM product_information 

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; 

Which statement is true regarding the execution of the query? 

A. Itwould executebut theoutput would return no rows. 

B. It would execute and the outputwould displaythedesired result. 

C. It wouldnotexecute because the entireWHEREclause conditionisnot enclosedwithinthe parentheses. 

D. Itwould not execute becausethesame column has been used in both sidesoftheANDlogical operatortoform the condition. 

View Answer

Q4. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGEjDRDERS table. The order ID, order date, and order total should be added to the ORDERJHISTORY table, and order ID and customer ID should be added to the CUSTJHISTORY table. Which multitable INSERT statement would you use? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

View Answer

Q5. Evaluate the following statement: 

INSERT ALL 

WHEN order_total < 10000 THEN 

INTO small_orders 

WHEN order_total > 10000 AND order_total < 20000 THEN 

INTO medium_orders 

WHEN order_total > 2000000 THEN 

INTO large_orders 

SELECT order_id, order_total, customer_id 

FROM orders; 

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement? 

A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause. 

B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. 

C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses. 

D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true. 

View Answer

Q6. Evaluate the following CREATE SEQUENCE statement: 

CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; 

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement: 

SELECT seql.nextval FROM dual; 

What is displayed by the SELECT statement? 

A. 1 

B. 10 

C. 100 

D. an error 

View Answer

Q7. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 

Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

View Answer

Q8. You need to create a table with the following column specifications: 

1. Employee ID (numeric data type) for each employee 

2. Employee Name, (character data type) which stores the employee name 

3. Hire date, to store the date when the employee joined the organization 

4. Status (character data type). It should contain the value if no data is entered. 

5. Resume (character large object [CLOB] data type), which would contain the resume submitted by the employee 

Which is the correct syntax to create this table? 

A. CREATETABLEEMP_1 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25), 

start_dateDATE, 

e_status VARCHAR2(10) DEFAULTACTIVE', 

resume CLOB(200)); 

B. CREATETABLE1_EMP 

(emp_idNUMBER(4), 

emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_status VARCHAR2(10) DEFAULT ACTIVE', 

resume CLOB); 

C. CREATETABLE1_EMP 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25),start_date DATE, 

emp_status VARCHAR2(10) DEFAULT "ACTIVE", 

resume CLOB); 

D. CREATE TABLEEMP_1 

(emp_id NUMBER, emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_statusVARCHAR2(10) DEFAULTACTIVE', 

resume CLOB); 

View Answer

Q9. View the Exhibit and examine the description of the EMPLOYEES table. 

Evaluate the following SQL statement: 

SELECT employee_id, last_name, job_id, manager_id, LEVEL 

FROM employees 

START WITH employee_id = 101 

CONNECT BY PRIOR employee_id=manager_id ; 

Which two statements are true regarding the output of this command? (Choose two.) 

A. Theoutputwouldbeintop-downhierarchy starting with EMPLOYEE_ID having value 101. 

B. Theoutput wouldbein bottom-up hierarchystartingwith EMPLOYEE_ID havingvalue101. 

C. TheLEVEL columndisplaysthenumber of employees in the hierarchy under the employee having theEMPLOYEE_ID 101. 

D. The LEVEL column displays the level in the hierarchy at which the employee is placed undertheemployee having the EMPLOYEE_ID 101 

View Answer

Q10. Which statement correctly grants a system privilege? 

A. GRANT EXECUTE ON prod TO PUBLIC; 

B. GRANT CREATE VIEW ON tablel TO User1; 

C. GRANT CREATE TABLE TO used,user2; 

D. GRANT CREATE SESSION TO ALL; 

View Answer