Free Oracle 1Z0-047 Exam Dumps Questions & Answers

Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'penci');
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10; ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?

• A.Only the second DELETE statement would be rolled back.
• B.Both the DELETE statements and the UPDATE statement would be rolled back.
• C.Both the DELETE statements would be rolled back.
• D.No SQL statement would be rolled back.

View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or
1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)

• A.DELETE FROM (SELECT* FROM order_items i.orders o
  WHERE i.order_id = o.order_id AND order_status IN (0,1));
• B.DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));
• C.DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));
• D.DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o
  WHERE i. order_id = o. order_id AND
  order_status IN (0,1));

View the Exhibit and examine the structure of the EMP table.
You executed the following command to add a primary key to the EMP table:
ALTER TABLE emp
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
USING INDEX emp_id_idx;
Which statement is true regarding the effect of the command?

• A.The statement produces an error because the USING clause is permitted only in the CREATE TABLE command.
• B.The PRIMARY KEY would be created in a disabled state because it is using an existing index.
• C.The PRIMARY KEY is created and it would use an existing unique index.
• D.The PRIMARY KEY is created along with a new index.

Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);
ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;
ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?

• A.It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.
• B.It would remain disabled and has to be enabled manually using the ALTER TABLE command.
• C.It would be automatically enabled and deferred.
• D.It would be automatically enabled and immediate.

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite
PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)

• A.CREATE INDEX ord_idx
  ON ord(ord_no);
• B.CREATE INDEX ord_idx
  ON ord_items(ord_no);
• C.CREATE INDEX ord_idx
  ON ord_items(item_no);
• D.CREATE INDEX ord_idx
  ON ord,ord_items(ord_no, ord_date,qty);