1z0-071 Exam Training Practice Tests (Oracle Database SQL)]

Question 1

Which three privileges can be restricted to a subset of columns in a table? (Choose three.)

A. DELETE B. REFERENCES C. UPDATE D. INDEX E. SELECT F. ALTER G. INSERT

Correct Answer: A,C,E

Question 2

Which three statements indicate the end of a transaction? (Choose three.)

A. after a COMMIT is issued B. after a SAVEPOINT is issued C. after a CREATE statement is issued D. after a SELECT statement is issued E. after a ROLLBACK is issued

Correct Answer: A,C,E

Question 3

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(TO_CHAR(cust_credit_limit*.15),'Not Available') "NEW CREDIT" FROM customers; B. SELECT TO_CHAR(NVL(cust_credit_limit*.15,'Not Available')) "NEW CREDIT" FROM customers; C. SELECT NVL(cust_credit_limit*.15,'Not Available') "NEW CREDIT" FROM customers; D. SELECT NVL(cust_credit_limit,'Not Available')*.15 "NEW CREDIT" FROM customers;

Correct Answer: A

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

Question 4

Examine the description of the SALEStable:

The SALEStable has 55,000 rows.
Examine this statement:

Which two statements are true? (Choose two.)

A. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table. B. SALES1 has PRIMARY KEY and UNIQUE constraints on any selected columns which had those constraints in the SALES table. C. SALES1 is created with no rows. D. SALES1 is created with 55,000 rows. E. SALES1 is created with 1 row.

Correct Answer: A,C

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

Question 5

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data? (Choose four.)

A. A table can have only one primary key and one foreign key B. Primary key and foreign key constraints can be defined at both the column and table level C. Only the primary key can be defined at the column and table level D. The foreign key columns and parent table primary key columns must have the same names E. It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted F. A table can have only one primary key but multiple foreign keys G. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Correct Answer: B,D,F,G

Question 6

Which two statements are true about *_TABLES views? (Choose two.)

A. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in DBA_TABLES. B. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in ALL_TABLES. C. You must have ANY TABLE system privileges, or be granted object privileges on the table, to view a table in USER_TABLES. D. ALL_TABLES displays all tables owned by the current user. E. USER_TABLES displays all tables owned by the current user. F. All users can query DBA_TABLES successfully.

Correct Answer: A,F

Question 7

Which two statements are true about transactions in the Oracle Database server? (Choose two.)

A. A user can always see uncommitted updates made by the same user in a different session. B. A session can always see uncommitted updates made by itself. C. If a session has an uncommitted transaction, then a DDL statement issues a COMMIT before starting a new transaction. D. Data Manipulation Language (DML) statements always start a new transaction. E. An uncommitted transaction commits automatically if the user exists SQL*Plus. F. A Data Definition Language (DDL) statement does a COMMIT automatically only for the data dictionary updates caused by the DDL.

Correct Answer: B,D

Question 8

Which three SQL statements would display the value 1890.55 as $1,890.55? (Choose three.)

A. SELECT TO_CHAR (1890.55, '$0G000D00')
FROM DUAL; B. SELECT TO_CHAR (1890.55, '$99,999D99')
FROM DUAL; C. SELECT TO_CHAR (1890.55, '$9,999V99')
FROM DUAL; D. SELECT TO_CHAR (1890.55, '$99G999D00')
FROM DUAL E. SELECT TO_CHAR (1890.55, '$99G999D99')
FROM DUAL

Correct Answer: A,D,E

Question 9

View the Exhibit and examine the structure of the stores table.

You want to display the name of the store along with the address, START_DATE, PROPERTV_PRICE, and the projected property price, which is 115% of the property price. The stores displayed must have START_DATE in the range of 36 months starting from 01- Jan-2000 and above.
Which SQL statement would get the desired output?

A. SELECT name, concat (address||','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36; B. SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, '01-JAN-2000') <=36; C. SELECT name, concat (address| | ','| |city| |', ', country) AS
full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36; D. SELECT name, address||','||city||','||country AS full_address,
start_date,
property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE('01-JAN-2000','DD-MON-RRRR'))
<=36;

Correct Answer: A

Question 10

Examine the commands used to createDEPARTMENT_DETAILS andCOURSE_DETAILS: