Oracle 1Z0-071 Quiz 36 Topic 2 Questions 176-180

Question: 1

Examine the structure of the EMPLOYEES table.

q1_1Z0-071

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

Which query would retrieve the required result?

ASELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;

BSELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);

CSELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;

D(SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE-180;

Show Answer

Question: 2

Examine the structure of the PROMOTIONS table:

q2_1Z0-071

Management requires a report of unique promotion costs in each promotion category.

Which query would satisfy this requirement?

ASELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;

BSELECT promo_category, DISTINCT promo_cost FROM promotions;

CSELECT DISTINCT promo_cost, promo_category FROM promotions;

DSELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Show Answer

Question: 3

You must create a table for a banking application.

One of the columns in the table has these requirements:

A column to store the duration of a short team loan

The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.

The maximum loan period is 30 days.

Interest must be calculated based on the number of days for which the loan remains unpaid.

Which data type would you use?

ADATE

BNUMBER

CTIMESTAMP

DINTERVAL DAY TO SECOND

EINTERVAL YEAR TO MONTH

Show Answer

Question: 4

Examine the structure of the CUSTOMERS table: (Choose two.)

q4_1Z0-071

CUSTNO is the PRIMARY KEY.

You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.

Which two methods can you use to get the required result?

ASubquery

BSelf-join

CFull outer-join with self-join

DLeft outer-join with self-join

ERight outer-join with self-join

Show Answer

Question: 5

Which two are the minimal requirements for a self-join? (Choose two.)

AOnly equijoin conditions may be used in the query.

BOuter joins must not be used in the query.

CThere must be a condition on which the self-join is performed.

DNo other condition except the self-join may be specified.

EThe table used for the self-join must have two different alias names in the query.

Show Answer

Oracle 1Z0-071 Quiz:36 Topic:2 Questions:176-180