6. Calculated Fields---SQL

Tip: The format of the client and server
Many of the transformations and formatting work that can be done within a SQL statement can be done directly within the client application. In general, however, these operations are done on the database server more than in the customer
It is much faster to finish in the end.

First, splicing fields

Stitching (CONCATENATE)
Joining values together (attaching one value to another value) constitutes a single value.
The solution is to stitch together two columns. In the SELECT statement in SQL, you can use a special operator to stitch together two columns. Depending on the DBMS you are using, this operator can be added
Number (+) or two vertical bars (| | Said In MySQL and MARIADB, special functions must be used.
Description: Yes + or | |?
Access and SQL Server use the + sign. DB2, Oracle, PostgreSQL, SQLite, and open Office Base use | |

 select  rtrim  (vend_name) +   "  (   

Output -- ---------------------------------------------------------   and Games (England) Furball Inc. (USA) Jouets et ours (France)

Analysis
The RTRIM () function removes all the spaces to the right of the value. Each column is organized by using RTrim ().
Description: Trim function
Most DBMS support RTrim () (as you can see, it removes the space to the right of the string), LTRIM () (Remove the space to the left of the string), and trim () (Remove the string
Left and right sides of the space).

Ii. use of aliases

An alias is an alternate name for a field or value. Aliases are given with a s keyword

 select  rtrim  (vend_name) +   "  (   

Output Vend_title -- ---------------------------------------------------------   and Games (England) Furball Inc. (USA) Jouets et ours (France)

Analysis
The SELECT statement itself is the same as previously used, except that the computed field here follows the text a S vend_title. It instructs SQL to create a name that contains the specified calculation result.
The calculated field for Vend_title. As you can see from the output, the result is the same as the previous one, but now the column name is Vend_title, and any client application can refer to the column by name, just as it
Is the same as an actual table column.
Description: As is usually optional
The AS keyword is optional in many DBMS, but it is best to use it as a best practice.
Hint: Other uses of aliases
Aliases also have other uses. Common uses include renaming an actual table column name when it contains an illegal character, such as a space, and expanding it when the original name is ambiguous or misleading.

Warning: Aliases
An alias can be either a word or a string. In the latter case, the string should be enclosed in quotation marks. Although this practice is legal, it is not recommended to do so. A multi-word
The readability of the name is high, but it can cause problems for the client application. Therefore, the most common use of aliases is to rename the column names of multiple words to a single word name.
　　
Description: Export Columns
Aliases are sometimes called export columns (derived column), and they represent the same thing, no matter what they are called.

Third, perform arithmetic calculation

SELECT prod_id,quantity,item_price,quantity *  as Expanded_price  from OrderItems WHERE = 20008;

output prod_id quantity item_price expanded_price---------- ----------- ------------ -----------------RGAN015 4.9900 24.9500BR035 11.9900 59.9500BNBG01Ten 3.4900 34.9000BNBG02Ten 3.4900 34.9000BNBG03Ten 3.4900 34.9000

Analysis
The Expanded_price column shown in the output is a calculated field, which is calculated as Quantity*item_price. The client app can now use this new computed column, just like using a different column
Sample.
SQL supports the basic arithmetic operators listed in table 7-1. In addition, parentheses can be used to differentiate the precedence order.

Iv. SQL Arithmetic operators

6. Calculated fields---SQL