SQL And, Or and Not Operators | SQL Operators and their Procedure | Operators in SQL Server


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

One Reply to “SQL And, Or and Not Operators | SQL Operators and their Procedure | Operators in SQL Server”

  1. 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]

Leave a Reply

Your email address will not be published. Required fields are marked *