---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to:

1. Describe the uses and limitations of data manipulation functions.
2. Use functions to manipulate text.
3. Use functions to manipulate dates and times.
4. Use functions to manipulate numbers.

" ---[SIDEBAR]--- ## Understanding Functions Structured query launguage supports the use of built-in functions. A function uses a defined algorithm to manipulate input data into a desired output. Functions are helpful and an important feature of SQL. ---[SIDEBAR]--- "type":"term", "title":"Function", "text":"A feature of SQL that uses a defined algorithm to manipulate input data into a desired output." ---[SIDEBAR]--- Functions can be used to manipulate different types of data including text, dates and time, and numbers. Functions can perform both simple and complex calculations. Functions can also be used to perform calculations on the data in individual rows of a query result or across the rows of a query result. In this lesson, you will learn how to use scalar functions, or functions that manipulate data on individual rows. Aggregate functions, or functions that perform calculations across the rows in a query result, will be discuss in a subsequent lesson. ---[SIDEBAR]--- "type":"term", "title":"Scalar Function", "text":"Functions that manipulate data on individual rows of a query result." ---[SIDEBAR]--- ## Using Functions Functions can be used to perform several different types of operations. These include: * **Text functions.** used to manipulate text. For example, you can use a function to convert all of the text to upper or lower case. * **Date and time functions.** used to work with date and time values. One use of a date or time function would be to extract the year from a from a date. * **Numeric functions.** used to perform math. You would use a numeric function to calculate the squared root of a number, for example. * **Formatting functions.** used to display data in a presentable way. For example, a formatting function would be used to display a monetary value using local formatting customs. * **System functions.** used to return information about the working environment or the DBMS used. A system function would be used to retrieve login information. Functions are versatile. They can be used in the SELECT statement to manipulate data before it is displayed to the user. They can also be included in the WHERE clause to create more advanced filtering criteria. ---[SIDEBAR]--- "type":"caution", "title":"DBMS and Function Support", "text":"Although SQL statements are generally supported the same by most DBMS, functions may not be. You may find that a function does not work if you migrate from using one DBMS to another." ---[SIDEBAR]--- To use a function, you insert the name of the function, then in parentheses list the arguments for the function. An argument is a piece of data used in the function to complete the work of the function. Arguments can be required, or necessary for the algorithm to work, or optional. A function may also use more than one argument. Additionally, a function can be used as the argument for another function. ---[SIDEBAR]--- "type":"term", "title":"Argument", "text":"Data used by a function to complete the work of the function." ---[SIDEBAR]--- An example function call would be UPPER('text'). The name of the function is UPPER. The argument for this function is a string of text (in this case the word 'text'). Notice that the argument is in parentheses. The function will manipulate this text and return the word 'TEXT' all in upper-case letters. ---[SIDEBAR]--- "type":"note", "title":"Function Names and Case Sensitivity", "text":"The SQL language is not case sensitive. The same is true of function calls. You can invoke the UPPER function by referring to it in uppercase letters (UPPER), lowercase letters (upper), or mixed-case letters (UpPeR). This book will use uppercase letters to refer to functions." ---[SIDEBAR]--- This lesson will provide examples of various types of functions. In particular it will discuss functions that manipulate text, dates and times, and numbers. ## Text Manipulation Functions Text manipulation functions are used to transform text-based data in a SQL statement. It is important to note that using functions in an SQL query will not change the underlying data in the database. There are a number of different text manipulation functions. Common functions are presented in the following table. #### Commonly Used Text Manipulation Functions

| Function | Argument(s) | Result | |:--------:|------------------------------|-------------------------| | UPPER | A string of text | Converts the text to all uppercase letters | | LOWER | A string of text | Converts the text to all lowercase letters | | LENGTH | A string of text | Returns the number of characters in the text | | SUBSTR | A string of text, the starting position, and the number of characters to return | Returns the specified number of characters starting at the specified starting position in the text | | INSTR | A string of text and a string of text to find within the first string | Returns the position number within the first string where the second string begins | | LTRIM | A string of text | Removes spaces from left of the string | | RTRIM | A string of text | Removes spaces from right of the string |

