Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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
SETcity=ANY(SELECT city
FROMlocations I);
B. UPDATE deptd
SET city=(SELECT city
FROM locations I)
WHERE d.location_id = l.location_id;
C. UPDATE dept d
SETcity =(SELECTcity
FROM locations I
WHEREd.location_id = l.location_id);
D. UPDATE deptd SET city =ALL(SELECT city FROM locations I WHEREd.location_id = l.location_id);
Answer: C
Q2. Which three statements indicate the end of a transaction? (Choose three.)
A. after a COMMIT is issued
B. aftera ROLLBACK is issued
C. afteraSAVEPOINTis issued
D. afteraSELECT statement is issued
E. aftera CREATE statementis issued
Answer: ABE
Q3. Which two statements are true regarding operators used with subqueries? (Choose two.
A. The NOT IN operator is equivalent to IS NULL
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries.
E. TheNOT operator can be used with IN, ANY and ALL operators.
Answer: BE
Q4. 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
Q5. Which two statements are true regarding operators used with subqueries? (Choose two.
A. The NOT IN operator is equivalent to IS NULL
B. The <ANY operator means less than the maximum.
C. =ANY and =ALL operators have the same functionality.
D. The IN operator cannot be used in single-row subqueries.
E. TheNOT operator can be used with IN, ANY and ALL operators.
Answer: BE
Q6. 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;
Answer: C
Q7. Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP;
Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the firstDEPTtable.
B. Itrecovers onlythesecondDEPTtable.
C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case.
D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN.
Answer: B
Q8. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGEcommand
C. themultitableINSERT command
D. INSERTusingWITHCHECK OPTION
Answer: C
Q9. 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);
Answer: C
Q10. View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement:
SELECT order_id, product_id, unit_price FROM order_jtems WHERE unit_price = (SELECT MAX(unit_price) FROM order_items GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
A. Replace = with theINoperator.
B. Replace = withthe >ANYoperator.
C. Replace = with the>ALLoperator.
D. Remove the GROUP BY clause from the subquery and placeitin the main query.
Answer: A
Q11. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables.
You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get the maximum salary in their respective departments. The following SQL statement was written:
WITH
SELECT employee_id, last_name, salary
FROM employees
WHERE (department_id, salary) = ANY (SELECT*
FROM dept_max)
dept_max as (SELECT d.department_id, max(salary)
FROM departments d JOIN employees j
ON (d. department_id = j. department_id)
GROUP BY d. department_id);
Which statement is true regarding the execution and the output of this statement?
A. The statementwouldexecute and give the desired results.
B. Thestatement wouldnotexecute becausethe= ANY comparison operator is used instead of=.
C. Thestatement wouldnot execute because themain queryblock usesthequery name beforeitis even created.
D. The statement wouldnot execute because the commaismissing betweenthemain query block and the query name.
Answer: C
Q12. Evaluate the following DELETE statement:
DELETE FROM orders; There are no other uncommitted transactions on the ORDERS table. Which statement is true about the DELETE statement?
A. Itremovesallthe rows in thetable andallows ROLLBACK
B. It would not removetherows if thetablehasaprimary key.
C. It removesallthe rows as well as the structure of the table.
D. Itremoves all the rows inthetable and doesnotallow ROLLBACK
Answer: A
Q13. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.
A. It is possible to retrieve data only in top-down hierarchy.
B. Itis possible to retrieve data in top-down or bottom-up hierarchy.
C. It is possible to remove an entire branch from the output of the hierarchical query.
D. You cannot specify conditions when you retrieve data by using a hierarchical query.
Answer: BC
Q14. Given below is the list of meta character syntaxes and their descriptions in random order: Meta character syntax Description 1)^ a) Matches character not in the list 2) [^…] b) Matches character when it occurs at the beginning of a line 3) | c) Treats the subsequent meta character as a literal 4) \ d) Matches one of the characters such as the OR operator
Identify the option that correctly matches the meta character syntaxes with their descriptions.
A. 1-b, 2-a, 3-d,4-c
B. 1-a, 2-b, 3-d,4-c
C. 1-d, 2-b, 3-a,4-c
D. 1-b,2-c,3-d,2-a
Answer: A
Q15. View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.
You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT ID for the last six months.
A. SELECT o.customer_Id, oi.productj_id, SUM(oi.unit_price*oi. quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id.oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6;
B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total"
FROM orderjtems oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id.oi.product_id)
HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6;
C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6;
D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total"
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
GROUP BY ROLLUP (o.customer_id, oi.product_id);
Answer: D