• No results found

Query-by-Example (QBE)


Academic year: 2021

Share "Query-by-Example (QBE)"

Show more ( Page)

Full text


Query-by-Example (QBE)

Module 3, Lecture 6

Example is the school of mankind, and they will learn at no other.


QBE: Intro

❖ A “GUI” for expressing queries.

– Based on the DRC!

– Actually invented before GUIs.

– Very convenient for simple queries.

– Awkward for complex queries.

❖ QBE an IBM trademark.

– But has influenced many projects


`Example Tables’ in QBE

Boats bid bname color

Sailors sid

sname rating age


sid bid


❖ Users specify a query by

filling in example tables, or skeletons; we will use these skeletons in our examples.



Sailors sid sname rating age

P._N P._A

❖ To print names and ages of all sailors:

Print all fields for sailors with rating > 8, in

ascending order by (rating, age):

Sailors sid sname rating



AO(1). >8


❖ QBE puts unique new variables in blank

columns. Above query in DRC (no ordering):


And/Or Queries

Sailors sid sname rating age P. < 30 P. > 20

Names of sailors younger than 30 or older than 20:

Names of sailors younger than 30 and older than 20:

Sailors sid sname rating age _Id P. < 30 _Id P. > 20

Names of sailors younger than 30 and rating > 4:

Sailors sid sname rating age _Id P. > 4 < 30

Note: MiniQBE uses a slightly different syntax!



Sailors sid sname rating age

UNQ. P. < 30

Single row with P: Duplicates not eliminated by

default; can force elimination by using UNQ.

Sailors sid sname rating age ALL. _Id P. < 30

_Id P. > 20

Multiple rows with P: Duplicates eliminated by


Join Queries

Sailors sid sname rating age _Id P._S > 25

Reserves sid bid day

_Id ‘8/24/96’

❖ Names of sailors who’ve reserved a boat for

8/24/96 and are older than 25 (note that dates and strings with blanks/special chars are quoted):

❖ Joins accomplished by repeating variables.

Note: MiniQBE

uses double


Join Queries (Contd.)

❖ Colors of boats reserved by sailors who’ve

reserved a boat for 8/24/96 and are older than 25 :

Sailors sid sname rating age

_Id _S > 25

Reserves sid bid day

_Id _B ‘8/24/96’

Boats bid bname color _B ‘Interlake’ P.


Join Queries (Contd.)

Sailors sid sname rating age

_Id P. P.

Reserves sid bid day 22 _B

_Id _B

❖ Names and ages of sailors who’ve reserved some


Unnamed Columns

Sailors sid sname rating age

_Id P. _R _A P._D P.(_R/_A)

Reserves sid bid day

_Id _D

❖ Useful if we want to print the result of an

expression, or print fields from 2 or more relations. – QBE allows P. to appear in at most one table!

MiniQBE allows


“Negative Tables”

❖ Can place a negation marker in the relation


Sailors sid sname rating age _Id P._S

Reserves sid bid day _Id _B



MiniQBE uses NOT or ~.

❖ Variables appearing in a negated

table must also appear in a positive table!



Sailors sid sname rating age

_Id G. G.P.AO _A P.AVG._A

❖ QBE supports AVG, COUNT, MIN, MAX, SUM

– None of these eliminate duplicates, except COUNT

– Also have AVG.UNQ. etc. to force duplicate elimination

The columns with G. are the group-by fields; all tuples

in a group have the same values in these fields.

—The (optional) use of .AO orders the answers. —Every column with P. must include G. or an


Conditions Box

Sailors sid sname rating age G.P. _A


❖ Used to express conditions involving 2 or more

columns, e.g., _R/_A > 0.2.

❖ Can express a condition that involves a group,

similar to the HAVING clause in SQL:

❖ Express conditions involving AND and OR:

Sailors sid sname rating age

P. _A



Find sailors who’ve reserved all boats

Sailors sid sname rating age P.G._Id



❖ A division query; need aggregates (or update

operations, as we will see later) to do this in QBE.


sid bid


_Id _B1

❖ How can we modify this query to print the names

Boats bid bname color _B2


Inserting Tuples

Sailors sid sname rating age


_N LIKE ‘C%’

❖ Single-tuple insertion:

Students sid name login age

_Id _N _A

Inserting multiple tuples (rating is null in tuples

inserted below):

Sailors sid sname rating age


Delete and Update

Sailors sid sname rating age

_Id < 4

Delete all reservations for sailors with rating < 4







Increment the age of the sailor with sid = 74

Sailors sid sname rating age


Restrictions on Update Commands

❖ Cannot mix I., D. and U. in a single example table,

or combine them with P. or G.

❖ Cannot insert, update or modify tuples using values

from fields of other tuples in the same table. Example of an update that violates this rule:

Sailors sid sname rating age

john _A

joe U._A+1

Should we update every Joe’s age?


Find sailors who’ve reserved all boats (Again!)

Sailors sid sname rating age _Id P._S

Reserves sid bid day _Id _B

❖ We want to find sailors _Id such that there is no

boat _B that is not reserved by _Id:

Boats bid bname color




❖ Illegal query! Variable _B does not appear in a

positive row. In what order should the two


A Solution Using Views

Sailors sid sname rating age _Id P._S

Reserves sid bid day _Id _B

❖ Find sailors who’ve not reserved some boat _B:

Boats bid bname color



BadSids sid

I. _Id

❖ Next, find sailors not in this `bad’ set:

