Question 1

Examine this query which executes successfully;

Select job,deptno from emp

Union all

Select job,deptno from jobs_history;

What will be the result?

Options :

A : It will return rows from both select statements after eliminating duplicate rows.

B :

It will return rows common to both select statements.

C :

It will return rows both select statements including duplicate rows.

D :

It will return rows that are not common to both select statements.

Answer: C

Question 2

Examine the description of the CUSTOMERS table:

Which three statements will do an implicit conversion?

Options :

A : SELECT * FROM customers WHERE TO_ CHAR (customer_ id) = '0001';

B : SELECT * FROM customers WHERE customer id = '0001';

C : SELECT * FROM customers WHERE customer_ id = 0001;

D : SELECT FROM customers WHERE insert date = '01-JAN-19';

E : SELECT. FROM customers WHERE insert_ date = DATE *2019-01-01';

F : SELECT. FRON customers WE TO DATE (Insert _ date) = DATE ‘2019-01-01’;

Answer: B,D,E

Question 3

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

You executed this SQL statement:

SELECT first_name, department_id, salary

FROM employees

ORDER BY department_id, first_name, salary desc;

Which two statements are true regarding the result? (Choose two.)

Options :

A :

The values in the SALARY column would be returned in descending order for all employees having

the same value in the DEPARTMENT_ID and FIRST_NAME column.

B :

The values in the FIRST_NAME column would be returned in ascending order for all employees

having the same value in the DEPARTMENT_ID column.

C :

The values in the SALARY column would be returned in descending order for all employees having

the same value in the DEPARTMENT_ID column.

D : The values in all columns would be returned in descending order.

E :

The values in the FIRST_NAME column would be returned in descending order for all employees

having the same value in the DEPARTMENT_ID column.

Answer: A,B

Question 4

Which two statements are true regarding the SQL GROUP BY clause? (Choose two.)

Options :

A : You can use a column alias in the GROUP BY clause.

B : Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.

C : The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

D : Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.

E : If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Answer: A,D

Question 5

Examine the data in the CUST NAME column of the CUSTOMERS table:

CUST_NAME

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

Renske Ladwig

Jason Mallin

Samuel McCain

Allan MCEwen

Irene Mikkilineni

Julia Nayer

You want to display the CUST_NAME values where the last name starts with Mc or MC. Which two

WHERE clauses give the required result?

Options :

A :

WHERE INITCAP (SUBSTR(cust_name, INSTR(cust_name,'') +1)) IN ('MC%','Mc%)

B :

WHERE UPPER (SUBSTR(cust_name, INSTR(cust_name, '') +1)) LIKE UPPER('MC%')

C :

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,'') +1)) LIKE'Mc%'

D :

WHERE SUBSTR(cust_name,INSTR(cust_name,'') +1) LIKE'Mc%' OR'MC%'

E :

WHERE SUBSTR(cust_name, INSTR(cust_name,'') +1) LIKE'Mc%'

Answer: B,C

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

Buying Options: