Hey guys welcome back to the SQL

tutorial series. In this session we’ll learn how to extract records on the

basis of multiple conditions using the and or and not operators. So we’ll start

with the and operator first then we’ll filter record for the or operator and

finally we’ll work with the not operator so with the and operator we can

basically fetch those records which satisfy all of the conditions separated

by and for example you want to find out all the doctors whose age is greater

than 60 so there are two conditions over here

first the age needs to be greater than sixty, second the occupation needs to be

doctor so will get only those records where both the conditions are being

satisfied. Now let’s have a look at the syntax, so here after we give the first

condition of the where clause we follow it up with and operator and give the

second condition and similarly if we have another condition to be satisfied

will again give the and operator and then give the next condition

so let’s fetch some records using the and operator. So I want to extract all of

those employees whose gender is male and age is less than thirty so the command

would be select start from employee I’ll use the where clause and then give in

the first condition, the first condition is gender needs to be equal to

male so e_gender is equal to male. I’ll use

the and operator and then given the second condition, so the second condition is the age needs to be less than 30 let me hit execute. Right so Bob and Jeff

are the two employees whose gender is male and age is less than 30, now

similarly I’d want to extract all of those employees who work in the

operations department and their salary is greater than hundred thousand dollars

so the command would be select start from employee where I have to give the first

condition so the first condition is the department needs to be equal to

operations I’ll use the and operator again

and then I’ll give a second condition so the second condition is the salary needs

to be greater than $100,000 right, execute so Jeff is the only

employee who works in operations and the salary is greater than $100,000. Then we

have the or operator, so the or operator displays those records whare at least

one of the conditions is satisfied for example if we want to filter out either

a software engineer or a doctor then they can use the or operator and this is

a syntax for the or operator so similar to and operator after we give the first

condition we follow it up with or operator and then give the second

condition. Now we’ll fetch some records from the employee table using the or

operator. So I would want to extract all of those records where the employee works either in operations or in analytics so the command would be select start from

employee where you need to get the first condition so the first condition is

Department should be equal to operations, I’ll use the or operator now

and then give the second conditions or the second condition is the department

needs to be equal to analytics so we have four records over here, so Sam works in operations, Ann works in

analytics, Julia works in analytics and Jeff works in operations so there are

four results out of which two people work in operations and two people work

in analytics. Now similarly I’d want to extract those records where either the

salary is greater than hundred thousand dollars or the age is greater than

thirty, so this would be the command select start from employee where salary is greater than $100,000 I’ll

have to use the or operator and then give the second condition so the second

condition is age needs to be greater than 30

execute, so again we have four records over here, so the first record is Sam

whose age is greater than 45 then we have Ann whose salary is greater than

hundred thousand dollars then we have Matt which satisfy both the conditions

actually his salary is greater than hundred thousand dollars and his age is

also greater than thirty, then we have Jeff whose salary is greater than

hundred thousand dollars so these four records we see that at least one of

these conditions is being satisfied and finally we have the not operator. So with

the not operator we can extract those records where the condition is not true for example if I wanted to extract all those

records where the occupation of the person is not equal to software

engineer then I’ll use the not operator so let’s have a look at the syntax of

not operator. So here we follow up the where clause with the not operator and

then give the condition. Now let’s work with this not operator so I want to

extract all of those records where the gender is not equal to female

so the command would be select start from employee where after where clause I need to use the not operator and then give the condition so gender is equal to female

right so before the condition I use the not operator, so this would basically

give me all of the records where gender is not equal to female so I’ll hit

execute to see that I get four records in total and all of those four records

the gender value is male. Now similarly I will extract all of those records were

the age is not less than thirty right so select start from employee where I need to follow it up

with not operator and then give the condition which would be e of age is less

than 30 so this is the actual condition if you’re age is less than 30 and

since I proceeded with the not operator I will get all of the records

where age is actually not less than 30 I’ll execute so these are the three

employees whose age is not less than 30 we have Sam whose age is 45 we have

Julia whose age is exactly 30 and then we have Matt whose age is 33. So guys

this is how we can work with and or and not operators. In the next session we’ll

learn how to extract records with a like and between operators. So thanks

for attending the session let’s meet in the next class

Got a question on operators in SQL server? Do write it in the comment and you will get a response immediately by our expert. For MS SQL Server training & certification, call us at US: 1800-216-8930 (Toll Free) or India: +917022374614. You can also write us at [email protected]