Question 1

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 2

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 3

Which two statements are true about single row functions?

Options :

A : CONCAT: can be used to combine any number of values

B : MOD: returns the quotient of a division operation

C : CEIL: can be used for positive and negative numbers

D : FLOOR: returns the smallest integer greater than or equal to a specified number

E : TRUNC: can be used with NUMBER and DATE values

Answer: C,E

Question 4

Examine the description of the ENPLYEES table:

Which two queries return all rows for employees whose salary is greater than the average salary in their department?

Options :

A :

SELECT ”

FROM employees

WHERE salary > ANY

SELECT AVG (salary)

EROM employees

GROUP BY department_ id);

B :

SELECT

FROM employees

WHERE salary > AVG (salary) OVER (PARTITION BY department _ id);

C :

SELECT”

FROM employees e1

WHERE salary >!

SELECT AVG (salary)

FROM employees e2

WHERE e1. Department _id = e2, department_ id

D :

SELECT.

FROM

SELECT e.", AVG (salary) OVER (PARTITION BY department id) avg_ sal

FROM employees e

WHERE salary > avg_ sal;

E :

SELECT”

FROM employees

WHERE salary >

( SELECT AVG

(salary) FROM

employees

GROUP BY department _ id

Answer: B,C

Question 5

Examine the description of the EMPLOYEES table:

You write this falling statement:

SELECT dept-no AS department_id MAX (salary) AS max-sal

FROM employees

WHERE salary > 10000

GROUP BY department_id

ORDER BY max-sal:

Which clause causes the error?

Options :

A : WHERE

B : SELECT

C : ORDER BY

D : GROUP BY

Answer: D

Free 1Z0-071 Mock Exam – Practice Online Confidently

Buying Options: