Fall Semester 2002


Lab Assignment Two: Querying a Database Using the Select Query Window

In this lab you should:

  1. Turn in your first in-lab assignment.

  2. Start working on Project Two (described below)

For Project Two

You could, if you wanted, print this and take it with you to lab, on Friday.

Before we start, let's make a brief presentation of database queries.

1. Introduction

The basic idea behind a table is simple. Conceptually, a database table is comprised of rows and columns. Columns represent a description of the contents of the table. The rows contain the actual data in the in the table.

2. Designing Tables

Database applications are usually comprosed of multiple table. Each table is designed to contain a specific type of object. For example the Marina table contains instances of marinas, as abstracted by our application.

2.1 Simple Design

The design of a table comprises:

2.2 Primary Keys

A key in a database system is used to give you access to a particular record in a database. Given the key the database management system should return a unique record.

In a relational database management system, a key usually consists of a set of attributes (columns) of a table that uniquely determines a record (row) of that table. In other words, no two rows in the table can have exactly the same values for the key columns. A simple example is the social security number in an employee database.

Sometimes a combination of columns is used as a key, otherwise we use a calculated key: most database systems support this capability through some sort of auto-numbering function. In this case, a unique number is assigned to each row of data added to the table. The two tables in our database application have keys which are similar in meaning to a calculated key (although the keys are determined by the user of the database).

3. Simple Queries

Database systems are excellent tools for storing and retrieving information. in a RDBMS data is stored in tables, specifically designed for the application. In addition, database systems provide mechanisms for retrieving information: certain commands, called queries, are used to retrieve information from the database.

There are many different interface for specifying and executing queries. As a generalization the book provides the Query Construction Method (QCM) as a technique for developing database queries.

3.1 The Query Construction Method

Providing the ability to retrieve the information from a database is an essential function of a relational database system. QCM provides a simplified technique for creating database queries.

Learning to use the QCM is as easy as filling in the following table:

DATA What pieces of information are required?
LOCATION Where is the information located?
CONDITION Which rows of data should be retrieved?
FORMAT How should the results be presented?
Answering the first two questions, DATA and LOCATION is straightforward. You just need to name the appropriate columns and tables. The third section, CONDITION, turns out to be where database queries become really interesting. We can avoid using this section in only the simplest types of queries.

The fourth section deals with the FORMAT of the results. This depends, by and large, on the intended recipient of the query results.

3.2 Simple Queries

In this section we will use QCM to create simple queries.

Use the tables below as a workbook. We'll work these queries out completely in lecture and labs.

1. List all marina numbers and marina names, as well as the technician numbers of the technicians that service that specific marina (from the Marina table).

DATA
LOCATION
CONDITION
FORMAT
2. List the entire information in the Marina table.

DATA
LOCATION
CONDITION
FORMAT
3. List the marina number, name, warranty and non-warranty amounts for the marina whose number is EL25.

DATA
LOCATION
CONDITION
FORMAT
4. List the marina number, name, warranty and non-warranty amounts for the marinas whose names start with Fe.

DATA
LOCATION
CONDITION
FORMAT
5. List the marina number, name, address, warranty and non-warranty amounts for the marinas located in Burton.

DATA
LOCATION
CONDITION
FORMAT
6. List the marina number, name, warranty and non-warranty amounts for those marinas whose non-warranty amounts are 0 (zero).

DATA
LOCATION
CONDITION
FORMAT
7. List the marina number, name, warranty and non-warranty amounts for those marinas that have a warranty amount greater than 1,000 dollars.

DATA
LOCATION
CONDITION
FORMAT
8. List the marina number, name, warranty and non-warranty amounts, as well as the technician number for those marinas whose warranty amounts are greater than 1,000 and whose technician number is 36.

DATA
LOCATION
CONDITION
FORMAT
9. List the marina number, name, warranty and non-warranty amounts, as well as the technician number for those marinas whose warranty amounts are greater than 1,000 or whose technician number is 36.

DATA
LOCATION
CONDITION
FORMAT
10. List all the cities that have a marina serviced by Bavant Marine Services, sorted in alphabetical (ascending) order.

DATA
LOCATION
CONDITION
FORMAT
11. List the marina number, name, tech number, and warranty amount for all marinas, sorted by technician number (ascending). Within the collection of marinas having the same technician, the marinas should be sorted by descending warranty amount.

DATA
LOCATION
CONDITION
FORMAT
3. Elementary SQL

The SQL standard defines a formal syntax for queries. The syntax includes the structure of commands and keywords. The basic syntax of an SQL query is as follows:

SELECT field list
FROM table list
WHERE condition
This syntax is closely matched with the QCM.

