Skip to content Skip to navigation

Connexions

You are here: Home » Content » Query By Example

Navigation

Lenses

What is a lens?

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

This content is ...

Affiliated with (What does "Affiliated with" mean?)

This content is either by members of the organizations listed or about topics related to the organizations listed. Click each link to see a list of all content affiliated with the organization.
  • VOCW

    This module is included inLens: Vietnam OpenCourseWare's Lens
    By: Vietnam OpenCourseWare

    Click the "VOCW" link to see all content affiliated with them.

Recently Viewed

This feature requires Javascript to be enabled.
 

Query By Example

Module by: Nguyen Kim Anh. E-mail the author

Summary: Query –By – Example is the name of both data manipulation language and the database system that include this language. It was developed at IBM’ T.J.Watson Research Center in the early 1970s. This language was also used in IBM’s Query Management Facility. Today, some database systems for personal computers such as Microsoft Access DBMS support variant of QBE languages.

Query –By – Example is the name of both data manipulation language and the database system that include this language. It was developed at IBM’ T.J.Watson Research Center in the early 1970s. This language was also used in IBM’s Query Management Facility. Today, some database systems for personal computers such as Microsoft Access DBMS support variant of QBE languages.

QBE is a graphical query language so it has some very distinctive features.

  • Unlike other query language and programming language, QBE has two-dimensional syntax. It means: queries looks like tables.
  • Query in QBE expressed by example. Instead of specifying the procedure to obtain desired answer, the users of QBE need to give example of what is desired. The user fills in templated of relations displayed on a monitor screen with constant and example elements which is very close to domain variables. Therefore, it is said that there is a correspondence between QBE and domain relational calculus.

In this lecture, we will explore the use of QBE for retrieving data in the database by considering several example queries.

The figure below shows the templates for relations of the COMPANY database

Table 1
EMPLOYEE SSN Name Bdate Address Salary DeptId
             
Table 2
DEPARTMENT DeptId Dname Office Mng-SSN
         
Table 3
PROJECT Code Name Budget DeptId
         
Table 4
JOIN ESSN PCode StartDate
       
Table 5
EMP-DEPENDENT ESSN Dependent-Name Bdate Relationship
         

Queries on One Relation

For a single relation query, we can enter either constants or example elements in the columns of the template of that relation. An example element (domain variable) is specified as an underscore character, as in _x and constant appear without any qualification.

Query 1: Find the name, address of employees who work for department number 1

Table 6
EMPLOYEE SSN Name Bdate Address Salary DeptId
    P._x   P._y   1

For the above query, the system will look in to the relation EMPLOYEE and look for tuples that have 1 as the value for DeptId column. For each such tuple, the value of Name attribute is assigned to variable x, the value of Address attribute is assigned to variable y. Because the command P. appear before variables x, y , the values of these variables will

be printed as the results of query. If a variable appears only once in a query, it may be omitted. Therefore, the previous query could be rewritten as

Table 7
EMPLOYEE SSN Name Bdate Address Salary DeptId
    P.   P.   1

Query 2: Find employee’s name of all employees who work for department number 1

QBE eliminate duplication automatically. Thus, in order to avoid duplicate elimination, we insert command ALL. after the P. command.

Table 8
EMPLOYEE SSN Name Bdate Address Salary DeptId
    P.ALL.       1

Query 3: Find the employees whose salary is greater than 30.000

For this query, we want to display the whole tuple if the value in the Salary is > 30.000. To do so, we place the P. command in the column headed by the relation name.

Table 9
EMPLOYEE SSN Name Bdate Address Salary DeptId
P.         > 30.000  

As we can see, a query in QBE can use arithmetic comparison. However, the comparisons can only involve only one arithmetic expression on the right hand side of the operation. QBE supports all operation such as > , <, = , ≥, ≤,  ( different).

Query 4: Find the name of dependents of all employees except employee number 123456674

Table 10
EMP-DEPENDENT ESSN Dependent-Name Bdate Relationship
  ¬ 123456674 ¬ 123456674 size 12{ neg "123456674"} {} P.    

Query 5a: Find the employee’s number of employees who join in project number 3 or in project number 4

Table 11
JOIN ESSN PCode StartDate
  P._x 3  
  P._y 4  

Query 5b: Find the employee’s number of employees who take part in both project number 3 and project number 4

Table 12
JOIN ESSN PCode StartDate
  P._x 3  
  ­_x 4  

