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. 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
Answer: B
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. Which statement is true regarding the SESSION_PRIVS dictionary view?
A. It contains the current object privileges available in the user session.
B. It contains the current system privileges available in the user session.
C. It contains the object privileges granted to other users by the current user session.
D. It contains the system privileges granted to other users by the current user session.
Answer: B
Q5. 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
Q6. View the Exhibit and examine the structure of the ORD table.
Evaluate the following SQL statements that are executed in a user session in the specified order: CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL, 25-jan-2007',101);
UPDATE ord
SET ord_no= ord_seq.NEXTVAL
WHERE cust_id =101;
What would be the outcome of the above statements?
A. All the statements would execute successfullyandthe ORD_NO column would containthevalue 2 for the CUSTJD101.
B. TheCREATE SEQUENCE command would not execute because the minimum value and maximum value for the sequence have not been specified.
C. The CREATE SEQUENCE command wouldnotexecute because the starting value of the sequence and the incrementvaluehave not been specified.
D. Allthe statementswould execute successfully and the ORD_NO column wouldhave the value 20 for the CUST_ID101becausethedefault CACHE value is 20.
Answer: A
Q7. View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables.
You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.
Which query would you execute?
A. SELECT orde_id, product_id, unit_price*quantity "TOTAL"
FROM order_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o.order_date>=SYSDATE-7;
B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
USING (order_id)
WHERE o.order_date>=SYSDATE-7;
C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id);
D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM orde_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o. order date>=SYSDATE-7;
Answer: D
Q8. Given below is a list of datetime data types and examples of values stored in them in a random order:
Datatype Example
1) INTERVAL YEAR TO MONTH a) 2003-04-15 8:00:00 -8:00'
2) TIMESTAMP WITH LOCAL TIME ZONE b) '-K)6 03:30:16.000000'
3) TIMESTAMP WITH TIME ZONE c) '17-JUN-03 12.00.00.000000 AM'4)INTERVAL DAY TO
SECOND d) '402-00'
Identify the option that correctly matches the data types with the values.
A. 1-d.2-c.3-a.4-b
B. 1-b.2-a.3-c.4-d
C. 1-b.2-a,3-d,4-c
D. 1-d.2-c.3-b.4-a
Answer: A
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. Given below is a list of functions and their purpose in random order.
Function Purpose 1)NVL a) Used for evaluating NOT NULL and NULL values 2)NULLIF b) Used to return the first non- null values in a list of expressions 3)COALESCE c) Used
to compare two expressions. If both are same, it returns NULL; otherwise, it returns only the first expression.
4)NVL2 d) Used to convert NULL values to actual values
Identify the correct combination of functions and their usage.
A. 1-a,2-c,3-b,4-d
B. 1-d,2-c,3-b,4-a
C. 1-b,2-c,3-d,4-a
D. 1-d,2-b,3-c,4-a
Answer: B