Question 1

An Oracle database server session has an uncommitted transaction in progress which updated 5000

rows in a table.

In which three situations does the transactions complete thereby committing the updates? (Choose

three.)

Options :

A : when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

B : when a CREATE INDEX statement is executed successfully in the same session

C : when a COMMIT statement is issued by the same user from another session in the same database instance

D : when the session logs out successfully

E : when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

F : when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Answer: A,B,D

Question 2

View the Exhibit and examine the data in EMP and DEPT tables.

In the DEPT table, DEPTNO is the PRIMARY KEY.

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing the

DEPTNO column in the DEPT table.

What would be the outcome of the following statements executed in the given sequence?

DROP TABLE emp;

FLASHBACK TABLE emp TO BEFORE DROP;

INSERT INTO emp VALUES (2, ‘SCOTT’, 10);

INSERT INTO emp VALUES (3, ‘KING’, 55);

Options :

A : Both the INSERT statements would fail because the constraints are automatically retrieved when the table is flashed back.

B : Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back.

C : Only the first INSERT statement would succeed because all constraints except the primary key constraint are automatically retrieved after a table is flashed back.

D : Only the SECOND INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back.

Answer: D

Question 3

SCOTT is a user in the database. Evaluate the commands issued by the DBA:

Which statement is true regarding the execution of the above commands?

Options :

A : Statement 1 would not execute because the WITH GRANT option is missing.

B : Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.

C : Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.

D : Statement 1 would not execute because the IDENTIFIED BY clause is missing.

Answer: B

Question 4

Evaluate these commands which execute successfully CREATE SEQUENCE ord_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 100000

CYCLE

CACHE 5000;

Create table ord_items(

ord_no number(4) default ord_seq.nextval not null,

Item_no number(3),

Qty number(3),

Expiry_date date,

Constraint it_pk primary key(ord_no,item_no),

Constraint ord_fk foreign key (ord_no) references orders(ord_no));

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Options :

A : Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

B :

Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.

C : Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

D :

IF sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

E : Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Answer: B,D

Question 5

Examine this SQL statement:

SELECT cust_id, cus_last_name 'Last Name'

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_nam

FROM customers

WHERE country_id = 30

Identify three ORDER BY clauses, any one of which can complete the query successfully.

Options :

A : ORDERBY 2, 1

B : ORDER BY 'CUST_NO'

C : ORDER BY 2,cust_id

D : ORDER BY CUST_NO

E : ORDER BY 'Last Name'

Answer: A,C