Brackets and order - SQL Tutorial

- [Instructor] Now the management has tasked us with a new request and they would like us to get a list of all invoices that are greater than $1.98 from any cities whose names start with P or D. Now, if we recall from our last query, which we still have here, we've satisfied the criteria for cities that start with P or cities that start with D. The one twist in the current request is to have all invoices that are greater than $1.98. So let's respond to this request by altering our WHERE clause, and saying: total greater than $1.98. In addition to this, you want to include the AND operator and say the complete WHERE clause criteria, which is now saying: total is greater than $1.98 AND BillingCity LIKE P OR BillingCity LIKE D. Now let's run our current statement and observe the result. We've gone to 43 rows, and let's take a look. If we really pay attention to our results here, we might find that we have some things that don't look quite right. One of the criteria is that all invoices must be greater than a dollar and 98 cent. And if that's the case, if we take a look at record number 4, we have an invoice item that is less than $1.98. What has gone wrong? This is one of the key things we should pay attention to when we're responding to a query that's asking for multiple criteria, like the one that we're doing now. When it comes to multiple criteria, we should employ something that's referred to as PEMDAS. Most likely, if you're in the European part of the world, it's referred to as this, or BEMDAS if you're in the US. This stands for parenthesis, exponents, multiplication, and division, addition, and subtraction. The only difference with BEMDAS is we've replaced parenthesis with brackets. Now, what does this mean? This refers to the order of operation. The order in which a query is processed. Now, if we take a look at our result here, what has been happening is, we are processing the query, like this. SQL is saying, "Give me all the records that are greater than $1.98 AND the BillingCity LIKE P." Okay? For all the records that are satisfying this particular part of the criteria, if we observe, the billing cities that start with P all satisfy this criteria and that they're all above $1.98. But the billing cities that do not start with P, like Dublin here, does not satisfy this request. As it is, the request is saying just include any city regardless of what that invoice amount is once it starts with D, and that includes even the ones that are below $1.98. We are able to control our results by including brackets that satisfy the PEMDAS or BEMDAS rule. So now let's alter our SQL statement here, and do this. Instead of leaving it at as is, we're going to put brackets around this part of our criteria. Now, what this does is that it directs SQL to say, "First, bring us all of the cities that satisfy this criteria." That is, all of the cities that start with P or start with D. Once we have satisfied this criteria from this subset, then give me all of the invoice totals that are greater than $1.98. So again, we're at 43 records. Let's rerun this altered statement and observe the result. We went down to 35 records, and if we take a look at the BillingCity, they all start with P or D. Great. The other part of the criteria is that the total must be greater than $1.98. And if we take a look, we have every one of our criteria being above this amount. 1.99 seems to be the lowest so far, and I'm scrolling down all the way to the end, and we see that none of our records are below that $1.98. And this is a very important concept when it comes to the order of operation. When we have multiple criteria that's being asked for in our request, we must consider the order in which it is going to be executed. And by specifying our brackets around the area that we want to execute first, we employ the PEMDAS or the BEMDAS rule and we get the correct result. So effectively we can tell WSDA Music Management that all of the invoices that are greater than a dollar and 98 cent from cities that start with P or D are 35 invoices. And here they are.