Oracle 1Z0-071 Quiz 28 Topic 11 Questions 136-140

Question: 1

View the Exhibit and examine the description of the EMPLOYEES table.

q1_1Z0-071

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.

Which SQL statement would you execute to get the desired output?

ASELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))''Total''FROM EMPLOYEES;

BSELECT first_name, salary, salary*12+salary*commission_pct ''Total''FROM EMPLOYEES;

CSELECT first_name, salary (salary + NVL (commission_pct, 0)*salary)*12 ''Total''FROM EMPLOYEES;

DSELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, ''Total''FROM EMPLOYEES;

Show Answer

Question: 2

View the Exhibit and examine the structure of the PROMOTIONS table.

q2_1Z0-071

Evaluate the following SQL statement:

q2_1Z0-071

Which statement is true regarding the outcome of the above query?

AIt produces an error because subqueries cannot be used with the CASE expression.

BIt shows COST_REMARK for all the promos in the promo category 'TV'.

CIt shows COST_REMARK for all the promos in the table.

DIt produces an error because the subquery gives an error.

Show Answer

Question: 3

Which statement is true regarding the USING clause in table joins? (Choose two.)

AIt can be used to join a maximum of three tables.

BIt can be used to access data from tables through equijoins as well as nonequijoins.

CIt can be used to join tables that have columns with the same name and compatible data types.

DIt can be used to restrict the number of columns used in a NATURAL join.

Show Answer

Question: 4

Examine the structure proposed for the TRANSACTIONS table:

q4_1Z0-071

Which two statements are true regarding the storage of data in the above table structure? (Choose two.)

AThe CUST_CREDIT_VALUE column would allow storage of positive and negative integers.

BThe TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

CThe CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.

DThe TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Show Answer

Question: 5

Examine the structure of the MARKS table:

q5_1Z0-071

Which two statements would execute successfully? (Choose two.)

ASELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1)FROM marksWHERE subject1 > subject2;

BSELECT student_name subject1FROM marksWHERE subject1 > AVG(subject1);

CSELECT SUM(subject1+subject2+subject3)FROM marksWHERE student_name IS NULL;

DSELECT student_name,SUM(subject1)FROM marksWHERE student_name LIKE 'R%';

Show Answer

Oracle 1Z0-071 Quiz:28 Topic:11 Questions:136-140