Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER__ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
A. DELETEorder_id
FROMorders
WHEREorder_total< 1000;
B. DELETEorders
WHERE order_total < 1000;
C. DELETE
FROM orders
WHERE (SELECTorder_id
FROM order_items);
D. DELETE orders o, order_itemsi
WHEREo.order id = i.order id;
Answer: B
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. 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
Q4. Which two statements are true regarding constraints? (Choose two.)
A. A foreign key cannot contain NULL values.
B. A column with the UNIQUE constraint can contain NULL.
C. A constraint is enforced only for the INSERT operation on a table.
D. A constraint can be disabled even if the constraint column contains data.
E. All the constraints can be defined at the column level as well as the table level
Answer: BD
Q5. Which statement is true regarding external tables?
A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table.
Answer: D
Q6. View the Exhibit and examine the structure of the ORDERS table.
The ORDERS table belongs to the user OE. HR is another user in the database.
Evaluate the commands issued by users OE and HR in the following order:
Statement 1 by user OE: GRANT SELECT,
UPDATE(customer_id, order_total)
ON orders
TOhr;
Statement 1 by user HR: SELECT * FROM oe.orders;
Statement 2 by user HR: UPDATE oe.orders
SET order_totah 10000;
Which statement is true regarding the above commands?
A. Statement 1 by user OE would not work because the statement has to be issued by the DBA.
B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update.
C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully.
D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns.
Answer: C