Databases : Queries

A query (also known as a filter or search) is used to answer a question from the data in a database. Queries look through the database and find all the records that match a particular condition (or criterion). They are normally defined in a very basic language called a query language. When a query is run it produces as output a list of all of the records that match the condition that defined the query. A common query language is called SQL which stands for Structured Query Language.

Recall the patients files in the doctors surgery database :

The following condition could be used to search this file to locate all of the patients who see Dr Jones :

Here are some other examples queries :

The comparisons that you can make in queries will depend upon the database program that is being used. Common ones are :

Reversing Queries

Sometimes you may want to search for records which do not match a particular criterion. For example you may want to find everyone who is not a patient of Doctor Jones. The keyword NOT is used to do this.

• Doctor = "Dr Jones" finds all the patients of Dr Jones.
• NOT Doctor = "Dr Jones" finds all the patients who do not see Dr Jones.

Queries Using More Then One Field

More complicated queries can be created by linking together more than one search condition. For example this condition can be used to find all of Dr Jones’ patients who have blood type O :

Blood Type = "O" AND Doctor = "Dr Jones"

This condition will find all of the patients who live in Hanley or were born after 1985 :

Address contains "Hanley" OR D-O-B >= 01/01/86

The words AND and OR are the only ones that can be used to link two simple conditions together to make a more complicated query. The difference between using AND or OR to join two conditions is :

• AND : Find only records which match both of the conditions.
• OR : Find records that match either or both of the conditions.

Using Brackets In Queries

For very complicated queries you may need to use brackets to tell the database what order to use to evaluate the conditions that make up the query. Brackets are used for the same purpose in mathematics :

e.g. (2*3)+4 does not give the same result as 2*(3+4)

The brackets tell the person doing the sum which order to use to calculate its different parts. Similarly when writing a query :

1. (Address contains "Hanley" OR Address contains "Stoke") AND D-O-B >= 01/01/86
2. Address contains "Hanley" OR (Address contains "Stoke" AND D-O-B >= 01/01/86)

would produce different results. Query (1) would find everyone who lives in either Hanley or Stoke who was born after 1985. Query (2) would find everyone who lives in Hanley (not just those born after 1985) as well as anyone who lives in Stoke who was born after 1985.

Queries Using More Than One File

So far all of the queries that we have looked at use data from one file only. With a flat file database this is not a limitation because flat file databases can only use one file at a time. However when working with relational databases this is a significant limitation. One of the important advantages of a relational database is that queries can be written which use data from more than one file. To enable this a much more sophisticated query language is required than the one we have used. SQL or Structured Query Language is one such language. The language must specify what data to look for and which file to look in.

Consider a database that stores information about which forms the students at a school are in. The database has two files. The first contains information about students and has the fields Student and Form. The second contains information about forms and has the fields Form and Form Tutor. Here is the contents of the two files :

Students File

Form Tutors File

To find out the names of all of the students in Mr J. Smith's form the database would have to :

1. Find out the name of Mr Smith's form (4LA) from the Form Tutors File.
2. Look in the Students File to see which students are in that form.

In SQL one way to express this query would be :

SELECT *
FROM [Students File], [Form Tutors File]
WHERE [Form Tutors File].[Form Tutor] = "Mr J. Smith" AND [Form Tutors File].Form = [Students File].Form

A detailed knowledge of a powerful query language such as SQL is not required for a GCSE course.

GCSE ICT Companion 04 - (C) P Meakin 2004