1Z0-071 Oracle Exam Questions and Free Practice Test

Question 19

Examine the structure of the MEMBERS table: NameNull?Type
------------------------------------------------------------ MEMBER_IDNOT NULLVARCHAR2 (6)
FIRST_NAMEVARCHAR2 (50)
LAST_NAMENOT NULLVARCHAR2 (50)
ADDRESSVARCHAR2 (50)
CITYVARCHAR2 (25)
STATEVARCHAR2 (3)
You want to display details of all members who reside in states starting with the letter A followed by exactly one character.
Which SQL statement must you execute?

A. SELECT * FROM MEMBERS WHERE state LIKE '%A_*;

B. SELECT * FROM MEMBERS WHERE state LIKE 'A_*;

C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';

D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

Correct Answer:B

Question 20

A non-correlated subquery can be defined as . (Choose the best answer.)

A. A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.

B. A set of sequential queries, all of which must return values from the same table.

C. A set of sequential queries, all of which must always return a single value.

D. A SELECT statement that can be embedded in a clause of another SELECT statement only.

Correct Answer:A

Question 21

View the exhibit and examine the structure of the CUSTOMERS table.
1Z0-071 dumps exhibit
Which two tasks would require subqueries or joins to be executed in a single statement?

A. finding the number of customers, in each city, whose credit limit is more than the average credit limit of all the customers

B. finding the average credit limit of male customers residing in 'Tokyo' or 'Sydney'

C. listing of customers who do not have a credit limit and were born before 1980

D. finding the number of customers, in each city, who’s marital status is 'married'.

E. listing of those customers, whose credit limit is the same as the credit limit of customers residing in the city 'Tokyo'.

Correct Answer:AE

Question 22

Which two statements are true regarding constraints? (Choose two.)

A. All constraints can be defined at the column level and at the table level.

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

C. A column with the UNIQUE constraint can contain NULLS.

D. A foreign key column cannot contain NULLS.

E. A constraint is enforced only for INSERT operations.

Correct Answer:BC

Question 23

Which two statements are true regarding the COUNT function?

A. A SELECT statement using the COUNT function with a DISTINCT keyword cannot have a WHERE clause.

B. COUNT (DISTINCT inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

C. COUNT (cust_id) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUST_ID column.

D. COUNT (*) returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.

E. The COUNT function can be used only for CHAR, VARCHAR2, and NUMBER data types.

Correct Answer:BD

Question 24

Evaluate the following CREATE TABLE command:
1Z0-071 dumps exhibit
Which statement is true regarding the above SQL statement?

A. It would execute successfully and only ORD_ITM_IDX index would be created.

B. It would give an error because the USING INDEX clause cannot be used on a composite primary.

C. It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.

D. It would give an error because the USING INDEX is not permitted in the CRETAE TABLE command.

Correct Answer:A

START 1Z0-071 EXAM