In general, expressions are commonly used to execute mathematical operations on data. We've seen how collecting accurate data from the database might be challenging at times. We prefer to use SQL Expressions to avoid and overcome these hurdles. Expressions are made up of different operators and operands that add up to a single value or a group of values. In this article, we'll go through expressions in depth.
Let us get started now:
What are SQL Expressions?
An expression is a value that is created by combining one or more values, operators, and SQL functions. An expression presumes the datatype of its components. These SQL expressions are written in query language and are similar to a formula. You can also use them to find a specific set of data in the database. Expressions are used in a number of contexts, such as specifying values to be fetched (in a SELECT clause) or comparing data (in a WHERE clause).
SQL expressions define a value, allowing you to execute arithmetic and logical operations on literals and stored values, as well as generate new values or Boolean results. Any of the following elements can make up an expression:
- Column Name
- Literal (sometimes referred to as a constant)
- USING variable
- Column names, literals, and functions are all connected through operators.
- Parameter marker (question mark (?) placeholder)
To execute the query, you need to first understand the syntax of the SQL to execute SQL expressions. In SQL, an expression is a combination of one or more values, functions, and operators. The following is an example of a SQL expression in its simplest form:
|SELECT columnA, columnB, ..columnN |
WHERE CONDITION | EXPRESSION;
Here, In the first statement, we can write columns on which we need to perform several calculations. In the second statement, we will include the table name following the “From”. And lastly, the condition and the expression are executed along with the “WHERE” clause, which will further result in returning an output matching the expression used.
To understand every expression in detail. We will take an employee database on which we will take several examples to dive deeper into the concepts of SQL expressions. This table consists of several attributes as information of an employee where ID is the key attribute of the employee, while other attributes would be Name, Age, Address and Salary.
Types of SQL Expressions
The following are the different types of SQL expressions:
1. Boolean Expressions
2. Numeric Expressions
3. Date Expressions
To understand SQL expressions in-depth, first, we need to understand every SQL expression in detail along with suitable examples, using the employee database and table.
The SQL Boolean Expression collects information based on the condition given in the SQL query. When the query is executed, it should only return a single value. Boolean expressions are supposed to be allowed in WHERE clauses and in CHECK constraints.
Without directly jumping to an example, let’s first understand the syntax for a boolean expression. With the SELECT statement, we need to take the columns to include into the calculations, and then in the FROM statement, the name of the table is included and further WHERE statement includes the single value matching expression including an operator to perform the calculation.
|SELECT column1, column2, ..columnN |
WHERE SINGLE_VALUE MATCHING EXPRESSION;
It’s time to use our employee database to understand the uses of boolean expressions and how they can be used to find single-value matching expressions.
SQL > SELECT * FROM EMPLOYEE;
| 8 | Ridam Choudary | 29 | Gurgaon | 750000.00 |
| 10 | Praveen Meena | 24 | New Delhi | 202000.00 |
Let’s take an example to get a better understanding of how to use a boolean expression to return a single value matching expression.
To select all employees on a particular property, like here we will print out all employees having salary as 450000, so to do that we will assign the required value to the Salary attribute which will be written after the WHERE clause. Let’s try to run this example and see what will be the output:
To return employees information having salary as 450000
|SQL> SELECT * FROM EMPLOYEE WHERE SALARY = 450000;|
| ID | NAME | AGE | ADDRESS | SALARY |
| 3 | Vinay Rathor | 26 | Chennai |450000.00 |
As we can see, only one row was returned because only one employee was having a salary similar to our requirement which made our selection process easy. So the above query will return a single tuple. Here, the attribute salary was used in the form of a boolean expression to match the single value matching expression. To ensure that you always obtain only one result, SQL Boolean expressions should be used with primary keys. This is required, especially in the case of nested SQL select queries.
A numeric expression can be used in any query to perform any mathematical action.
There are various built-in functions that do aggregate data calculations against a table or a specific table column, such as avg(), sum(), count(), and so on. To conduct mathematical operations on the recorded data, we require numerical expressions.
The syntax for Numeric Expressions is as follows-
|SELECT numeric_expression as OPERATION_NAME|
WHERE CONDITION] ;
To Do a mathematical calculation using sql numeric expression.
The following is a simple illustration of how SQL Numeric Expressions can be used.
An example would be best to explain Numeric Expressions.
SQL> SELECT (25 - 6) AS SUBTRACTION
Here, we performed a simple mathematical expression using two operators, and a basic operation was performed of subtraction, where we totally followed the SELECT statement and the operation name was followed by AS(alias). In this way, we just learned how to write a simple query for such calculations.
Let us see another example:
Use Table data and return the number of employees
Let’s take another example which takes data from our table so that we also get to know how to perform operations using the table.
|SQL> SELECT COUNT(*) AS "No. of employees" FROM EMPLOYEE; |
| No. of employees |
| 10 |
In the above example, we counted the number of employees using COUNT(*) and utilised the alias to name the resulting column.
Date expressions in SQL are used to compare and retrieve data based on a variety of date and time-related queries and conditions. SQL Date Expressions returns the current system date and time values. Here is the general form of the SQL Date.
Let’s look at its Syntax -
|SELECT * FROM tableName WHERE date condition ;|
Let’s take a look at a simple expression that results in returning the current date and time of the system using the SQL query.
|SQL> SELECT CURRENT_TIMESTAMP;|
| Current_Timestamp |
| 2021-12-12 07:30:13 |
We have one more expression called GETDATE() to return the current date and time of our system.
|SQL> SELECT GETDATE();|
| GETDATE |
| 2021-12-10 10:47:16.230 |
Frequently Asked Questions
Q.1) Which SQL expression is allowed to be used in the WHERE clause & in check constraints?
Boolean expression is the only expression that is supposed to be used in where clause and check constraints.
Q.2) Which date expression is merely used in Oracle DB to get the current date and time?
In general, we use current_timestamp and getdate() to get the current date and time, but, In oracle, the preferred expression is “sysdate from dual” to return the latest date and time records.
Q.3) Is there any specific query to find the absolute value of a number using numerical expressions?
The answer is yes, we use ABS(number) to return the absolute value of the number, and with the absolute value, we mean the magnitude part of a number.
To summarize the article, we learned how an SQL query is used to make easy calculations just by using SQL expressions in the queries, while boolean expressions have a major role when talking about the expressions in SQL as it returns the single value matching expression. As it was very interesting to learn about, and the list does not stop here, you can read various interesting topics regarding SQL on our platform.
Also, you can check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.