Oracle 1Z0-071 Quiz 68 Topic 9 Questions 336-340

Question: 1

View the Exhibit and examine the data in the PRODUCTS table. (Choose the best answer.)

q1_1Z0-071

You must display product names from the PRODUCTS table that belong to the 'Software/other' category with minimum prices as either $2000 or $4000 and with no unit of measure.

You issue this query:

SQL > SELECT prod_name, prod_category, prod_min_price FROM products

Where prod_category LIKE '%Other%' AND (prod_min_price = 2000 OR prod_min_price = 4000) AND prod_unit_of_measure <> ' ';

Which statement is true?

AIt executes successfully but returns no result.

BIt executes successfully and returns the required result.

CIt generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

DIt generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Show Answer

Question: 2

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

q2_1Z0-071

You must display the maximum and minimum salaries of employees hired 1 year ago.

Which two statements would provide the correct output?

ASELECT MIN(Salary) minsal, MAX(salary) maxsalFROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

BSELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365)GROUP BY maxsal, minsal;

CSELECT minsal, maxsalFROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employeesWHERE hire_date < SYSDATE-365GROUP BY MIN(salary), MAX(salary);

DSELECT MIN(Salary), MAX(salary)FROM (SELECT salary FROM employeesWHERE hire_date < SYSDATE-365);

Show Answer

Question: 3

You issued this command:

CHOOSE THREE

SQL > DROP TABLE employees;

Which three statements are true?

ASequences used in the EMPLOYEES table become invalid.

BIf there is an uncommitted transaction in the session, it is committed.

CAll indexes and constraints defined on the table being dropped are also dropped.

DThe space used by the EMPLOYEES table is always reclaimed immediately.

EThe EMPLOYEES table can be recovered using the ROLLBACK command.

FThe EMPLOYEES table may be moved to the recycle bin.

Show Answer

Question: 4

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)

q4_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?

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: 5

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

q5_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:

SQL > SELECT p.prod_id, count(s.prod_id)

FROM products p ______________ sales s

ON p.prod_id = s.prod_id

GROUP BY p.prod_id;

Which two JOIN operations can be used to obtain the required output?

AFULL OUTER JOIN

BJOIN

CLEFT OUETR JOIN

DRIGHT OUTER JOIN

Show Answer

Oracle 1Z0-071 Quiz:68 Topic:9 Questions:336-340