Introduction to Select Queries

Instructor: Charles Ott

Lecture Outline

Text: Chapter 2, pp 64-87

1. Select queries based on one table.
2. Sorting the results.
3. Using LIKE.

Query-by-Example

There's no use putting all of a company's information into a database unless you can get it back out in a helpful way. A "query" is a question that can be answered from the database, such as "What customers live in Tinley Park?" or "What were our sales last month?" Actually, we will use the word "query" two ways: it means the question, and it also means the answer. The answer is usually a list of records.

However, computers are pretty stupid. No one would "hire" a computer for a business except that they work cheap and are very fast. When you want to put a query to the database, you have to express it in a way that even a dumb computer can understand:

To ask a question of a smart person, you would say:

"Please make an alphabetical list of all the customers in Tinley Park, with names and addresses."

... and she would do it, that's all.

To ask a question of a dumb computer, you would say:

"Here is an example of a record from the CUSTOMER database. Find all the records that look like this: that is, where the City is 'Tinley Park'. Then make a list which includes all the fields marked with an 'x' for each record. Sort the list by the contents of the Customer Name field."

This is called "query-by-example". The advantage of doing this with DBMS software is that you can save this query under a name such as "Tinley Park Customers", and re-run it as often as you like. Each time you run the query, the program will very quickly compile a current list of all the Tinley Park customers. The answer will look like this:

This answer looks a lot like a database table: it's as though we had a special table in the database called "Tinley Park Customers" that had fields called "First Name", "Last Name" and "Address". In fact, once you have created this query, you can use it almost as though it really was a table.

The Access Query-by-Example Grid

Access has a particularly nice way to describe your query. This grid lets you pick fields out of a table and arrange them the way you want them:

This grid means "From the Customer table, show me the CustomerNum, Customer Name and Street fields for every record where the City is 'Tinley Park', sorted in ascending order by Customer Name". Notice that the City field is unchecked here, which means that it won't show up in the result list. We don't need to display this because it's always going to be "Tinley Park".

A criterion (plural "criteria") is a way to make a decision about which records to include in the result list. In this case, the criterion is "City = 'Tinley Park'". We will see more examples of more sophisticated criteria later.

Choosing Columns to Display

Your results list can include all of the fields (columns) in the original, or only as many as you need. You can drag field names into any column. You can also have fields that you need for sorting, but which don't need to appear in the results. In this case, uncheck the "Show" box and the data in that column will be used for the query, but not displayed.

Sorting and Filtering

Queries such as the ones we have defined so far will show you all fields you have asked for in all the records in all the linked tables. Also, these records will be in the order they happen to be in in the table, which usually is fairly random. (Records in a database could be sorted in some order, but this is quite unusual. Ordinarily, records are kept in a completely undetermined order. They are sorted only in the result of a query.)

To show records in a sorted order, you add the sort criterion "Ascending" (A to Z, or 0 to 9) or "Descending" (Z to A, or 9 to 0). You can also sort on two different columns: for example, you can sort all the Customers numerically by credit limit, and then alphabetically by last name within each value of credit limit. But note this restriction:

Important: The only way that Access sorts on multiple columns is by position in a query -- the columns are sorted from left to right. Therefore, you must arrange the columns in the query in the order you want them sorted. (Later on, you will see how to print this data in a different order if you need to do that.)

You don't need to write the sort order into the query: when you view the results, you can select a column and choose the menu Records/Sort/Ascending (or Descending) to sort temporarily on that column. There is also a toolbar button for this.

"Filtering" means to show only that data that matches some criterion. You can write this into the query, or you can select one field of one record in the datasheet view and choose the menu Records/Filter/Filter by Selection. There is also a toolbar button for this.

"Like"

If you are searching for part of a phrase, you can use the LIKE keyword and the wildcard (*) character. The asterisk means "any number of any characters, including none".

Multiple Criteria and Sorting

Important: When you select sorting and/or filtering from the menus or toolbar, or as part of a query, you must remember that all of the criteria you have selected apply at the same time. Examples:

• If you have a query that shows only customers from Tinley Park, and you then select Filter by Selection for the last name "Jones", you will see only people named Jones who also live in Tinley Park. You will not see customers named Jones if they live anywhere else. You will not see customers in Tinley Park unless they are also named Jones.
• If you a list sorted alphabetically and then add a filter to see only people older than 50, you will see an alphabetical list of people over 50. You won't see all the people.

Adding up criteria like this is called a logical "AND". We will talk more about this later.

Week 3 Homework

Create these five select queries for your fictitious company database (which you entered into Access last week), and print the result sets. For queries 4 and 5, you must also write the question on your page.

1. List all the customers in one zip code.
2. List all products in ascending order by price. (Note: if your Product table does not include prices, you must add that field now.)
3. List all employees alphabetically by last name.
4. (Write a new question similar to the above, create and run the query, and print the result. You must write the question on the paper.)
5. (Write a new question similar to the above, create and run the query, and print the result. You must write the question on the paper.)