Free Oracle 1z0-071 Practice Test & Real Exam Questions, Page 1

Question #1

Which two are true about constraints?

A. Constraints are enforced only during INSERT operations.

B. A column with a foreign key constraint can never contain a null value.

C. All constraints can be defined at the table or column level.

D. A column with a UNIQUE constraint can contain a NULL value.

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

Discussion 0

Correct Answer: D,E Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #2

Examine the description of the EMPLOYEES table:

NLS_DATE_FORMAT is set to DD-MON-YY.
Which query requires explicit data type conversion?

A. SELECT salary + 120.50 FROM employees;

B. SELECT join date 11.'11 salary FROM employees;

C. SELECT SUBSTR(join date, 1, 2)- 10 FROM employees;

D. SELECT join date + 20 FROM employees;

E. SELECT join date FROM employees where join date > *10-02-2018*;

Discussion 0

Correct Answer: E Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #3

Which statement will return a comma-separated list of employee names in alphabetical order for each department in the EMP table?

A. SELECT deptno,LISTAGG(ename, ' , ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno;

B. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (GROUP BY deptno) AS employee_list FROM emp ORDER BY ename;

C. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY ename) AS employee_list FROM emp GROUP BY deptno;

D. SELECT deptno,LISTAGG(ename, ', ') WITHIN GROUP AS employee_list FROM emp GROUP BY deptno ORDER BY ename;

Discussion 0

Correct Answer: C Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #4

Examine the description of the CUSTOMERS table:

You want to display details of all customers who reside in cities starting with the letter D followed by at least two character.
Which query can be used?

A. SELECT * FROM customers WHERE city LIKE'D_';

B. SELECT * FROM customers WHERE city ='D_%';

C. SELECT * FROM customers WHERE city LIKE'D %';

D. SELECT * FROM customers WHERE city ='%D_';

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #5

Which two are true about rollbacks?

A. Data consistency is not guaranteed after a rollback.

B. A transaction interrupted by a system failure is automatically rolled back.

C. The ROLLBACK statement does not release locks resulting from table updates.

D. If the ROLLBACK statement is used without TO SAVEPOINT, then all savepoints in the transaction are deleted .

E. Data Control L anguage (DCL) statements, such as GRANT and REVOKE, can be rolled back.

Discussion 0

Correct Answer: B,D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #6

Which three queries use valid expressions?

A. SELECT product_id,unit_price || 5 "Discount" , unit_price + surcharge - discount FROM products;

B. SELECT product_id,(unit_price * 0.15 / (4.75 + 552.25)) FROM products;

C. SELECT product_id,unit_price,5 "Discount", unit_price + surcharge-discount FROM products;

D. SELECT product_id,(expiry_date - delivery_date) * 2 FROM products;

E. SELECT product_id, expiry_date * 2 from products;

F. SELECT product_id, unit_price, unit_price + surcharge FROM products;

Discussion 0

Correct Answer: B,D,F Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #7

Examine the description of the CUSTOMERS table:
Which three statements will do an implicit conversion?

A. SELECT * FROM customers WHERE TO_CHAR(customer_id)='0001';

B. SELECT * FROM customers WHERE customer_id='0001';

C. SELECT * FROM customers WHERE insert_date=DATE'2019-01-01';

D. SELECT * FROM customers WHERE customer_id=0001;

E. SELECT * FROM customers WHERE insert_date'01-JAN-19';

F. SELECT * FROM customers WHERE TO_DATE(insert_date)=DATE'2019-01-01';

Discussion 0

Correct Answer: A,B,E Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #8

Examine the description of the BOOKS table:

The table has 100 rows.
Examine this sequence of statements issued in a new session;
INSERT INTO BOOKS VALUES ('ADV112' , 'Adventures of Tom Sawyer', NULL, NULL); SAVEPOINT a; DELETE from books; ROLLBACK TO SAVEPOINT a; ROLLBACK; Which two statements are true?

A. The first ROLLBACK command restores the 101 rows that were deleted, leaving the inserted row still to be committed.

B. The second ROLLBACK command undoes the insert.

C. The second ROLLBACK command does nothing.

D. The second ROLLBACK command replays the delete.

E. The first ROLLBACK command restores the 101 rows that were deleted and commits the inserted row.

Discussion 0

Correct Answer: B,C Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #9

Which two are true about granting object privileges on tables, views, and sequences?

A. SELECT can be granted on tables, views, and sequences.

B. DELETE can be granted on tables, views, and sequences.

C. INSERT can be granted only on tables and sequences.

D. REFERENCES can be grantrd only on tables.

E. ALTER can be granted only on tables and sequences.

Discussion 0

Correct Answer: A,D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #10

Which three statements are true about Structured Query Language (SQL)?

A. It is the only language that can be used for both relational and object-oriented databases

B. It is used to define encapsulation and polymorphism for a relational table

C. It provides independence for logical data structures being manipulated from the underlying physical data storage

D. It best supports relational databases

E. It guarantees atomicity, consistency, isolation, and durability (ACID) features

F. It requires that data be contained in hierarchical data storage

Discussion 0

Correct Answer: C,D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #11

Examine the description or the BOOKS_TRANSACTIONS table:

FOR customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

A. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level IS NOT NULL
AND due_amount IS NOT NULL;

B. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND due_amount !=NULL;

C. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level !=NULL
AND cust credit_level !=NULL;

D. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust_income_level IS NOT NULL
AND cust_credit_limit IS NOT NULL;

E. SELECT cust_first_name, cust_credit_limit * . 05 AS DUE AMOUNT
FROM customers
WHERE cust income_level <> NULL
AND due_amount <> NULL;

Discussion 0

Correct Answer: D Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Question #12

Which statement is true about aggregate functions?

A. Aggregate functions can be nested to any number of levels

B. The MAX and MIN functions can be used on columns with character data types

C. The AVG function implicitly converts NULLS to zero

D. Aggregate functions can be used in any clause of a SELECT statement

Discussion 0

Correct Answer: B Vote an answer

Explanation: Only visible for Pass4Leader members. You can sign-up / login (it's free).

Free Oracle 1z0-071 Practice Test & Real Exam Questions

Download Free Oracle 1z0-071 Demo