Please login

Prime

Prepinsta Prime

Video courses for company/skill based Preparation

(Check all courses)
Get Prime Video
Prime

Prepinsta Prime

Purchase mock tests for company/skill building

(Check all mocks)
Get Prime mock

SQL Expressions in DBMS

SQL Expressions in DBMS

 

In this article, we will learn about SQL Expressions in DBMS.

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];
SQL Expressions in DBMS

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

  • Boolean
  • Numeric
  • Date

Consider the following sample table emp

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285050030
7782CLARKMANAGER783909-JUN-81245050010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080050020

 

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
EMPNOENAMEJOBDEPTNOSAL
7566JONESMANAGER202975
7788SCOTTANALYST203000
7902FORDANALYST203000
7369SMITHCLERK20800

 

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.
ENAMEJOBSALANNUALSALARYBONUS
KINGPRESIDENT5000600001250
BLAKEMANAGER285034200712.5
CLARKMANAGER245029400612.5
JONESMANAGER297535700743.75
SCOTTANALYST300036000750
FORDANALYST300036000750
SMITHCLERK8009600200

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_TIMESTAMPSYSDATE
08-JUN-19 10.07.51.709047 AM US/PACIFIC08-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.
ENAMEJOBSALHIREDATEHIREDATE+2
KINGPRESIDENT500017-NOV-8119-NOV-81
BLAKEMANAGER285001-MAY-8103-MAY-81
CLARKMANAGER245009-JUN-8111-JUN-81
JONESMANAGER297502-APR-8104-APR-81
SCOTTANALYST300019-APR-8721-APR-87
FORDANALYST300003-DEC-8105-DEC-81
SMITHCLERK80017-DEC-8019-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

Learn more about DBMS here on this page.