Suppose you want to view the full name of the artists in all uppercase letters. Name the calculated field full_name_uppercase. ---[EVALUATE]--- "type":"plan", "task":"Retrieve the full name of the artists in all uppercase letters.", "columns":"full_name in uppercase letters (full_name_uppercase)", "tables":"artist", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to display the full_name of artists in uppercase letters. Name the column full_name_uppercase.", "pretext": "To display the full_name of artists in uppercase letters, you can use the UPPER() function. Here's the SQL statement:", "response": "SELECT UPPER(full_name) as full_name_uppercase \nFROM artist;", "posttext": "In this query, the UPPER() function is applied to the full_name column, which converts the text to uppercase letters. The result is aliased as full_name_uppercase. The query will return the full_name of artists in uppercase letters under the column name full_name_uppercase." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT UPPER(full_name) as full_name_uppercase FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT UPPER(full_name) as full_name_uppercase \nFROM artist;" ---[EVALUATE]--- This query will retrieve the correct results. The full_name column is returned from the artist table and transformed to uppercase using the UPPER function. Notice the syntax of the use of the UPPER function in the SELECT clause. The name of the function is UPPER. The argument for the UPPER function is a string of text. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas|visible owner:atlas-query result:artist-fullname-upper ---[SQL]--- SELECT UPPER(full_name) as full_name_uppercase FROM artist; ---[SQL]--- Notice the results of the function. The values in the full_name column are text. The UPPER function transformed all of the text to uppercase. For the next example, suppose you want to calculate the number of characters in each artists' full name. Name the calculated column 'characters_in_name' (without the quotes). ---[EVALUATE]--- "type":"plan", "task":"Calculate the number of characters in each artists' full name.", "columns":"full_name, the number of characters in the full_name column", "tables":"artist", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to calculate the number of characters in each artists' full_name. Name the calculated field characters_in_name.", "pretext": "To calculate the number of characters in each artist's full_name and name the calculated field as characters_in_name, you can use the LENGTH() function. Here's the SQL statement:", "response": "SELECT full_name, LENGTH(full_name) as characters_in_name \nFROM artist;", "posttext": "In this query, the LENGTH() function is applied to the full_name column, which calculates the number of characters in each artist's full name. The result is aliased as characters_in_name. The query will return the full_name of artists along with the corresponding number of characters in their full names under the column name characters_in_name." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT full_name, LENGTH(full_name) as characters_in_name FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, LENGTH(full_name) as characters_in_name \nFROM artist;" ---[EVALUATE]--- This query will return the correct results. The full name column will be retrieved from the artist table. Additionally, the length of the full_name column is calculated using the LENGTH function. Notice the syntax of the use of the LENGTH function in the SELECT clause. The argument for the LENGTH function is a string of text. The values in the full_name column are text. The LENGTH function will calculate the number of characters in each artists' full name. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-fullname-length ---[SQL]--- SELECT full_name, LENGTH(full_name) as characters_in_name FROM artist; ---[SQL]--- The query displays the values in the full_name column and the number of characters in each name. As expected, the number of characters is different for each artist. In the next example assume you want to extract the first two characters of the artists' names. Name the calculated column 'artist_first_letters' (without the quotes). ---[EVALUATE]--- "type":"plan", "task":"Extract the first two characters of the artists' names.", "columns":"full_name, first two characters in full_name", "tables":"artist", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to extract the first two characters from each artists' full name . Name the calculated column 'artist_first_letters' (without the quotes).", "pretext": "To extract the first two characters from each artist's full name and name the calculated column as artist_first_letters, you can use the following SQL statement:", "response": "SELECT full_name, SUBSTR(full_name, 1, 2) AS artist_first_letters \nFROM artist;", "posttext": "In this query, the SUBSTR() function is used to extract the substring from the beginning of the full_name column up to the second character. The result is aliased as artist_first_letters. The query will return the full_name of artists along with the first two characters of their full names under the column name artist_first_letters." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT full_name, SUBSTR(full_name, 1, 2) AS artist_first_letters FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, SUBSTR(full_name, 1, 2) AS artist_first_letters \nFROM artist;" ---[EVALUATE]--- This query will return the correct results. It retrieves the full_name column from the artist table. It also uses the SUBSTR function to extract the first two characters in the full_name column. The SUBSTR function is used to parse text by extracting a subset of text from another block of text. Notice the syntax of the SUBSTR function. It requires three arguments: the text to parse, where to start extracting text from the text, and how many characters to extract from the text. In this case, the text to parse is the values in the full_name column. You want to extract the first two characters, so you wish to start extracting at the first position. The final argument, the number of characters extract, is 2. The result of this function is the first two characters of each artists' name. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-two-letters-from-fullname ---[SQL]--- SELECT full_name, SUBSTR(full_name, 1, 2) AS artist_first_letters FROM artist; ---[SQL]--- The query results display two columns. The first column is the full name of each artist. The second is a calculated field that produces the first two characters of each artists' full_name. In the last example of text functions you will need to use two functions. Suppose you want to extract the first name for each artists from the full name column. You will need to use the SUBSTR and INSTR functions. Name the calculated column 'artist_first_name' (without the quotes). ---[EVALUATE]--- "type":"plan", "task":"Extract the first name for each artists from the full name column.", "columns":"full_name, first name extracted from full_name", "tables":"artist", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to extract the first name for each artists from the full name column using a combination of the SUBSTR and INSTR functions. Name the calculated column 'artist_first_name' (without the quotes).", "pretext": "To extract the first name for each artist from the full_name column using a combination of the SUBSTR() and INSTR() functions and to name the calculated column as artist_first_name, you can use the following SQL statement:", "response": "SELECT full_name, SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1) AS artist_first_name \nFROM artist;", "posttext": "In this query, the INSTR() function is used to find the position of the first space character in the full_name column. The SUBSTR() function is then used to extract the substring from the beginning of the full_name column up to the position of the first space character (effectively extracting the first name). The result is aliased as artist_first_name. The query will return the full_name of artists along with their extracted first name under the column name artist_first_name, using the specified combination of functions." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT full_name, SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1) AS artist_first_name FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1) AS artist_first_name \nFROM artist;" ---[EVALUATE]--- This SQL statement will return the correct results. The full_name column is retrieved from the artist table. The combination of the SUBSTR and INSTR functions are used to extract the first name from the values in the full_name column. This is a more complicated example. It demonstrates a couple of important elements of using functions. First, it demonstrates that a function result can be used as an argument for a different function. This is called "nesting" a function. Second, it also demonstrates that the result of a function may need to be adjusted to achieve the goal of the query. The SUBSTR function is used to extract the first name of the artists from the full_name column. Recall that the SUBSTR function requires three arguments: the text to parse, the position of the first character to extract, and the number of characters to extract. In this example, the first name begins in the first character of the full_name column. Therefore, the first argument for the SUBSTR function is full_name and the second argument (where to start extracting the text) is 1 or the first position in the full_name column. The number of characters to extract will be different for each artist, since the first name of each artist is different. The first name ends one character before the position of the first space (' ') in the full_name column for each artist. The INSTR function is used to find the location of the space character (' '). The first argument of the INSTR function is the text to search - in this case full_name. The second argument is the character to find which is the space (' '). The result of the INSTR function for each artist will be the location of the space character. This position must be adjusted by subtracting 1 character to reach the last character of the artists' first names. This is the third argument to the SUBSTR function (INSTR(full_name) - 1). **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-firstname-from-fullname ---[SQL]--- SELECT full_name, SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1) AS artist_first_name FROM artist; ---[SQL]--- Examine the results of the query. Notice how the first name is effectively extracted from the values in the full_name columns, even though the first name is a different length for each artist. ## Date and Time Manipulation Functions Date and time functions are among the most useful SQL functions. Unfortunately, the various DBMS support different date and time functions. This is partly due to the fact that the different DBMS use their own formatting for dates and times. Common date and time functions are shown on the following table: #### Commonly Used Date and Time Functions

