Q1. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234;
B. DELETE FROM empdet WHERE ename LIKE 'J%'
C. CREATE VIEWempvu AS SELECT* FROMempdept;
D. CREATEINDEX empdet_dx ON empdet(empno);
Answer: C
Q2. View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
A. WHERE
B. HAVING
C. GROUP BY
D. START WITH
E. CONNECT BY
Answer: ADE
Q3. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.)
A. it can be used to concatenate two strings.
B. it can be used to find out the total length of the string.
C. it can be used for string manipulation and searching operations.
D. it can be used to format the output for a column or expression having string data.
E. it can be used to find and replace operations for a column or expression having string data.
Answer: CDE
Q4. View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?
A. Change the WHERE clause to WHERE list_price = 0
B. Change the WHERE clause to WHERE list_price = ''.
C. Change the WHERE clause to WHERE list_price IS NULL.
D. In the WHERE clause, enclose NULL within single quotation marks.
E. In the WHERE clause, enclose NULL within double quotation marks.
Answer: C
Q5. View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statement:
SELECT phone_number,
REGEXP_REPLACE(phone_number,'([[: digit: ]]{3})\.([[: digit: ]]{3})\.([[: digit: ]]{4})', ,(\1)\2-\3')
"PHONE NUMBER"
FROM employees;
The query was written to format the PHONE_NUMBER for the employees. Which option would be the correct format in the output?
A. xxx-xxx-xxxx
B. (xxx) xxxxxxx
C. (xxx) xxx-xxxx
D. xxx-(xxx)-xxxx
Answer: C
Q6. Which two statements are true? (Choose two.)
A. The USER_SYNONYMSviewcan provide information about private synonyms.
B. The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.
C. All the dynamic performance views prefixed with V$ are accessible to all the database users.
D. The USER_OBJECTS view can provide information about the tables and views created by the user only.
E. DICTIONARY is a view thatcontains thenamesof allthe datadictionary views that theuser can access.
Answer: AE
Q7. View the Exhibit and examine the descriptions of the DEPT and LOCATIONS tables.
You want to update the CITY column of the DEPT table for all the rows with the corresponding value in the CITY column of the LOCATIONS table for each department.
Which SOL statement would you execute to accomplish the task?
A. UPDATE deptd
SET city = ANY (SELECT city FROM locations I);
B. UPDATE deptd
SET city = (SELECT city
FROM locations I)
WHERE d.location_id = l.location_id;
C. UPDATE deptd
SET city = (SELECT city
FROM locations I
WHERE d.location_id = l.location_id);
D. UPDATE deptd
SET city = ALL (SELECT city
FROM locations I
WHERE d.location_id = l.location_id);
Answer: C
Q8. 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
Q9. View the Exhibit and examine the description of the ORDER_ITEMS and
PRODUCT_INFORMATION tables.
The ORDER_ITEM table has records pertaining to details for each product in an order. The
PRODUCT_INFORMATION table has records for all the products available for ordering.
Evaluate the following SOL statement:
SELECT oi.order_id, pi.product_id
FROM orderjtems oi RIGHT OUTER JOIN product_information pi
ON (oi. product_id=pi. Product_id);
Which statement is true regarding the output of this SOL statement?
A. The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered.
B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered.
C. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table.
D. The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table.
Answer: B
Q10. Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)
A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.
Answer: AD