Question 1

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 2

Examine the structure of the MEMBERS table:

NameNull?Type

------------------ --------------- ------------------------------

MEMBER_IDNOT NULLVARCHAR2 (6)

FIRST_NAMEVARCHAR2 (50)

LAST_NAMENOT NULLVARCHAR2 (50)

ADDRESSVARCHAR2 (50)

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME " FROM

members;

What is the outcome?

Options :

A : It fails because the alias name specified after the column names is invalid.

B : It fails because the space specified in single quotation marks after the first two column names is invalid.

C : It executes successfully and displays the column details in a single column with only the alias column heading.

D : It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

Answer: D

Question 3

View the Exhibit and examine the data in the PROMOTIONS table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr.

You need to produce a report that provides the name, cost, and start date of all promos in the POST

category that were launched before January 1, 2000.

Which SQL statement would you use?

Options :

A : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category = 'post' AND promo_begin_date < '01-01-00';

B : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';

C : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';

D : SELECT promo_name, promo_cost, promo_begin_dateFROM promotionsWHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Answer: D

Question 4

Examine the description of the PRODUCT_STATUS table:

The STATUS column contains the values ‘IN STOCK’ or ‘OUT OF STOCK’ for each row.

Which two queries will execute successfully?

Options :

A :

SELECT prod_id "CURRENT AVAILABILITY" || q'(-s not available)' FROM product_status WHERE status = ’OUT OF STOCK';

B : SELECT prod_id || q-s not available'' FROM product_status WHERE status=’OUT OF STOCK’;

C :

SELECT prod_id || q'(-s not available)’ "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

D : SELECT prod_id || q'(-s not available)' FROM product_status WHERE status = ’OUT OF STOCK’;

E :

SELECT prod_id || q’(’s not available)' 'CURRENT AVAILABILITY' FROM product_status WHERE status = 'OUT OF STOCK';

F : SELECT prod_id || q"-s not available" FROM product_status WHERE status = 'OUT OF STOCK';

Answer: C,D

Question 5

Examine the description of EMPLOYEES table:

Which three queries return all rows for which SALARY+COMMISSION is greate than 20000?

Options :

A : SELECT * FROM employees WHERE salary+NULLF(commission,0)>=20000;

B : SELECT * FROM employees WHERE salary+NVL2(commission,commission,0)>=20000;

C : SELECT * FROM employees WHERE NVL2(salary)+commission,salary+commission,

D : SELECT * FROM employees WHERE salary+NVL(commission,0)>=20000;

E : SELECT * FROM employees WHERE NVL(salary+commission,0)>=20000;

F : SELECT * FROM employees WHERE NVL(salary+commission,0)>==20000;

Answer: A,B,D

Higher Test Marks with Free Online 1Z0-071 Exam Practice

Buying Options: