**Learning Objectives**After completing this session, you will be able to:

- Define simple and complex expressions
- Define Case expressions
- Define date time expressions
- Define function expressions

**Understand the SQL Expressions**An

**expression**is a combination of one or more values, operators, and SQL functions that evaluates to a value. An expression generally assumes the datatype of its components.

You can use expressions in:

- The select list of the SELECT statement
- A condition of the WHERE clause and HAVING clause
- The CONNECT BY, START WITH, and ORDER BY clauses
- The VALUES clause of the INSERT statement
- The SET clause of the UPDATE statement

SQL Expressions can be classified as

- Simple Expressions
- Compound Expressions
- CASE Expressions
- Date time Expressions
- Function Expressions

**Simple Expressions**A simple expression specifies a column, pseudo column, constant, sequence number, or null.

Some valid simple expressions are as follows:

- Daily_pipeline_loan_fact.loanno
- 'this is a text string'
- 10

Compound ExpressionsCompound Expressions

A compound expression specifies a combination of other expressions. You can use any built-in function as an expression .

In a compound expression, some combinations of functions are inappropriate and are rejected. For example, the LENGTH function is inappropriate within an aggregate function.

Some valid compound expressions are:

Code:

('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))

__CASE Expressions__CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.

Code:

CASE expr WHEN comparision_expr1 THEN return_expr1 [WHEN comparision_expr2 THEN return_expr2 WHEN comparision_exprn THEN return_exprn ELSE else_expr] END

You cannot specify the literal null for all the return_exprs and else_expr.

**Example :**Suppose, the ABC wants to give a interest rate of 15 percent on all personal loans ‘P’, a interest rate of 12 percent on home loan ‘H”, and a interest rate of 16 percent on all other loans. The code to retrieve the data is as follows:

Code:

SELECT loan_no,loan_amount, CASE loan_type WHEN ‘P‘ THEN 15 WHEN ‘H‘ THEN 12 ELSE 16 END as interestrate FROM daily_pipeline_loan_fact;

__Date and Time Expressions__A datetime_value_expr can be a datetime column or a compound expression that yields a datetime value.

**As the database stores dates as numbers, you can perform calculation using arithmetic operators such as addition and subtraction.**

Arithmetic With date:

Arithmetic With date:

Operation |
Result |
Description |

Date + number | date | Adds a number of days to a date |

Date - number | date | Subtract a number of days to a date |

Date - Date | Number of days | Subtract one date from another |

Date + number/24 | date | Adds a number of hours to a date. |

**Example:**

Code:

SELECT loanno,(SYSDATE – update_date)/7 AS weeks FROM daily_pipeline_loan_fact

**An interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND.**

Interval Expression:

Interval Expression:

**Stored as an interval of years and months.**

INTERVAL YEAR TO MONTH:

INTERVAL YEAR TO MONTH:

INTERVAL YEAR [(year_precision)] TO MONTH

year_precision is the number of digit in the year date time field. The default value of year_precision is 2.

**Example**

Code:

INTERVAL ‘312-2’ YEAR(3) TO MONTH Indicates an interval of 312 years and 2 months. INTERVAL ‘312’ YEAR(3) Indicates an interval of 312 years and 0 months. INTERVAL ‘300’ MONTH(3) Indicates an interval of 300 months.

**The INTERVAL DAY TO SECOND data type stores a period of time represented as days, hours, minutes and seconds with a fractional part .**

INTERVAL DAY TO SECOND:

INTERVAL DAY TO SECOND:

Code:

INTERVAL DAY [(year_precision)] TO SECOND [(sec_precision)]

sec_precision is the number of digits in the fractional part of the SECOND datetime field. The range of sec_precision is 0 to 9. The default value is 6.

**Example:**

Code:

SELECT (SYSTIMESTAMP - update_date) DAY(9) TO SECOND FROM daily_pipeline_loan_fact WHERE loanno = ‘10001’;

**Function Expressions**You can use any built-in SQL function or user-defined function as an expression. Some valid built-in function expressions are:

- LENGTH('BLAKE')
- ROUND(1234.567*43)
- SYSDATE

**Summary**- A simple expression specifies a column, pseudo column, constant, sequence number, or null
- A compound expression specifies a combination of other expressions
- CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures

Test Your UnderstandingTest Your Understanding

- What are different types of expression?
- Give an example of compound expression?
- Give an example of date time expression?
- Give an example of function expression?
- What is a pseudo column?

torus