The primary pupose of variables in QBE is to force values of certain tuples to have the same values on certain columns. For the above query, QBE will find all pairs of tuples that have the same values of ESSN attribute but the value of PCode in one tuple is 3 and the value of this attribute in other tuple is 4.

Query 5c: Find the employee’s number of employees who join in project number 3 but donot take part in project number 4

Table 13
JOIN ESSN PCode StartDate
  P._x 3  
  _x ¬ 4 ¬ 4 size 12{ neg 4} {}  

Query 6: Find the employee’s number of employees who join in the same projects with employee number 123456674

Table 14
JOIN ESSN PCode StartDate
  123456674 _x  
  ­P._y _x  

Queries on Several Relations

QBE supports queries that span several relations. The connections among those relations are archived through variables that force certain tuples to have the same value on certain attributes.

Query 7: Find the name of employees who works for department Human Resource.

Table 15
EMPLOYEE SSN Name Bdate Address Salary DeptId
    P._y       _x
Table 16
DEPARTMENT DeptId Dname Office Mng-SSN
  _x Human Resource    

Query 8: Find the name of the employees who do not join in any project

Table 17
EMPLOYEE SSN Name Bdate Address Salary DeptId
  _y P.        
Table 18
JOIN ESSN PCode StartDate
¬ ¬ size 12{ neg } {} ­_y    

This query is expressed by placing the sign’ ¬¬ size 12{ neg } {}’ under the relation name. For this expression, QBE finds all the _y values such that: There is a tuple in the EMPLOYEE relation whose SSN is the domain variable y and there is no tuple in the relation JOIN whose ESSN is the same as in the domain variable y. The ‘ ¬¬ size 12{ neg } {}’ sign under the relation name can be read as “there does not exists”. It is different from when this sign is place under attribute. In that case, the ‘ ¬¬ size 12{ neg } {}’ means ‘≠’.

The Condition Box

The Condition Box is a additional feature provided by QBE. This Box is used to specify complex constraints which might involve any domain variable in the queries.

Query 9: Find the SSN and name of employees whose salary is between 25.000 and 40.000

Table 19
EMPLOYEE SSN Name Bdate Address Salary DeptId
  P._y P._x     _z  
Table 20
CONDITIONS
­_z ≥ 25000 and_z ≤ 40000

Query 10: Find the SSN and name of employees whose salary is > 50000 and works for department number 1 or department number 2

Table 21
EMPLOYEE SSN Name Bdate Address Salary DeptId
  P._y P._x     _z _t
Table 22
CONDITIONS
­_z > 50000 and (_t = 1 or _t = 2)

Query 11: Find the name of projects that have budget that are at least twice as large as the budget of the Green House project

Table 23
PROJECT Code Name Budget DeptId
    Green House _x  
    P._z _y  
CONDITIONS  
­_y ≥ ( 2 * _x)  

The Result Relation

Once the results to be displayed appear in the same relation, the query is used only command P. in certain columns of the templates of the relation. If the results of the query include attributes from several relations, we need a mechanism to display the specified attributes as a single table. In QBE, for this purpose, we can use a temporary relation called Result Relation.

Query 12: Find the employee’s name, dependent’s name and their relationship for all the employees in department number 1

Table 24
EMPLOYEE SSN Name Bdate Address Salary DeptId
  _x _y       1
Table 25
EMP-DEPENDENT ESSN Dependent-Name Bdate Relationship
  _x _z   _t
Table 26
RESULTS Name Dependent-Name Relationship
P. _y _z _t

Ordering the results

User can specify the order in which tuples of the result set are displayed. Two commands to do so are called AO (stands for ascending order) and DO (descending order). By placing these commands in the appropriate column, we can make the result be ordered.

Query 13: List SSN, Name, Bdate of employees in department number 1, ordering the list by the values of attribute Name in ascending order.

Table 27
EMPLOYEE SSN Name Bdate Address Salary DeptId
  P._x P.AO._y P._z     1

In QBE, users can also do sorting in multiple columns.

Query 14: List SSN , Name, Bdate of employees in department number 1, ordering the list firstly by the values of attribute Name in ascending order then by the values of attribute Bdate by descending order

Table 28
EMPLOYEE SSN Name Bdate Address Salary DeptId
  P._x P.AO(1)._y P.DO(2)._z     1

Grouping and Aggregate Operations

