Chapter 7 QueryByExample

1
Chapter 7Query-By-Example

• by
• Monica Chan
• CS157B
• Professor Lee

2
Structure of the chapter

• 7.1 Introduction to Microsoft Access Queries
• 7.2 Building Select Queries Using QBE
• 7.3 Using Advanced Queries
• 7.4 Changing the Content of Tables Using Action
  Queries

3
Objectives I

• In this chapter you will learn
• The main features of Query-By-Example (QBE).
• The types of query provided by the Microsoft
  Access DBMS QBE facility.
• How to use QBE to build queries to select fields
  and records.

4
Objectives II

• How to use QBE to target single or multiple
  tables.
• How to perform calculations using QBE.
• How to use advanced QBE facilities including
  parameter, ?nd matched, ?nd unmatched, crosstab,
  and autolookup queries.
• How to use QBE action queries to change the
  content of tables.

5
QBE facilities

• QBE was developed originally by IBM in 1970s to
  help users in their retrieval of data from a
  database
• QBE is now provided by most popular Data Base
  Management Systems including Microsoft Access

6
Microsoft Access 2000

• Visual approach for retrieving information from
  database using query templates
• When a query is created using QBE, Microsoft
  constructs the equivalent SQL statement in the
  background

7
Microsoft Access 2000

• Many of the Microsoft Access SQL statements
  displayed in this chapter do not comply with the
  SQL standard presented in Chapters 5 and 6

8
7.1 Introduction to Microsoft Access Queries

• When we create or open a database using Microsoft
  Access, the Database window is displayed showing
  the objects such as tables, forms, queries, and
  reports in the database.

9
DreamHome database

• See Appendix A for users requirements
  specification.

10
Introduction to Microsoft Access
11
Summary of Microsoft Access Query Types
12
Create a new query

• When we create a new query, Microsoft Access
  displays the New Query dialog box
• The dialog box shows the options
• Design View for building a new query from scratch
• Wizards for help to build the queries such as
  select, crosstab, duplicates, or unmatched
  queries

13
Figure 7.2 Microsoft Access New Query dialog box
14
7.2 Building Select Qqueries Using QBE

• Select query
• - is the most common type of query
• retrieves the data from one or more tables and
  displays the results in a datasheet (similar to a
  spreadsheet) where we can update and edit the
  records with some restrictions
• can group records and calculate sums, counts,
  averages, and other types of total

15
Select Query window

• Select Query window is a graphical QBE tool
• Because of its graphical features, we can use a
  mouse to select, drag, or manipulate objects in
  the window to define an example of the records we
  want to see
• We specify the fields and records we want to
  include in the query in the QBE grid

16
Figure 7.3 (a) QBE grid to retrieve the
propertyNo, city, type, and rent fields of the
PropertyForRent table (b) resulting datasheet
(c) equivalent SQL statement.
17
AND operator

• If the expressions are in different cells in the
  same row, which means only the records that meet
  the criteria in all the cells will be returned.

18
Figure 7.4 (a) QBE grid of select query to
retrieve the properties in Glasgow with a rent
between 350 and 450 (b) resulting datasheet
(c) equivalent SQL statement.
19
OR operator

• If the expressions are in different rows of the
  design grid, which means records that meet
  criteria in any of the cells will be returned.

20
Figure 7.5 (a) QBE grid of select query to
retrieve the properties in Glasgow with a rent
between 350 and 450 and all properties in
Aberdeen (b) resulting datasheet (c) equivalent
SQL statement.
21
Creating Multi-Table Queries

• In a database that is correctly normalized,
  related data may be stored in several tables.
• Microsoft Access automatically shows a join line
  (a join line is only shown with a symbol if
  relationship has been previously established
  between two tables) between tables in the QBE
  grid if they contain a common field.

22
Figure 7.6 (a) QBE grid of multi-table query to
retrieve the first and last names of owners and
the property number and city of their properties
(b) resulting datasheet (c) equivalent SQL
statement.
23
Totals (Aggregate) queries

• MS Access provides aggregate functions such as
  Sum, Avg, Min, Max, and Count
• To access these functions, we change the query
  type to Totals, which results in the display of
  an additional row called Total in the QBE grid
• When a totals query is run, the resulting
  datasheet is a snapshot, a set of records that is
  not updatable

