SQL Query

Speaker Notes

Slide 1:

This slide show will introduce SQL in Access.

Slide 2:

This is the database that we are looking at. The database has one table called book.

Slide 3:

This shows the results of a query showing all columns/fields for all rows/records. The SQL equivalent of this query is shown on the next slide.

Slide 4:

This shows a basic SELECT where columns/fields are listed in the SELECT and the table name is given in the FROM.

Slide 5:

When Access translates the query into SQL it creates a query that is more complex than required if you know SQL. On this slide, I have include two simplified SQL codings.

Slide 6:

This shows the simple AND query and the results.

Slide 7:

When a query is developed, the SQL is automatically generated.

Slide 8:

The simplified version eliminates all of the unneeded parenthesis around the fields. They are their for clarity in some sense of the word!

Slide 9:

Note that the OR requires separate lines in the query.

Slide 10:

This is very similar to the AND query on the previous slides.

The simple version is shown as well!

Slide 11:

This is a case where the price must be greater than 40 AND either the pubid is 001 or the pubid is 002.

Note that when the condition is expressed in logical terms the OR has to be surrounded by parenthesis.

price > 40 AND (pubid = 001 OR pubid = 002)

The rule is ANDs are resolved before ORs. If there were no parenthesis then the two conditions around the AND would be treated as a unit and the condtion after the OR would stand alone.

Slide 12:

It should be notes that when I went back and looked at the query after these changes they were expressed differently.

Price must be greater than 40 AND either pubid = 001 OR pubid = 002.