Hey guys welcome back to this SQL tutorial series. In this session we will learn
how to extract records with a like and between operators. We'll start with the
like operator. So the like operator is used to extract those records from a
table where a particular pattern is present for example we'd want to extract
all those records where the person's name includes John. So if you have
records of 3 people in our table with the names Jonathan Johnny and Marcos
then with the help of like operator we can extract records where the names are
Jonathan and Johnny since they have the required pattern present in them. Now the
like operator is used in conjunction with the wildcard characters and there
are two wildcard characters first one is the percentage symbol and the second one
is the underscore character so the percentage symbol is substitution for zero
one or more characters while the percentage symbol a substitution for a
single character and this is a syntax for like operator. After giving the
where Clause be given the column name followed by the like operator and then
the pattern which you want to extract and keep us in mind guys the pattern should
be inside single quotes. Now let's do some operations for the like operator so
let's say I want to extract all of those records where the employees name starts
with the letter J so the command would be select start from employee where I
will give in the condition which would be e name following which I will give in
the like operator and inside single quotes since the name should start with
J I will type J after which I will use the percentage wildcard so all of those
names which start with J would be extracted with this command so let me
get execute so Julia and Jeff are the two employees whose name starts with
J now similarly I'd want to extract all of those employees whose age is in
the thirties right, select start from employee where I need to get the
condition so e_age following which I'll get the like operator and then inside single
quotes age needs to be in 30s so after three I will use the underscore wildcard
character and then put in the semicolon let me hit execute so we have Julia and
Matt over here so Julia's age is 30 and Matt's age is 33 so the other two
employees whose age is in the 30s. Now let's look at the between operator
so the between operator is used to select values between a given range for
example we can extract only those records where the age of the person is
between 20 and 25 and this is the syntax after where clause we give in the name of the
column then we give the between keyword after which we give in the lower value of column
the range then we use the and operator and then give the maximum value of the range
so i want to extract all of those employees whose age is between 25 and 35
so the command would be select start from employee where the condition would be e_age
should be between 25 and 35, execute so Ann, Julia, Matt and Jeff are
the four employees whose age is between 25 and 35 so as you see over here this
value is inclusive right 25 is inclusive so Ann's age is 25, Julia's age is 30
Matt's age is 33 and Jeff age is 27 and these are the four employees whose age
is between 25 and 35. Now similarly I would want to extract all of those employees
whose salary is between 90 thousand dollars and 120 thousand dollars so the
command would be select start from need to give in the name of the table which
is employee then I'll use the where clause and then give the condition so
the condition would be e_salary between so the salary needs to be between ninety
thousand dollars and 120 thousand dollars
I ll press semicolon, let me hit execute so Sam and Jeff are the two employees whose
salary is between ninety thousand dollars and one hundred and twenty
thousand dollars. So Sam salary is $95,000 and Jeff salary is one hundred
and twelve thousand dollars and in the next session we'll be working with some
basic and in build functions so thanks for attending the session. Let's meet in the
next class