Oracle 1Z0-071 Quiz 38 Topic 11 Questions 186-190

Question: 1

View the exhibit and examine the data in the PROJ_TASK_DETAILS table.

q1_1Z0-071

The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.

The BASED_ON column indicates dependencies between tasks.

Some tasks do not depend on the completion of other tasks.

You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.

Which query would give the required result? (Choose the best answer.)

ASELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);

BSELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details dON (p.based_on = d.task_id);

CSELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);

DSELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details dON (p.based_on = d.task_id);

Show Answer

Question: 2

View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

q2_1Z0-071

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.

Examine this query which is missing a JOIN operator:

q2_1Z0-071

Which two JOIN operations can be used to obtain the required output? (Choose two.)

AFULL OUTER JOIN

BJOIN

CLEFT OUTER JOIN

DRIGHT OUTER JOIN

Show Answer

Question: 3

View the exhibit and examine the description of the EMPLOYEES table. (Choose two.)

q3_1Z0-071

You executed this SQL statement:

SELECT first_name, department_id, salary

FROM employees

ORDER BY department_id, first_name, salary desc;

Which two statements are true regarding the result? (Choose two.)

AThe values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.

BThe values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.

CThe values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

DThe values in all columns would be returned in descending order.

EThe values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Show Answer

Question: 4

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

q4_1Z0-071

Examine this statement:

q4_1Z0-071

Which two statements are true about the SALES1 table? (Choose two.)

AIt will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.

BIt will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.

CIt will not be created because of the invalid WHERE clause.

DIt is created with no rows.

EIt has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

Show Answer

Question: 5

Examine this SELECT statement and view the Exhibit to see its output:

q5_1Z0-071

SELECT constraints_name, constraints_type, search_condition, r_constraint_name, delete_rule, status,

FROM user_constraints

WHERE table_name = 'ORDERS';

Which two statements are true about the output? (Choose two.)

AThe DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.

BThe R_CONSTRAINT_NAME column contains an alternative name for the constraint.

CIn the second column, 'c' indicates a check constraint.

DThe STATUS column indicates whether the table is currently in use.

Show Answer

Oracle 1Z0-071 Quiz:38 Topic:11 Questions:186-190