In a table.
It's not unusual for a column to contain rows with a duplicate values in the teachers table.
For example, school column lists the same school names multiple times because each school employs many
teachers.
So to understand the range of values in a column, we can use the distinct keyword as a part of the
query that eliminates duplicates and shows only unique values.
So use the distinct immediately after the select here.
So firstly, actually, instead of here, we will use the order by school as well.
And now we will use select.
This thing.
Actually, Slater here, select here.
Distinct this thing.
And school here.
From.
From teachers.
Table.
And order by.
Ordered by school.
That's it.
And here, as you can see here, the result is this just a two output, even though it's six rows are
in the table, the output shows the two unique school names in the school column.
So this is a helpful first step toward assembling and assessing data quality.
For example, if a school name is spelled more than one way, use spellings.
Variations will be easy to spot and correct, especially if you sort the output.
So when you are working with dates or numbers distinct will help highlight inconsistent or broken formatting.
For example, you might inherit a dataset which dates were entered in a column formatted with a text
data type.
So.
And this is the practice that you should avoid, by the way.
And this allows the dates malformed dates to exist.
So and the distinct keywords also works on more than one column at a time.
So if we add a column, so order by.
So select the things school and salary from teachers order by school and salary.
Here and now.
The query returns here and the query returns each unique or distinct salary earned at each school.
So the two teachers at Myers Middle School earns near $43,500.
That pair is listed in just one row.
And the courier returns five rows rather than all six in a table.
And the techniques gives us the ability to ask for each x in the table.
What are the A values?
Right.
So for each factory, what are the all the chemicals it produced for each selection district?
Who are all the candidates running for office for each concert hall?
Who are the artists playing this month?
Also offers more sophisticated techniques with aggregate functions that lets us count sum and find minimum
and maximum values.
So I will cover this in detail of later lectures of our course so we can also filter the rows with where
here sometimes we where keywords.
So sometimes you will want to limit the rows curtains to only those in which one or more columns meet
certain criteria.
So using teachers as an example, you might want to find all teachers hired before a particular year,
or all teachers making more than 75 or $70,000 at elementary schools.
So for these tasks we use the where clause.
So the where clause allows you to find rows that match a specific value, a range of values or multiple
values based on a criteria supplied via an operator.
And this is the keyword that lets us perform math comparison and logical operations.
So you can also use criteria to exclude rows here.
So now we will do some basic example here.
Select the last name.
Last name.
And school.
To.
And higher date.
Higher.
Air date.
From teachers.
So and we will use where?
Where?
Keyword school.
And now we will add the let's use the Myers Middle School.
Right.
Myers.
My.
Medal.
School.
That's it.
So now after that, we will do the semicolon.
And here that's it, as you can see here.
So here I'm using the equals comparison operator to find rows that exactly match a value.
But of course, you can use other operators with where to customize your filter criteria.
So here we have some most commonly used comparison operators.
So firstly, this is the equal to.
So this is.
Actually, let's use the here.
So this is the equal to.
And this is the not equal to greater than less than greater than or equal to less than or equal to.
And we also have between.
This is the between means within a range.
So in here, like.
I like.
We will use the patch.
So this is the case.
Insensitive.
Case Insensitive.
Like I like and like is a case sensitive and not here not negates a condition.
So now let's go here.
You can also, by the way, find that on Internet in Wikipedia, these are pretty popular comparison
operators in programming.
So and I will also share these operators with you here.
So now let's.
Use the equal operator.
Here with that.
So where?
First name.
Uh, it was the call.
Right?
Or last name.
We can also use last name.
Where?
Last name.
And actually, let's see also first name.
First name.
Here.
So where last name equals Jane.
Last name call.
Right?
Holy.
And here, as you can see, Samuel Cole Myers Middle School and 2005, August 1st.
So next, we can also list our school names in the table, but exclude them if the Roosevelt High School
using the not equal operator remember our not equal operator was this and we can for not equal we can
also use this so you can choose it.
And here we will.
So to do that, to exclude all Roosevelt High School result from we will use where.
School.
Cool.
Not equals to f.
T.
F t.
Whose wealth?
Just about.
High school.
Now.
Here.
So we excluded here we use the less than operator to the list teachers here and we are here that we
are only seeing the Myers Middle School on the output.
So now we will do.
Let's actually use the less than operator to list teachers hired before January 1st, 2002 or January
1st or January 2nd, 2000.
Here, using the date format here, of course, we're going to use date format here.
So where hire date is less than operator.
Remember, this is the less than operator.
And.
Here.
So sorry.
Let's actually.
Display.
This display, we can assume.
100.
I hope we can.
Okay, Perfect.
Just perfect.
So now we will use their higher date is less than 2000.
Uh, January.
Here.
So you remember this is the month of January, and here, let's make it 20.
So that's it.
Run.
And here, as you can see here, we just got one result because there's just only one, which is Lee
Reynolds that hired before 2000 January twins.
Right.
So then we can also find the teachers who earned 43,000 or more using the less than greater than or
equal operator.
So now we will use the salary.
Salary.
Greater than or equal to 43,000.
Here, as you can see here.
And let's also print the salary here.
And we can also, instead of writing each one of here, just select one teachers.
As you can see here, the salary is numeric.
And that's it.
So we can also do, for example, 43,500.
Right.
So if we do this.
We will still get that same result.
But if we just do one more and here, as you can see, we just have one output here.
So the next query uses here, let's say we will use the between operator to find teachers who earn from
earn from 30,000 to 70,000.
Right.
And also keep in mind that between is inclusive, meaning that result will include values matching the
start and end ranges specified.
So.
Now select our.
Everything from teachers and where salary between bit been or actually let's make it 40,000 40,060 5000.
Now that's it.
So now run it.
And here.
That's it.
This is our.
Output that we get with between operator.
So you also keep in mind that use caution with between because it's inclusive nature can lead to inadvertent
double counting values.
So for example, if you filter for values between 10 and 12 and run a second query using a between 20
and.
Uh, 30 a row with the value of 20 will appear in both query results.
So you can avoid this by using the more explicit, greater than and less than operators to define ranges.
For example, they are salary.
Uh, surveyor salary greater than or equal to 40,000.
And salary less than or equals to 65,000.
So.
We will return to these operators throughout this course because they will play a key role in helping
us ferret out the data and answers we want to find.
My name is Stefon, and I'm waiting you in another lecture of Oxley.
So see you later.