Question 1

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 2

Examine the description of the CUSTONERS table

CUSTON is the PRIMARY KEY.

You must derermine if any customers'derails have entered more than once using a different

costno,by listing duplicate name

Which two methode can you use to get the requlred resuit?

Options :

A : RIGHT OUTER JOIN with seif join

B : FULL OUTER JOIN with seif join

C : SUBQUERY

D : seif join

E : LEFT OUTER JOIN with seif join

Answer: C,D

Question 3

Examine the description of the CUSTOMERS table:

You need to display last names and credit limits of all customers whose last name starts with A or B In lower

or upper case, and whose credit limit is below 1000.

Examine this partial query:

SELECT cust_last_nare, cust_credit_limit FROM customers

Which two WHERE conditions give the required result?

Options :

A : WHERE UPPER(cust_last_name) IN ('A%', 'B%') AND cust_credit_limit < 1000:

B :

WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR ITITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000

C :

WHERE UPPER(cust_last_name) BETWEEN UPPER('A%' AND 'B%’) AND ROUND (cust_credit_limit) < 1000;

D :

WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND ROUND(cust_credit_limit) < 1000;

E :

WHERE (UPPER(cust_last_name) like INITCAP ('A') OR UPPER(cust_last_name) like INITCAP ('B')) AND ROUND(cust_credit_limit) < ROUND(1000) ;

Answer: B,D

Question 4

Examine this description of the PRODUCTS table:

You successfully execute this command:

Which two statements execute without errors?

Options :

A :

B :

C :

D :

Answer: B

Question 5

Examine this statement:

SELECT cust_id, cust^last_ndma "Last Name"

FROM customers

WHERE country_id — 10 UNION

SELECT cust_ID_CUST_NO, cust_last_name

PROM customers

WHERE country__id = 30

Identify three order by clauses, any one of which will complete the query successfully

Options :

A : ORDER BY 2. cust _id

B : ORDER BY 2,

C : ORDER BY "CUST^NO

D : ORDER BY 'Last Name

E : ORDER BY CUST_NO

Answer: A,B,D