If you don't already have SQL Server Management Studio open, go ahead and open it now.
If there are any open query windows, it might be a good idea to close them to prevent confusion.
Let's open a new query window.
We're going to be learning about how to use the where and order by clauses.
We are going to create a SQL file to save that will be part of our class resources.
And so let's get started.
The Where clause allows us to restrict results to data that meets specific search conditions.
So we can select everything from customers.
And get all the records.
But let's say that we only want customers that live in the state of Virginia, which is designated by
VA.
We can then put a Where clause, filter the results by the state ID field, and we just use equals VA
and we can hit execute.
And now we have a list of our customers that are in the state of Virginia.
Now if we want to order these by last name first name, then we can use the order by clause and just
put in the names of the columns that we want to order by.
And by default this is going to be ascending.
So we're ordering by the last name column and then the first name column.
So alphabetically chin is going to appear first then dent Doe and Jones.
And then after that is ordered then it is ordered by the first name column.
So you have al John and John Paul.
So I will put my first comment in there.
And then for the next example I am going to use the same thing.
But we will do order by descending order.
And I will use the same query, but we will do the DSC which is descending.
Notice it turns blue and then you can also use ASC, but that's always there.
By default we can execute this.
And now our last names are in descending order instead of ascending.
So to sum up, the Where clause is used to restrict the results to records that meet specific search
conditions rather than all the records, and the order by clause is used to order the results by one
or more columns.
But what if we wanted to include more than one search result?
And what other operators can we use other than equals?
We can use boolean operators to include more than one search criteria using and or, or.
We can also use not to exclude values from the results.
We can also use relational operators to specify the search conditions.
We can use equal to, less than, greater than, less than or equal to.
Greater than or equal to or not equal to.
Let's see how these work.
So in this next example let's take a look at including multiple search conditions using.
And this time we're going to be looking at the employees table.
So first we will just do select star from employees just to get everything in there and take a look
at our data.
Let's only include employees where the state ID is equal to Ma.
And then we just type and that are in department ID equals 100.
And let's execute this.
So this is a list of all employees that are in the same department, the Department 100 and also live
in Massachusetts.
And then of course we can order these by last name and we can execute this.
So for the next example, let's add an or to our multiple search conditions.
And I am just going to use this same select statement.
And let's say that we want all employees who live in the state of Maryland and are either in Department
ID 100 or Department ID 200.
Now here is something interesting.
I can just copy this and I'll just change this to two.
And when we execute this.
We don't quite get the results we expected because we have people that are not in Maryland.
And so what is happening is it's evaluating this as where the state is in Massachusetts and the department
ID is in 100, or department ID is in 200.
So all of your two hundreds are the ones not in Massachusetts.
Well, what if we want to make this show employees that live in Ma and are either in department ID 100
or 200, well, we can just use parentheses to basically enforce the order of precedence on how and
and or are used.
And so now this is signifying that we want records where our employees are in Massachusetts and they're
either in department 100 or 200.
So if we execute this then all of our employees are in Massachusetts and they're in department 100 or
200.
So you can use parentheses to make the results come out the way you want.
And one of the cardinal rules of SQL programming is just because you get results back doesn't mean they're
correct.
So when you're working on queries, just because you get some records back doesn't mean it's right.
You need to look at it, evaluate it, make sure it meets what your expectation is.
And so we'll do one more exercise with multiple search conditions.
And this time we're going to exclude records using not.
So I'm going to build on this query we've been playing with.
And let's say that we want employees where they live in Massachusetts.
They're in 100 or 200.
And I'm just going to go to the next line and then we'll do another.
And and let's say that we don't want anyone living in the city of Milton.
So we just do and not city equals Milton.
So let's select this and hit execute.
And you can see that we don't have anyone here that lives in Milton.
So now let's do some exercises using relational operators in our Where clause.
So we're going to still look at our employees table.
And so let's just start over with select star from employees.
And let's say that we want everyone who's salary is greater than $40,000.
So we'll say where salary greater than.
40,000.
So let's execute that.
And then of course let's order by salary and we'll do descending which should put the highest one on
top.
So Mary Ann Shea is killing it here 138,000.
And then everybody below her is less.
So there's all our salary.
And then we can also do another example where we want everybody who's salary is greater than 40,000.
And we can do.
And department ID does not equal 300.
So let's execute this and our results.
Here is everyone whose salary is greater than 40,000.
And they're in any department other than 300.
So we can use boolean operators to have more than one search condition.
We can use not to exclude records.
And we can use our relational operators to filter our results in different ways.
Now, I haven't done examples with all of the relational operators, but I think you're probably familiar
with them.
We did the equal to we did the greater than we did the not equal to.
So in the next assignment, some of the solutions will use some of these relational operators that we
didn't use.
See you in the next assignment.