SQL QUIZ 2: Functions

1. The output of day('2008-01-12') would be same as that of day('12.01.2008')

True

False

The output of the day() function in both cases would be the same because the date format does not affect the result. The day() function extracts the day component from a given date, regardless of the format it is in. Therefore, whether the date is in the format '2008-01-12' or '12.01.2008', the day() function will extract the day component '12' in both cases.

Explanation

The output of the day() function in both cases would be the same because the date format does not affect the result. The day() function extracts the day component from a given date, regardless of the format it is in. Therefore, whether the date is in the format '2008-01-12' or '12.01.2008', the day() function will extract the day component '12' in both cases.

2. Which one of the following queries will select the Number of Unique Departments from the Department Table below

Department Number	Employee Number
11	248525
11	267712
12	112456
11	234567
13	661231

Select count(*) from Department_Table

Select count(distinct DepartmentNumber) from Department_Table having distinct DepartmentNumber

Select count(distinct DepartmentNumber) from Department_Table

Select distinct departmentNumber from Department_Table

The correct answer is "Select count(distinct DepartmentNumber) from Department_Table". This query will count the number of unique departments in the Department_Table by using the "distinct" keyword to eliminate duplicate values of DepartmentNumber. The "count" function will then count the number of distinct DepartmentNumbers remaining in the table.

Explanation

The correct answer is "Select count(distinct DepartmentNumber) from Department_Table". This query will count the number of unique departments in the Department_Table by using the "distinct" keyword to eliminate duplicate values of DepartmentNumber. The "count" function will then count the number of distinct DepartmentNumbers remaining in the table.

3. The output from the function dec(-99.9999) would be

-99.9999

-99

99.9999

99

The given function dec(-99.9999) would output -99. This is because the function is likely designed to truncate the decimal portion of the input number and return the integer value. In this case, the decimal value -99.9999 is truncated to -99, resulting in the output of -99.

Explanation

The given function dec(-99.9999) would output -99. This is because the function is likely designed to truncate the decimal portion of the input number and return the integer value. In this case, the decimal value -99.9999 is truncated to -99, resulting in the output of -99.

4. What would be output of the below query Select max(min(SLSVLM)) from sales

SLSVLM	SLSDate
1	20121010
12	20121110
5	20120111
2	20120111
-1	20120112
0	20120106

12

0

-1

Error

The query is finding the minimum value of the column SLSVLM and then finding the maximum value from that result. In this case, the minimum value of SLSVLM is -1, and since it is the only value, it is also the maximum value. Therefore, the output of the query would be -1.

Explanation

The query is finding the minimum value of the column SLSVLM and then finding the maximum value from that result. In this case, the minimum value of SLSVLM is -1, and since it is the only value, it is also the maximum value. Therefore, the output of the query would be -1.

5. What would the output of coalesce(NULLIF(4500.00,4500),'1')

4500

1

Null

Error

The coalesce function returns the first non-null value from the given arguments. In this case, the NULLIF function compares the first argument (4500.00) with the second argument (4500), and since they are equal, it returns null. Therefore, the coalesce function will return the next argument, which is '1'.

Explanation

The coalesce function returns the first non-null value from the given arguments. In this case, the NULLIF function compares the first argument (4500.00) with the second argument (4500), and since they are equal, it returns null. Therefore, the coalesce function will return the next argument, which is '1'.

6. How to display Number of days from today for a given date(2012-07-31)

Select date(‘2012-07-31’) – curdate() from sysibm/sysdummy1

Select ‘2012-07-31’ – date(curdate()) from sysibm/sysdummy1

Select ‘2012-07-31’ – curdate() from sysibm/sysdummy1

Select date(‘2012-07-31’) – date(curdate()) from sysibm/sysdummy1

The correct answer is "Select date('2012-07-31') - curdate() from sysibm/sysdummy1". This query subtracts the current date (curdate()) from the given date ('2012-07-31') to calculate the number of days between them.

Explanation

The correct answer is "Select date('2012-07-31') - curdate() from sysibm/sysdummy1". This query subtracts the current date (curdate()) from the given date ('2012-07-31') to calculate the number of days between them.

