- SQL Select
- SQL Select Distinct
- SQL Where
- SQL And, Or, Not
- SQL Order By
- SQL Insert Into
- SQL Null Values
- SQL Update
- SQL Delete
- SQL Select Top
- SQL Min and Max
- SQL Count, Avg, Sum
- SQL Like
- SQL Wildcards
- SQL In
- SQL Between
- SQL Aliases
- SQL Joins
- SQL Inner Join
- SQL Left Join
- SQL Right Join
- SQL Full Join
- SQL Self Join
- SQL Union
- SQL Group By
- SQL Having
- SQL Exists
- SQL Any, All
- SQL Select Into
- SQL Insert Into Select
- SQL Case
- SQL Null Functions
- SQL Stored Procedures
- SQL Comments
- SQL Operators

TOP
# SQL-Lesson 7. Data processing functions

Text functions used for text processing (extracting part of the characters from the text, determining the length of the text, converting characters to upper or lower case...);
Numerical functions. They are used to perform mathematical operations on numerical values;
Date and time functions (date and time manipulations are performed, the period between dates is calculated, dates are checked for correctness, etc.);
Statistical functions (for calculation of maximum/minimum values, average values, calculation of quantity and sum...);
System functions (provide various types of service information about DBMS, user, etc.).
## 1. SQL functions for text processing

## 2. SQL functions for processing numbers

## 3. SQL functions for date and time processing

## 4. SQL statistical functions

### 4.1 Examples of using the COUNT() function

### 4.2 Examples of using the SUM() function

### 4.3 Example of using the MIN() function

### 4.4 Example of using the MAX() function

### 4.5 Example of using the function AVG()

As in most programming languages, SQL has functions for processing data. It is worth noting that, unlike SQL statements, functions are not standardized for all types of DBMS, that is, to perform the same operations on data, different DBMSs have their own function names. This means that the query code written in one DBMS may not work in another, and this should be taken into account in the future. This mostly applies to functions for processing text values, converting data types and manipulating dates.

Most DBMSs support a standard set of function types, namely:

The SQL implementation in MS Access has the following functions for text processing:

Function | Description |
---|---|

Left() | Selects the characters in the text from the left |

Right() | Selects the characters in the text on the right |

Mid() | Selects characters from the middle of the text |

UCase() | Converts characters to upper case |

LCase() | Converts characters to lower case |

LTrim() | Removes all blank characters to the left of the text |

RTrim() | Removes all blank characters to the right of the text |

Trim() | Removes all blank characters from both sides of the text |

Let's convert product names to uppercase using the UCase() function:

Run SQL`SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct`

Let's separate the first three characters in the text using the LEFT() function:

Run SQL`SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct`

Number processing functions are designed to perform mathematical operations on numerical data. These functions are designed for algebraic and geometric calculations, so they are used much less often than date and time processing functions. However, numeric functions are the most standardized across all versions of SQL. Let's look at the list of numerical functions:

Function | Description |
---|---|

SQR() | Returns the square root of the specified number |

ABS() | Returns the absolute value of a number |

EXP() | Returns the exponent of the specified number |

SIN() | Returns the sine of the specified angle |

COS() | Returns the cosine of the specified angle |

TAN() | Returns the tangent of the specified angle |

We have listed only a few of the main features, but you can always refer to your DBMS documentation for a full list of supported features with detailed descriptions.

For example, let's write a query to obtain the square root of the numbers in the Amount column using the SQR() function:

Run SQL`SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct`

MySQL uses the SQRT() function to get the square root of a number.

Date and time manipulation functions are among the most important and frequently used SQL functions. In databases, date and time values are stored in a special format, so they cannot be used directly without additional processing. Each DBMS has its own set of functions for processing dates, which, unfortunately, does not allow them to be transferred to other platforms and SQL implementations.

List of some functions for handling date and time in MS Access:

Function | Description |
---|---|

DatePart() | Returns part of a date: year, quarter, month, week, day, hour, minute, second |

Year(), Month() | Returns the year and month respectively |

Hour(), Minute(), Second() | Returns the hour, minutes, and seconds of the specified date |

WeekdayName() | Returns the name of the day of the week |

Let's look at an example of how the DatePart() function works:

```
SELECT Date1, DatePart("m", Date1) AS Month1
FROM Sumproduct
```

The DatePart() function has an additional parameter that allows us to display the required part of the date. In the example, we used the value of the parameter "m", which displays the month number (in the same way, we can display the year - "yyyy", the quarter - "q", the day - "d", the week - "w", the hour - "h" , minutes - "n", seconds - "s", etc.).

Statistical functions help us get ready data without sampling it. SQL queries with these functions are often used to analyze and generate various reports. An example of such selections can be: determining the number of rows in a table, obtaining the sum of values for a certain field, searching for the largest/smallest or average value in the specified column of the table. We also note that statistical functions are supported by all DBMS without any special changes in writing.

List of statistical functions in DBMS Access:

Function | Description |
---|---|

COUNT() | Returns the number of rows in a table or column |

SUM() | Returns the sum of the values in a column |

MIN() | Returns the smallest value in a column |

MAX() | Returns the largest value in a column |

AVG() | Returns the average value in a column |

Returns the number of all rows in the table:

Run SQL`SELECT COUNT(*) AS Count1 FROM Sumproduct`

Returns the number of all non-empty rows in the Product field:

```
SELECT COUNT(Product) AS Count2
FROM Sumproduct
```

We intentionally removed one value in the Product column to show the difference in performance between the two queries.

Run SQL`SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = 'April'`

With this request, we displayed the total amount of goods sold in the month of April.

Run SQL`SELECT SUM(Quantity*Amount) AS Sum2 FROM Sumproduct`

As you can see, in statistical functions we can also perform calculations on several columns using standard mathematical operators.

Run SQL`SELECT MIN(Amount) AS Min1 FROM Sumproduct`

Run SQL`SELECT MAX(Amount) AS Max1 FROM Sumproduct`

Run SQL`SELECT AVG(Amount) AS Avg1 FROM Sumproduct`

- 1. What is VBA, basic concepts.
- 2. Code Debugger
- 3. Working with worksheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties
- 6.1. Data types (Variables)
- 6.2. Data types (Continued)
- 7.1. Conditions
- 7.2. Conditions (Continued)
- 8.1. Loops
- 8.2. Loops (Continued)
- 9. Procedures and functions
- 10. Dialog boxes (Dialog boxes)
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls
- 12.3. Controls (Continued)
- 12.4. Controls (Exercises)
- 13.1. Arrays
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. What is SQL? SQL essentials.
- 2. Select data from a database (SELECT)
- 3. Sorting the result records (ORDER BY)
- 4. Filter records (WHERE)
- 5. Using wildcards (LIKE)
- 6. Computed (calculated) columns
- 7. Aggregate functions (MIN, MAX, AVG etc.)
- 8. Grouping of the resulting set (GROUP BY)
- 9. Subqueries
- 10. Combining records from two tables (INNER JOIN)
- 11. Outer join (LEFT, RIGHT, FULL JOIN)
- 12. Combining the resulting sets (UNION)
- 13. Insert new records (INSERT INTO)
- 14. Create a new table (CREATE TABLE)