Query By Example (QBE)

A query by example (QBE) is a special visual display terminal to ask a question using graphical interface. Query by example is a graphical way of asking simple to very complex database questions. The functional level of QBE is approximately same as SQL. In QBE, designer pull relevant fields from the selected tables and also apply the selection criteria graphically to retrieve the desired results. Most of the database management systems provide facility of QBE. QBE has a two-dimensional syntax as both criteria and operations are specified in tabular form. It offers a mechanism to DBA to raise a database query without the complexity of typing out the commands using SQL. Basically, user need to fill the selection criteria in tables on the screen. These tables are created partly by the database management system and rest of it by the user and then these queries are converted to SQL, automatically by DBMS so that it can be interpreted by the database and the results are displayed to the user in tabular form.

In QBE, examples are used to specify the query. The user need not to remember the names of all of the attributes or relations as they are graphically represented. Every operator in QBE ends with ”.” like ‘P’ stands to print the result. Example elements are indicated by underlining like PA. Example elements are example of the possible answer to the query. A constant value need not be underlined.

1. QBE Dictionary

The dictionary in a database is used to store data about tables. QBE provides a built-in dictionary that is represented to user as a collection of tables. There are two built-in tables in QBE data dictionary. The first is, Table-in which the names of all the tables are stored and second is, Domain-in which the names of all the domains currently known to the system are stored. Now what is Domain ? While creating a table it is required to specify the names of columns and also the types of those columns i.e., Char, Float(15) etc. By using built in data types, if you make your own data type and gives it a name then it is known as Domain. Domain increases re-usability. Ex. Consider, Two tables Employee and Student and both tables having field Employee-Name and Student-Name of type Char(50). Now define a Domain ‘Name’ of type Char(50) in one of the table and provide it to second table. It is possible to  define  a  new  Domain  only  inside  the  table.

A typical  QBE graphical  interface is  shown  in Figure  7.4.

• Retrieval of table  names  :  The following  query  retrieve  names  of all tables.

At the table name position, specify P. and “P.” stands for “print”. It indicates the values that  are  to  appear  in  the result.

• Retrieval of column names of a table : The following query retrieves names of all columns of a given table.

User enters the name of table (Table_name) followed by P. and get name of all columns of given table.

2. Data Types

The data types supported by QBE are Float, Date, Time, Char(n), Char(variable length character string).

3. Data Definition Functions

Data definition functions are used to define new tables etc., in the database. Following are the data  definition  functions  :

• Creation of a new table : The following query creates a table

First user mention the table name and column names. Here, first I. Creates a directory entry in table Table of data dictionary for table Flight_Master and second I. creates a directory entry in  the same  table for  all the  5 columns  of  table Flight_Master.

After that user needs to provide additional information for each column. It includes the name of domain. If user wants to create new domain then data type of domain is required to be specified. Second, whether or not the column acts as a primary key. Third, whether or not an index known as “inversion” is to be built on the column (Index is used for faster searching). By default, QBE assumes that each column is a part of primary key and is to be indexed.  The  additional  information  need to  create  Flight_Master  is  follows:

In this example,  all the  domains are  not known  so type  information is  also provided.

Ex. Create a table Schedule_Master.

The additional information needed as follows:

In this example, domains Air_Name and Aero_Name are already known to QBE so there is no need to specify their type.

• Creating a snapshot : Suppose, you want to retrive data from one or more tables and save the result to database as a new This process is known as creation of snapshot. Domain specifications for the columns of this table are inherited by underlying tables. Key and Inversion are  taken  by  default  but  can  be  changed.

It creates a snapshot “Result” and specifications are inherited by tables Flight_Master and Schedule_Master.

• Dropping a table : In QBE, a table can be dropped only if it is So, the user needs to delete all records of table before  dropping it. Drop table  “Result”.

Step 1. Delete all entries of table “Result”

Step 2. Drop table “Result”

Note: D.is a deletion operator.