The select query window in Access (that you will be using in lab) is offering a QBE (Query By Example) interface. You will see that for each query specified in QBE there is an SQL equivalent that Access can display for you if you want to.

Next lecture(s) and lab(s) will cover:

  1. joining tables.
  2. restricting records in a join.
  3. using calculated fields in a query.
  4. calculating statistics.
  5. using criteria in calculating statistics.
  6. grouping.
After this we should all have the lab manual and the lecture textbook, I hope.


So now here is Project Two: Querying a database using the select query window. Querying the Bavant Marine Services database. Opening the database. Creating a new query. Using the select query window. Displaying selected fields in a query. Running a query. Printing the results of a query. Returning to design view. Closing a query. Including all fields in a query. Clearing the design grid. Entering criteria. Using text data in criteria. Using wildcards. Criteria for a field not in a result. Using numeric data in criteria. Using comparison operators. Using compound criteria. Using AND criteria. Using OR criteria. Sorting data in a query. Sorting on multiple keys. Omitting duplicates.
1. Open the Bavant Marine Services database.

The database opens, and the Bavant Marine Services: Database window displays.

2. If the Tables object is not already selected, click Tables on the Objects bar.

Make sure that the Marina table is selected.

3. You are now ready to create your first query.

Click the New Object: AutoForm button arrow, on the Database window toolbar.

4. Point to Query on the New Object: AutoForm menu, and click on it.

The New Query dialog box displays. Make sure Design View is selected, then click the OK button.

5. The Query1: Select Query window displays and the Query Design toolbar replaces the Database window toolbar.

Maximize the Query1: Select Query window, then point to the dividing line that separates the upper and lower panes of the window: the mouse pointer will change shape to a two-headed arrow with a horizontal bar.

6. The upper pane contains a field list for the Marina table. The lower pane contains the design grid, which is the area where you specify fields to be included, sort order, and the criteria the records you are looking for must satisfy.

Drag the line down; the two panes will be resized. Then move the mouse pointer to the lower edge of the field box (in the upper pane) so it changes shape to a two-headed arrow.

7. Drag the lower edge of the field box down far enough so all fields in the Marina table are visible.

Now the Query1: Select Query window is maximized, contains a field list for the Marina table in the upper pane of the window, and an empty design grid in the lower pane.

8. Double-click the Marina Number field to include it in the query: the Marina Number field is included as the first field in the design grid.

Double-click the Name field to include it in the query. Include the Tech Number field using the same technique. The Marina Number, Name, and Tech Number fields are now included in the query.

9. Now point to the Run button on the Query Design toolbar and click on it. The query is executed and the results display. The Query Datasheet toolbar replaces the Query Design toolbar. The Sort Ascending button on the Query Datasheet toolbar now occupies the position of the Run button.

The data displays. Notice that an extra blank row, marking the end of the table, displays at the end of the results. If you want you can print the results of the query: simply click the Print button on the toolbar (when the printer is ready).

10. What query did you just design and run?

Write your answer here:




11. Now point to the View button arrow on the Query Datasheet toolbar, then click on it. The View button menu displays.

Click Design View. The Query1: Select Query window displays.

12. Click the Close Window button for the Query1: Select Query window. The Microsoft Access dialog box will display. Clicking the Yes button saves the query, and clicking the No button closes the query without saving.

Click the No button. The Query1: Select Query window closes and is removed from the desktop.

13. Open a maximized new Query1: Select Query window containg a field list for the Marina table in the upper pane and an empty design grid in the lower pane (see steps 3-7 above to create the query and resize the window).

A maximized Query1: Select Query displays. The two panes have been resized.

14. Double-click the asterisk in the field list: the table name, Marina, followed by a period and an asterisk is added to the design grid, indicating that all fields are included. Then point to the Run button on the Query Design toolbar, and click on it.

The results display, and all the fields in the Marina table are included. The Tech Number may not, as it may not fit on the screen. Click the View button on the Query Datasheet toolbar to return to Design View.

15. What query did you just design and run?

Write your answer here:



16. Click Edit on the Menu bar. The Edit menu displays. Click Clean Grid. Access clears the design grid so you can enter your next query.

Now, one by one, double-click the Marina Number, Name, Warranty, and Non-warranty fields to add them to the query.

17. The Marina Number, Name, Warranty, and Non-warranty fields are added to the design grid. Point to the Criteria row for the first field in the design grid: the mouse pointer on the Criteria entry for the first field (Marina Number) has changed shape to an I-beam.

Click the Criteria row, type EL25 as the criterion for the Marina Number field. The criterion is entered. Then click the Run button: the results display.

18. Only marina EL25 is included. The extra blank row contains $0.00 in the Warranty and Non-warranty fields. Unlike text fields, which are left blank, number and currency fields in the extra row contain 0, and the format is specific.

