QBE QueryByExample

1
QBE Query-By-Example

• Presented by Angela Tong
• CS157A
• Dr. Sin Min Lee

2
Introduction

• Query By Example (QBE)
• QBE and its variants are widely used in database
  system on PCs.
• QBE is the name of both a data-manipulation
  language and an early database that included this
  language.
• QBE database system was developed at IBMs
  T.J.Watson Research Center in the early 1970s.

3
Overview

• Basic Structure
• Queries on One Relation
• Queries on Several Relations
• The Condition Box
• The Result Relation
• Ordering the Display of Tuples
• Aggregate Operations
• Modifying the Database
• Deletion
• Insertion
• Updates
• QBE in Microsoft Access

4
Basic Structure

• QBE has two-dimensional syntax Queries look like
  tables.
• QBE queries are expressed by example.
• Close correspondence with domain relational
  calculus.
• Non-procedural.
• Queries are expressed using skeleton tables.
• User selects the skeletons needed.
• User fills in skeletons with example rows.
• An example row consists of constants and example
  elements which are really domain variable.
• Domain variables are preceded by an underscore
  character.
• Constants appear without any qualification.

5
Queries on One Relation

• Examples
• To find all loan numbers at the Perryridge
  branch

We bring up the skeleton for the loan relation
Filling these information
Perryridge
P._x

• P. before the variable causes displaying the
  value of x.

6
Queries on One Relation (cont)

• If a variable does not appear more than once in a
  query, it may be omitted.

P.ALL.

• To suppresses duplicate elimination.

• P. in front of the row displays the entire
  relation.

7
Queries on One Relation (cont)

• Find the names of all branches that are not
  located in Brooklyn

• Find the loan numbers of all loans with a
  loan amount
• of more than 700

8
Queries on One Relation (cont)

• Find the loan numbers of all loans made jointly
  to Smith and Jones

• Find all customers who live in the same city as
  Jones

9
Queries on Several Relations

• QBE allows queries span several different
  relations
• The connections among the various relations are
  achieved through variables that force certain
  tuples to have the same value on certain
  attributes.

10
Queries on Several Relations (cont)

• Examples

• Find the names of all customers who have a loan
  from the
• Perryridge branch

11
Queries on Several Relations (cont)

• Find the names of all customers who have both
  an account
• and a loan at the bank

• Find the names of all customers who have both
  an account
• at the bank, but who do not have a loan from
  the bank

12
The Conditional Box

• QBE includes a condition box feature that
  allows the expression
• of general constraints over any of the
  domain variables.
• QBE allows logical expressions to appear in a
  condition box.
• The logical operators are and and or
• Examples
• Find the loan numbers of all loans made to
  Smith, to Jones

13
The Conditional Box (cont)

• Find all account numbers with a balance between
  1300 and 1500

• Find all account numbers with a balance between
  1300 and 2000, but not exactly 1500

14
The Conditional Box (cont)

• Find all branches that have assets that are at
  least twice as large as the assets one branches
  located in Brooklyn

• Find all branches that have assets greater than
  those of at least one branch located in Brooklyn

15
The Conditional Box (cont)

• Find all branches that are located in either
  Brooklyn or Queens

16
The Result Relation

• If the result of a query includes attributes from
  several relation schemas, we can declare a
  temporary result relation that includes all the
  attributes of the result of the query.
• Example
• Find the customer-name, account-number, and
  balance for all account at the Perryridge branch

The result skeleton table
17
Ordering of the Display of Tuples

• QBE offers user control over the order in which
  tuples in a relation are displayed.
• Insert the command AO (ascending order) or the
  command DO (descending order) after P. in the
  appropriate column.
• Example
• List all customers who have an account at the
  bank in ascending alphabetic order

18
Ordering of the Display of Tuples (cont)

• QBE also provides a mechanism for sorting and
  displaying data in multiple columns.
• Example

Account number sorts first in ascending order
Balances for each account sort second in
ascending order
19
Aggregate Operations

• QBE includes the aggregate operations AVG, MAX,
  MIN, SUM, and CNT.
• Must postfix these operators with ALL to create a
  multiset on which the aggregate operations is
  evaluate.
• The ALL ensures duplicates are not eliminated.
• To eliminate duplicates, use the UNQ operator.
• To compute functions on groups of tuples use the
  G.

20
Aggregate Operations (cont)

• Examples
• Find the total balance of all the accounts
  maintained at the Perryridge branch

• Find the total number of customers who have an
  account at the bank

21
Aggregate Operations (cont)

• Find the average balance at each branch

• To find the average account balance at only those
  branches where the average account balance is
  more than 1200, we add the following condition
  box

22
Aggregate Operations (cont)

• Find all the customers who have accounts at each
  of the branches located in Brooklyn

23
Modification of the Database1) Deletion

• Deletion of tuples from a relation is expressed
  in the same way as a query. However, we use D.
  instead of P.
• A D. command operates on only one relation.
• To delete tuples from several relations use one
  D. operator for each relation.
• Examples
• Delete customer Smith

• Delete the branch-city value of the branch
  whose name is Perryridge

24
Modification of the Database1) Deletion (cont)

• Delete all loans with a loan amount between 1300
  and 1500

25
Modification of the Database2) Insertion

• To insert data into a relation, we either specify
  a tuple to be insert or write a query whose
  result is a set of tuples to be insert.
• Example
• Insert account A-9732 at the Perryridge
  branch has a balance 700 to branch relation
• We can also insert a tuple that contains only
  partial information.
• Example
• Insert a new branch name Capital
  and city Queens, but with null assets
  value

26
Modification of the Database2) Insertion (cont)

• Insert tuples on the basic of the result of a
  query into a relation.
• Example

27
Modification of the Database3) Updates

• Change one value in a tuple without changing all
  values in the tuple.
• Use U. operator for updating.
• QBE does not allow users to update the primary
  key fields.
• Example Update the assets value of the
  Perryridge branch to 10,000,000

• To update a value by using previous value, we
  add the variable
• after U.
• Example Suppose the interest payments are being
  made, and all balances are to
  be increased by 5

28
QBE in Microsoft Access

• The original QBE was designed for a text-based
  display environment.
• Access QBE (or graphical query-by-example GQBE)
  is designed for a graphic display environment.
• A minor difference in GQBE version is that the
  attributes of the table are written one below the
  other, instead of horizontal.

29
QBE in Microsoft Access (cont)

• A more significant difference is the GQBE uses a
  line linking attributes of 2 tables, instead of a
  shared variable.
• An interesting feature of Access QBE links
  between tables are created automatically, on the
  basic of the attribute name.
• Another minor difference in Access QBE is that it
  specifies attributes to be printed in a separate
  box, called the design grid, instead of using a
  P. in the table.

30
THE END

• THANK YOU FOR LISTENNING!