Question 1

Which statement is true regarding the INTERSECT operator?

Options :

A : The names of columns in all SELECT statements must be identical.

B : It ignores NULLs.

C : Reversing the order of the intersected tables alters the result.

D : The number of columns and data types must be identical for all SELECT statements in the query.

Answer: D

Question 2

Which two statements are true about * _ TABLES views?

Options :

A : You must have SELECT privileges on a table to view it in ALL _TABLES.

B : You must have SELECT privileges on a table to view it in DBA TABLES.

C : USER_ TABLES displays all tables owned by the current user.

D : ALL TABLES displays all tables owned by the current user.

E : You must have SELECT privileges on a table to view it in USER TABLES.

F : All users can query DBA TABLES successfully.

Answer: C,F

Question 3

View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at

the base location.

Examine the following two SQL statements:

Statement 1

SQL>SELECT faculty_name, COUNT(student_id)

FROM student JOIN faculty

USING (faculty_id, location_id)

GROUP BY faculty_name;

Statement 2

SQL>SELECT faculty_name, COUNT(student_id)

FROM student NATURAL JOIN faculty

GROUP BY faculty_name;

Which statement is true regarding the outcome?

Options :

A : Only statement 2 executes successfully and gives the required result.

B : Only statement 1 executes successfully and gives the required result.

C : Both statements 1 and 2 execute successfully and give different results.

D : Both statements 1 and 2 execute successfully and give the same required result.

Answer: B

Question 4

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 5

View the exhibit and examine the structure of the PROMOTIONS table.

You have to generate a report that displays the promo name and start date for all promos that

started after the last promo in the ‘INTERNET’ category.

Which query would give you the required output?

Options :

A : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ALL (SELECT MAX (promo_begin_date)FROM promotions) ANDpromo_category= ‘INTERNET’;

B : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date IN (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

C : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date > ALL (SELECT promo_begin_dateFROM promotionsWHERE promo_category = ‘INTERNET’);

D : SELECT promo_name, promo_begin_date FROM promotionsWHERE promo_begin_date> ANY (SELECT promo_begin_dateFROM promotionsWHERE promo_category= ‘INTERNET’);

Answer: C