| Function | Argument(s) | Result | |:--------:|------------------------------|-------------------------| | DATE | None | Returns the current date. | | TIME | None | Returns the current time. | | STRFTIME | optionally a date or time element to extract from a date/time value and a date/time value | Converts a date/value to text and optionally extracts a specified element of the date or time. |

Consider a straightforward example of a query that uses a date and time function. Suppose you want to list the names of museums and the current date. Name the date column 'current_date' (without the quotes). ---[EVALUATE]--- "type":"plan", "task":"List the names of museums and the current date.", "columns":"museum name, current date", "tables":"museum", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to list the museums and today's date. Name the date column 'current_date' (without the quotes).", "pretext": "To list the museums along with today's date and name the date column as 'current_date', you can use the following SQL statement:", "response": "SELECT name, DATE() AS current_date \nFROM museum;", "posttext": "In this query, the SELECT clause selects the name column from the museum table. The DATE() function is used to get today's date, and it's aliased as current_date. The query will return the names of museums along with today's date under the column name current_date." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT name, DATE() AS current_date FROM museum;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT name, DATE() AS current_date \nFROM museum;" ---[EVALUATE]--- The query will retrieve the correct results. This query lists the name of museums. It also includes the calculated field current_date which used the DATE() function to return the current date. The DATE function does not have an argument, since it always returns the current date. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:current-date ---[SQL]--- SELECT name, DATE() AS current_date FROM museum; ---[SQL]--- Notice that the value for current_date is the same for each row of the query result. The NOW function works in a similar manner and returns the current time. The next example is trickier. Suppose you want to list the names of museums and the year of the current date. Name the date column 'current_year' (without the quotes). You will use the STRFTIME function for this query. ---[EVALUATE]--- "type":"plan", "task":"List the names of museums and the year of the current date.", "columns":"museum name, year of the current date", "tables":"museum", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to list the museums and the year part of today's date. Use the STRFTIME function. Name the date column 'current_year' (without the quotes).", "pretext": "To list the museums along with the year part of today's date using the STRFTIME function and name the date column as 'current_year', you can use the following SQL statement:", "response": "SELECT name, STRFTIME('%Y', DATE()) AS current_year \nFROM museum;", "posttext": "In this query, the SELECT clause selects the name column from the museum table. The STRFTIME('%Y', 'now') function is used to extract the year part from today's date, and it's aliased as current_year. The query will return the names of museums along with the year part of today's date under the column name current_year." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT name, STRFTIME('%Y', DATE()) AS current_year FROM museum;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT name, STRFTIME('%Y', DATE()) AS current_year \nFROM museum;" ---[EVALUATE]--- This query returns the correct results. This query lists the name of museums. It also includes the calculated field current_year. The calculated field uses the STRFTIME() function. This function has two arguments. The first '%Y' instructs the function to return the year part of the date. The second argument is the result of the DATE() function which will return the current date. The calculated field will calculate the current year (the year part of the current date). **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:current-year ---[SQL]--- SELECT name, STRFTIME('%Y', DATE()) AS current_year FROM museum; ---[SQL]--- The current_year calculation is the same for each row of the query result, because the year is calculated from the current date (the result of the DATE function). ## Numeric Manipulation Functions Numeric function are user to perform calculations on numbers that are more complicated than simple arithmetic. Unlike the text and date and time manipulation functions, these functions tend to be supported by most DBMS. The table below contains some example numeric functions. #### Commonly Used Numeric Manipulation Functions

