1Z0-071 Online Practice Questions and Answers

Examine the description of the CUSTOMERS table CUSTNO is the PRIMARY KEY.

You must derermine if any customers'derails have entered more than once using a different costno,by listing duplicate name

Which two methode can you use to get the requlred resuit?

A. RIGHT OUTER JOIN with seif join

B. FULL OUTER JOIN with seif join

C. SUBQUERY

D. seif join

E. LEFT OUTER JOIN with seif join

Which two statements are true regarding indexes? (Choose two.)

A. An update to a table can result in updates to any or all of the table's indexes.

B. An update to a table can result in no updates to any of the table's indexes.

C. A UNIQUE index can be altered to be non-unique.

D. When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped.

E. A table belonging to one user cannot have an index that belongs to a different user.

Which three items does a direction of a relationship contain?

A. an attribute

B. a cardinality

C. label

D. an optionality

E. a unique identifier

F. an entity

Examine the description of the EMPLOYEES table:

Which two queries return rows for employees whose manager works in a different department?

A. Option A

B. Option B

C. Option C

D. Option D

E. Option E

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.

Examine the structure of the EMPLOYEES table.

There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.

You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.

Which query provides the correct output?

A. SELECT e.last_name, m.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.manager_id = m.employee_id) AND e.employee_id = 123;

B. SELECT e.last_name, m.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.employee_id = m.manager_id) WHERE e.employee_id = 123;

C. SELECT e.last_name, e.manager_id FROM employees e RIGHT OUTER JOIN employees m on (e.employee_id = m.employee_id) WHERE e.employee_id = 123;

D. SELECT m.last_name, e.manager_id FROM employees e LEFT OUTER JOIN employees m on (e.manager_id = m.manager_id) WHERE e.employee_id = 123;

Which three statements are true about the ALTER TABLE....DROP COLUMN.... command?

A. A column can be dropped only if it does not contain any data.

B. A column can be dropped only if another column exists in the table.

C. A dropped column can be rolled back.

D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.

E. A parent key column in the table cannot be dropped.

View the exhibit and examine the description of the PRODUCT_INFORMATION table.

Which SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL?

A. SELECT COUNT (DISTINCT list_price) FROM product_information WHERE list_price is NULL

B. SELECT COUNT (NVL(list_price, 0)) FROM product_information WHERE list_price is NULL

C. SELECT COUNT (list_price) FROM product_information WHERE list_price i= NULL

D. SELECT COUNT (list_price) FROM product_information WHERE list_price is NULL

Which three tasks can be performed using SQL functions built into Oracle Database?

A. displaying a date in a nondefault format

B. finding the number of characters in an expression

C. substituting a character string in a text expression with a specified string

D. combining more than two columns or expressions into a single column in the output

Which two statements are true about indexes and their administration in an Oracle database? (Choose two.)

A. A new index can be created or an existing one reused when a primary key constraint is created.

B. An INVINSIBLE index is maintained by DML operations on the underlying table.

C. If a query filters on an indexed column, the index will always be accessed during execution of the query.

D. A DROP INDEX statement always prevents updates to the table during the drop operation.

E. The same table column cannot be part of a unique and non-unique index.