Official Query Language Crib Sheet

Relational algebra: Duplicates are eliminated, general expressions are formed out of smaller subexpressions using:

• select (p a predicate)
• project (s a list of attributes)
• rename (x a relation name)
• union
• set difference
• cartesian product
• natural join
• intersect
• division
• assignment
• update

Tuple relational calculus:

Nonprocedural language. A query in the tuple relational calculus is expressed as

i.e. the set of tuples for which predicate is true.

Logical connectives and , plus existential and universal quantifiers ( and ) are allowed. Implies () also used (If , then ).

A sample query:

Domain Relational Calculus:

An expression is of the form

where the represent domain variables, and is a formula.

Logical connectives and , plus existential and universal quantifiers ( and ) are allowed. Implies () also used (If , then ).

A sample query:

SQL: Typical query has the form:

where each represents an attribute, each a relation, and is a predicate.

• SQL allows duplicates.
• distinct: remove duplicates
• set operations union, intersect and minus
• predicates can involve
  • logical connectives and, or and not.
  • arithmetic expressions on constant or tuple values.
  • between operator for ranges of values.
  • string matching operators % (any substring) and _ (underscore, matching any character).
  • like operator to use string matching.
  • not like for string mismatching.
• in and not in operations for set membership.
• equality or inequality operators with some or all.

• contains: set containment

• order by makes tuples appear in sorted order

• desc specifies descending order

• asc specifies ascending order
• average value - avg
• minimum value - min
• maximum value - max
• total sum of values - sum
• number in group - count

• tuples satisfying where clause are placed into groups by the group by clause
• the having clause is applied to each group
• groups satisfying the having clause are used by the select clause to generate the result tuples
• if no having clause is present, the tuples satisfying the where clause are treated as a single group

• delete: delete tuples
• insert into: insert tuples
• update: update tuples
• create view v as: create view
• create table: create a relation
• drop table: destroy a relation
• alter table: change a relation

QBE: Uses skeleton tables.

• Domain variables are preceded by an underscore character.
• Constants appear without any qualification.
• A P. in variable causes printing.
• A P.ALL. prefix suppresses duplicate elimination.
• A P. in front of the row prints all attributes.
• The domain variable may be omitted if it is not used elsewhere.

• Arithmetic expressions are allowed.
• Comparison operators are allowed, space on left hand side is left blank.
• Queries on several relations require several skeleton tables.
• condition box may be used.
• Logical expressions , and .
• Temporary result relation can be created.

• The order in which tuples are displayed can be controlled by adding the command AO. (ascending order) or DO. (descending order) to the print command.
• QBE includes the aggregate operators AVG, MAX, MIN, SUM and CNT. As QBE eliminates duplicates by default, they must have ALL. appended to them.
• To override the ALL. we can add UNQ. (unique).

• To compute functions on groups, we use the G. operator.
• Delete: use D. instead of the P. operator. Whole tuples may be deleted, or only some columns.
• Insertion uses the I. operator.
• We can update individual attributes with the U. operator. Fields left blank are not changed.

Sample query: