The objective of this activity is to learn how to query the database using Microsoft Access’s query-by-example (QBE) feature. (This practice is a warm-up for later discussion of SQL queries.) In particular, you are to do the following:

  1. Retrieve information from the database using Query by Example (QBE). The queries are to be saved as part of the database.
  2. Examine the SQL statements generated by each of your QBE queries.

This assignment was originally written by Dr. Kevin Treu and used in CSC-341: Database Management Systems at Furman University.

Select queries

A select query is one of several kinds of queries that might be made on a database (others including insert, update, and delete). It is a request to retrieve information from a database. In Microsoft Access, the query is typically created by giving a symbolic example of the information to be retrieved. This technique is called Query-by-Example (QBE). It uses a special form called the QBE grid. In this section, an example is presented to show the general details of the approach. At the end of the section is a list of queries that you are to create and save as part of the database.

Recall from the prior activity that the record type for the grade database is the following:

Student
SSN, First, Last, Street, City, State, Zip, Phone, Major, Class, Birthdate, Aid, Gender, HrsAttempted, HrsEarned, QualPoints
Grades
SSN, Secid, Grade
Sections
Secid, Cournum, Semester, Year, Instr
Course
Cournum, Courname, Credithr, Dept

where the primary key of each table is italicized. We must refer to this record type in creating a query.

Suppose that we need the following information from the database.

List the SSN, Last, Major, Cournum, Secid, and Grade for all students in section 82.

To carry out this request using Microsoft Access QBE, we begin by going to the Create ribbon.

Because we want to create a new query, we click the Query Design button.

On the initial display of the QBE grid, the grid may be partially hidden by the “Show Table” dialog box. A table is added to the upper portion of the QBE grid by either double clicking the name of the table or by highlighting the table name and clicking the Add button.

We will need to add Sections, Grades, and Student to the grid. (Why those three? See if you can answer that question for yourself.)

In the QBE grid, field names to be used in the query are dragged from the tables in the top part to the field row in the grid. This can also be accomplished by double-clicking on the field names. For any field whose value is to be displayed, the “Show” box for that column must be clicked. In this case, all the fields are to be displayed. Add the fields SSN, Last, Major, Cournum, Secid, and Grade to the QBE grid in this manner so the QBE grid matches the following screenshot.

QBE Grid with added tables, fields, and show boxes

In the criteria row, enter the formula =82 for the section identification field, Secid. This causes only those rows having 82 as the section id to be displayed. Criteria can be entered in this way for as many fields as needed. Fields having selection criteria can be included but not displayed (when the “Show” box is not checked). The following important rules hold for criteria on the QBE grid.

  • Criteria in the same QBE grid row are combined using the AND operator.
  • Criteria in different QBE grid rows are combined using the OR operator.

Clicking the Run button (exclamation point) in the Query Tools: Design ribbon causes the query to be executed with the result displayed in the Datasheet View window. You may toggle between the design view, the data view, and (this will become relevant soon) the SQL view by clicking the View button in the ribbon.

Query Dataset View

A given query can be given a name and saved as part of the database. This is what you will be doing in this activity.

A query can also be based on the results of a previously saved query. In many cases (e.g., in a couple of the assigned queries below), the easiest method to create a complex query is to create and save intermediate results as queries, and then combine them to get the final result.

Queries you are to create

Create the following queries. For the sake of simplicity, give your answers the names “Query1”, “Query2”, etc. (Ordinarily you’d use more meaningful names.) Save them as part of the database.

  1. List the social security number, first name, last name, and major of all CS majors.
  2. List the last name of the student, the course number, the section identification, and grade for all courses taught in 2015.
  3. List the last names and section id for students who made a grade of B in section 82 or a grade of A in section 83.

  4. List the first name and last name of all students who have made a grade of A in any course. Do not repeat names.

    Hint: Right-click above the query grid and choose “Properties”. See if you can figure out how to avoid displaying repeated records.

    Query Property Sheet

  5. List the social security number and last name of students who have made at least one A and at least one B. Do not repeat names. Do not include the names of students who made either an A or a B but not both.

    Hint: One way to do this is to create two simple queries first, then do another query on the two that you saved. Note that relating two tables in a query is just like relating them in the “Relationships” window – using drag and drop.

  6. EXTRA CREDIT: List the social security number and last name of students who have never made a grade of A.

    This one is a bit more difficult than the others. Give it your best shot and see if you can figure it out. What makes it difficult is actually that QBE doesn’t provide an obvious way to do it. As we’ll see later, it’s much easier to do with SQL directly. A hint for now is that once you relate two tables in a query (perhaps one of the query tables you created for Query #5?) you can right-click on the link between them in the query window and choose “Join Properties”. One of the options that presents itself is very useful to this query. Another useful hint is that you can search for records with no value for a particular attribute by searching for NULL.)