A grouping operator G. can be specified in a column to indicate that tuples should be grouped by the value of that column. Once the tuples is grouped, we can apply the aggregate operations in each group. The available aggregate functions in QBE are: AVG. ; MIN.; MAX.; SUM.; and CNT.

Suppose we wish to eliminate duplicates when an aggregate operator is used, we must add command UNQ as postfix of the aggregate functions. Otherwise, if we do not want to do duplicate elimination, use ALL command.

Query 15: Find the average salary of all employees in the company

Table 29
EMPLOYEE SSN Name Bdate Address Salary DeptId
          P.AVG.ALL._x  

Query 16: Find the total number of employees who at least once join in a project

Table 30
JOIN ESSN PCode StartDate
  ­P.CNT.UNQ._y    

Query 17: Find the number of employees in each department

Table 31
EMPLOYEE SSN Name Bdate Address Salary DeptId
  P.CNT.ALL.         P.G.

Query 18: Find the name of the department which has more than 20 employees

Table 32
EMPLOYEE SSN Name Bdate Address Salary DeptId
  CNT.ALL._y         G._x
Table 33
DEPARTMENT DeptId Dname Office Mng-SSN
  _x P.    
Table 34
CONDITIONS
­CNT.ALL._y > 20

Query 19: Find the name of the employees who joins in every project

Table 35
EMPLOYEE SSN Name Bdate Address Salary DeptId
  _y P.        
Table 36
JOIN ESSN PCode StartDate
  G._y CNT.UNQ._z  
Table 37
PROJECT Code Name Budget DeptId
  _x      
Table 38
CONDITIONS
­CNT.UNQ._z = CNT.UNQ._x

Modification of Database in QBE

In addition to the data manipulation capability, QBE has the data definition facilities. Users can specify table structure, alter the structure of existing tables, add, remove or update the information in the table etc.

For modifying the database, we use the following operator:

  • I. : For inserting new tuples into a relation
  • D. : For deleting some information in a relation
  • U. : For updating information in a relation

Insertion

Insert a project 12 of the name “Network” has budget of 100.000 and this project belongs to department number 1

Table 39
PROJECT Code Name Budget DeptId
I. 12 Network 100000 1

We insert a tuple that contains only partial information: Insert a new employee “John Smith” with SSN 123141411, birthdate is ‘12/12/1978’, work for department 4

Table 40
EMPLOYEE SSN Name Bdate Address Salary DeptId
  123141411 John Smith 12/12/1978     5

Deletion

In QBE, we can delete the whole tuple or values in a certain columns

Delete employee John Smith

Table 41
EMPLOYEE SSN Name Bdate Address Salary DeptId
D.   John Smith        

Delete the salary of employee 123456789

Table 42
EMPLOYEE SSN Name Bdate Address Salary DeptId
  123456789       D.  

Delete the project with budget <20.000

Table 43
PROJECT Code Name Budget DeptId
D. _x   <20000  
Table 44
JOIN ESSN PCode StartDate
D.   _x  

Note that to delete a project we must delete the tuples in JOIN relation that associated with that project.

Updates

As for the deletion, updates can be done for a set of tuples, a tuple or a values of certain attribute.

Budget of Project Green House is increase from 100.000 to 200.000

Table 45
PROJECT Code Name Budget DeptId
    Green House U.200000  

Salary of all employees in department 1 are to be increased by 15%

Table 46
EMPLOYEE SSN Name Bdate Address Salary DeptId
U.         _x * 1.15 1

Content actions

Download module as:

PDF | EPUB (?)

What is an EPUB file?

EPUB is an electronic book format that can be read on a variety of mobile devices.

Downloading to a reading device

For detailed instructions on how to download this content's EPUB to your specific device, click the "(?)" link.

| More downloads ...

Add module to:

My Favorites (?)

'My Favorites' is a special kind of lens which you can use to bookmark modules and collections. 'My Favorites' can only be seen by you, and collections saved in 'My Favorites' can remember the last module you were on. You need an account to use 'My Favorites'.

| A lens I own (?)

Definition of a lens

Lenses

A lens is a custom view of the content in the repository. You can think of it as a fancy kind of list that will let you see content through the eyes of organizations and people you trust.

What is in a lens?

Lens makers point to materials (modules and collections), creating a guide that includes their own comments and descriptive tags about the content.

Who can create a lens?

Any individual member, a community, or a respected organization.

What are tags? tag icon

Tags are descriptors added by lens makers to help label content, attaching a vocabulary that is meaningful in the context of the lens.

| External bookmarks