What query did you just design and run? Write your answer below:




19. Click the View button to return to Design view. Click the Criteria row under the Marina Number field and then use the Delete or Backspace key to delete the current entry (EL25).

Then click the Criteria row under the Name field. Type LIKE Fe* as the entry. (The criterion is entered). Then click the Run button.

20. The results display. Only the marinas whose names start with Fe are included.

What query did you just design and run?





21. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Address, Warranty, and City fields in the query. Type Burton as the criterion for the City field and then point to the City field's Show checkbox.

22. Click the Show checkbox to remove the check mark. The City field will not show in the result. Access has added quotation marks before and after Burton automatically. Now run the query by clicking the Run button.

The results display. The City field does not display. The only marinas included are those located in Burton. What query did you just design and run?




23. Click on the View button to return to Design view. On the Edit menu, click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Warranty and Non-warranty fields in the query. Type 0 (zero) as the criterion for the Non-warranty field. You need not enter a dollar sign or decimal point in the criterion).

24. Run the query by clicking the Run button. The results display. Only those marinas that have a non-warranty amount of $0.00 are included.

What query did you just design and run?




25. Click the View button to return to Design view. On the Edit menu click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Warranty, and Non-warranty fields in the query. Type >1000 as the criterion for the warranty field. The fields are selected and the criterion is entered.

26. Run the query.

The results display; only those marinas that have a warranty amount greater than $1,000 are included. What query did you just design and run? Write your answer below:





27. Click the View button to return to Design view. Include the Tech Number field in the query. If necessary, click the Criteria entry for the Warranty field, and then type >1000 as the criterion for the Warranty field. Click the Criteria entry for the Tech Number field and then type 36 as the criterion for the Tech Number field.

Criteria have now been entered for the Warranty and Tech Number fields. Because both conditions are on the same line, both must be true.

28. Now run the query. Only the single marina whose warranty amount is greater $1,000.00 and whose technician number is 36 will be included.

What query did you just design and run?
 





29. Click the View button to return to the Design view. Click the Criteria entry for the Tech Number field. Use the Backspace key to delete the entry ("36"). Click the or row (below the Criteria row) for the Tech Number field, then type 36 as the entry. The criteria are entered for the Warranty and Tech Number fields on different lines. Because conditions are on different lines, only one needs to be true.

Now run the query. The results display. Only those marinas whose warranty amount is greater than $1,000.00 or whose technician number is 36 are included. What query did you design and run? Write your answer below:




30. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Include the City field in the design grid. Click the Sort row below the City field, and then click the Sort row arrow that displays.

A list of available sort orders displays. Click Ascending; Ascending is selected as the sort order. Now run the query. The results contain the cities from the Marina table. The cities display in alphabetical order. Duplicates, that is, identical rows, are included. What query did you just design and run? Please write your answer below:




31. Click the View button to return to Design View. On the Edit menu, click Clear Grid. Include Marina Number, Name, Tech Number, and Warranty fields in the query in this order. Select Ascending as the sort order for the Tech Number field and Descending as the sort order for the Warranty field. Then run the query.

The results display. The marinas are sorted by technician number. Within the collection of marinas having the same technician, the marinas are sorted by descending warranty amount. (Overall order is by technician number). What query did you just design and run? Write your answer here:




32. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Include the City field, click Ascending as the sort order and right-click the second field in the design grid (the empty field following City).

You must right-click the second field or you will not get the correct results. The shortcut menu displays. Click Properties on the shortcut menu.

33. The Query Properties sheet displays. Click the Unique Values property box, and then click the arrow that displays to produce a list of available choices for Unique Values. Click Yes and then close the Query Properties sheet by clicking its Close button.

Run the query. The results display and the cities are sorted alphabetically. Each city is included only once; what query did you just design and run? Write your answer below:






Project Two continued: Joining tables. Restricting records in a join. Using calculated fields in a query. Calculating statistics. Using criteria in calculating statistics. Grouping. Saving a query. Closing a database. Case perspective summary. Project summary.
1. Open the Bavant Marine Services database and make sure that the Marina table is selected.

Open a new Select Query window.

2. Right-click any open area in the upper pane of the Query1: Select Query window.

The shortcut menu displays.

3. Click Show Table on the shortcut menu.

The Show Table dialog box displays.

4. Click Technician to select the Technician table and then click the Add button.

Close the Show Table dialog box by clicking the Close button.

5. Expand the size of the field list so all the fields in the Technician table display.

Include the Marina Number, Name, and Tech Number fields from the Marina table and the Last Name and First Name fields from the Technician table.

6. The fields from both tables are now included. Run the query.

