SQL Questions and Answers – SQL Null Functions

This set of SQL Multiple Choice Questions & Answers (MCQs) focuses on “SQL Null Functions”.

1. IS NULL( ) function in SQL server is used to _______
a) Return NULL values
b) Replace NULL values
c) Check NULL values of a table
d) Insert NULL values into a table
View Answer

Answer: b
Explanation: The ISNULL ( ) function has different uses in SQL Server and MySQL. In SQL server ISNULL ( ) function is used to replace the NULL values with some other value.

2. What is the syntax of ISNULL ( ) function used in SQL server?
a)

SELECT column_list, ISNULL (column_name, replace_value)
FROM TABLE_NAME;

b)

advertisement
advertisement
SELECT column_list
FROM TABLE_NAME
WHERE ISNULL (column_name, replace_value);

c)

SELECT column_list, ISNULL (replace_value)
FROM TABLE_NAME;

d)

advertisement
SELECT column_list
FROM TABLE_NAME
WHERE ISNULL (replace_value);
View Answer
Answer: a
Explanation: ISNULL ( ) function in SQL server is used to specify how we want to treat NULL values. Syntax of ISNULL ( ) function is:

SELECT column_list, ISNULL (column_name, replace_value)
FROM TABLE_NAME;

3. In MySQL ISNULL ( ) function is used to _____
a) Replace the NULL values
b) Insert NULL values into a table
c) Check NULL values in a table
d) Return NULL values
View Answer

Answer: c
Explanation: In MySQL, ISNULL ( ) function is used to test whether an expression is NULL or not. If the expression is NULL it returns TRUE, else FALSE.
advertisement

4. What is the syntax of ISNULL ( ) function used in MySQL?
a)

SELECT column_list ISNULL(column_name)
FROM TABLE_NAME;

b)

SELECT column_list
FROM TABLE_NAME
ISNULL (column_name);

c)

SELECT column_list ISNULL
FROM TABLE_NAME;

d)

SELECT column_list
FROM TABLE_NAME
WHERE ISNULL (column_name);
View Answer
Answer: d
Explanation: In MySQL, ISNULL ( ) function is used to test whether an expression is NULL or not. Its syntax is as follows:

SELECT column_list
FROM TABLE_NAME
WHERE ISNULL (column_name);

5. IFNULL ( ) function is available in which of the following environment?
a) MySQL, SQL Server
b) SQL server, Oracle
c) MySQL
d) SQL server
View Answer

Answer: c
Explanation: IFNULL ( ) function is available in MySQL, and not in SQL Server or Oracle. This function takes two arguments.

6. If the first argument of IFNULL ( ) function is NULL, the function returns the second argument.
a) True
b) False
View Answer

Answer: a
Explanation: If the first argument of IFNULL ( ) function is not NULL, the function returns the first argument. Otherwise, the second argument is returned.

7. What is the syntax of IFNULL ( ) function?
a)

SELECT column_list
FROM TABLE_NAME
WHERE IFNULL (column_name, value_to_replace);

b)

SELECT column_list, IFNULL(column_name, value_to_replace)
FROM TABLE_NAME;

c)

SELECT column_list IFNULL(value_to_replace)
FROM TABLE_NAME;

d)

SELECT column_list
FROM TABLE_NAME
WHERE IFNULL (value_to_replace);
View Answer
Answer: b
Explanation: IFNULL ( ) function is commonly used to replace NULL value with another value. Syntax of IFNULL ( ) function is as follows:

SELECT column_list, IFNULL (column_name, value_to_replace)
FROM TABLE_NAME;

8. COALESCE ( ) function in SQL returns which of the following?
a) All non-NULL expressions among its arguments
b) All NULL expressions among its arguments
c) First NULL expression among its arguments
d) First non-NULL expression among its arguments
View Answer

Answer: d
Explanation: COALESCE ( ) function in SQL returns the first non-NULL expression or value among its arguments.

9. If all the expressions of a COALESCE ( ) function are evaluated to be NULL, then it returns ______
a) Nothing
b) Error
c) Null
d) Some default value
View Answer

Answer: c
Explanation: The COALESCE ( ) function returns the first expression with non-null value from a list. If all the expressions are evaluated to be null, then the COALESCE ( ) function will return null.

10. What is the syntax of COALESCE ( ) function?
a)

SELECT column_list, COALESCE(expressions_list)
FROM TABLE_NAME;

b)

SELECT column_list
FROM TABLE_NAME
WHERE COALESCE (expressions_list);

c)

SELECT column_list, COALESCE(expressions_list, replace_value)
FROM TABLE_NAME;

d)

SELECT column_list
FROM TABLE_NAME
WHERE COALESCE (expressions_list);
View Answer
Answer: a
Explanation: syntax for COALESCE function in SQL is as follows:

SELECT column_list, COALESCE (expressions_list)
FROM TABLE_NAME;

11. NULLIF ( ) function returns _______ if both the arguments of the function are equal.
a) Zero
b) False
c) Null
d) True
View Answer

Answer: c
Explanation: If the two arguments of NULLIF ( ) function are equal, then it returns NULL value. Otherwise the first argument is returned.

12. How many arguments are there for NULLIF ( ) function?
a) Zero
b) One
c) Two
d) Any number
View Answer

Answer: c
Explanation: The NULLIF ( ) function compares two expressions. If exp1 and exp2 are equal, the function returns NULL. Otherwise, it returns exp1.

13. What is the syntax of NULLIF ( ) function?
a)

SELECT column_list
FROM TABLE_NAME
WHERE NULLIF (exp1, exp2);

b)

SELECT column_list, NULLIF (exp1, exp2)
FROM TABLE_NAME;

c)

SELECT column_list, NULLIF (exp1, exp2,exp3)
FROM TABLE_NAME;

d)

SELECT column_list
FROM TABLE_NAME
WHERE NULLIF (exp1);
View Answer
Answer: b
Explanation: Syntax for using NULLIF ( ) function is:

SELECT column_list, NULLIF (exp1, exp2)
FROM TABLE_NAME;

14. Expressions used in NULLIF ( ) function may or may not be of same data types.
a) True
b) False
View Answer

Answer: b
Explanation: The two expressions that will be compared using NULLIF ( ) function must be of same data type.

15. NVL ( ) function is similar to which of the following function(s)?
a) IFNULL ( ) in MySQL, ISNULL ( ) in SQL server
b) IFNULL ( ) in MySQL, COALESCE ( ) in SQL server
c) COALESCE ( ), ISNULL ( ) in SQL server
d) COALESCE ( ), NULLIF ( ) in SQL server
View Answer

Answer: a
Explanation: NVL ( ) function is used to replace NULL value with another value. It is similar to the IFNULL function in MySQL and the ISNULL function in SQL Server.

Sanfoundry Global Education & Learning Series – SQL.

To practice all areas of SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.