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
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)
SELECT column_list FROM TABLE_NAME WHERE ISNULL (column_name, replace_value);
c)
SELECT column_list, ISNULL (replace_value) FROM TABLE_NAME;
d)
SELECT column_list FROM TABLE_NAME WHERE ISNULL (replace_value);
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
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.
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);
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
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
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);
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
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
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);
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
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
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);
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
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
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.