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

Predicate in DBMS

Predicate in DBMS

 

In this article, we will learn about Predicate in DBMS.

Sometimes expressions need to be evaluated to true or false, based on this decision of true or false then the records are retrieved from the database tables

A Predicate in DBMS is a condition expression which evaluates and results in boolean value either true or false which enables decision making in retrieving and manipulating a record.

Predicate in SQL

A predicate  is a condition that is specified for:

  • Filtering  the data using the WHERE clause,
  • Pattern matching in LIKE operator,
  • Specifying a set of list for  using   IN  operator,
  • Manipulating a range of values using BETWEEN operator, etc

 Consider a 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

 

The predicate in where clause

select * from emp
where [job='MANAGER'];

O/P

3 rows selected.
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520

In our emp table, there are three managers that’s why only three records are displayed, because the condition is true for only those three rows i.e when the job is a manager.

 

The predicate in ‘IN’ clause

It is used to specify a set of values and where manipulation is performed on all the values specified in the set and if any of the value that is present in the list matches with the values present in a table then it returns true and is operation is performed

Example

select empno,job,sal,hiredate 
from emp
where [ename in('SCOTT','FORD','SMITH','JONES')];

O/P

4 rows selected
  EMPNO 
JOBSALHIREDATE
7566MANAGER297502-APR-81
7788ANALYST300019-APR-87
7902ANALYST300003-DEC-81
7369CLERK80017-DEC-80

Records of all those employees that are specified in the list of in clause are displayed.

 

Predicate in ‘BETWEEN CAUSE

It is used to perform data comparison and manipulation over a range of values present in the database table

Example

select empno,job,sal,hiredate 
from emp
where [sal between 800 and 2900];

O/P

3 rows selected
EMPNOJOBSALHIREDATE
7698MANAGER285001-MAY-81
7782MANAGER245009-JUN-81
7369CLERK80017-DEC-80
  • The details of those employees whose salary is present in the range between 800/- to 2900/- are  retrieved and it also considers specified values inclusive of the range
  • In the case of between operator lower value is first specified and followed by the higher value  & an and operator in between these higher and lower values.

 

The predicate in ‘LIKE ‘ clause

The like operator is a pattern matching operator that returns those records that match with the specified data pattern

Example

select empno,ename,hiredate,sal,job
from emp
where [ename like 'S%'];

O/P

2 rows selected
EMPNOENAMEHIREDATESALJOB
7788SCOTT19-APR-873000ANALYST
7369SMITH17-DEC-80800CLERK

All the records of employees whose names starting with the letter ‘S’ are displayed.

Predicate  in ‘IS NULL’ clause

All operations upon null values present in the table must be done using this is null operator, we cannot compare null value using the assignment operator(=)

select * from emp
where [comm is null]

O/P

4 rows selected
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

The details of those employees whose commission value is Null are displayed.

 

The predicate in NOT clause

Not operator is  negation operator which is used along with like, between, is null, in operators, It performs the reverse action of all these operators

Example

select * from emp
where [sal NOT between 800 and 2900 ];

O/P

4 rows selected
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020

The details of those employees whose salary does not fall in the range between 800 to 2900 are displayed

Learn more about DBMS here on this page.