24
Figure 7.7 QBE grid of totals query to calculate
the number of properties for rent in each city
(b) resulting datasheet (c) equivalent SQL
statement.
25
Figure 7.8 (a) QBE grid of select query to
calculate the yearly rent for each property (b)
resulting datasheet (c) equivalent SQL statement.
26
7.3 Using Advanced Queries

• MS Access provides a range of advanced queries
  such as
• parameter queries
• crosstab queries
• find duplicate queries
• find unmatched queries

27
Parameter query

• A parameter query displays one or more predefined
  dialog boxes that prompt the user for the
  parameter value(s) or criteria.
• Parameter queries are created by entering a
  prompt enclosed in square brackets in the
  Criteria cell for each field we want to use as a
  parameter.

28
Figure 7.9 (a) QBE grid of example parameter
query (b) dialog boxes for first and last name
of owner (c) resulting datasheet (d) equivalent
SQL statement.
29
Crosstab query

• A crosstab query can be used to summarize data in
  a compact spreadsheet format which enables users
  of large amounts of summary data to more easily
  identify trends and to make comparisons.
• The result datasheet is a snapshot.

30
Figure 7.10 (a) QBE grid of example totals query
(b) resulting datasheet (c) equivalent SQL
statement.
31
Figure 7.11 (a) QBE grid of example crosstab
query (b) resulting datasheet (c) equivalent
SQL statement.
32
Find Duplicates query

• The Find Duplicates Query Wizard is used to
  determine if there are duplicate records in a
  table or determine which records in a table share
  the same value.

33
Figure 7.12 (a) QBE for example Find Duplicates
query (b) resulting datasheet (c) equivalent
SQL statement.
34
Find Unmatched query

• The Find Unmatched Query Wizard is used to find
  records in one table that do not have related
  records in another table

35
Figure 7.13 (a) QBE grid of example Find
Unmatched query (b) resulting datasheet (c)
equivalent SQL statement.
36
Autolookup query

• It can be used to automatically fill in certain
  field values for a new record.
• When we enter a value in the join field in the
  query or in a form based on the query, MS Access
  looks up and fills in existing data related to
  that value, and displays an error if no matching
  data is found.

37
Figure 7.14 (a) QBE grid of example autolookup
query (b) datasheet based on autollokup query
(c) equivalent SQL statement.
38
7.4 Changing the Content of Tables Using Action
Queries

• When we run a select query, MS Access displays
  the resulting datasheet. As the datasheet is
  updatable, we can make changes to the data
  however, we must make the changes record by
  record.
• Action query can be used to make changes to many
  records at the same time.

39
Four types of action queries

• Make-Table Action Query
• Delete Action Query
• Update Action Query
• Delete Action Query

40
Make-Table Action Query

• It creates a new table from all or part of the
  data in one or more tables.
• The newly created table can be saved to the
  currently opened database or exported to another
  database.
• The data in the new table does not inherit the
  field properties including the primary key from
  the original table.

41
Figure 7.15 (a) Make-Table dialog box (b) QBE
grid of example make-table query (c) warning
message (d) resulting datasheet (e) equivalent
SQL statement.
42
Delete Action Query

• It deletes a group of records from one or more
  tables.
• We can use a single delete query to delete
  records form a single table, from multiple tables
  in a 11 relationship, or from multiple tables in
  a 1M relationship with referential integrity set
  to allow cascading deletes.

43
Figure 7.16 (a) QBE grid of example delete action
query (b) warning message (c) resulting
PropertyForRent and Viewing datasheets with
records deleted (d) equivalent SQL statement.
44
Update Action Query

• It makes global changes to a group of records in
  one or more tables.

45
Figure 7.17 (a) QBE grid of example update action
query (b) warning message (c) resulting
datasheet (d) equivalent SQL statement.
46
Append Action Query

• It is used to insert records from one or more
  source tables into single target table in the
  same database or in another database.

47
Figure 7.18 (a) Append dialog box (b) QBE grid
of example append action query (c) warning
message
48
Figure 7.18 (d) the NewOwner table and the
PrivateOwner table with the newly appended
records (e) equivalent SQL statement.
49
Reference

• Begg C. (2002). Database Systems. Pearson
  Education Limited