View the Exhibit and examine the data in the products table.
You need to display product names from the products table that belong to the ‘software/other’
category with minimum prices as either S2000 or S4000 and no unit of measure.
You issue the following query: 
Which statement is true regarding the above query?
A.
It executes successfully but returns no result.
B.
It executes successfully and returns the required result.
C.
It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not
valid.
D.
It generates an error because the condition specified for the prod category column is not valid.
Explanation:
About NULLs
http://docs.oracle.com/database/121/SQLRF/sql_elements005.htm#SQLRF30037
I think option C is correct as NULL can not be specified like that.
And I will be A) becase comparing anything to NULL will result as false
You cannot compare NULL like that… equality operators cannot be used to check NULL constraints. An error will be generated as soon as try to compare anything with NULL like that using non-equality operator.
A comparison with null will be evaluated as null. It is not true. The condition returns rows only if expression returns true. So rows with null value would not be returned
It’s no doubt it’s the A.
The statement will execute no issue as there aren’t any condition errors.
That will eliminate you C and D.
IT Can’t NEVER return you the required output cause on the query is trying to get those not NULL on the column which will mean it will be returning the rows with product_unit_of measure.
That eliminates you B.
And to not be just an scratching gamble. The condition can and will return rows if you use where prod_unit_of_measure ‘ ‘. Problem with the example query is that is comparing to nothing:
‘ ‘ = blank_space
” = Nothing
Yes, A is correct. The following statement executes without error but no results.
select id, lname from cust
where lname ”;
I don’t agree with option A. It states that no resuls will be returned although for product id 103 ‘Nos.’ ” is true, so this line would be returned.
I agree.
—
http://www.yandataellan.com
@Leandro, an Oracle “Expert”.
A is correct!
An empty string is treated as a NULL value in Oracle.
Tested:
SQL> SELECT prod_name, prod_category, prod_min_price
2 FROM products
3 WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
4 prod_min_price = 4000) AND prod_unit_of_measure ”;
no rows selected
SQL> SELECT prod_name, prod_category, prod_min_price
2 FROM products
3 WHERE prod_category LIKE ‘%Other%’ AND (prod_min_price = 2000 OR
4 prod_min_price = 4000) AND prod_unit_of_measure IS NOT NULL;
PROD_NAME PROD_CATEGORY PROD_MIN_PRICE
——————– ————————- ————–
DVD-R Disc, 4,7 GB Software/Other 2000
Sorry for my mistake, master. I hope one day I will reach your level of awesomeness of running a broken sql and still getting an OK:
AND prod_unit_of_measure ”;
no rows selected
/*+ did anybody forget an equal there? */
To keep this clear, take a look at this comment:
http://www.aiotestking.com/oracle/which-statement-is-true-regarding-above-query/#comment-750659