W3. SQL Null Functions

Question 1

Q: What do SQL functions like IFNULL(), ISNULL(), COALESCE(), and NVL() do?

Answer 1

A: They allow you to return an alternative value when an expression is NULL.

Question 2

Q: Why would you use functions like IFNULL() in a SQL query?

Answer 2

A: To replace NULL values with an alternative value, avoiding NULL results in calculations.

Question 3

Q: Write a query using IFNULL() in MySQL to replace NULL in UnitsOnOrder with 0.

Answer 3

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;

Question 4

Q: How does the COALESCE() function work, and is it database-specific?

Answer 4

A: COALESCE() returns the first non-NULL value from a list, and it is compatible across multiple databases (MySQL, SQL Server, Oracle).

Question 5

Q: Write a query using COALESCE() to replace NULL in UnitsOnOrder with 0.

Answer 5

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;

Question 6

Q: Which function does SQL Server use to handle NULL values similarly to IFNULL()?

Answer 6

A: ISNULL()

Question 7

Q: Write a query using ISNULL() in SQL Server to replace NULL in UnitsOnOrder with 0.

Answer 7

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;

Question 8

Q: How does the IsNull() function work in MS Access?

Answer 8

A: It returns TRUE (-1) if the expression is NULL, and FALSE (0) otherwise.

Question 9

Q: Write a query using IIF() and IsNull() in MS Access to replace NULL in UnitsOnOrder with 0.

Answer 9

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;

Question 10

Q: What function does Oracle use to handle NULL values, similar to IFNULL()?

Answer 10

A: NVL()

Question 11

Q: Write a query using NVL() in Oracle to replace NULL in UnitsOnOrder with 0.

Answer 11

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;