Mathematical functions in Azure Cosmos DB SQL API are a powerful tool for manipulating numerical data points. They allow us to perform a wide range of mathematical procedures such as rounding numbers (ROUND function), calculating absolute values (ABS function), identifying the smallest numbers (MIN function), or finding the largest numbers (MAX function).
Here’s an implementation example of a mathematical function:
SELECT VALUE ROUND(c.someNumber)
FROM c
In the above query, the ROUND mathematical function is used to round off the values of a property named ‘someNumber’.
Implementing Queries using String Functions
The Cosmos DB SQL API supports a multitude of string functions. Using these functions, you can create, aggregate, modify, and perform operations on strings. Few popular operations include CONCAT for combining two strings, LEFT for extracting a certain number of characters from the left of the string, LEN for calculating the length of the string, and REPLACE for replacing certain characters in the string.
Here’s an example that uses the LEN string function:
SELECT c.customerName, LEN(c.customerName) as NameLength
FROM c
In the above example, the LEN string function is calculating the length of ‘customerName’ for each record in the data.
Implementing Queries using Date Functions
Azure Cosmos DB also provides several date functions that can manipulate date and time values to a substantial degree. Developer can use these functions to extract elements from a date (YEAR, MONTH, DAY), get the current date and time (GETCURRENTDATETIME function), and calculate the difference between dates (DATEDIFF function).
Here is an example:
SELECT VALUE c.createdAt, DAY(c.createdAt) as DayCreated
FROM c
In the preceding query, the DAY function is used to extract the day from the ‘createdAt’ date for every record.
Advantages of Using Built-in Functions
Progressive utilisation of these built-in mathematical, string, and date functions can result in more compact and legible code, efficient queries, and reduced server-side computational overhead. They provide the power to perform these operations directly on the database side, therefore, reducing network traffic and boosting application performance.
Wrapping Up
Incorporating Mathematical, String, and Date Functions into your queries can help refine and streamline data handling while also improving the performance of your Azure Cosmos DB-based applications. Understanding and mastering these functions are essential for clearing the “DP-420 Designing and Implementing Native Applications Using Microsoft Azure Cosmos DB” examination, and contribute greatly to your repertoire as an Azure Developer.
Practice Test
True or False: In Azure Cosmos DB, you can execute mathematical functions in your queries.
- True
- False
Answer: True.
Explanation: Azure Cosmos DB SQL API supports a number of mathematical functions such as ABS, ACOS, ASIN, ATAN, CEILING, COS, etc.
Which of the following string functions are supported by Azure Cosmos DB SQL API?
- A. CONCAT
- B. LENGTH
- C. SUBSTRING
- D. LENGHT
- E. ALL OF THE ABOVE
Answer: E. ALL OF THE ABOVE
Explanation: Azure Cosmos DB SQL API supports CONCAT, LENGTH, SUBSTRING functions in query specification.
True or False: In Azure Cosmos DB, date functions use the ISO 8601 Date and Time format only.
- True
- False
Answer: True.
Explanation: In Azure Cosmos DB, date functions use the ISO 8601 Date and Time format. The year, month, day, etc. are all represented in this format.
True or False: Azure Cosmos DB’s SQL API supports case-insensitive string comparisons.
- True
- False
Answer: True.
Explanation: Azure Cosmos DB’s SQL API supports case-insensitive string comparisons using string functions such as STRINGEQUALS.
Which of the following mathematical functions are not supported by Azure Cosmos DB?
- A. LOG10
- B. POWER
- C. SQRT
- D. LOG100
Answer: D. LOG100
Explanation: LOG100 is not a supported mathematical function in Azure Cosmos DB.
True or False: Azure Cosmos DB does not have any built-in date functions.
- True
- False
Answer: False.
Explanation: Azure Cosmos DB provides built-in date functions such as GETCURRENTDATETIME and DATETIME_UTILITIES.
Which of the following mathematical functions are not available in Azure Cosmos DB?
- A. ROUND
- B. FLOOR
- C. CEILING
- D. RECTANGLE
Answer: D. RECTANGLE
Explanation: Azure Cosmos DB supports ROUND, FLOOR and CEILING mathematical functions but RECTANGLE function does not exist.
In Azure Cosmos DB, which function would deliver the current date and time?
- A. GETCURRENTDATETIME
- B. GETDATE
- C. DATE NOW
- D. TODAY
Answer: A. GETCURRENTDATETIME
Explanation: GETCURRENTDATETIME() is a system function that returns the current coordinated universal time (UTC) in the Azure Cosmos DB SQL API.
Which of the following is not a string function in Azure Cosmos DB?
- A. UPPER
- B. LOWER
- C. MID
- D. REVERSE
Answer: D. REVERSE
Explanation: REVERSE is not a string function in Azure Cosmos DB. UPPER, LOWER, MID are the supported string functions in Azure Cosmos DB SQL API.
True or False: To extract the year from a date in Azure Cosmos DB, you can use the YEAR() function.
- True
- False
Answer: False.
Explanation: There’s no YEAR() function in Azure Cosmos DB. You must employ DATEPART or to extract pieces of a date from a string formatted as an ISO 8601 date.
Interview Questions
How can you use mathematical functions in Azure Cosmos DB’s SQL query language?
Azure Cosmos DB’s SQL query language supports many built-in mathematical functions such as ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, and much more where you can directly apply these functions to your queries.
Which function can be used to convert strings to lowercase in Azure Cosmos DB?
The LOWER() function can be used to convert all uppercase characters in a string to lowercase in Azure Cosmos DB.
How can you extract the date from a timestamp in Azure Cosmos DB?
You can extract the date from a timestamp using the DAY() function in Azure Cosmos DB.
What is the purpose of the CONCAT function in Azure Cosmos DB?
The CONCAT function in Azure Cosmos DB is used to concatenate two or more strings into one string.
How do you calculate the square root of a number in Azure Cosmos DB?
The SQRT function is used to calculate the square root of a number in Azure Cosmos DB.
How can you find the current timestamp in Azure Cosmos DB?
The CURRENT_TIMESTAMP() function is used to return the current timestamp in Azure Cosmos DB.
What is the syntax to use the UPPER() string function in Azure Cosmos DB?
The syntax to use the UPPER() function is UPPER(string_expression), where string_expression is the string you want to convert to upper case.
Can you get the first day of the next month from a specific date in Azure Cosmos DB?
Yes, you can use the EOMONTH() function with an addition of 1 day to get the first day of the next month from a specific date in Azure Cosmos DB.
What is the purpose of the LEFT() function in Azure Cosmos DB’s query language?
The LEFT() function in Azure Cosmos DB’s query language returns the left part of a string with the specified number of characters.
How can you round a decimal number to its nearest integer in Azure Cosmos DB?
The ROUND() function can be used to round a decimal number to its nearest integer in Azure Cosmos DB.
What is the syntax for using the DATEADD function in Azure Cosmos DB?
The syntax for using the DATEADD function is DATEADD (datepart , number , date), where datepart is the part of date to which an integer is added, number is the amount to add, and date is the date to which the number is added.
How can you calculate the logarithm of a number in Azure Cosmos DB?
The LOG() function is used to calculate the logarithm of a number in Azure Cosmos DB.
Can TRIM() function be used to remove leading and trailing spaces in Azure Cosmos DB?
Yes, the TRIM() function in Azure Cosmos DB is used to remove all leading and trailing spaces from a string.
How do you find the largest integer less than or equal to a specified decimal number in Azure Cosmos DB?
The FLOOR() function is used to find the largest integer less than or equal to a specified decimal number in Azure Cosmos DB.
What string function is used to return the length of a string in Azure Cosmos DB?
The LEN() function is used to return the length of a string in Azure Cosmos DB.
extutorials.com