����ý

Queries are a method used for requesting information from a database which matches set criteria. Queries are designed using a programming language called SQL (Structured Query Language).

An SQL Query could be used to find customers who live in Belfast:

SELECT First name, Last name FROM Customer ID WHERE Address = 'Belfast'.

This would return: Bob Buchannon

Query By Example

Query By Example (QBE) is a method of creating queries in the database program. QBE feature provides a simple interface for a user to enter queries. User can fill in blanks or select items to define the query they want to perform.

For example, if you wanted to find customers who live in Belfast using QBE:

This would return:

QBE is often used as it is much easier to learn than SQL.

Complex Queries

A simple query searches records in a database using one parameter value (see above). Whereas, a complex query searches records using more than one parameter value, therefore on two or more search criteria.

AND Query

AND logic allows the creation of complex queries. Only the results that match all the criteria will be returned. For example: Searching for customers who are female AND live in Belfast.

This will return the records of all female customers who live in Belfast. In this example, no customers meet this criteria, so no records are returned.

OR Query

Complex queries can be created using OR logic. If one criteria evaluates to true, the results will be returned. For Example: Searching for Customers who are female AND live in Lisburn OR Newry.

This will return:

NOT Query

NOT logic allows us to create complex queries that will return the negation of the search criteria. For example, to find the customers who do not live in Belfast.

This will return:

Calculated Query

It is not good practice to store calculations in tables, as part of the data itself. Not only does it waste disk space, if any of the data changes, the fields would have to be recalculated. It is better to create the calculations when needed.

We can create calculations in queries, reports and forms.

Instead of making an ORDER table containing the fields Quantity, Price and Total (calculated field).

We create a PRODUCT table that contains the price and we build an ORDER table containing the Quantity.

We can then create a query (showing these two fields plus the additional calculated field Total) which multiplies the two together.

No calculations are stored, saving space in the database, but the results are instantly accessible by running the query which calculates them when they are needed.

Calculated fields can contain all the basic mathematical operators (+, -, *, /) and will carry out a calculation in the order, Brackets (calculate stuff inside brackets first), Order (indices or those bits that are powers or roots), Division, Multiplication, Addition, Subtraction (BODMAS).

The results of queries can also be sorted into either ascending or descending order on any of the fields in the query.