• Expanding a table : In QBE, user can add new column to an existing table only if it is currently Dynamic addition of a new column is not supported by QBE. Following are the steps to  expand table  “Result” by  adding one  more column  e.g., Airline_Name.

Step 1.  Define  a new  table  similar  to the  existing  table  with the  new  column.

Step 2. Insert records from old table to new table using a multiple-record insert.

Step 3. Delete all records from old table

Step 4. Drop old table

Step 5. Change the name of new table to that of the old table

Note: U.is a update operator.

4. Update Operations

Update operations are used to insert new records, to modify and deletion of existing records in an  existing  table  in  database.  Following  are the  Update  operations:

• Single record update : is updation operator. Update the number of seats in table Flight_Master of Airline_Name “Kingfisher” and Aeroplane_No “K100”  by 250.

Primary key values cannot be updated and record to be updated is identified by its primary key value. You can also put “U.” below the Flight_Master.

• Single record update based on previous value : To update a record based on previous value, user enters a row representing old value and another represents the new Here “U.” indicates the new value. Increase Fare of Airline “Jet Airways” and Aeroplane number “J150” by Rs. 1000.

• Multiple record update : Doubles the distance of all airways having Departure_City “New York” in table Schedule_Master.

Note: An example element need not actually appear in the resulting set, or even in the original set, it is totally arbitrary.

Here user specify Primary key “Airline_Name” by example element Airline. It fetches all the records of table because example element is totally arbitrary.

At the same time on QBE screen, it is possible to update more than one table (Several updates can  be  entered  simultaneously).

• Single record insertion : is insertion operator. To insert a record user must provide primary key value that should be non-null and distinct from all existing primary key values in the table. Any  blank  value  can  be  considered  as  NULL.

Insert a new record in table Flight_Master with Airline_Name “Spice Airways”, Aeroplane_ No “S222” on date 12-6-08 with seats 350.

• Multiple record insertion : User can insert more than one record at a single time from one table to If user specify a part of primary key or used an example element, QBE retrives more  than one  record  at a  time.

Insert all records from Schedule_Master to table Result for all airways having Departure_ City “New Delhi”.

• Single record deletion : is deletion operator. User must specify all primary key values to delete a single record. Delete a record from Flight_Master having Airline_Name “Sahara”, Aeroplane_No “SH10”  and  Flight_Date  1-2-2007

• Multiple records deletion : Delete all records from table

5. Data Retrieval Operations

Following are the  two tables, that  are used for  all Data Retrieval  Operations.

• Flight_Master (Details of all flights)

• Schedule_Master (Schedules of all flights)

• Simple retrieval : Get names of all Airlines from table

is similar to

QBE automatically eleminates duplicate entries. To avoid the elimination of duplicate entries user  can  specify  the  keyword ALL.

• Retrieve whole data  of  a table :  Retrieve  all  data from  Schedule_Master

is similar to

• Qualified retrieval : Retrieval of records who satisfy a given condition is known as qualified retrieval.
• Qualifled retrieval using comparison operators : QBE supports following comparison operators > (greater than), < (less than), = (equal to), Ø= (not equal to), <= (less than and equal to), >= (greater than and equal to).

Ex. Retrieve Airlines name and Aeroplane numbers from table Flight_Master having Airfare more  than  500

• Qualifled retrieval using logical operators : QBe supports following logical operators AND, Or, and

Ex. Retrieve Airlines name and Aeroplane numbers from table Flight_Master having airfare more  than  700  or  seats  less than  100.

To specify OR condition, it is necessary to specify them in separate rows with different example elements because if same example element is used then it means same example element must satisfy both conditions.

Ex. Retrieve Airline_Name and Aeroplane_No from Flight_Master where airfare is greater than  500  and seats  are  greater than  250.

To specify AND condition, it is necessary to specify them in separate rows with same example elements.

• Retrieval with ordering : The records from database can be retrieved in ascending order or in descending order.
  • Retrieval with ascending order : is the operator used to retrieve records in ascending order.

Ex. Retrieve Airline_Name and Aeroplane_No from Flight_Master in ascending order to Airfare

