Separate speaker notes to accompany the introduction to Access using SQL. The presentation is called SQLnotes:
This slide show will introduce SQL using Access. It assumes only an introductory level of knowledge about Access.
This is the database that I am using to do my queries.
First I choose doing a query in design view the way I do when I am creating other queries.
Now I am going to select the table which is tablestu to do the query. I do this by clicking on Add and then clicking on Close since I am only using 1 table.
I have now selected the table, I click on the down arrow beside the Datasheet View icon to review SQL View. This is what I am going to select.
Since I did not select any fields when I was in the query interface, just a table, this is what appears in the SQL Query window.
Now I have to make changes to tell it what to SELECT.
In SQL, you can select all of the fields from the database records by using an * or you can choose individual fields by naming them. The named elements will be separated by a comma as shown on the next slide.
Naming fields in the SELECT statement is the same as clicking on them or otherwise bringing them down to the query. You do not have to bring down all of the fields to make the query work.
Note that the fields appear on the output in the order that they are named in the SELECT.
Notice that I said I wanted the order to be idno, name, gpa, credits and major in my SELECT and this is the order that I see the data in my output.
The where clause allows me to put conditions on the select. In this example I only want records that meet the condition or criteria of major = "CI". CI must be enclosed in quotes because it is a literal using characters or text. If it was not enclosed in quotes it would be unclear whether this was a variable name or a literal.
Note that the commands can be in either upper or lower case. In this example, the where was coded in lower case. I prefer the uppercase, because they stand out, but programming conventions seem to be leaning toward lowercase.
In this example, I am comparing gpa which is a numeric field against the number 3. The 3 is not in quotes because it is a literal and a number. Non-numeric literals must be in quotes, but numeric literals should not be in quotes.
This shows a compound AND where I want major = "CI" and gpa > 3.25. Note that this is a typical AND where both components of the condition have to be true.
This is a classic OR where one or the other of the criteria has to be true to be displayed. Either one can be true. For that matter both can be true, but that is not what we are testing for. We are testing to see if either one is true.
This shows the relationship of 2 questions when looked at when the conditions are in an AND relationship and when looked at when the conditions are in an OR relationship.
In this condition, the major has to be CI and then either of the other two criteria must be true. Remember that in logic AND gets resolved before OR. This would mean that major = "CI" and gpa > 3.25 would be grouped together and credits > 40 would stand alone. This is not what we want. We can use parenthesis to change the order. By grouping the two conditions in the or relationship with the parenthesis, we end up with major = "CI" and EITHER gpa > 3.25 OR credits > 40 which is exactly what we want.
In this example I want major = “CI” and credits outside a certain range. I do not want CI majors with credits in the range of 20 to 40. I want CI majors below the minimum of 20 and above the maximum of 40.