1Z0-071 Online Practice Questions and Answers

Which two statements are true regarding a SAVEPOINT?

A. Rolling back to a SAVEPOINT can undo a CREATE INDEX statement.

B. Only one SAVEPOINT may be issued in a transaction.

C. A SAVEPOINT does not issue a COMMIT

D. Rolling back to a SAVEPOINT can undo a TRUNCATE statement.

E. Rolling back to a SAVEPOINT can undo a DELETE statement

Examine the ORDER _ITEms table:

Which two queries return rows where QUANTITY is a multiple of ten? (Choose two.)

A. SELECT * FROM order_items WHERE quantity / 10 - TRUNC(quantity)

B. SELECT * FROM order_items WHERE MOD(quantity, 10) - 0;

C. SELECT * FROM order_items WHERE FLOOR(quantity / 10) = TRUNC(quantity / 10);

D. SELECT * FROM order_items WHERE quantity = TRUNC(quantity, -1);

E. SELECT * FROM order_items WHERE quantity = ROUND(quantity, 1);

Examine the description of the BOOKS table: Examine these requirements:

1.

Display book titles for books purchased before January 17, 2007 costing less than 500 or more than 1000.

2.

Sort the titles by date of purchase, starting with the most recently purchased book. Which two queries can be used? (Choose two.)

A. Opion A

B. Opion B

C. Opion C

D. Opion D

E. Opion E

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 ='D_%';

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

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

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

Examine the description of the EMPLOYEES table:

Which two queries will execute successfully? (Choose two.)

A. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id HAVING hire_date > '01-JAN-19';

B. SELECT dept_id, SUM(salary) FROM employees WHERE hire_date > '01-JAN-19' GROUP BY dept_id;

C. SELECT dept_id, MAX(SUM(salary)) FROM employees GROUP BY dept_id;

D. SELECT dept_id, AVG(MAX(salary)) FROM employees GROUP BY dept_id, salary;

E. SELECT AVG(MAX(salary)) FROM employees GROUP BY salary;

You must find the number of employees whose salary is lower than employee 110.

Which statement fails to do this?

A. Option A

B. Option B

C. Option C

D. Option D

You want to return the current date and time from the user session, with a data type of TIMESTAMP WITH TIME ZONE.

Which function will do this?

A. CURRENT DATE

B. CURRENT_ TIMESTAMP

C. SYSDATE

D. LOCALTIMESTAMP

Which two are true about using constraints? (Choose two.)

A. A FOREIGN KEY column in a child table and the referenced PRIMARY KEY column in the parenttable must have the same names.

B. A table can have multiple PRIMARY KEY and multiple FOREIGN KEY constraints.

C. A table can have only one PRIMARY KEY and one FOREIGN KEY constraint.

D. PRIMARY KEY and FOREIGN KEY constraints can be specified at the column and at the table level.

E. A table can have only one PRIMARY KEY but may have multiple FOREIGN KEY constraints.

F. NOT NULL can be specified at the column and at the table level.

You need to allow user ANDREW to:

1.

Modify the TITLE and ADDRESS columns of your CUSTOMERS table.

2.

GRANT tha permission to other users.

Which statement will do this?

A. GRANT UPDATE (title, address) ON customers TO andrew WITH ADMIN OPTION;

B. GRANT UPDATE ON customers.title, customers.address TO andrew WITH GRANT OPTION;

C. GRANT UPDATE ON customers.title, customers.address TO andrew WITH ADMIN OPTION;

D. GRANT UPDATE (title, address) ON customers TO andrew;

E. GRANT UPDATE ON customers. title, customers.address TO andrew;

F. GRANT UPDATE (title, address) ON customers TO andrew WITH GRANT OPTION:

No-user-defined locks are used in your database.

Which three are true about Transaction Control Language (TCL)?

A. COMMIT erases all the transaction's savepoints and releases its locks.

B. COMMIT ends the transaction and makes all its changes permanent.

C. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.

D. ROLLBACK to SAVEPOTNT undoes the transaction's changes made since the named savepoint and then ends the transaction.

E. ROLLBACK without the TO SAVEPOINT clause undoes alt the transaction's changes, releases its locks, and erases all its savepoints.

F. ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

Examine these statements:

Which is true ahout modifyIng the columns in AITER_TEST?

A. c1 can be changed to NUMBER(10) and c2 can be changed to VARCHAN2 (10).

B. c2 can be changed to NUMBER(5) but c1 cannot be changed to VARCHAN2 (5).

C. c2 can be changed to VARCHAR2(10) but c1 cannot be changed to NUMBER (10).

D. c1 can be changed to NUMBER(10) but c2 cannot be changed to VARCHAN2 (10).

E. c1 can be changed to VARCHAR2(5) and c2 can be changed to NUMBER (12,2).

Which three statements are true about Data Manipulation Language (DML)?

A. UPDATE statements can have different subqueries to specify the values for each updated column.

B. INSERT INTO...SELECT...FROM statements automatically commit.

C. DML statements require a primary key be defined on a table.

D. DELETE statements can remove multiple rows based on multiple conditions.

E. INSERT statements can insert NULLs explicitly into a column.

Which statement is true about using functions in WHERE and HAVING?

A. Using aggregate functions in the WHERE clause requires a subquery.

B. Using aggregate functions in the HAVING clause requires a subquery.

C. Using single-row functions in the WHERE clause requires a subquery.

D. Using single-row functions in the HAVING clause requires a subquery.

You and your colleague Andrew have these privileges on the EMPLOYEE_RECORDS table:

1.

SELECT

2.

INSERT

3.

UPDATE

4.

DELETE

You connect to the database instance and perform an update to some of the rows in EMPLOYEE_RECORDS, but do not commit yet.

Andrew connects to the database instance and queries the table.

No other users are accessing the table.

Which two statements are true at this point? (Choose two.)

A. Andrew will be unable to see the changes you have made.

B. Andrew will be unable to perform any INSERTs, UPDATEs, or DELETEs on the table.

C. Andrew will be able to SELECT from the table, but be unable to modify any existing rows.

D. Andrew will be able to see the changes you have made.

E. Andrew will be able to modify any rows in the table that have not been modified by your transaction.

You need to display the date 11-oct-2007 in words as `Eleventh of October, Two Thousand Seven'.

Which SQL statement would give the required result?

A. SELECT TO_CHAR (TO_DATE ('11-oct-2007'), `fmDdthsp "of" Month, Year') FROM DUAL

B. SELECT TO_CHAR (`11-oct-2007', `fmDdspth "of" Month, Year') FROM DUAL

C. SELECT TO_CHAR (TO_DATE (`11-oct-2007'), `fmDdspth of month, year') FROM DUAL

D. SELECT TO_DATE (TO_CHAR ('11-oct-2007'), `fmDdspth "of" Month, Year')) FROM DUAL