Sailors sid sname rating age _Id P._S

BadSids sid _Id




❖ QBE is an elegant, user-friendly query language

based on DRC.

❖ It is quite expressive (relationally complete, if

the update features are taken into account).

❖ Simple queries are especially easy to write in

QBE, and there is a minimum of syntax to learn.

❖ Has influenced the graphical query facilities

offered in many products, including Borland’s Paradox and Microsoft’s Access.


Related documents

Field query examples for example, sorts options for fields only partially matching score from a sorted result by using kql, such as a phrase.. This example queries can improve

Easter means games, Easter quizzes, Easter crime stories and Easter decorations for family and friends as a way of celebration. Food Traditions

Results indicate that the lazy learning instance based classifier performed slightly better than the neural network with a maximal accuracy of 97.86% and they

We conduct a specification search using the posterior deviance criterion of Spiegelhalter, Best, Carlin, and van der Linde (2002) for a disequilibrium model of the Polish

O’Neill held that “mere words” of “vulgar or low abuse” did not constitute an affray. The court contrasted this with the rule that “if persons arm themselves with deadly or

The evidence in chief of a witness giving opinion evidence must explain how the field of specialised knowledge possessed by the witness, by reason of training, study or

With the Datatrans payment application the merchant has the option to add credit card information to his customer profiles without offending against the data security regulations

years of the new 100 m wind product from ECMWF at 16 km horizontal resolution is 20 % of maximum capacity of an ex- emplary wind turbine power curve.. This is five percent of

A multivariate analysis of the untargeted serum metabolomes, combined with a targeted analysis of candidate FIBs previously reported in urine samples from the same study,

If the shock ever loses oil or if it makes sounds of excessive topping out, stop riding the bicycle immediately and have the shock inspected by a dealer or call RockShox4.

How many examples of the example uses the knack you want to mysql nested outer select queries that meet the following?. Used in nested query

Rather than working on the impossible task of extracting unambiguous meaning from images, we should provide the user with the tools he needs to drive the database in the areas of

To search queries for example of aggregation queries are term query elasticsearch term queries using both possible without drowning you should match instead... Elasticsearch Or

Impacts to NM 109 and other local roads considered whether access was restricted during or after construction. Other than Alternative G, there will be temporary closures to tie-in

ceremonial and court ritual in the Sultanate of Oman after the accession of Sultan Qaboos bin Said in 1970.. It will attempt to show that while the creation of rituals of

AGM Range Longhaul Surface Pumps Rugged, high capacity pumps with heavy duty bearing housings Stainless steel wear resistant rotor with hard chrome plating and stainless steel

One grouping and redaction platform that access, with sql group query example for contributing an input queries we will see the original letter values are grouped, and online

Employee object into clause like meta data in the highlighter chosen for digital marketing campaigns, hibernate query language example you list to do all users a language sql query

COPY %1 A: shift if not (%1)==() goto LOOP  {Beginning with the first 

2 % prevalence of different respiratory disorders by gender: PNE, pneumonia; CRF, chronic respiratory failure; ACOS, asthma-Chronic obstructive pulmonary disease overlap syndrome;

Considering the commercial implications, health risks asso- ciated with poisonous bamboo shoots, and nonavailability of optimized method for removal of TCC, we highlight the

Background: This study evaluated the effectiveness of Extra-amniotic saline infusion (EASI) in comparison with that of intracervical Prostaglandin E2 (PGE2) gel for cervical