WHERE Query in DBMS

WHERE Query  in DBMS

 

In this article, we will learn about WHERE Query in DBMS.

Sometimes a user is interested in filtering data and retrieving only specific data that meet certain requirements and conditions, this can be done using WHERE  clause which performs data filtering

  • WHERE  clause is used to specify a condition while retrieving and updating data from the database table and display only those records in the table for which the condition specified in the where clause becomes true
  • The WHERE clause is most commonly used with a select, update and delete  statements
WHERE Query in DBMS

General Syntax for WHERE clause

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Consider the sample table emp

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

 

Display the records of those employees whose designation is ‘MANAGER’

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 are why only three records are displayed, because the condition is true for only those three rows i.e when the job is a manager.

Display name, job, the salary of that employee whose salary is greater than 2900

select ename,job,sal 
from emp
where sal>2900;

O/P

ENAMEJOB  SAL 
KINGPRESIDENT5000
JONESMANAGER2975
SCOTTANALYST3000
FORDANALYST3000

You can also display  only specified columns and filter the records

 

Operators that can be used in Where query in DBMS

Not only comparison(=) operator many operators shown below can be used with where clause

OperatorDescription
=Equal to
!=Not Equal to
<Less than
>Greater than
<=Less than or Equal to
>=Greater than or Equal to
BETWEENBetween a specified range of values
LIKEThis is used to search for a pattern in value.
INIn a given set of values

Display the records of all employees other than managers

select ename,sal,job,deptno from emp
where job != 'MANAGER';

O/P

4 rows selected

ENAMESALJOBDEPTNO
KING5000PRESIDENT10
SCOTT3000ANALYST20
FORD3000ANALYST20
SMITH800CLERK20

Display records of all employees whose salary is between 1000/- to 2500/-

select ename,sal,job,deptno from emp
where SAL between 1000 AND 2500;
  • Here we can make use of between operator where the condition is applied for all the values within the specified range
  • In the above example all the records of employees whose salary in between the range of 2000 and 2500 is displayed(inclusive of 2000 and 2500)
  •  

Deleting records based on the condition specified in where clause

Conditions can be specified by using where the where clause for deletion and updation of data

delete from emp
where empno=7934;

O/P

1 row deleted