Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS tables.
Evaluate the following MERGE statement:
MERGE INTO orders_master o USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total);
What would be the outcome of the above statement?
A. The ORDERS_MASTER table would contain the ORDERJDs1and 2.
B. TheORDERS_MASTERtablewould containtheORDERJDs 1,2and3.
C. TheORDERS_MASTERtable would containtheORDERJDs 1,2 and 4.
D. The ORDERSMASTER table would containtheORDER IDs 1,2,3 and4.
Answer: C
Q2. 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
Answer: C
Q3. 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
Answer: DE
Q4. View the Exhibit and examine the structure of the ORDERS table.
NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES
(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’);
Why would the INSERT statement fail?
A. because column names in NEWORDERS and ORDERS tables do not match
B. because the VALUES clause cannot be used in an INSERT with a subquery
C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement
D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table
Answer: B
Q5. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables.
In the ORDERS table, ORDER_ID is the PRIMARY KEY and ORDER_DATE has the DEFAULT value as SYSDATE.
Evaluate the following statement:
UPDATE orders
SET order_date=DEFAULT
WHERE order_id IN (SELECT order_id FROM order_items
WHERE qty IS NULL);
What would be the outcome of the above statement?
A. The UPDATEstatementwould not work because the main queryandthe subquery usedifferenttables.
B. The UPDATEstatement would not work becausetheDEFAULTvaluecan be used only in INSERT statements.
C. TheUPDATEstatementwould changeall ORDER_DATE values to SYSDATE provided the current ORDER_DATE is NOT NULLand QTYis NULL
D. The UPDATE statement would change all the ORDER_DATE values to SYSDATE irrespective of what the current ORDER_DATE value is for all orders where QTY is NULL
Answer: D
Q6. View the Exhibit and examine the details for the CATEGORIES_TAB table.
Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description
FROM categories_tab
You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)
A. WHEREREGEXP_LIKE(category_description, 'hard+.s’);
B. WHERE REGEXP_LIKE(category_description,‘^H|hard+.s’);
C. WHERE REGEXP_LIKE (category_description, '^H|hard+.s$');
D. WHEREREGEXP_LIKE (category_description, '[^Hlhard+.s]');
Answer: AB