Predicate in DBMS

January 31, 2023

Predicate in DBMS

On this page we will discuss 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

Predicate in DBMS

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.

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
DBMS desktop gif
DBMS mobile adv gif

What is Database

Participation Constraint

DBMS Architecture

Data Models

Cardinality

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

The predicate in where clause

select * from emp
where [job='MANAGER'];
O/P 
3 rows selected.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 – 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 – 10
7566 JONES MANAGER 7839 02-APR-81 2975 – 20
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 JOB SAL HIREDATE
7566 MANAGER 2975 02-APR-81
7788 ANALYST 3000 19-APR-87
7902 ANALYST 3000 03-DEC-81
7369 CLERK 800 17-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
EMPNO JOB SAL HIREDATE
7698 MANAGER 2850 01-MAY-81
7782 MANAGER 2450 09-JUN-81
7369 CLERK 800 17-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
EMPNO ENAME HIREDATE SAL JOB
7788 SCOTT 19-APR-87 3000 ANALYST
7369 SMITH 17-DEC-80 800 CLERK
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT – 17-NOV-81 5000 – 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
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
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT – 17-NOV-81 5000 – 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
The details of those employees whose salary does not fall in the range between 800 to 2900 are displayed.

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get Prime
DBMS Tutorial banner

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout