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

1. Create calculated fields to perform simple mathematical calculations in a query.
2. Use aliases to name calculated fields.
3. Use calculated fields to manipulate text.

" ---[SIDEBAR]--- ## Understanding Calculated Fields Often the data that you need is not stored in the database in the precise way that you need it. When this is the case, you can choose to manipulate the data when you retrieve it with a query or you can choose to make the changes you need in a separate application. In this lesson you will learn how to use calculated fields to manupulate data using a query. Here are some reasons why you might choose to manipulate data when you retrieve it in a query. * You need to perform some arithmatic on the value in a database table. For example, the database might store when a customer starts and finishes using a service and you need to know how much time the customer used the service for billing purposes. To determine this, you would need to take the difference between the starting and ending times. * You may need to combine data from multiple columns in a database table. For example, the customer's first and last names may be stored in different columns, but you need their full name combined together. In both of these cases, you could use a calculated field to perform the needed manipulation. A calculated field is a column that is created by the query when it is excuted. Because of this, it appears in the query result, but it is not stored in the database table. This has an important benefit. If any of the underlying data where to change, the calculated field would be refreshed when the query is executed again. ---[SIDEBAR]--- "type":"term", "title":"Calculated Field", "text":"A column in a query result that contains the results of an algorithm used to manupulate the data in stored in the database." ---[SIDEBAR]--- A calculated field is used as part of the SELECT clause of the SQL statement. Any of the columns in the database table outlined in the FROM clause can be used as part of a calculated field, even if the column is not used elsewhere in the SELECT clause. You can have as many calculated fields in a query as you need. ---[SIDEBAR]--- "type":"note", "title":"Fields and Columns", "text":"Often the terms 'column' and 'field' are used interchangably. Technically, column refers to the values that are stored in the database and a field is calculated and appears only in the query result. You will likely find even the most experienced SQL programmers will refer to both as columns or fields." ---[SIDEBAR]--- There are a couple of important things to keep in mind about calculated fields. First, calculated fields are performed on every row of the query result. This means that you will have a separate value for each row of the calculated field based on the other values in that row. You will learn how to create calculations across the rows of a query result in a later lesson. Second, because the SELECT clause is executed before the ORDER BY clause, you can sort the query result by the values in the calculated field. Third, you can manipulate a query result is a separate application, but it is often easier to use a calculated field in the query to do so. ## Performing Mathematical Calculations Calculated fields can be used to perform arithmetic on the values in database tables. You can use the standard mathematical operations below in your calculations. When using multiple operators, the standard order of operations applies (multiplication and division will be completed before addition and subtraction). You can also use parentheses to ensure different ordering of calculations. Calculated fields can use the values from a single column or multiple columns at the same time. Calculated fields can also be used to create expressions that involve multiple mathematical operations. #### Mathematical Operators used in SQL

| Operator | Description | |:--------:|------------------------------| | + | Addition | | - | Subtraction | | * | Multiplication | | / | Division |

Consider an example. Suppose you want to list the artists and calculate the age of each at their death. This can be calculated as the difference between their birth year and their death year (of course this is not necessarily their exact age as an artist may die before their birthday in the year of their death). ---[EVALUATE]--- "type":"plan", "task":"Retrieve the artists and calculate the age of each at their death.", "columns":"full_name, age at death", "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 difference between their death and birth.", "pretext": "To list the artists and the difference between their death and birth, you can use the following SQL statement:", "response": "SELECT full_name, (death - birth) AS age_difference \nFROM artist;", "posttext": "This query selects the full_name column from the artist table and calculates the difference between the death and birth columns using (death - birth). The calculated difference is aliased as age_difference. The result will show the names of artists and the calculated age difference between their death and birth." } ] ---[AI]---

