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