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
A simple expression specifies a column, pseudo column, constant, sequence number, or null.
Some valid simple expressions are as follows:
- 'this is a text string'
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:
('CLARK' || 'SMITH') LENGTH('MOOSE') * 57 SQRT(144) + 72 my_fun(TO_CHAR(sysdate,'DD-MMM-YY'))
CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.
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:
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.
Arithmetic With date: As the database stores dates as numbers, you can perform calculation using arithmetic operators such as addition and subtraction.
|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.|
SELECT loanno,(SYSDATE – update_date)/7 AS weeks FROM daily_pipeline_loan_fact
Interval Expression: An interval expression yields a value of INTERVAL YEAR TO MONTH or INTERVAL DAY TO SECOND.
INTERVAL YEAR TO MONTH: Stored as an interval of years and months.
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.
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.
INTERVAL DAY TO SECOND: 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 [(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.
SELECT (SYSTIMESTAMP - update_date) DAY(9) TO SECOND FROM daily_pipeline_loan_fact WHERE loanno = ‘10001’;
You can use any built-in SQL function or user-defined function as an expression. Some valid built-in function expressions are:
- 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 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?