Hey there! Welcome to Hostman! 🎉

SQL Expressions: Uses and Examples

21.12.2023
Reading time: 6 min
Hostman Team
Technical writer

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:

ID

FirstName

LastName

Post

Wages

1

Alexander

Avery

Manager

105000

2

Jorge

Alvarez

Junior QA specialist

80000

3

Victoria

Dunn

Manager

95000

4

Michael

Lee

Main Developer

140000

5

Natalie

Morgan

Senior QA specialist

125000

6

Adrian

Garcia

Analyst

100000

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:

ID

FirstName

LastName

Post

Wages

1

Alexander

Avery

Manager

105000

3

Victoria

Dunn

Manager

95000

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:

ID

FirstName

LastName

Post

Wages

5

Natalie

Morgan

Senior QA specialist

125000

1

Alexander

Avery

Manager

105000

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.

Function

Argument

Returns

Example

DAYOFWEEK()

Date

The weekday index for a given date.
1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday 

SELECT DAYOFWEEK(CURRENT_TIMESTAMP);

Result: 6

DAYOFMONTH()

Date

The day of the month for a given date (a number from 1 to 31) 

SELECT DAYOFMONTH(CURRENT_TIMESTAMP);

Result: 3

DAYOFYEAR()

Date

The day of the year (a number from 1 to 366) 

SELECT DAYOFYEAR(CURRENT_TIMESTAMP);

Result: 34

LAST_DAY()

Date

The last day of the month 

SELECT LAST_DAY(CURRENT_TIMESTAMP);

Result: 2023-02-28

MONTH()

Date

The month index for a given date (a number from 1 to 12)

SELECT MONTH(CURRENT_TIMESTAMP);

Result: 2

YEAR()

Date

The year

SELECT YEAR(CURRENT_TIMESTAMP);

Result: 2023

HOUR()

Time

The hour part for a given date (a number from 0 to 838)

SELECT HOUR(CURRENT_TIMESTAMP);

Result: 8

MINUTE()

Time

The minutes part of a datetime (a number from 0 to 59)

SELECT MINUTE(CURRENT_TIMESTAMP);

Result: 25

SECOND()

Time

The seconds part of a datetime (a number from 0 to 59)

SELECT SECOND(CURRENT_TIMESTAMP);

Result: 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.

Function

Description

Examples

DATE_ADD(date, time interval)

Designed to add a date and some time interval to it

SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 MONTH);

Result: 2023-03-03 08:25:39

DATE_SUB(date, time interval)

Designed to subtract some time interval from the date

SELECT DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH);

Result: 2023-01-03 08:25:39

DATEDIFF(date1, date2)

Calculates the difference in days between two specified dates

SELECT DATEDIFF(CURRENT_DATE, '2022-02-01');

Result: 367

TO_DAYS(date)

Converts the date to the number of days starting from year zero

SELECT TO_DAYS(CURRENT_TIMESTAMP);  

Result: 738919

TIME_TO_SEC(time)

Calculates the number of seconds in the specified time

SELECT TIME_TO_SEC(CURRENT_TIME);

Result: 30 339

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.