A SQL statement that accomplishes this task is: SELECT full_name, (death - birth) AS age_difference FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name, (death - birth) AS age_difference \nFROM artist;" ---[EVALUATE]--- This query will return the correct results. The full name, death, and birth will be retrieved from the artist table. The values for death and birth will be used to calculate and display the age_difference. All artists will be included in the query results. Notice that the arithmetic in this calculated field involves two columns from the artist table. The calculated field subtracts the birth year from the death year. The expression is intuitive. The value for birth year will be subtracted from the value for the death year. This calculation will be made for each row of the query result. You will also notice some additional syntax after the calculation. This is used to assign an alias, or name, to the calculated field. This will be discussed in the next section. ---[SIDEBAR]--- "type":"tip", "title":"Parentheses and Calculations", "text":"The parentheses are not needed for the calculation in this example. The calculation could be written as death - birth or (death - birth). Parentheses must be used to enforce the order for a calculation if it is different than the standard order of of operations." ---[SIDEBAR]--- **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas|visible owner:atlas-query result:artist-age-difference ---[SQL]--- SELECT full_name, (death - birth) AS age_difference FROM artist; ---[SQL]--- The resulting calculation approximates the artists' age at their death (an artist may die before their birthday in their death year). Notice that the calculation is completed separately for each artist. ## Using Aliases Since a calculated field manipulates the data in one or more columns of the database table, it is unclear what the same of the column should be. Often the DBMS will return the calculated values without a column name. If all you need is to see the calculated values, this is not a big deal. If you want to sort the query results by the calculated values or use them in a different application, you will need to create a name for the calculated column. SQL allows you to create an alias, or a column name, for the calculated field. Recall the additional syntax in the last example after the calculation (death - birth *AS age_difference*). The syntax for creating an alias to a column involves adding the keyword AS followed by the name you want to use for the column. In the above example, the calculated field could also be named "Aged" using similar syntax. ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-aged ---[SQL]--- SELECT full_name, death - birth AS Aged FROM artist ORDER BY Aged; ---[SQL]--- Notice the syntax of the SELECT clause. The calculated field is followed by the keyword AS and the alias, "Aged". Notice that the alias Aged is also used to sort the query result. Because the ORDER BY clause processes after the SELECT clause, the name Aged can be used to define the sort column for the ORDER BY clause. The query results will be sorted by the age of the artists from the youngest to the oldest. ---[SIDEBAR]--- "type":"term", "title":"Alias", "text":"An alias is used to specify the name of a column in a query." ---[SIDEBAR]--- There are a few things to keep in mind about using aliases. 1. Using aliases is optional for calculated fields, but it is a best practice even if you don't need the calculated field to have a column name. 2. Aliases can be a single word or multiple words. If an alias contains spaces, the entire alias must be enclosed in single quotes. Although it is possible, it is best practice to not use spaces in an alias. 3. You can use an alias to rename *any* column name, even a column that exists in the database table. If you create an alias for an existing column, the new name applies to the query. It won't change the name of the column in the underlying database table. ## Concatenating Fields The term "calculated field" implies math. You've already seen how to used a calculated field to perform arithmatic on the numeric values in a query result. However, calculated fields can also be used to manipulate text. Perhaps the most common way to manipulate text involves joining text together into a single value. When you join text, you concatenate it. ---[SIDEBAR]--- "type":"term", "title":"Concatenate", "text":"Joining separate text values together into a single value." ---[SIDEBAR]--- To concatenate text into a single value, you use the double pipe "||" operator. To concatenate the text "Hello" and "World" together, the syntax would be: 'Hello' || ' ' || 'World'. In this example, three text values are concatenated: 1. The word "Hello" 2. a single space 3. The word "World" The result of the concatenation is "Hello World". You can also concatenate text values from columns in a database. Suppose you want to list the artists by concatenating the full name of the artist with their nationality in parentheses. You will use the alias artist_and_nationality for the calculated field. ---[EVALUATE]--- "type":"plan", "task":"Retrieve the artists by concatenating the full name of the artist with their nationality in parentheses.", "columns":"full_name concatenated with nationality", "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 that will work in ^db_name^ that lists the artists concatenated with their nationality in parentheses. Name the column artist_and_nationality.", "pretext": "Sure. Here's the SQL statement for ^db_name^ that lists the artists concatenated with their nationality in parentheses, and names the column as artist_and_nationality:", "response": "SELECT full_name || ' (' || nationality || ')' as artist_and_nationality \nFROM artist;", "posttext": "In ^db_name^, you can use the || operator for concatenation. The query will return a list of artists with their respective nationalities in the format \"Full Name (Nationality)\" under the column name artist_and_nationality." } ] ---[AI]---

The syntax for a query that accomplishes this is: SELECT full_name || ' (' || nationality || ')' as artist_and_nationality FROM artist;

---[EVALUATE]--- "type":"evaluate", "query":"SELECT full_name || ' (' || nationality || ')' as artist_and_nationality \nFROM artist;" ---[EVALUATE]--- This query will return the correct results. The name and nationality of the artists retrieved from the artist table. The values for the columns are concatenated using a calculated field. The syntax for the concatenation may seem a little complicated. In this case, there are four different text values that are concatenated into a single value for each row. These are: **1. full_name:** the value for the full name of the artist from the database table. **2. ' (':** a literal (fixed) text value of a space and an open parenthesis. **3. nationality:** the value for the artists' nationality from the database table. **4. ')':** a literal text value of a close parenthesis. Notice that each text element is concatenated using the double pipe "||" operator. **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:artist-nationality-concat ---[SQL]--- SELECT full_name || ' (' || nationality || ')' as artist_and_nationality FROM artist; ---[SQL]--- The result of the calculated field is a single text value for each artist that contains the artists' full name followed by their nationality in parentheses. The calculated field is named artist_and_nationality using the keyword AS. ---[SIDEBAR]--- "type":"note", "title":"Concatenation in Different DBMS", "text":"Unfortunately, the different DBMS use different methods for concatenating text values. DB2, Oracle, PostgreSQL, and SQLite use the double pipe \"||\" operator. SQL Server uses the plus \"+\" operator. Other DBMS use a function, not an operator. Using functions to manipulate data in a query will be discussed in the next lesson." ---[SIDEBAR]--- ## Summary In this lesson, you learned how to manipulate data in a query using calculated fields. Calculated fields can perform arithmetic or manipulate text. They are helpful, because the data may not be stored in the database in a way that it can be directly used to meet your needs. Using a calculated field alters the data in the query results, but does not affect the underlying data in the database table.