Free Oracle 1Z0-071 Exam Dumps and 1Z0-071 Exam Questions

Question No: 41

MultipleChoice

Examine this data in the EMPLOYEES table:

Which statement will execute successfully?

Options

ASELECT dept_id, LENGTH (last: name) , SUM(salary) FROM employees GROUP BV dept_id;

BSELECT dept_id, STDDEV(last_name), SUM( salary) FROM employees GROUP BV dept_id;

CSELECT dept_id, MAX (last_name) . SUM(salary) FROM employees GROUP BY dept_id;

DSELECT dept_id( INSTR(last_name, 'A'), SUM (salary) FROM employees GROUP BY dept_id;

Question No: 42

MultipleChoice

You execute these commands:

CREATE TABLE customers (customer_id INTEGER, customer_name VARCHAR2 (20) )

TNSERT INTO customers VALUES (1, 'Customer 1 ' ) ;

SAVEPOTNT post_insert;

INSERT INTO customers VALUES (2, 'Customer 2');

SELECT COUNT(*) FROM customers;

Which two, used independently, can replace so the query returns 1?

Options

AROLLBACK TO SAVEPOINT post_insert;

BROLLBACK TO post_insert;

CCOMMIT TO SAVEPOINT post_insert;

Dcommit;

EROLLBACK,'

Question No: 43

MultipleChoice

BOOK_SEQ is an existing sequence in your schema.

Which two CREATE TABLE commands are valid?

A)

B)

C)

D)

E)

Options

AOption A

BOption B

COption C

DOption D

EOption E

Question No: 44

MultipleChoice

Examine these statements which execute successfully:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYY HH24 :MT:SS;'

ALTER SESSION SET TIME-ZONE = '-5:00;:

Examine the result:

If LOCALTIMESTAMP was selected at the same time, what would it return?

Options

A11-JUL-2019 6.00.00.00000000 AM

B11-JUL-2019 11.00.00.00000000 AM

C11-JUL_2019 6.00.00.00000000 AM -05:00

D11-JUL_2019 11.00.00.00000000 AM -05:00

Question No: 45

MultipleChoice

Examine this query and its output:

Examine this query with an incomplete was WHERE clause:

Which two are true about operators that can be used In the WHERE clause?

Options

AUsing not in or <> any will give the same result.

BUsing in will display all the product names,

CUsing not in or <> all will give the same result.

DUsing <> any will display all the product names except the product named Fork.

EUsing <> any will display all the product names.

Question No: 46

MultipleChoice

Examine this query:

SELECT 2

FROM DUAL D1

CROSS JOIN DUA d2

CROSSS JOIN DUAL D3

WHERE 2 =3;

What is the result?

Options

ANo rows

BAn error

C3 rows

D8 row

E1 row

F6 rows

Question No: 47

MultipleChoice

Which two are true about constraints?

Options

AA constraint can be disabled even if the constrained column contains data.

BConstraints are enforced only during insert operations.

CA column with a FOREIGN key constraint can never contain a null value.

DA column with a unique constraint can contain a null value.

EAll constraints can be defined at the table or column level.

Question No: 48

MultipleChoice

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

AWHERE

BSELECT

CORDER BY

DGROUP BY

Question No: 49

MultipleChoice

Which two are true about savepoints?

Options

AAfter issuing a savepoint, you cannot roll back the complete transaction.

BYou can commit updates done between two savepoints without committing other updates In the current transaction.

CAfter issuing a savepoint, you can roll back to the savepoint name within the current transaction.

DThey make uncommitted updates visible to other sessions owned by the? same user.

EA rollback to savepoint command issued before the start of a transaction results In an error.

FThey make uncommitted updates visible to sessions owned by other users.

Question No: 50

MultipleChoice

Examine the description of the EMPLOYERS table:

Examine these requirements:

Disable the manager ID and salary of the lowest paid employees for that manager.

Exclude anyone whose manager is not known.

Exclude any managers where the minimum salary is 6000 or less.

Sort the output by minimum salary with the highest salary shown first.

Which statement will do this?

Options

ASELECT manager_id, MTN(salary)
FROM employees
WHERE manager_ id IS NOT NULL
GROUP BY manager_id
HAVING MlN (salary) > 6000
ORDER BV MlN (salary);

BSELECT managar_id, MIN (salary)
FROM employees
HAVING MIN(salary) > 6000
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY MTN(salary) DESC;

CSELECT manager_id, HTN(salary)
FROM employees
WHERE manager_id IS NOT NULL
HAVING MIN (salary) > 6000
GROUP BY manager_id
ORDEU BY MIN (salary) DESC;

DSELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
AND MIN(salary) > 6000
GROUP BY manager_id
ORDER BY MlN (salary) DESC;