SQL Expressions: Uses and Examples

SQL expression is a set of values, operators, column names, functions, or other components available in SQL. They are formulas designed to retrieve data. 

In this article, we'll look at the three main types of expressions in SQL: boolean, numeric, and date and time expressions. 

SQL boolean expressions

Boolean expressions in SQL are used to retrieve necessary data based on a condition, which is often specified in a WHERE statement. Queries with this type of expression may begin with statements such as SELECT, UPDATE, DELETE, or INSERT. 

GROUP BY, HAVING, and ORDER BY operators can also be used with WHERE. They are intended for filtering, sorting, and restricting the strings received in the query. 

The syntax for boolean expressions looks like this:

SELECT column_name1, column_name2, column_name3, ..., column_nameN
FROM table_name
WHERE boolean_expression;

As an example, let's take the "Staff" table containing data on company employees. It will consist of the following columns:

• ID, which is a unique identifier of the employee.

• FirstName

• LastName

• Post, which is the employee's position.

• Wages, which is the employee's salary.

Here's our table:

Now, let's make a query to this table by applying a boolean expression:

SELECT * FROM Staff WHERE Post = Manager;

This query should provide the details of the company employees working as managers.

The result of the query will be as follows:

As you can see from the table, the result includes only the data that satisfy the condition in WHERE.

You can use AND, OR, XOR, or NOT operators in the WHERE operator to organize a more complex expression.

For example, let's query only the data of employees who receive a salary greater than 100 000 but less than 130 000. Let's also sort the obtained data by the "Wages" column in descending order. To do this, we will use the following boolean expression in the SQL query: 

SELECT * FROM Staff 
WHERE Wages > 100000 and Wages < 130000
ORDER BY Wages DESC;

The result is:

Numeric SQL expressions

Numeric expressions in SQL are used to perform mathematical operations. They are formed using aggregate functions such as MIN, MAX, COUNT, COUNT_BIG, AVG, SUM, and others. You can also use addition, subtraction, division, or multiplication operators.

The syntax is as follows:

SELECT numeric_expression
FROM table_name
[WHERE condition];

To provide an example, let's take our "Staff" table again and compose an expression in an SQL query:

SELECT count(*) FROM Staff;

This query will return the number of rows in the "Staff" table. It will be equal to 6. 

Now let's complicate the mathematical expression and return the average salary of employees who hold the position of manager in the company. To do this, let's make the following query:

SELECT avg(Wages) FROM Staff WHERE Post = Manager;

The result is 100 000.

SQL expressions of date and time

Now, we will look at expressions for retrieving and working with the current time and date. 

Example:

SELECT CURRENT_TIMESTAMP;

This query will return the current date and time. CURRENT_TIMESTAMP is the SQL expression for the date and time, but it is also a function.

To return only the current time, you would use CURRENT_TIME, and to get only the current date, you would use CURRENT_DATE.

SQL also has a set of functions designed to split the date and time into separate parts. The table below shows their names, examples of use, and query results. 

The SELECT CURRENT_TIMESTAMP result for the examples is 2023-02-03 08:25:39.

There are also functions for processing the obtained time data. You can find them in the table below. In these examples, we use the same CURRENT_TIMESTAMP value.

What to remember

• SQL expression is a set of values, operators, column names, functions or other components available in SQL.

• There are three main types of SQL expressions: boolean, numeric, and date and time expressions.

• Boolean expressions in SQL are used to retrieve the data based on a condition, that is often specified in a WHERE statement. Syntax:

SELECT column_name1, column_name2, column_name3, ..., column_nameN
FROM table_name
WHERE boolean_expression;

• GROUP BY, HAVING and ORDER BY operators can also be used with WHERE. They are intended for filtering, sorting, and restricting the rows received in the query.

• Numeric expressions in SQL are used to perform mathematical operations. Syntax:

SELECT numeric_expression
FROM table_name
[WHERE condition];

Conclusion

We have looked at three main types of SQL expressions: boolean, numeric, and those related to date and time. For each type, we have provided examples. Knowing how to use SQL expressions will help to correctly compose queries and get the necessary data for further processing.