Oracle Database Sql Certified Associate · Free Practice Question Easy
Question 19
View the exhibit and examine the description of the PRODUCT_INFORMATION table.
Identify the SQL statement would retrieve from the table the number of products having LIST_PRICE as NULL.
-
A
- SELECT COUNT (NVL(list_price, 0))
- FROM product_information
- WHERE list_price IS NULL
-
B
- SELECT COUNT(*)
- FROM product_information
- WHERE list_price IS NULL;
-
C
- SELECT COUNT (list_price)
- FROM product_information
- WHERE list_price IS = NULL
-
D
- SELECT COUNT (list_price)
- FROM product_information
- WHERE list_price IS NULL
-
E
- SELECT COUNT (DISTINCT list_price)
- FROM product_information
- WHERE list_price IS NULL
Reveal correct answer
Correct answer: B
Explanation
Correct SQL Statement:
- SELECT COUNT(*)
- FROM product_information
- WHERE list_price IS NULL;
This counts all rows regardless of column content after filtering to only those where
LIST_PRICE IS NULL.
Incorrect Options:
- SELECT COUNT (DISTINCT list_price)
- FROM product_information
- WHERE list_price IS NULL
Incorrect:
COUNT(DISTINCT list_price)ignoresNULLvalues, so it will return0.
- SELECT COUNT (NVL(list_price, 0))
- FROM product_information
- WHERE list_price IS NULL
Incorrect: Although
NVLreplacesNULLwith0,COUNT(...)still works after theWHEREclause filters forNULLs, but this is an unnecessary and confusing way to do it.
- SELECT COUNT (list_price)
- FROM product_information
- WHERE list_price IS = NULL
Syntax error. The correct comparison is
IS NULL, notIS = NULL.
- SELECT COUNT (list_price)
- FROM product_information
- WHERE list_price IS NULL
Incorrect:
COUNT(list_price)ignores NULLs, so this will return0, even though you're filtering forNULLs.
Oracle NVL()
The Oracle NVL() function allows you to replace null with a more meaningful alternative in the results of a query.
The following shows the syntax of the NVL() function:
- NVL(e1, e2)
The NVL() function accepts two arguments. If e1 evaluates to null, then NVL() function returns e2. If e1 evaluates to non-null, the NVL() function returns e1.
Where SELECT COUNT (NVL(list_price, 0)) when the list_price value returns a NULL, the NVL() returns a zero. If list_price returns a NOT NULL, the NVL() function returns the value in the cell.
COUNT inherently ignores null and duplicate values. In this case, COUNT counts the number of values returned by NVL(list_price, 0) filtered by the WHERE clause.
A. This SQL statement uses the NVL function to replace NULL values with 0 before counting the number of products with a NULL LIST_PRICE. However, this is unnecessary as the COUNT function automatically excludes NULL values, so the NVL function is not needed in this context.
B. This SQL statement correctly retrieves the number of products with a NULL value in the LIST_PRICE column from the PRODUCT_INFORMATION table. The COUNT(*) function counts all rows that meet the condition specified in the WHERE clause, which in this case is where LIST_PRICE is NULL.
C. This SQL statement attempts to count the number of products with a NULL LIST_PRICE but contains a syntax error in the WHERE clause. The correct syntax for comparing NULL values is "IS NULL" not "i = NULL".
D. This SQL statement correctly selects the COUNT of the LIST_PRICE column from the PRODUCT_INFORMATION table where the LIST_PRICE is NULL. However, the COUNT function should be applied to the entire row, not a specific column.
E. This SQL statement incorrectly uses the DISTINCT keyword with the COUNT function to count the number of unique values in the LIST_PRICE column where LIST_PRICE is NULL. This is not necessary for this specific query, as the goal is to count the total number of products with a NULL LIST_PRICE, not the distinct values.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
