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.
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
Function
Literal (sometimes referred to as a constant)
Parameter
USING variable
Column names, literals, and functions are all connected through operators.
Parameter marker (question mark (?) placeholder)
Syntax
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 FROM table_name 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.
Example Database:
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.
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.
Boolean Expressions
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.
Syntax
SELECT column1, column2, ..columnN FROM tableName 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.
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;
Output:
+----+--------------+-----+---------+----------+ | 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.
Numeric Expressions
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 [FROM tableName WHERE CONDITION] ;
Example 1:
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.
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:
Example 2:
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> SELECTCOUNT(*) AS"No. of employees"FROMEMPLOYEE; +------------------+ | 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
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 WHEREdate condition ;
Example 1:
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.
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.
Key takeaways
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.