1Z0-071 Online Practice Questions and Answers

Which two statements are true about Oracle databases and SQL? (Choose two.)

A. Updates performed by a database user can be rolled back by another user by using the ROLLBACK command.

B. The database guarantees read consistency at select level on user-created tablers.

C. When you execute an UPDATE statement, the database instance locks each updated row.

D. A query can access only tables within the same schema.

E. A user can be the owner of multiple schemas In the same database.

Which two statements are true about single row functions?

A. CONCAT: can be used to combine any number of values

B. MOD: returns the quotient of a division operation

C. CEIL: can be used for positive and negative numbers

D. FLOOR: returns the smallest integer greater than or equal to a specified number

E. TRUNC: can be used with NUMBER and DATE values

SELECT *

FROM bricks,colors;

Which two statements are true?

A. You can add an ON clause with a join condition.

B. You can add a WHERE clause with filtering criteria.

C. It returns the number of rows in BRICKS plus the number of rows in COLORS.

D. You can add a USING clause with a join condition.

E. It returnsthe same rows as SELECT * FROM bricks CROSS JOIN colors.

Which three statements are true about dropping and unused columns in an Oracle database? (Choose three.)

A. An UNUSED column's space is reclaimed automatically when the row containing that column is next queried.

B. A column that is set to UNUSED still counts towards the limit of 1000 columns per table.

C. A DROP COLUMN command can be rolled back.

D. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option.

E. An UNUSED column's space is reclaimed automatically when the block containing that column is next queried.

F. Partition key columns cannot be dropped.

Which two are true about the data dictionary? (Choose two.)

A. The data dictionary is constantly updated to reflect changes to database objects, permissions, and data.

B. All user actions are recorded in the data dictionary.

C. All users have permissions to access all information in the data dictionary by default.

D. The SYS user owns all base tables and user-accessible views in the data dictionary.

E. Base tables in the data dictionary have the prefix DBA_.

Which two are true about the WITH GRANT OPTION clause? (Choose two.)

A. It can be used for system and object privileges.

B. The grantee can grant the object privilege to any user in the database, with or without including this option.

C. The grantee must have the GRANT ANY OBJECT PRIVILEGE system privilege to use this option.

D. It cannot be used to pass on privileges to PUBLIC by the grantee.

E. It can be used when granting privileges to roles.

F. It can be used to pass on privileges to other users by the grantee.

The BOOKS_TRANSACTIONS table exists in your database.

SQL>SELECT * FROM books_transactions ORDER BY 3; What is the outcome on execution?

A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.

B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.

C. Rows are displayed in the order that they are stored in the table only for the first three rows.

D. Rows are displayed sorted in ascending order of the values in the third column in the table.

Examine the structure of the MEMBERS table.

Which query can be used to display the last names and city names only for members from the states MO and MI?

A. SELECT last_name, city FROM members WHERE state ='MO' AND state ='MI';

B. SELECT last_name, city FROM members WHERE state LIKE 'M%';

C. SELECT last_name, city FROM members WHERE state IN ('MO', 'MI');

D. SELECT DISTINCT last_name, city FROM members WHERE state ='MO' OR state ='MI';

Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

A. changing the password for an existing database user

B. connecting to a database instance

C. querying data from tables in different databases

D. starting up a database instance

E. executing operating system (OS) commands in a session

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.