SQL and Conditions - speaker notes

Slide 1:

This presentation introduces conditions in SQL.

See the notes for additional and more detailed information.

Slide 2:

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.

Slide 3:

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.

Slide 4:

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

Slide 5:

Note that the column/field names are:

pay_id

name

jobcode

startdate

salary

bonus

Slide 6:

Remember NULL is not the same as zeros or spaces. NULL is the absence of data.

Slide 7:

This slide looks at the logical operator AND.

AND means that both conditions must be true for the action to be taken.

Slide 8:

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.

Slide 9:

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.

Slide 10:

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.

Slide 11:

OR relationship

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.

Slide 12:

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.

Slide 13:

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.

Slide 14:

condition 1 AND condition 2

OR

just condition 3

Slide 15:

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.

Slide 16:

condition 1

AND

either

condition 2 or condition 3

Since AND gets resolved before OR, you need to group the OR with parenthesis

Slide 17:

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.

Slide 18:

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.

Slide 19:

Parenthesis could have been used for clarity in this example. For example:

SELECT *

FROM first_pay

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.

Slide 20:

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.

Slide 21:

The NOT before the parenthesis groups the items inside the parenthesis so the NOT operates on them as a group with an AND relationship.

Slide 22:

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.

Slide 23:

BETWEEN, IN and LIKE are three operators that can be used with the WHERE clause effectively.

Slide 24:

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.

Slide 25:

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 %.