SQL and Conditions - speaker notes
This presentation introduces conditions in SQL.
See the notes for additional and more detailed information.
The WHERE clause allows the developer to specify the criteria for the query in the form of condition statements.
In this example, we only see records that contain a CI in the jobcode. No other records are displayed.
The SELECT * means that all fields on ther record are displayed. The select could specify fields to be seen.
Comparing this slide to the previous, a major difference is enclosing string literals in quotes versus not enclosing numeric literals in quotes.
Comparison operators used in SQL:
Operator Definition = equal to > greater than < less than <> not equal to >= greater than or equal to <= less than or equal to
Note that the column/field names are:
Remember NULL is not the same as zeros or spaces. NULL is the absence of data.
This slide looks at the logical operator AND.
AND means that both conditions must be true for the action to be taken.
With an AND relationship:
Ask the first question, if the answer is YES then move on to ask the second question. If the answer is NO, there is no further action.
If the answer to the first question is YES, ask the second question. If the answer to the second question is YES do the action. If the answer is NO, there is no further action.
This shows an AND test on the same column/field and shows it using the field specified as date. Note that the date literal is enclosed in quotes.
This slide looks at the logical operator OR which means that either one criteria or the other must be true. If both are true that is not a problem.
Ask the first question. If the answer is YES, then display the record.
If the answer to the first question is NO, you get a second chance with the OR relationship. Therefore, if the answer to the first question is NO, ask the second question. If the answer to the second question is YES display the record. If the answer to the second question is no there are no further actions.
Dates stored with two digit years have to be handled differently in the logic then dates stored with 4 digit years.
We will look at 4 digit years later.
Ths slide illustrates the RULE that ANDs are resolved before ORs.
This means the conditions around an AND get grouped together. In this case, the OR condition is therefore handled separately.
condition 1 AND condition 2
just condition 3
The developer must be aware of the rule that ANDs are resolved before ORs and use parenthesis to change this order of resolution when needed.
condition 2 or condition 3
Since AND gets resolved before OR, you need to group the OR with parenthesis
This shows the OR in parenthesis but the test is on two different fields. In the previous example it was looking at one field for two values.
Note: In some circumstances it helps to read the parenthesis as either. This is one of those cases.
In this case two OR conditions are set up and the AND combines them so that one condition in the first OR has to be true AND one condition in the second OR has to be true.
Parenthesis could have been used for clarity in this example. For example:
WHERE (bonus = 2000 and jobcode = 'CI') OR
(bonus = 500 AND jobcode = 'AP');
The WHERE can be on two lines or one line. It is on two lines here because of space constraints.
The NOT in the first example applies only to the bonus = 2000 criteria.
The NOT in the second example applies only to the salary > 45000.
The NOT before the parenthesis groups the items inside the parenthesis so the NOT operates on them as a group with an AND relationship.
With an AND or an OR relationship, if the NOT is attached to one of them it is resolved first.
If a NOT is attached to an AND or an OR condition grouped with parenthesis, the AND or OR is resolved first because it is in the parenthesis and then the NOT is resolved.
The examples on the previous two slides illustrate this logic.
BETWEEN, IN and LIKE are three operators that can be used with the WHERE clause effectively.
The IN clause looks at the list while the between checks a range.
Note in the first example the date is enclosed in quotes while in the second example there are no quotes because it is a numeric field.
The underscore is used to skip one character. Since I wanted to skip 3 characters, I used three underscores. Then since I wanted a J in the fourth character I used a J. Since I didn't care how many characters followed, I used the %.