1Z0-071 Free Practice Test

In which normal form is a table, if it has no multi-valued attributes and no partial dependencies?

1. A. second normal form
2. B. first normal form
3. C. third normal form
4. D. fourth normal form

Correct Answer: A
References:
https://blog.udemy.com/database-normal-forms/

Examine the structure of the EMPLOYEES table. (Choose two.)

You must display the maximum and minimum salaries of employees hired 1 year ago. Which two statements would provide the correct output?

1. A. SELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);
2. B. SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE>SELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE>SELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Correct Answer: BD

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

1. A. The outer query stops evaluating the result set of the inner query when the first value is found.
2. B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
3. C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query.
4. D. The outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Correct Answer: AC
References:
http://www.techonthenet.com/oracle/exists.php

Which three statements are true regarding the usage of the WITH clause in complex correlated subqueries: (Choose three.)

1. A. It can be used only with the SELECT clause.
2. B. The WITH clause can hold more than one query.
3. C. If the query block name and the table name are the same, then the table name takes precedence.
4. D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block

Correct Answer: ABD

Which statement is true regarding external tables?

1. A. The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.
2. B. The data and metadata for an external table are stored outside the database.
3. C. The default REJECT LIMIT for external tables is UNLIMITED.
4. D. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.

Correct Answer: A
References:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm