Query by Example (QBE) is a
database
In computing, a database is an organized collection of data or a type of data store based on the use of a database management system (DBMS), the software that interacts with end users, applications, and the database itself to capture and a ...
query language
A query language, also known as data query language or database query language (DQL), is a computer language used to make queries in databases and information systems. In database systems, query languages rely on strict theory to retrieve informa ...
for
relational database
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s.
History
Query by Example was devised by
Moshé M. Zloof at
IBM Research
IBM Research is the research and development division for IBM, an American Multinational corporation, multinational information technology company. IBM Research is headquartered at the Thomas J. Watson Research Center in Yorktown Heights, New York ...
during the mid-1970s, in parallel to the development of
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
, and influenced by the work on
relational database
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s of
Edgar Codd.
It is the first graphical query language, using visual tables where the user would enter commands, example elements and conditions. Many graphical front-ends for databases use the ideas from QBE today. Originally limited only for the purpose of
retrieving data, QBE was later extended to allow other operations, such as inserts, deletes and updates, as well as creation of temporary tables.
The motivation behind QBE is that a
parser
Parsing, syntax analysis, or syntactic analysis is a process of analyzing a string of symbols, either in natural language, computer languages or data structures, conforming to the rules of a formal grammar by breaking it into parts. The term '' ...
can convert the user's actions into statements expressed in a database manipulation language, such as
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
. Behind the scenes, it is this statement that is actually executed. A suitably comprehensive front-end can minimize the burden on the user to remember the finer details of SQL, and it is easier and more productive for end-users (and even programmers) to select tables and columns by selecting them rather than typing in their names.
In the context of
information retrieval
Information retrieval (IR) in computing and information science is the task of identifying and retrieving information system resources that are relevant to an Information needs, information need. The information need can be specified in the form ...
, QBE has a somewhat different meaning. The user can submit a document, or several documents, and ask for "similar" documents to be retrieved from a document database
ee search by multiple examples Similarity search is based comparing document vectors (see
Vector Space Model
Vector space model or term vector model is an algebraic model for representing text documents (or more generally, items) as vector space, vectors such that the distance between vectors represents the relevance between the documents. It is used in i ...
).
QBE represents seminal work in
end-user development, frequently cited in research papers as an early example of this topic.
Currently, QBE is supported in several relational database front ends, notably
Microsoft Access
Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational database, relational Access Database Engine (ACE) with a graphical user interface and software-development tools. It is a member of the Microsof ...
, which implements "Visual Query by Example", as well as Microsoft SQL Server Enterprise Manager. It is also implemented in several
object-oriented databases (e.g. in
db4o).
QBE is based on the logical formalism called
tableau query, although QBE adds some extensions to that, much like SQL is based on the
relational algebra
In database theory, relational algebra is a theory that uses algebraic structures for modeling data and defining queries on it with well founded semantics (computer science), semantics. The theory was introduced by Edgar F. Codd.
The main applica ...
.
Example
An example using the
Suppliers and Parts database is given here to illustrate how QBE works.
As a general technique
The term also refers to a general technique influenced by Zloof's work whereby only items with search values are used to "filter" the results. It provides a way for a software user to perform queries without having to know a query language (such as
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
). The software can automatically generate the queries for the user (usually behind the scenes). Here are two examples based on a Contacts table with the following text (character) columns: Name, Address, City, State, and Zipcode:
Contacts Query Form - Example A:
.....Name: Bob
..Address:
.....City:
....State: TX
..Zipcode:
Resulting
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
:
SELECT * FROM Contacts WHERE Name='Bob' AND State='TX';
Note how blank items do not generate
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
terms. Since "Address" is blank, there is no clause generated for it.
Contacts Query Form - Example B:
.....Name:
..Address:
.....City: Sampleton
....State:
..Zipcode: 12345
Resulting
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
:
SELECT * FROM Contacts WHERE City='Sampleton' AND Zipcode='12345';
More advanced versions of QBE have other comparison operator options, often via a pull-down menu, such as "Contains", "Not Contains", "Starts With", "Greater-Than", and so forth.
Another approach to text comparisons is to allow one or more
wildcard character
In software, a wildcard character is a kind of placeholder represented by a single character (computing), character, such as an asterisk (), which can be interpreted as a number of literal characters or an empty string. It is often used in file ...
characters. For example, if an asterisk is designated as a wildcard character in a particular system, then searching for last names using "Rob*" would return (match) last names such as "Rob", "Robert", "Robertson", "Roberto", etc.
Contacts Query Form - Example C:
.....Name: Rob*
..Address:
.....City:
....State:
..Zipcode:
Resulting
SQL
Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel")
is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...
:
SELECT * FROM Contacts WHERE Name LIKE 'Rob%'
In standard SQL, the percent sign functions like a wildcard in a LIKE clause. In this case, the query-by-examplme form processing software would translate the asterisk to a percent sign. (An asterisk is a more common wildcard convention outside of SQL, so here the form is attempting to be more
user friendly
''User Friendly'' was a webcomic written by J. D. Frazer, also known by his pen name Illiad. Starting in 1997, the strip was one of the earliest webcomics to make its creator a living. The comic is set in a fictional internet service provider a ...
.)
WARNING: Query-by-example software should be careful to avoid
SQL injection. Otherwise, devious users may penetrate further into the database than intended by builders of the query forms.
See also
*
CRUD
*
Microsoft Query by Example
*
GraphQL
GraphQL is a data query and manipulation language that allows specifying what data is to be retrieved (" declarative data fetching") or modified. A GraphQL server can process a client query using data from separate sources and present the res ...
a QBE for JSON front-ends.
*
QBIC
References
Sources
* .
* .
* .
* .
* .
External links
*
* {{Cite journal , url= http://www.ahmetsoylu.com/wp-content/uploads/soylu_UAIS_2016.pdf , title= Experiencing OptiqueVQS: a multi-paradigm and ontology-based visual query system for end users , first1= Ahmet , last1= Soylu , first2= Martin , last2= Giese , first3= Ernesto , last3= Jimenez-Ruiz , first4= Guillermo , last4= Vega-Gorgojo , first5= Ian , last5= Horrocks , journal= Universal Access in the Information Society , date= 2015 , volume= 15 , pages= 129–152 , quote= Visual Query System (VQS) , doi= 10.1007/s10209-015-0404-5 , hdl= 11250/2388519 , s2cid= 254169347
Relational model
Query languages
Human–computer interaction
1970s software
IBM software