Topic: 1z0-082 topic 1 question 36

Examine the description of the CUSTOMERS table:

For customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.
Which query should be used?

A.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;
B.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND cust_credit_level !=NULL;
C.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level <> NULL AND due_amount <> NULL;
D.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level != NULL AND due_amount != NULL;
E.
SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL;

Re: 1z0-082 topic 1 question 36

E is correct

  • Ekos
  • New member
  • Offline

Re: 1z0-082 topic 1 question 36

i agree

Re: 1z0-082 topic 1 question 36

I agree

Re: 1z0-082 topic 1 question 36

Column aliases can only be referenced within order by, but nowhere else like where, group by or having. That is why the correct answer is E

Re: 1z0-082 topic 1 question 36

A - FALSE; returns error 'ORA-00904: "DUE_AMMOUNT": invalid identifier'. Conditions in WHERE clause are evaluated before column names between SELECT and FROM, so expression alias DUE_AMMOUNT is not known and error is returned.
B - FALSE; != is not a valid operator for handling with NULL values. No rows are returned when this operator is used. 
C - FALSE;  <> is not a valid operator for handling with NULL values. No rows are returned when this operator is used.
D - FALSE; same asnwer as B
E - TRUE; IS NOT NULL is a valid operator, it is applied on both cust_income_level column and cust_credit_limit column. The other column is a source for computate DUE_AMMOUNT value. When a value of cust_credit_limit is NULL, then also DUE_AMMOUNT is null and therefore the appropriate row is not displayed.

  • zouve
  • New member
  • Offline

Re: 1z0-082 topic 1 question 36

A. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND due_amount IS NOT NULL;

This query selects the cust_first_name and calculates the DUE_AMOUNT as 5% of the cust_credit_limit from the customers table. The WHERE clause filters the rows to include only those where the cust_income_level is not null and the due_amount is not null. This means that only customers whose income level has a value and whose due amount is not null will be displayed in the result.

Re: 1z0-082 topic 1 question 36

E is correct , IS NULL/IS NOT NULL is used to compare nulls (discard B,C,D),and due_amount is a alias columns cannot be used in where ( discard A) , only posible option is E

Re: 1z0-082 topic 1 question 36

E, You can only filter by NULL using 'IS NULL' or 'IS NOT NULL'.

Re: 1z0-082 topic 1 question 36

there is not due_amount in the table. So E is correct

  • ryuah
  • New member
  • Offline

Re: 1z0-082 topic 1 question 36

E is correct

Re: 1z0-082 topic 1 question 36

A is the correct answer.  E does not address the fact that customer's with a due_amount of null should not be displayed

Re: 1z0-082 topic 1 question 36

it is impossible WHERE clause used alias column. A is wrong.