Transcript Chapter7QBEgood

Chapter 7
Query-By-Example
by
Monica Chan
CS157B
Professor Lee
1
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
2
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.
3
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, find matched, find unmatched,
crosstab, and autolookup queries.
How to use QBE action queries to change the
content of tables.
4
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
5
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
6
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
7
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.
8
DreamHome database
See Appendix A for users’ requirements
specification.
9
Introduction to Microsoft Access
10
Summary of Microsoft Access
Query Types
11
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
12
Figure 7.2 Microsoft Access New Query dialog box
13
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
14
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
15
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.
16
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.
17
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.
18
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.
19
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.
20
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.
21
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.
22
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
23
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.
24
Figure 7.8 (a) QBE grid of select query to calculate the yearly rent for
each property; (b) resulting datasheet; (c) equivalent SQL statement.
25
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
26
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.
27
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.
28
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.
29
Figure 7.10 (a) QBE grid of example totals query; (b) resulting datasheet; (c)
equivalent SQL statement.
30
Figure 7.11 (a) QBE grid of example crosstab query; (b) resulting datasheet; (c)
equivalent SQL statement.
31
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.
32
Figure 7.12 (a) QBE for example Find Duplicates query; (b) resulting datasheet;
(c) equivalent SQL statement.
33
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
34
Figure 7.13 (a) QBE grid of example Find Unmatched query; (b) resulting
datasheet; (c) equivalent SQL statement.
35
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.
36
Figure 7.14 (a) QBE grid of example autolookup query (b) datasheet based on
autollokup query; (c) equivalent SQL statement.
37
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.
38
Four types of action queries
Make-Table Action Query
Delete Action Query
Update Action Query
Delete Action Query
39
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.
40
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.
41
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 1:1 relationship, or
from multiple tables in a 1:M
relationship with referential integrity set
to allow cascading deletes.
42
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.
43
Update Action Query
It makes global changes to a group of
records in one or more tables.
44
Figure 7.17 (a) QBE grid of example update action query; (b) warning message;
(c) resulting datasheet; (d) equivalent SQL statement.
45
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.
46
Figure 7.18 (a) Append dialog box; (b) QBE grid of example append
action query; (c) warning message;
47
Figure 7.18 (d) the NewOwner table and the PrivateOwner table with the newly
appended records; (e) equivalent SQL statement.
48
Reference
Begg C. (2002). Database Systems.
Pearson Education Limited
49