Online Access Free 1Z0-047 Practice Test

Evaluate the following SQL statement:
SELECT product_name || 'it's not available for order'
FROM product_information
WHERE product_status = 'obsolete';
You received the following error while executing the above query:
ERROR:
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?

• A.Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.
• B.Enclose the character literal string in the SELECT clause within the double quotation marks.
• C.Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.
• D.Do not enclose the character literal string in the SELECT clause within the single quotation marks.

Which three statements indicate the end of a transaction? (Choose three.)

• A.after a SAVEPOINT is issued
• B.after a ROLLBACK is issued
• C.after a SELECT statement is issued
• D.after a CREATE statement is issued
• E.after a COMMIT is issued

The user SCOTT who is the owner of ORDERS and ORDERJTEMS tables issues the following GRANT command: GRANT ALL ON orders, order_items TO PUBLIC;
What correction needs to be done to the above statement?

• A.WITH GRANT OPTION should be added to the statement.
• B.PUBLIC should be replaced with specific usernames.
• C.ALL should be replaced with a list of specific privileges.
• D.Separate GRANT statements are required for ORDERS and ORDERJTEMS tables.

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 first DEPT table.
• B.It recovers only the second DEPT table.
• C.It recovers both the tables but the names would be changed to the ones assigned in the RECYCLEBIN.
• D.It does not recover any of the tables because FLASHBACK is not possible in this case.

Evaluate the following CREATE TABLE command:
CREATE TABLE order_item (order_id NUMBER(3),
Item_id NUMBER(2),
qty NUMBER(4),
CONSTRAINT ord_itm_id_pk
PRIMARY KEY (order_id jtem_id)
USING INDEX
(CREATE INDEX ord_itm_idx
ON order_item(order_id item_id)));
Which statement is true regarding the above SOL statement?

• A.It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID_PK would be created.
• B.It would give an error because the USING INDEX clause is not permitted in the CREATE TABLE command.
• C.It would execute successfully and only ORD_ITM_IDX index would be created.
• D.It would give an error because the USING INDEX clause cannot be used on a composite primary key.