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 and data in the PRICE_LIST table:

You plan to give a discount of 25% on the product price and need to display the discount amount in

the same format as the PROD_PRICE.

Which SQL statement would give the required result?

Options :

A : SELECT TO_CHAR (prod_price* .25, ‘$99,999.99’)FROM PRICE_LIST

B : SELECT TO_CHAR (TO_NUMBER(prod_price) * .25, ‘$99,999.00’)FROM PRICE_LIST

C : SELECT TO_CHAR (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST

D : SELECT TO_NUMBER (TO_NUMBER(prod_price, ‘$99,999.99’) * . 25, ‘$99,999.00’)FROM PRICE_LIST

Answer: C

Question 3

Examine this SQL statement:

Which two are true?

Options :

A : The subquery is not a correlated subquery.

B : The subquery is executed before the DELETE statement is executed

C : The DELETE statement executes successfully even if the subquery selects multiple rows

D : The subquery is executed for every row in the EMPLOYEES table.

E : All existing rows in the EMPLOYEES table are deleted

Answer: B,D

Question 4

Which three statements are true regarding subqueries? (Choose three.)

Options :

A : Multiple columns or expressions can be compared between the main query and subquery.

B : Subqueries can contain ORDER BY but not the GROUP BY clause.

C : Main query and subquery can get data from different tables.

D : Subqueries can contain GROUP BY and ORDER BY clauses.

E : Main query and subquery must get data from the same tables.

F : Only one column or expression can be compared between the main query and subquery.

Answer: A,C,D

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

Confidently Practice Online with Free 1Z0-071 Exam Cram

Buying Options: