Q1. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables.
Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them?
SELECT location_id, city
FROM locations
SELECT location_id, city
FROM locations JOIN departments
USING(location_id);
A. UNION
B. MINUS
C. INTERSECT
D. UNIONALL
Answer: C
Q2. View the Exhibit and examine the structure of the ORDERS table.
Which UPDATE statement is valid?
A. UPDATE orders
SETorder_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455;
B. UPDATE orders
SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455;
C. UPDATE orders
SET order_date = '12-mar-2007'
AND order_total = TO_NUMBER(NULL)
WHERE order_id = 2455;
D. UPDATE orders
SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total =
TO_NUMBER(NULL) WHERE order id = 2455;
Answer: B
Q3. Which statement is true regarding synonyms?
A. Synonyms can be created for tables but not views.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
Answer: C
Q4. 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
Q5. View the Exhibit and examine the structure of the CUST table.
Evaluate the following SQL statements executed in the given order:
ALTER TABLE cust
ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED;
INSERT INTO cust VALUES (1 /RAJ1); --row 1
INSERT INTO cust VALUES (1 ,'SAM); --row 2
COMMIT;
SET CONSTRAINT cust_id_pk IMMEDIATE;
INSERT INTO cust VALUES (1 /LATA1); --row 3
INSERT INTO cust VALUES (2 .KING1); --row 4
COMMIT;
Which rows would be made permanent in the CUST table?
A. row4only
B. rows 2 and 4
C. rows 3 and 4
D. rows1and 4
Answer: C
Q6. Which statement correctly differentiates a system privilege from an object privilege?
A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object.
B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema.
C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database.
D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database.
Answer: D
Q7. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can containgroupfunctions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. Theyshouldnot be used withthe NOTIN operator inthemainquery if NULLislikelytobea part ofthe result of thesubquery.
Answer: AE
Q8. View the Exhibit and examine the structure of the ORDER_ITEMS table.
You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired output?
A. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id;
B. SELECT order_id FROM order_items
WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP
BY order_id;
C. SELECT order_id FROM order_items
WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP
BY order_id);
D. SELECT order_id FROM order_items GROUP BY order_id
HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)jFROM order_items
GROUP BY order_id);
Answer: D
Q9. 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.
Which SQL statement would you execute to get the desired output?
A. 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;
B. 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) 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
Q10. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,^A-Z’)NOVALIDATE;
B. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: alpha:]]'))NOVALIDATE;
D. ALTER TABLE CUSTOMERS ADD CONSTRAINT
cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[: digit: ]]’))NOVALIDATE;
Answer: C