SQL-Lesson 7. Data processing functions

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:

  • 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

    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 SQLSELECT Product, UCase(Product) AS Product_UCase 
    FROM Sumproduct

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

    Run SQLSELECT Product, LEFT(Product, 3) AS Product_LEFT 
    FROM Sumproduct

    2. SQL functions for processing numbers

    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 SQLSELECT Amount, SQR(Amount) AS Amount_SQR 
    FROM Sumproduct
    MySQL uses the SQRT() function to get the square root of a number.

    3. SQL functions for date and time processing

    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.).

    4. SQL statistical functions

    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

    4.1 Examples of using the COUNT() function

    Returns the number of all rows in the table:

    Run SQLSELECT 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.

    4.2 Examples of using the SUM() function

    Run SQLSELECT 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 SQLSELECT 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.

    4.3 Example of using the MIN() function

    Run SQLSELECT MIN(Amount) AS Min1 
    FROM Sumproduct

    4.4 Example of using the MAX() function

    Run SQLSELECT MAX(Amount) AS Max1 
    FROM Sumproduct

    4.5 Example of using the function AVG()

    Run SQLSELECT AVG(Amount) AS Avg1
    FROM Sumproduct