SQL: How to Use SQL Expressions

SQL expressions are combinations of one or more values, operators, and SQL functions that evaluate a single value. Expressions can be used in various SQL statements, such as SELECT, WHERE, and HAVING clauses, to filter, transform, or aggregate data. Common examples of expressions include arithmetic operations, logical comparisons, string concatenation, and date/time manipulations. SQL also provides built-in functions that allow you to perform a wide range of operations on data, such as mathematical calculations, string manipulation, and data formatting. Understanding SQL expressions are essential for working with databases, as they allow you to extract and manipulate data in a powerful and flexible way.

SQL Expressions

A SQL expression is a combination of one or more values, operators, and/or functions that evaluates to a single value. Expressions can be used in various contexts, such as in the SELECT, WHERE, and HAVING clauses of a SQL statement. Examples of SQL expressions include mathematical calculations (e.g. “price * 0.9”), string concatenation (e.g. “first_name || ‘ ‘ || last_name”), and date calculations (e.g. “CURRENT_DATE – birthdate”). Expressions can also include column names, which will be replaced with the corresponding values from the rows being selected, filtered, or grouped.

Here’s an example of a SQL SELECT statement that uses expressions:

SELECT first_name, last_name,
salary * 1.1 as "new_salary",
hire_date + INTERVAL '1 year' as "promotion_date"
FROM employees
WHERE salary > 50000 AND
hire_date < CURRENT_DATE - INTERVAL '5 years';

In the above example salary * 1.1 is an arithmetic expression that multiplies the current salary by 1.1, and hire_date + INTERVAL ‘1 year’ is a date expression that adds 1 year to the hire date. These expressions are used in the SELECT clause to create new columns called “new_salary” and “promotion_date” respectively.

In the WHERE clause, salary > 50000 and hire_date < CURRENT_DATE – INTERVAL ‘5 years’ are also expressions that are used to filter the employees returned by the query. The first expression filters out employees whose salary is less than or equal to 50,000, and the second expression filters out employees who have been hired within the last 5 years.

The above query is just an example of how expressions can be used in SQL, there are many other types of expressions and ways to use them depending on the context.

Boolean Expressions

A boolean expression is an expression that evaluates to either true or false. Boolean expressions are often used in the WHERE clause of a SQL statement to filter the rows that are returned. Here’s an example of a SQL SELECT statement that uses a boolean expression in the WHERE clause:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000 AND department = 'IT';

In this example, salary > 50000 is a boolean expression that evaluates to true if the salary is greater than 50,000, and false otherwise. Similarly, department = ‘IT’ is a boolean expression that evaluates to true if the department is ‘IT’ and false otherwise.

The AND operator is used to combine these two boolean expressions, and the query will return only the rows where both expressions evaluate to true. In this example, only the employees who work in the IT department and have a salary greater than 50,000 will be returned.

Note that there are other logical operators that can be used in SQL such as OR and NOT to combine multiple boolean expressions.

SELECT first_name, last_name, salary
FROM employees
WHERE (salary > 50000 OR salary < 30000 ) OR NOT department = 'IT';

This statement will select the first name, last name, and the salary of an employee whose salary is greater than 50000 or less than 30000 and who also not working in the IT department.

In the above example, the query uses the logical operator OR to combine the two boolean expressions salary > 50000 and salary < 30000, so it will return employees with either of these conditions. Then the NOT operator is used to exclude the employees working in the IT department.

It’s important to note that the order of operations of logical operators is important, just like in mathematics, you have to use parenthesis to clarify the order of operations, in the above example (salary > 50000 OR salary < 30000 ) is the first logical operation and NOT department = ‘IT’ is the second.

Boolean expressions are a powerful tool for filtering and manipulating data in SQL, and can be used in conjunction with other expressions and operators to create complex queries that can retrieve and manipulate large amounts of data.

Numerical Expressions

In SQL, a numeric expression is an expression that evaluates to a numeric value, such as an integer or a floating point number. Numeric expressions can be used in various contexts, such as in the SELECT, WHERE, and HAVING clauses of a SQL statement.

Here’s an example of a SQL SELECT statement that uses a numeric expression in the SELECT clause:

SELECT first_name, last_name, salary, salary * 1.1 as "new_salary"
FROM employees
WHERE department = 'IT';

In this example, salary * 1.1 is a numeric expression that multiplies the current salary by 1.1 and creates a new column called “new_salary” in the result set.

Numeric expressions can also be used in the WHERE clause to filter the rows returned by the query:

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000 AND (bonus / salary) > 0.1;

In this example, salary > 50000 is a boolean expression that is used to filter employees whose salary is greater than 50,000, and (bonus/salary) > 0.1 is a numeric expression that calculates the ratio of bonus to salary and compares it with 0.1, it will return only those employees whose bonus is greater than 10% of salary.

These are just a couple of examples of how numeric expressions can be used in SQL, there are many other types of numeric expressions and ways to use them depending on the context.

Date Expressions

A date expression is an expression that evaluates a date or a date-time value. Date expressions can be used in various contexts, such as in the SELECT, WHERE, and HAVING clauses of a SQL statement.

Here’s an example of a SQL SELECT statement that uses a date expression in the SELECT clause:

SELECT first_name, last_name, hire_date, hire_date + INTERVAL '1 year' as "promotion_date" FROM employees;

In this example, hire_date + INTERVAL ‘1 year’ is a date expression that adds 1 year to the hire date and creates a new column called “promotion_date” in the result set.

Date expressions can also be used in the WHERE clause to filter the rows returned by the query:

SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date > CURRENT_DATE - INTERVAL '5 years';

In this example, hire_date > CURRENT_DATE – INTERVAL ‘5 years’ is a date expression that compares the hire date with the date that is 5 years ago from the current date, it will return only those employees who have been hired within the last 5 years.

These are just a couple of examples of how date expressions can be used in SQL, there are many other types of date expressions and ways to use them depending on the context. You can use various date functions such as DATE(), DATE_TRUNC(), EXTRACT(), etc. to extract parts of date and time or to perform calculations on dates.