• • Retrieval with descending order : is the operator used to retrieve records in descending order.

Ex. Retrieve Airline_Name and Departure_City from Schedule_Master in descending order to  Distance.

• Retrieval using a link : Links are similar to the nested select queries in A link is used to retrieve records from a table that satisfy the condition based on values on another table.

Ex. Retrieve all Flight_Date from Flight_Master where departure city is Delhi

Here, Airline acts as a link between Flight_Master and Schedule_Master. QBE first retrieves Airline_Names having departure city ‘Delhi’ from Schedule_Master and then matches them with values  in  Flight_Master.

• Retrieval using negation : Retrieve all Flight_Date from Flight_Master where departure city is not Delhi.

¬ is NOT operator. The query may be parapharased. “Print Flight dates for Airlines Airline such  that  it is  not  the  case that  Airline  is having  departure  city  Delhi”.

• Retrieval using a link within a single table : Retrieve Aeroplane_No from Schedule_ Master whose departure  city  is same  as  of Aeroplane_No “A999”.

The query may be parapharased. “Print Aeroplane_No Aeroplane such that Departure city City  is  same  as  of Aeroplane_No A999.

• Retrieval records from more than one table : To retrieve data from two tables or projection of a join of two existing tables, user must first create a table with columns as expected in Data types of these columns are same as in existing tables. User can give any name to these columns or they may even be left unnamed.

Ex. Retrieve Flight_Date and Departure_City for all Aeroplane_No

• Retrieval using the condition box : In some situations, it is not possible to express some desired condition within the framework of the query In this situation, QBE provides a separate  box  named  as  “Condition Box”  to  specify  such  conditions.

Ex. Get all pairs of Aeroplane_No from Schedule_Master such that their departure city is same.

 The result is

6. Built-In Functions

QBE provides a number of built-in functions. ALL. operator is always specified with built-in functions. UNQ. operator eliminates the redundant duplicates before applying the functions. It is optional.

1. ALL : It is used to count number of records. It can be used with or without condition.

Ex. Count Airline_Names from Flight_Master

gives result 8

If CNT.UNQ.ALL.Air is used then it returns 5.

2. ALL : It  is  used  to  retrieve total  of  any  column  of  table.

Ex. Retrieve the sum of all seats from Flight_Master.

It results 1840.

If SUM.UNQ.ALL.seats  is used  then it  returns 1540.

3. ALL : It is used to retrieve average of total of any column of table. In the above example, if P.AVG.ALL.seats is used then it gives 230. If P.AVG.UNQ.ALL. seats is used then it gives 256.6.
4. ALL : It is used to retrieve maximum value of any column of table. UNQ. is not applicable here.
5. ALL : It is used to retrieve minimum value of any column of table. UNQ. is not applicable here.

Ex. Retrieve maximum Airfare and minimum seats from Flight_Master

It results 1200 and 50.

7. Features of QBE

The various features of QBE are as follows:

1. It provides a graphical interface  to write queries
2. It provides a separate condition box to define conditions that are not possible to specify in graphical frame
3. It provides built-in functions.

8. Advantages of QBE

The major  advantages of  QBE are  as follows:

1. It provides a graphical interface to write queries and hence eliminates the need to write complex SQL commands.
2. It reduces the probability of manual mistakes.
3. It shortens the development of code because it is very easy to write.
4. User needs to drag the appropriate  objects around,  in design view  of QBE.
5. Database engine first checks  the syntax  and  then execute it.
6. QBE is highly non-procedural language.

9. Limitations of QBE

A major limitation of QBE is that it is not possible to execute all types of operations that are possible to execute using SQL e.g., set operations, Dynamically changing the data sources etc.

10. Commercial Database Management Systems Providing QBE Feature

The following are the commercially available DBMS’s providing QBE feature.

1. Microsoft Access QBE
2. SQL Server QBE
3. Microsoft query
4. FoxPro QBE.

Source: Gupta Satinder Bal, Mittal Aditya (2017), Introduction to Basic Database Management System, 2nd Edition-University Science Press (2017)