Examine the description of the PRODUCT_INFORMATION table:
A.
SELECT (COUNT(list_price) FROM Product_intormation WHERE list_price=NULL;
B.
SELECT count(nvl( list_price,0)) FROM product_information WHERE list_price is null;
C.
SELECT COUNT(DISTINCT list_price) FROM product_information WHERE list_price is null.
D.
BELECT COUNT(list_price) FROM product_information where list_price is NULL;
The Answer Is:
B
This question includes an explanation.
Explanation:
In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.
A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.
The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.
[Reference: Oracle 12c SQL Documentation on Aggregate Functions and NVL function., ]
1z0-071 PDF/Engine
Printable Format
Value of Money
100% Pass Assurance
Verified Answers
Researched by Industry Experts
Based on Real Exams Scenarios
100% Real Questions
Get 60% Discount on All Products,
Use Coupon: "8w52ceb345"