| Function | Argument(s) | Result |:--------:|------------------------------|---------------| | ABS() | A number | Returns the distance of the number from zero (absolute value) | | SQRT() | A number | Returns the squared root of the number | | PI() | None | Returns the value of PI |

Most numeric functions require numbers as arguments since they perform mathematical calculations. Some, like the PI function, return a known constant. Functions like PI are often used a part of a larger calculation. There are dozens of numeric functions implemented in most DBMS. Suppose you want to calculate the approximate age of each artist at their death. This can be calculated as the difference between the birth and death value in the artist table. If you subtract the death year from the birth year, you will end up with a negative number. Use the ABS function to take the absolute value of this calculation. Name the calculated column 'age_at_death' (without the quotes). ---[EVALUATE]--- "type":"plan", "task":"Calculate the approximate age of each artist at their death.", "columns":"full_name, difference between death and birth", "tables":"artist", "rows":"all", "order":"none specified" ---[EVALUATE]---

You can use a chat with ^ai_name^ to create the SELECT statement that will do this: ---[AI]--- [ { "prompt": "Write an SQL statement to list the artists and the absolute value of their birth minus their death. Name the date column 'age_at_death' (without the quotes).", "pretext": "To list the artists along with the absolute value of their birth year minus their death year, and name the calculated column as 'age_at_death', you can use the following SQL statement:", "response": "SELECT full_name, ABS(birth - death) AS age_at_death \nFROM artist;", "posttext": "In this query, the SELECT clause selects the full_name column from the artist table. The ABS(birth - death) calculates the absolute value of the difference between the birth and death years, and it's aliased as age_at_death. The query will return the names of artists along with the calculated absolute difference between their birth and death years under the column name age_at_death." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT full_name, ABS(birth - death) AS age_at_death FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, ABS(birth - death) AS age_at_death \nFROM artist;" ---[EVALUATE]--- This SQL statement retrieves the correct results. It returns the full_name column from the artist table and also calculates the approximate age of the artists at their death. This example demonstrates the use of the ABS function. Admittedly, you would not subtract the larger number death from birth to calculate the approximate age at birth. Notice that birth - death will produce a negative number. The ABS function calculates and returns the absolute value of each calculation. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-age-at-death-abs ---[SQL]--- SELECT full_name, ABS(birth - death) AS age_at_death FROM artist; ---[SQL]--- ## Summary Functions are useful and can be used to perform calculations for each row of a query result and across the rows of a query result. This lesson described how to perform scalar calculations, or calculations on each row of the query results. It discussed how to use functions to manipulate text, dates and times, and numeric values. Unfortunately, not all DBMS implement these calculations the same way. Consult the documentation of your DBMS to learn which functions are supported.