7. What would be the output of STRIP('555Dollor55Dollor555',B,'5')

55Dollor55Dollor55

Dollor55Dollor55

Dollor55Dollor

DollorDollor

The STRIP function is used to remove characters from a string. In this case, the function is removing the character '5' from the given string '555Dollor55Dollor555'. The function is also replacing the character 'B' with '5'. So, the output would be 'Dollor55Dollor'.

Explanation

The STRIP function is used to remove characters from a string. In this case, the function is removing the character '5' from the given string '555Dollor55Dollor555'. The function is also replacing the character 'B' with '5'. So, the output would be 'Dollor55Dollor'.

8. The output of LTRIM(' Test Yourself') || Strip('$when$ you have problem','B','$') would be

Test Yourselfwhen$you have problem

Test Yourself when you have problem

Test Yourself$when you have problem

Test Yourself$when$you have problem

The LTRIM function removes any leading spaces from the string " Test Yourself". The Strip function removes any occurrences of the character "B" and the character "$" from the string "$when$ you have problem". Therefore, the output would be "Test Yourselfwhen$you have problem".

Explanation

The LTRIM function removes any leading spaces from the string " Test Yourself". The Strip function removes any occurrences of the character "B" and the character "$" from the string "$when$ you have problem". Therefore, the output would be "Test Yourselfwhen$you have problem".

9. The output of the below query would be Select ISNULL(day(SLSDate),SUBSTR(SLSDATE,9,2)) from sales

SLSVLM	SLSDate
1	10/10/2012
12	10/11/2012
5	1/11/2012
2	1/11/2012

Note*: each value in an option is a row

NULLNULL0101

10101111

NULL110101

None of the above

The query is selecting the day component of the SLSDate column using the DAY() function. However, if the SLSDate column is NULL, it will instead select a substring of the SLSDATE column starting from the 9th character and with a length of 2 characters. In the given table, the SLSDate column is not NULL for any of the rows, so the DAY() function will be used. Since the DAY() function cannot be applied to a NULL value, the output will be NULL.

Explanation

The query is selecting the day component of the SLSDate column using the DAY() function. However, if the SLSDate column is NULL, it will instead select a substring of the SLSDATE column starting from the 9th character and with a length of 2 characters. In the given table, the SLSDate column is not NULL for any of the rows, so the DAY() function will be used. Since the DAY() function cannot be applied to a NULL value, the output will be NULL.

10. How to convert date YYYY-MM-DD to MMDDYYYY format

Substr(YYYY-MM-DD,6,2) || Substr(YYYY-MM-DD,9,2) || Substr(YYYY-MM-DD,1,4)

Concat(Substr(YYYY-MM-DD,9,2) , Substr(YYYY-MM-DD,6,2) , Substr(YYYY-MM-DD,1,4))

Substr(YYYY-MM-DD,9,2) || Substr(YYYY-MM-DD,6,2) || Substr(YYYY-MM-DD,1,4)

None of the above

The correct answer is the third option, "Substr(YYYY-MM-DD,9,2) || Substr(YYYY-MM-DD,6,2) || Substr(YYYY-MM-DD,1,4)". This answer suggests using the Substr function to extract the year, month, and day from the given date string, and concatenating them in the desired format of MMDDYYYY. The Substr function is used to extract a portion of a string based on the specified starting position and length. In this case, it is used to extract the year (Substr(YYYY-MM-DD,1,4)), month (Substr(YYYY-MM-DD,6,2)), and day (Substr(YYYY-MM-DD,9,2)) from the date string.

Explanation

The correct answer is the third option, "Substr(YYYY-MM-DD,9,2) || Substr(YYYY-MM-DD,6,2) || Substr(YYYY-MM-DD,1,4)". This answer suggests using the Substr function to extract the year, month, and day from the given date string, and concatenating them in the desired format of MMDDYYYY. The Substr function is used to extract a portion of a string based on the specified starting position and length. In this case, it is used to extract the year (Substr(YYYY-MM-DD,1,4)), month (Substr(YYYY-MM-DD,6,2)), and day (Substr(YYYY-MM-DD,9,2)) from the date string.

SQL Quiz 2: Functions