The results display and contain data from both the Marina and the Technician tables.

7. What query did you just design and create?

Write your answer below:




8. Click the View button to return to Design view. Add the Warranty field to the query.

The Warranty field displays in the design grid. Type >1000 as the criterion for the Warranty field and then click the Show check box for the Warranty field to remove the check mark.

9. A criterion is entered for the Warranty field and the Show check box is empty, indicating that the field will not display in the results of the query.

You're ready to run the query.

10. Run the query.

The results display. Only those marinas with a warranty amount greater than $1,000 display in the result. The Warranty field does not display.

11. Can you write the QCM (what, where and how) for the above query?

Write your answer here:




12. Click the View button to return to Design view. Right click any field in the Technician table field list.

The shortcut menu displays. Click Remove Table to remove the Technician table from the Query1: Select Query window.

13. On the Edit menu, click Clear Grid.

Include the Marina Number and Name fields.

14. Right-click the Field row in the third column in the design grid and then click Zoom on the shortcut menu. The Zoom dialog box displays.

Type Total Amount:[Warranty]+[Non-warranty] in the Zoom dialog box that displays.

15. The expression you typed displays in the dialog box. Click the OK button.

The Zoom dialog no longer displays. A portion of the expression you entered displays in the third field in the design grid.

16. Run the query and check the results.

The results display. Microsoft Access has calculated and displayed the results.

17. What is the SQL for this new query?

Can you write the QCM (what, where and how) for it?





18. Click the View button to return to Design view.

On the Edit menu, click Clear Grid.

19. Right-click the grid.

The shortcut menu displays.

20. Click Totals on the shortcut menu and then include the Warranty field. Point to the Total row in the Warranty column.

The Total row now is included in the design grid. The Warranty field is included, and the entry in the Total row is Group By. The mouse pointer, which has changed shape to an I-beam, is positioned on the Total row under the Warranty field.

21. Click the Total row in the Warranty column, and then click the arrow that displays.

The list of available selections displays.

22. Click Avg.

Avg is selected.

23. Run the query.

The result displays, showing the average warranty amount for all marinas.

24. Click the View button to return to Design view.

Include the Tech Number field in the design grid. Produce the list of available options for the Total row entry just as you did when you selected Avg for the Warranty field.

25. The list of available selections displays.

The Group By entry in the Tech Number field may not be highlighted on your screen depending on where you clicked in the Total row.

26. Use the vertical scroll bar to move through the options until the word, Where, displays.

Click Where. Type 36 as the criterion for the Tech Number field.

27. Where is selected as the entry in the Total row for the Tech Number field and 36 is entered as the Criterion.

Run the query.

28. The result displays giving the average warranty amount for marinas of technician 36.

Click the View button to return to Design view. On the Edit menu, click Clear Grid.

29. Include the Tech Number field. Include the Warranty field, and then select Avg as the calculation in the Total row.

The Tech Number and Warranty fields are included. Group By currently is the entry in the Total row for the Tech Number field, which is correct; thus, it was not changed.

30. Run the query.

The result displays showing each technician's number along with the average warranty amount for the marinas of that technician.

31. Click the view button and then click the Save button.

Type Average Warranty Amount by Technician and then point to the OK button.

32. The Save As dialog box displays with the query name you typed.

Click the OK button to save the query, and then close the query by clicking the Query window's Close Window button.

33. Access saves the query and closes the Query1: Select Query window.

Once you have saved the query, you can use it at any time in the future by opening it.

34. To open a saved query, click the Queries object in the Database window, right-click the query, and then click Open on the shortcut menu.

The query is run against the current database.

35. Thus, if changes have been made to the data since the last time you ran it, the results of the query may be different.

A Case Perspective Summary and Project Summary follow.

CASE PERSPECTIVE SUMMARY

You have been successful in assisting the management of Bavant Marine Services by creating and running queries to obtain answers to important questions. You used various types of of criteria in these queries. You joined tables in some of the queries. Some Bavant Marine Services queries used calculated fields and statistics. Finally, you saved one of the queries for future use.

Project Summary

In Project 2 you created and ran a variety of queries. You learned how to select fields in a query. You used text data and wildcards in criteria. You also used comparison operators in criteria involving numeric data. You combined criteria with both AND and OR. You learned how to sort the results of a query, how to join tables, and how to restrict records in a join. You created computed fields and calculated statistics. You learned how to use grouping as well as how to save a query for future use.

You can now start working on, and finish Homework Assignment One.

Chapters One and Two from the Pratt and Adamski textbook are relevant so far.

Projects One and Two from the Shelly Cashman lab manual are relevant also, for now.


Last updated: Sep 5, 2002 by Adrian German for A114/I111