Q1. Evaluate the following expression using meta character for regular expression: 

'[AAle|ax.r$]' 

Which two matches would be returned by this expression? (Choose two.) 

A. Alex 

B. Alax 

C. Alxer 

D. Alaxendar 

E. Alexender 

View Answer

Q2. View the Exhibit and examine the structure of the CUSTOMERS table. 

CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. 

CUSTOMERS needs to be updated to reflect the latest information about the customers. 

What is the error in the following MERGE statement? 

MERGE INTO customers c USING customer_vu cv ON (c.customer_id = cv.customer_id) WHEN MATCHED THEN UPDATE SET c.customer_id = cv.customer_id, c.cust_name = cv.cust_name, 

c.cust_email = cv.cust_email, 

c.

 income_level = cv. Income_level 

WHEN NOT MATCHED THEN 

INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) 

WHERE cv. Income_level >100000; 

A. The CUSTOMER_ID column cannot be updated. 

B. TheINTO clause is misplaced in the command. 

C. The WHERE clause cannot be usedwithINSERT. 

D. CUSTOMER VU cannot beusedasadata source. 

View Answer

Q3. User OE, the owner of the ORDERS table, issues the following command: 

GRANT SELECT,INSERT 

ON orders 

TO hr 

WITH GRANT OPTION; 

The user HR issues the following command: GRANT SELECT ON oe. orders TO scott; 

Then, OE issues the following command: 

REVOKE ALL ON orders FROM hr; 

Which statement is correct? 

A. The user SCOTT loses the privilege to select rows fromOE.ORDERS. 

B. The user SCOTT retains the privilege to select rows from OE.ORDERS. 

C. The REVOKE statement generates an error because OE has to first revoke the SELECT privilege from SCOTT. 

D. The REVOKE statement generates an error because the ALL keyword cannot be usedforprivilegesthathave been granted using WITH GRANT OPTION. 

View Answer

Q4. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount" 

FROM order_items oi JOIN orders o 

ON oi.order_id = o.order_id 

GROUP BY CUBE (o.customer_id, oi.product_id); 

Which three statements are true regarding the output of this SQL statement? (Choose three.) 

A. t wouldreturn thesubtotals for theOrder Amount of every CUSTOMER_ID. 

B. twould returnthesubtotals fortheOrderAmountfor every PRODUCT_ID. 

C. twould return the subtotals fortheOrder Amount of every PRODUCT_IDandCUSTOMER_ID asonegroup. 

D. t would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

E. t wouldreturnonly thegrandtotal for theOrderAmount ofeveryCUSTOMER_ID and PRODUCT_ID as onegroup. 

View Answer

Q5. Evaluate the following SELECT statement and view the Exhibit to examine its output: 

SELECT constraint_name, constraint_type, search_condition, r_constraint_name, delete_rule, status FROM user_constraints WHERE table_name = ORDERS 

Which two statements are true about the output? (Choose two.) 

A. In the second column, indicates a check constraint. 

B. The STATUS column indicates whether the table is currently in use. 

C. The R_CONSTRAINT_NAME column gives the alternative name for the constraint. 

D. The column DELETE_RULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table. 

View Answer

Q6. Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.) 

A. You can use the JOIN clause to join only two tables. 

B. You can explicitly provide the join condition with a NATURAL JOIN. 

C. You can use the USING clause to join tables on more than one column. 

D. You can use the ON clause to specify multiple conditions while joining tables. 

View Answer

Q7. View the Exhibit and examine the data in the LOCATIONS table. 

Evaluate the following SOL statement: 

SELECT street_address 

FROM locations 

WHERE 

REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1; 

Which statement is true regarding the output of this SOL statement? 

A. It would displayallthe street addresses thatdo nothaveasubstring 'alpha'. 

B. It would displayallthestreetaddresseswhere the first character isaspecial character. 

C. It would display allthe streetaddresses wherethefirst character is aletterofthealphabet. 

D. It would displayall thestreet addresses where the first character isnota letter of the alphabet. 

View Answer

Q8. View the Exhibit and examine the structure of the ORDERS table. 

You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101 

Which query would give you the desired output? 

A. SELECT order id, order_date FROM orders 

WHERE order_date > ALL (SELECT MAX(order_date) 

FROM orders)AND 

Customer_id = 101; 

B. SELECT order id, order_date FROM orders 

WHERE order_date > ANY (SELECT order_date 

FROM orders 

WHERE customer_id = 101); 

C. SELECT order _id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101); 

D. SELECT order id, order_date FROM orders WHERE order_date IN (SELECT order_date FROM orders WHERE customer id = 101); 

View Answer

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

SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information 

Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.) 

A. ORDERBY4 

B. ORDER BY MIN_PRICE 

C. ORDER BY DIFFERENCE 

D. ORDER BY LIST_PRICE 

E. ORDERBYLIST_PRICE–MIN_PRICE 

View Answer

Q10. Evaluate the following statements: 

CREATE TABLE digits 

(id NUMBER(2), 

description VARCHAR2(15)); 

INSERT INTO digits VALUES (1,'ONE); 

UPDATE digits SET description =’TWO'WHERE id=1; 

INSERT INTO digits VALUES (2 .’TWO’); 

COMMIT; 

DELETE FROM digits; 

SELECT description FROM digits 

VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE; 

What would be the outcome of the above query? 

A. Itwouldnot display any values. 

B. It would displaythevalue TWO once. 

C. Itwould display the valueTWOtwice. 

D. Itwould display the values ONE, TWO, andTWO. 

View Answer