SQL Expressions in DBMS

SQL Expressions

An expression is a combination of data, operators and other functions  which finally computes to obtain a value

Basic Syntax for SQL expressions usage

SELECT column1, column2, columnN 
FROM table_name 
WHERE [CONDITION|EXPRESSION];

There are three different types of SQL expressions, which are mentioned below

  • Boolean
  • Numeric
  • Date
Expressions in SQL
Consider the following sample table emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 500 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 500 20

Boolean Expressions

  • Boolean expressions generally correspond to a condition specified in the where clause for retrieving the required set of data
  • Whenever the data you specified in the expression is matched with the data in the table then the expression evaluates to true and the record is obtained

Syntax

SELECT column1, column2, columnN 
FROM table_name 
WHERE SINGLE VALUE MATCHING EXPRESSION;

Example

select empno,ename,job,deptno,sal
from emp
where deptno=20;
O/P
4 rows selected
EMPNO ENAME JOB DEPTNO SAL
7566 JONES MANAGER 20 2975
7788 SCOTT ANALYST 20 3000
7902 FORD ANALYST 20 3000
7369 SMITH CLERK 20 800
The above query displays the rows  of the table only if there is a match for the department number as specified in where clause i.e only when dept-no is 20

Numeric expression

These expressions are  used to perform arithmetical calculations on the data present in the table

Syntax

SELECT numerical_expression as OPERATION_NAME
[FROM table_name
WHERE CONDITION] ;

Example

select ename,job,sal,sal*12 as annualsalary ,sal/4 as bonus from emp;
O/P
7 rows selected.
ENAME JOB SAL ANNUALSALARY BONUS
KING PRESIDENT 5000 60000 1250
BLAKE MANAGER 2850 34200 712.5
CLARK MANAGER 2450 29400 612.5
JONES MANAGER 2975 35700 743.75
SCOTT ANALYST 3000 36000 750
FORD ANALYST 3000 36000 750
SMITH CLERK 800 9600 200
The above query perform multiplication and division operation on the data of the salary column and displayed

Date expression

  • Date expression performs manipulation on ‘date data type ‘values
  • It is also used to return the current system, date timestamp and arithmetic operations on date values

Example

SELECT CURRENT_TIMESTAMP,sysdate from dual;
O/P
CURRENT_TIMESTAMP SYSDATE
08-JUN-19 10.07.51.709047 AM US/PACIFIC 08-JUN-19

Example

We can also perform arithmetic operations on date values
select ename,job,sal,hiredate,hiredate+2 from emp;
O/P
7 rows selected.
ENAME JOB SAL HIREDATE HIREDATE+2
KING PRESIDENT 5000 17-NOV-81 19-NOV-81
BLAKE MANAGER 2850 01-MAY-81 03-MAY-81
CLARK MANAGER 2450 09-JUN-81 11-JUN-81
JONES MANAGER 2975 02-APR-81 04-APR-81
SCOTT ANALYST 3000 19-APR-87 21-APR-87
FORD ANALYST 3000 03-DEC-81 05-DEC-81
SMITH CLERK 800 17-DEC-80 19-DEC-80
Hiredate+2 is nothing but 2 days from current date value is advanced suppose it hiredate  is july 24 then hiredate +2 will give July 26

Please Login/Signup to comment