Our pass rate is high to 98.9% and the similarity percentage between our 1Z0-071 study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Oracle 1Z0-071 exam in just one try? I am currently studying for the Oracle 1Z0-071 exam. Latest Oracle 1Z0-071 Test exam practice questions and answers, Try Oracle 1Z0-071 Brain Dumps First.

Q1. Which statement correctly grants a system privilege?

A. GRANT EXECUTE

ON prod

TO PUBLIC;

B. GRANT CREATE VIEW

ON tablel TO used;

C. GRANT CREATE TABLE

TO used ,user2;

D. GRANT CREATE SESSION TO ALL;

Answer: C


Q2. See the Exhibit and Examine the structure of the CUSTOMERS table:

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

A. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;

B. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers;

C. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers;

D. SELECT NVL(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers;

Answer:

Explanation: NVL Function

Converts a null value to an actual value:

Data types that can be used are date, character, and number. Data types must match:

– NVL(commission_pct,0)

– NVL(hire_date,'01-JAN-97')

– NVL(job_id,'No Job Yet')


Q3. Which three statements are true reading subquenes?

A. A Main query can have many subqueries.

B. A subquery can have more than one main query

C. The subquery and main query must retrieve date from the same table.

D. The subquery and main query can retrieve data from different tables.

E. Only one column or expression can be compared between the subquery and main query.

F. Multiple columns or expressions can be compared between the subquery and main query.

Answer: A,D,F


Q4. View the Exhibit and examine the ORDERS table.

The ORDERS table contains data and all orders have been assigned a customer ID. Which statement would add a NOT NULL constraint to the CUSTOMER_ID column?

A. ALTER TABLE orders

ADD CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

B. ALTER TABLE orders

MODIFY customer_id CONSTRAINT orders_cust_id_nn NOT NULL;

C. ALTER TABLE orders

MODIFY CONSTRAINT orders_cust_id_nn NOT NULL (customer_id);

D. ALTER TABLE orders

ADD customer_id NUMBER(6)CONSTRAINT orders_cust_id_nn NOT NULL;

Answer: B


Q5. Evaluate the following SQL statements that are issued in the given order:

CREATE TABLE emp

(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),

salary NUMBER(8,2),

mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

ALTER TABLE emp

DISABLE CONSTRAINT emp_emp_no_pk CASCADE; ALTER TABLE emp

ENABLE CONSTRAINT emp_emp_no_pk;

What would be the status of the foreign key EMP_MGR_FK?

A. It would be automatically enabled and deferred.

B. It would be automatically enabled and immediate.

C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.

D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Answer: A,B,D


Q6. View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers. The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address. Which SET operator would you use to get the required output?

A. INTERSECT

B. UNION ALL

C. MINUS

D. UNION

Answer: C


Q7. Examine the structure of the employees table.

There is a parent/child relationship betweenEMPLOYEE_IDandMANAGER_ID.

You want to display the last names and manager IDs of employees who work for the same manager asthe employee whoseEMPLOYEE_ID123.

Which query provides the correct output? 

A)

B)

C)

D)

A. Option A

B. Option B

C. Option C

D. Option D

Answer: B


Q8. You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

A. All data in the table are deleted but the table structure will remain

B. All data along with the table structure is deleted

C. All viewsand synonyms will remain but they are invalidated

D. The pending transaction in the session is committed

E. All indexes on the table will remain but they are invalidated

Answer: B,C,D


Q9. Which two statements are true regarding multiple-row subqueries? (Choose two.)

A. They can contain group functions.

B. They always contain a subquery within a subquery.

C. They use the < ALL operator to imply less than the maximum.

D. They can be used to retrieve multiple rows from a single table only.

E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

Answer: A,E


Q10. You are designing the structure of a table in which two columns have the specifications:

COMPONENT_ID - must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row

EXECUTION_DATETIME - contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.

Which two options define the data types that satisfy these requirements most efficiently?

A. The EXECUTION_DATETIME must be of INTERVAL DAY TO SECOND data type.

B. The EXECUTION _DATETIME must be of TIMESTAMP data type.

C. The EXECUTION_DATATIME must be of DATE data type.

D. The COMPONENT_ID must be of ROWID data type.

E. The COMPONENT_ID must be of VARCHAR2 data type.

F. The COMPONENT_ID column must be of CHAR data type.

Answer: C,E