Queries and SQL in Access

1
Queries and SQL in Access
2
Textbook.mdb
3
Query - all fields, all rows
4
Query with SQL
All columns/fields for all rows/records means
that in this case all of the columns/fields are
listed in the SELECT clause with their table
name in front. The table name also appears in
the FROM clause
Note that SQL code end with a semi-colon.
5
SQL versions
In this version, the book.ISBN has been changed
to ISBN - this has been applied to all
columns/fields. The table name is only needed if
two tables are being used and the column/field
appears on both.
In this version, the SELECT means select all
columns/fields. You only need to list the
columns/fields by name if you want only specific
ones to appear in the results.
6
Query - AND
This is an AND query asking for all books with a
year published greater than 1998 AND a price gt
40. The results are shown below.
7
SQL for AND query
The SELECT statement is specifying certain
fields/columns that should be shown. In this
case the fields are ISBN, title, yrpub and price.
As you can see the table name is shown in front
of the column name in the format book.ISBN etc.
The FROM clause specifies the table name. The
WHERE clause gives the condition. In this case
the condition is the yrpub gt 1998 or price gt 40.
8
SQL - AND
The SELECT only lists the columns/fields by name
because there is no chance of duplication with
only one table being used.
The WHERE is coded by simply using the column
name, the comparison operator and the field being
compared against. 1998 is in quotes because it
was defined as a text field. 40 is not in quotes
because it was defined as a number (currency)
field.
9
OR Query
Yrpub must be greater than 1999 OR price must be
greater than 40. Either one is sufficient.
10
SQL - OR
Again I have eliminated the table name in the
select because there is no chance for duplication
and the multiple parenthesis in the WHERE.
11
AND - OR
The table is shown in ascending order by
ISBN. The query wants books with price greater
than 40 AND with a pubid of either 001 OR 002.
12
AND - OR
Notice the ORDER BY ISBN clause which
accomplishes the ascending sort.
Logic cond A AND either (cond B OR cond C)
Note the way the OR is expressed.