Comparison operators are fairly straightforward, but matching the operators, for example, like and
I like deserve additional explanation.
So both let you find the variety of values that include characters matching a specified pattern, which
is handy if you don't know exactly what you are searching for or if you are rooting out misspelled words.
So to use like and I like you specify a pattern to match using one or both of the symbols, which is
the first symbol is present sign and the second symbol first symbol is present sign.
And the second symbol is a wildcard.
Matching just one character and present side means the matching one or more characters.
So, for example, if you are trying to find a baker we will use here from teachers like Be.
We can also use like.
A key.
Or we can also use.
Acre.
Just.
Underscore acre.
Or we can also use the pay underscore error here.
So the difference is the lag operator, which is a part of the ANSI SQL standard, is case sensitive.
The I like operator, which is a PostgreSQL only implementation, is case insensitive.
So here we will use this.
Explanation.
So select.
Select First name.
From teachers.
Teachers wear first name first.
Name like Sam.
Sum and percentage character.
And here, this is another query here, and we will select the first name.
So actually, let's first run this or actually first name, write this and then run write features.
A teachers.
And their first name.
I like.
We can also do right like that.
I like.
Some.
Run this.
That's it.
Let's delete this firstly.
We'll get same result.
No.
So over the years I have gravitated toward using I like and wild card operators, but to make sure I'm
not inadvertently excluding results from searches.
So particularly when vetting data.
So I don't ensure that whoever typed the names of people, places or products or other proper nouns
always remembers to capitalize them.
And if one of the goals of interviewing data is to understand its quality using a case, insensitive
search will help you to find variations.
So in this case, let's actually delete this.
Here it is.
And here, as you can see, we are getting results.
But with, like, without.
I like.
We will get nothing.
That's it.
So because like and like search for patterns performance on a large databases can slow so we can improve
using indexes which I will cover in next lectures.
So you can also combine operators with and and or here.
So comparison operators become even more useful when we combine them, right?
So to do this, we connect them using a logical operators and.
And or.
Or along with if needed, parentheses.
So here we will use the select Select here.
All from teachers where.
Where?
School here.
Myers Middle.
Middle school.
And and salary or salary is less than 40,000.
And we can also use another query here.
So first, actually, let's run this.
This is the first run.
You can also do select.
Select all from teachers.
And where?
Last name?
Last.
Last name is.
The call.
Paul and or.
Or not.
And last name.
His brush.
Let's write another query here.
Select.
From teachers.
Where school.
If the.
Rules.
Age high school and we will use and.
Salary.
Less than 38,000.
Or.
Salary is greater than 40,000.
That's it.
So we will copy this and we will.
We will export these one by one.
Let's export.
Let's run first the.
This.
As you can see, we got this here.
So the first query here, we write, we excluded users and.
Uh, in the where clause.
To find teachers who work at Myers Middle School and have a salary less than $40,000.
So because we connect the two conditions using and both must be true for a row to meet the criteria
in the where clause and be returned in the query result.
And now let's run the second example.
Yeah, let's run the second example.
And with this second example.
That's it.
So.
Uh, we are using or to search for any teacher whose last name matches Cole or brush.
So when we connect finding, when we connect conditions using or only one of the conditions must be
true for a row to meet the criteria of the where clause.
So now let's get to the final example here.
So the final example looks for a teachers at Roosevelt whose salaries are either less than 38,000 or
greater than 40,000 when and when we place the statements inside parentheses.
These are evaluated as a group before being combined with other criteria.
So in this case, the school name must be exactly if the Roosevelt High School and the salary must be
either must be either less or higher than specified for a row to meet the criteria of where class.
So if we use both and and or in a class but don't use any parentheses, the database will evaluate the
end condition first and then the or condition.
So in the in the final example, in this example, that means we'd see different result if we omitted
parentheses.
So here the database would look for rows where the school name is Roosevelt and the salary is less than
38,000.
That's it.
As you can see here, we see if the Roosevelt Middle School here.