```The Languages

Query By Example (QBE) – Base on
domain relational calculus.

Quel – Base on tuple relational calculus.

Datalog – modeled after the Prolog
Language.
I. QBE: Introduction
For Data Manipulation

1. It has two dimensional syntax, so it
requires two dimensions for its expressions.

2. QBE queries are expressed “by
example.”
QBE tables

Users specify a
query by filling in
tables.
Reserves
Books
Student sid
sid bid day
bid btitle type
sname class age
Basics
 Print names and ages of all students:
Student Sid Sname Class age
P._N
P._A
• Print all fields of students who are at
least sophomores in ascending order by
(class, age):
Student
P.
Sid
Sname Class
AO(1). >
sophomore
age
AO(2).
Continue

Names of student younger than 20 or older than 25:
Student
Sid Sname Class age
P.
P.

>25
<20
Duplicates not eliminated by default:
Student
UNQ
Sid Sname Class
P.
age
<25
Print unique student names older than 25.
Join Queries

Joins are accomplished by repeating variables
Student Sid
Sname Class
_Id
P.S
Reserves Sid
Bid
_Id
Age
day
`11/30/01’
sid is the common attribute that join the two tables.
• Print students who borrowed a book on
11/30/01.
Join Queries

Types of books reserved by students who have
reserved a book for 11/30/01 and are older than
25.
Student Sid Sname Class age
_Id
Reserves
_S
Sid Bid day
_Id _B
Books
>25
`11/30/01’
Bid Bname type
_B
P.
Aggregates
QBE supports: AVG, COUNT, MIN, MAX, SUM
Student
Sid Sname Class
age
_Id G.
G.P.AO _A P.AVG._A

G. are the group by fields – All tuples have
the same values.

Unnamed columns – Print result of an
expression.
Conditions Box

Used to express conditions involving 2 or more
columns.
 Conditions can be expressed involving a group.
Student Sid
Sname Class age CONDITIONS
P.

_A 20 < _A AND _A <25
Print student names that are between the ages of 20 and
25.
Inserting & Deleting Tuples

Tuple insertion:
Student
Sid
Sname Class
I.
1369 Lisa
age
Senior 23
• Tuple deletion: Delete all reservations for students with age<23
Student Sid
_Id
Reserves Sid
D.
_Id
Sname Class
Bid
age
< 23
day
II. Quel – Basic Structure

Range of t is r
- Declares t to be a tuple variable restricted
to take on values of tuples in relation r.
•Retrieve (t.attribute)
- The retrieve clause is similar in function
to the select clause of SQL.
Continue…

Where P
- The where clause contains the selection
predicate.
Quel Query Structure
Range of t is r
Retrieve (t.A)
Where P

Each t is a tuple variable.
 Each r is a relation.
 Each A is an attribute.
 The notation t.A denotes the value of tuple
variable t on attribute A.
Example
Find the names of all customers having a
loan at the bank.
Range of t is borrower
Retrieve (t.CustomerName)
Example: Tuple Variables
Certain queries need 2 variables over
the same relation.
Example:
Find all customers who live in the same city as Smith.
Range of s is customer
Range of t is customer
Retrieve ( s.CustName )
Where t.CustName = “Smith and
s.CustCity = t.CustCity
Aggregate Function
 Aggregate
functions in Quel compute
functions on groups of tuples.
 An
aggregate expression appear
anywhere a constant may appear.
For Example
In a where clause.
Find the average balance for all San Jose accounts.
Range of t is account
Retrieve avg (t.balance Where t.Branch = “San Jose”)
Modification of Database
Deletion:

The form of a Quel deletion is:
range of t is r
delete t
where p
•t can be implicitly defined.
•Predicate P can be any valid Quel predicate.
If the where clause is omitted, all tuples
in the relation are deleted.
Example:

Delete all of Lee’s account record:
range of t is depositor
delete t
where t.CustName = “Lee”
Insertion

Insertions are expressed in Quel using the
append to.
Insert the account 123456 at the San Jose
branch with a balance of \$5000.00:
append to account (branch = “San Jose”
account = “123456”
balance = “5000”)

Updates are expressed in Quel using the
replace command
Increase all account balances by 5 percent:
range of t is account
replace t (balance = 1.05 * t.balance)
III. Datalog – Basic Structure

Logic based language that allows recursive
queries.

A Datalog program consists of a set of rules
that defines views.
Example:

Define a view relation vt containing account
numbers and balances for accounts at the
San Jose branch with a balance of over
\$100.
vt (A B) :- account ( “San Jose”, A B) B > 100
for all A,B
if
(“San Jose”, A,B) E account A and B > 100
then (A,B) E vt
Datalog Rules

A positive literal has the form:
p(t1,t2,…,tn)
 A negative literal has the form:
not p(t1,t2,…,tn)
p
is the name of the relation with n attributes.
Each t is a constant or variable.
Continue…

Rules are built out of literals and have the
form:
p(t1,t2,…,tn) :- L1,L2…Ln
 Each L is a literal
 Head – the literal p(t1,t2,…,tn)
 Body – the rest of the literals.
Semantics of a Rule

An instantiation rule is the result of
replacing each variable in the rule by some
constant.
 Rule defining v1:
v1 (A,B):- account(“SanJose”, A,B), B>100
 An instantiation Rule:
v1 (123456, 300) :- account (“San Jose”,
“123456”,300) 300 > 100
Semantics of Recursion in Datalog

The view relations of a recursive program containing a
set of rules K are defied to contain exactly the set of facts
/.  facts / are derived from rules K.
Facts / is compute by a recursive procedure called
Datalog-Fixpoint: Procedure Datalog- Fixpoint
/ = set of facts in the database
repeat
Old./ = /
/ = / U infer (K,/)
until / = Old./

At the end of the procedure, infer ( K, / )= /
 Datalog-Fixpoint will computes all the facts / until
the rules in the program has all negative literal or
no more true record according to the rules K.
```