1Z0-071 Online Practice Questions and Answers

Which two statements cause changes to the data dictionary? (Choose two.)

A. DELETE FROM scott.emp;

B. ALTER SESSION SET NLS_DATE_FORMAT = `DD/MM/YYYY';

C. GRANT UPDATE ON scott.emp TO fin manager;

D. SELECT * FROM user_tab_prive;

E. TRUNCATE TABLE emp;

You create a table named 123. Which statement runs successfully?

A. SELECT * FROM TABLE (123) ;

B. SELECT * FROM andapos;123andapos;;

C. SELECT * FROM "123";

D. SELECT * FROM Vandapos;123Vandapos;;

Which three statements are true about an ORDER BY clause? (Choose three.)

A. By default an ORDER BY clause sorts rows in descending order

B. An ORDER BY clause will always precede a HAVING clause if both are used in the same top-level query.

C. An ORDER BY clause always sorts NULL values last.

D. By default an ORDER BY clause sorts rows in ascending order.

E. An ORDER BY clause can perform a binary sort.

F. An ORDER BY clause can perform a linguistic sort.

Which two statements are true about INTERVAL data types?

A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

B. INTERVAL DAY TO SECOND columns support fractions of seconds.

C. INTERVAL YEAR TO MONTH columns support yearly intervals.

D. The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

E. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

F. The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

Which statement executes successfully?

A. SELECT TO_NUMBER(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

B. SELECT TO_CHAR(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

C. SELECT TO_NUMBER(TO_DATE(INTERVAL `800' SECOND)) FROM DUAL;

D. SELECT TO_DATE(TO_NUMBER(INTERVAL `800' SECOND)) FROM DUAL;

E. SELECT TO_DATE(INTERVAL `800' SECOND, `HH24:MM') FROM DUAL;

Evaluate the following CREATE TABLE command:

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 clause is not permitted in the CREATE TABLE command.

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

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

Customers with no credit limit should have "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;

You must display details of all users whose username contains the string 'ch_'. (Choose the best answer.)

Which query generates the required output?

A. SELECT * FROM users Where user_name LIKE '%ch_';

B. SELECT * FROM users Where user_name LIKE '%ch_%'ESCAPE'%';

C. SELECT * FROM users Where user_name LIKE 'ch\_%' ESCAPE '_';

D. SELECT * FROM users Where user_name LIKE '%ch\_%' ESCAPE '\';

You currently have an active transaction in your session and have been granted SELECT access to

V$TRANSACTION.

Executing:

SELECT xid, status FROM v$transaction;

in your session returns:

In which three situations will re-executing this query still return a row but with a different XID, indicating a new transaction has started? (Choose three.)

A. after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statement

B. after successfully executing a TRUNCATE statement followed by a DML statement

C. after successfully executing a DML statement following a failed DML statement

D. after successfully executing a CREATE TABLE AS SELECT statement followed by a SELECT FOR UPDATE statement

E. after successfully executing a COMMIT or ROLLBACK followed by a DML statement

F. after successfully executing a COMMIT or ROLLBACK followed by a SELECT statement

Table HR.EMPLOYEES contains a row where the EMPLOYEE_ID is 109.

User ALICE has no privileges to access HR.EMPLOYEES.

User ALICE starts a session.

User HR starts a session and successfully executes these statements:

GRANT DELETE ON employees TO alice;

UPDATE employees SET salary = 24000 WHERE employee_id = 109;

In her existing session ALICE then executes:

DELETE FROM hr.employees WHERE employee_id = 109;

What is the result?

A. The DELETE command will wait for HR's transaction to end then return an error.

B. The DELETE command will immediately delete the row.

C. The DELETE command will wait for HR's transaction to end then delete the row.

D. The DELETE command will immediately return an error.