SQL Expressions – 5 Value Expressions In SQL

1. Expressions in SQL

In this SQL tutorial, we will discuss SQL Expressions. Moreover, we will learn about 5 value expressions in SQL. First, we will see what exactly SQL Expressions are. Along with this, we will discuss 5 types of SQL value expressions – string, numeric, datetime, interval, conditional value SQL expressions. Also, we will see the example of SQL Expressions.
So, let us start learning Expressions in SQL.

SQL Expressions

SQL Expressions – 5 Value Expressions In SQL

2. What are SQL Expressions?

SQL expressions could also be easy or complicated. The expression will contain literal values, column names, parameters, host variables, subqueries, logical connectives, and arithmetic operators. Notwithstanding its complexity, an expression in SQL should reduce to one value.
Do you know about SQL RDBMS Concept
For this reason, SQL expressions are commonly referred to as price expressions. Combining multiple value expressions into one expression is feasible, as long because the component value expressions reduce to values that have compatible data types.
SQL has 5 kinds of value expressions:

  • String value expressions
  • Numeric value expressions
  • Datetime value expressions
  • Interval value expressions
  • Conditional value expressions

3. 5 Types of SQL Value Expressions

Below we are discussing 5 types of Value Expressions in SQL:

SQL Expression

5 Types of SQL Value Expressions

a. String Value Expressions

A single string value is simplest string value expression. Alternative potentialities include a column reference, a set function, a scalar subquery, a CASE expression, a cast expression, or a complex string value expression.
Only one operator is feasible in an exceeding string value expression: the concatenation operator. You will concatenate any expressions with another expression to create an additional complicated string value expression. A pair of vertical lines (||) represents the concatenation operator. The subsequent table shows some examples of string value expressions.
Have a look at SQL Data Types

Expression Produces
‘Peanut ‘ ||
‘brittle’ ‘Peanut brittle’
‘Jelly’ || ‘ ‘ ||
‘beans’ ‘Jelly beans’
FIRST_NAME || ‘ ‘ ||
LAST_NAME ‘Joe Smith’
B’1100111′ ||
B’01010011′ ’110011101010011′
‘’ ||
‘Asparagus’ ‘Asparagus’
‘Asparagus’ ||
‘’ ‘Asparagus’
‘As’ || ‘’ ||
‘par’ || ‘’ ||
‘agus’ ‘Asparagus’

If you concatenate a string to a zero-length string, the result’s a similar because of the original string.

b. Numeric Value Expressions

In numeric value expressions, you’ll apply the addition, subtraction, multiplication, and division operators to numeric-type knowledge. The expression should reduce to a numeric price. The elements of a numeric value expression could also be of various data types as long as all the info types are numeric.
The data type of the result depends on the data types of the elements from that you derive the result. Here are some examples of SQL numeric value expressions:

–27
49 + 83
5 * (12 – 3)
PROTEIN + FAT + macromolecule
FEET/5280
COST * :multiplierA

c. DateTime Value Expressions

Datetime value expressions perform operations on data that deal with dates and times. These price expressions will contain elements that square measure of the categories of DATE, TIME, TIMESTAMP, or INTERVAL. The results of a datetime price expression is usually a datetime kind (DATE, TIME, or TIMESTAMP). The subsequent expression, to Illustrate, offers the date one week from today:
Let’s discuss SQL operators
CURRENT_DATE + INTERVAL ‘7’ DAY
Times are maintained in universal time|Greenwich Time|GMT|UT|UT1|time} Coordinated (UTC) — acknowledged within the {uk|United Kingdom|UK|Great Britain|GB|Britain|United Kingdom of Great Britain associate degreed Northern Ireland|kingdom} as Greenwich Mean Time — however you’ll specify an offset to create the time correct for any specific zone. For your system’s time zone, you’ll use the easy syntax given within the following example of DateTime value SQL expressions:

TIME '22:55:00' AT local
Alternatively, you'll specify this value the long way:
TIME '22:55:00' AT zone INTERVAL '-08.00' HOUR TO MINUTE

This expression defines the time because the zone for Portland, Oregon, that is eight hours earlier than that of Greenwich, England.

d. Interval Value Expressions

If you figure one DateTime from another, you get an interval. Adding one DateTime to a different makes no sense, thus SQL doesn’t allow you to do this. If you add 2 intervals along or subtract one interval from another interval, the result’s an interval. You’ll conjointly either multiply or divide an interval by a numeric constant.
SQL has 2 kinds of intervals: Year-month and Day-time. To avoid ambiguities, you need to specify what to use in an interval expression. The subsequent expression, to illustrate, offers the interval in years and months till you reach retirement age:
(BIRTHDAY_65 – CURRENT_DATE) YEAR TO MONTH
The following example of SQL interval value expressions offers an interval of forty days:
Do you know about SQL RDBMS Database

INTERVAL '17' DAY + INTERVAL '23' DAY

The example that follows approximates the whole range of months that a mother of five has been pregnant (assuming that she’s not presently expecting range six!):

INTERVAL '9' MONTH * five

Intervals are often negative similarly as positive and should comprise any price expression or combination of value expressions that evaluates to an interval.

e. Conditional Price Expressions

The price of a conditional value expression in SQL depends on a condition. The conditional price expressions CASE, NULLIF, and COALESCE are considerably additional complicated than the opposite forms of SQL value expressions.
So, this was all in SQL Expressions. Hope you like our explanation.

4. Conclusion

Hence, in this SQL Expressions Tutorial, we learned about the value expressions in SQL. Moreover, we discussed types of SQL value expression, that are String, Normal, DateTime, Interval, and conditional value expressions in SQL. Still, if any query regarding SQL Expressions, ask in the comment tab.
For reference

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.