SQL Advance Functions MCQs

What does the function CAST(Sales AS VARCHAR) do?

• Rounds the Sales to integer

• Converts Sales into a string

• Trims Sales value

• Encrypts Sales

Answer explanation

The function CAST(Sales AS VARCHAR) converts the Sales value from its original data type into a string format (VARCHAR). This allows for text manipulation or display, making 'Converts Sales into a string' the correct choice.

Which of these is an equivalent to CAST(Sales AS INT)?

• CONVERT(INT, Sales)

• TO_INT(Sales)

• CAST(Sales TO INT)

• PARSE(Sales, INT)

Answer explanation

The function CONVERT(INT, Sales) is equivalent to CAST(Sales AS INT) in SQL, as both convert the Sales value to an integer type. The other options are either incorrect syntax or do not exist in SQL.

What is the output of COALESCE(NULL, '', 'Hello')?

• NULL

• '' (empty string)

• 'Hello'

• Error

Answer explanation

COALESCE returns the first non-null value. Here, NULL is ignored, '' (empty string) is the first non-null value, so it is returned. 'Hello' is not considered since '' is already a valid output.

What does NVL(Sales, 0) do?

• Returns NULL if Sales is 0

• Returns 0 if Sales is NULL

• Converts Sales to text

• Deletes NULL rows

Answer explanation

NVL(Sales, 0) returns 0 when Sales is NULL, effectively replacing NULL values with 0. This is useful for calculations to avoid NULL results.

Which function aggregates values into a comma-separated string?

• CONCAT_ALL

• LISTAGG

• GROUP_CONCAT

• STRING_JOIN

Answer explanation

LISTAGG is the correct function that aggregates values into a comma-separated string in SQL. While CONCAT_ALL, GROUP_CONCAT, and STRING_JOIN are similar, LISTAGG is the standard function used for this purpose in many SQL databases.

What does LPAD('SQL', 5, '*') return?

• SQL**

• **SQL

• SQL

• SQL

Answer explanation

LPAD('SQL', 5, '*') pads the string 'SQL' on the left with '*' until the total length is 5. Since 'SQL' is 3 characters long, it adds 2 '*' characters, resulting in '**SQL', which is the correct answer.

Which function removes spaces from the beginning of a string?

• RPAD

• LTRIM

• RTRIM

• TRIMEND

Answer explanation

The LTRIM function is specifically designed to remove spaces from the beginning of a string. In contrast, RPAD and RTRIM deal with padding and trimming spaces from the right side, while TRIMEND is not a standard function.

What is the difference between NVL and ISNULL?

• No difference

• ISNULL can’t be used in Oracle

• NVL works only for numbers

• ISNULL is faster

Answer explanation

The correct choice is that ISNULL can’t be used in Oracle. NVL is an Oracle-specific function for handling null values, while ISNULL is used in SQL Server. Thus, they are not interchangeable across these database systems.

What does EXISTS return?

• TRUE/FALSE

• COUNT

• NULL

• A table

Answer explanation

EXISTS is a SQL operator that checks for the existence of rows in a subquery. It returns TRUE if the subquery returns any rows and FALSE if it does not, making TRUE/FALSE the correct answer.

Which function replaces NULL with a fallback value?

• COALESCE

• NVL

• ISNULL

• All of the above

Answer explanation

All of the functions COALESCE, NVL, and ISNULL are designed to replace NULL values with a specified fallback value, making 'All of the above' the correct choice.