Databasics V: Getting information out

The whole point of using a database is to turn data into information. Data are facts which have no inherent meaning; information is data put into context to convey meaning.

Think of a customer database containing information such as customer names, addresses and phone numbers. In the State field, you’ll find the values QLD, VIC, WA, NT, NSW and so on repeated over and over (that is, if you live in Australia – adjust the values to suit your locale if you live elsewhere). Without a context, this list of state abbreviations is meaningless. Put a question to your database such as “What percentage of our customers live in WA?”, however, and the resulting answer is useful, meaningful information.

Queries

We’ve spent the last few tutorials looking at how to structure a database and how to design data entry forms. It’s vital to get this design phase right, otherwise you can’t be sure any information you get from your database will be trustworthy. Additionally, you want to make sure data entry is as easy as possible not only for the comfort of the people stuck with this task, but also to help ensure data is entered correctly. The more attention you pay to the design phase, the better. But the really interesting and useful stuff happens when you start trying to get information out of your database. This is where you turn the stored data into information – a process called querying.

A query is a question you ‘ask’ a database in order to get information in a useful format. As databases don’t understand plain English (although there have been attempts to design database programs that do), you have to phrase your question according to rules. The rules are usually referred to as a query language.

Query language

If this sounds a little intimidating, don’t let it faze you. Chances are you’re already familiar with using a simple form of query language on the Internet. That’s because Web search engines are, in fact, a type of database. When you type a search phrase such as:

piano steinway bechstein

into a Web search engine, the search engine queries its stored database of websites using the three keywords you’ve supplied. Your search will turn up all web pages that contain the word piano or Steinway or Bechstein, or any combination of the three words.

If you’ve done any advanced web searching, you may have tried creating Boolean search queries (named after George Boole, an English mathematician who helped develop symbolic logic). In Boolean queries you use logical operators such as AND (or &), OR and NOT to create more precise searches. Like this:

piano AND (steinway OR bechstein)

This search query will locate web pages that contain the words piano and Steinway, or the words piano and Bechstein, or all three words, but it won’t turn up pages that mention Steinway or Bechstein but don’t contain the word piano.

Querying a database is similar. The query language may be more extensive and you’ll be given tools that make the job very precise, but if you can use a search engine you won’t have too many problems learning how to query a database.

The actual way you create a query depends on the database program you use. For example, the database in Microsoft Works uses an ultra-simple, fill-in-the-blanks approach called query by example (QBE for short). Microsoft Access also has a QBE feature, called Filter By Form. Filter by Form is the easiest way to pull information out of an Access database, but for more complex queries and to create queries which can be re-run at any time, you’ll probably use Access’s Query Builder, a highly sophisticated query tool. Alpha Five and FileMaker Pro also offer a choice of methods to query your data, including QBE.

Making comparisons

You need more than Boolean operators to create a query. You also need a way to compare one value to another, and that’s where comparison operators come into play. In combination with Boolean operators, they let us create questions about our data that the database can understand.

You’ll be familiar with comparison operators from school arithmetic. Here’s a list of them.

To understand how to use comparison operators, imagine a database containing a country table which includes the following fields (the data type of each field is shown in parentheses):

(Note: You’ll find a link at the end of this article which will let you download a copy of the Countries database containing 194 country records, which you can use to try out your own queries.)

Using operators to make comparisons

Consider the types of information you might try to get from the database. Here are some examples:

1. What’s the population of Namibia?
2. Which countries are members of the Security Council and when did they join the UN?
3. Which countries are not members of the UN?
4. Which countries have a population under 20 million?
5. Which countries names begin with A, B or C?
6. Which countries have a population over 20 million or an area of over 2 million square kilometres?
7. Which countries have a population over 20 million and an area over 2 million square kilometres?

Take that first question – What’s the population of Namibia? To rephrase it so our database can understand what we’re asking, we make use of the = (is equal to) operator:

Show me the record in the Countries table where the Name field=”Namibia“, and display the value of the Population field for that record.

So, here are paraphrases for all of our questions:

1. Show me the record in the Countries table where the Name field=”Namibia”, and display the value of the Population field for that record.
2. Show me the records where SecurityCouncilMember=Yes, and display the value of the JoinedUN field for the matching records
3. Show me the records where UnitedNationsMember<>Yes
4. Show me the records where Population<20,000,000
5. Show me the records where Name<“D”
6. Show me the records where Population>20,000,000 OR Area>=2,000,000
7. Show me the records where Population>20,000,000 AND Area>=2,000,000

Broadening and narrowing the scope

Most of these are self explanatory, but let’s take a closer look at the last two where we’ve combined comparison and Boolean operators.

Example 6 displays all countries which either have a population of more than 20 million or which have an area of 2 million square miles or more or which have both a population over a million and an area that’s 2 million square miles or more.

Example 7 displays only those countries which have both a population of over 20 million and an area of 2 million square miles or greater.

The OR operator broadens the scope of the query to include more records. The AND operator narrows the scope of the query.

One other thing you may have noticed in example 5: You’re not restricted to using operators such as < and > with numbers alone. They work equally well with text and dates.

Using QBE

If all these operators have your head in a spin, never fear! Most database programs do all the hard work of concocting queries in the background, while you merely fill in a form to show the type of information you’re after. Nevertheless, if you want to create more complex queries or milk the most subtle information from your data, you’ll certainly put this knowledge to use. Even using query by example you’ll find yourself using comparison operators regularly.

Let’s step through a query by example in Microsoft Access and in FileMaker Pro. [You can download a copy of the sample Access database or the FileMaker database to try it for yourself. Note that you’ll get a warning when you try to download the Access sample (countries.mdb) that it is an executable file. The file is completely safe, but if you’re concerned, simply scan the file with your antivirus software before opening it.]

An example in Access

1. Double-click the Countries table in the Database window to open the table in Datasheet View. This view shows a list of all the records in the database.
2. Click the Filter By Form button on the toolbar (it has a picture of a funnel on top of a form). Your records will disappear and you’ll be presented with a single blank record.
3.  In the name field type Namibia and then click Apply Filter (the button with the filter on it). The matching record will be displayed.
4. Click the Apply Filter button (it’s now called the Remove Filter button) once more to redisplay all your records.

A more complex example

Now, for a slightly more complex query, let’s search for countries that have a population of 50 million or less which are also members of the UN Security Council:

1. Keep the Countries database open in Datasheet View.
2. Click the Filter By Form button.
3. If “Namibia” still shows in the Name field, delete it.
4. In the Population field, type <=50000000 (don’t put commas in the number) and tick the box in the SecurityCouncilMember field.
5. Click the Apply Filter button. You should see the records for Mali, Jamaica and the Ukraine.

Query By Example is delightfully simple, but it still has its pitfalls. For example, if you fail to tick (place a checkmark in) the UnitedNationsMember field in this Access query, you’ll get no matches. That’s because you’d be asking for records where the country is not a member of the United Nations but is a member of the UN Security Council, which, of course, cannot be. Click the image to see a full-sized version.

The same thing in FileMaker Pro

1. Open the Countries database and press Ctrl-F to go into Find Mode.
2. Type Namibia in the Name field and press Enter to display the matching record. Note that the number of matching records is displayed on the left.
3. Click the Show All Records button (the one with an eye) to redisplay all your records.

For the second query:

1. Press Ctrl-F to enter Find Mode.
2. Tab down to the Population field and type <=50000000.
3. Tab to the SecurityCouncilMember field, type Yes and press Enter to display the matching records. Flip through the matches by clicking the Records icon on the left.

More complex still

So far we’ve merely skimmed the surface of using queries. The QBE examples show how easy it is to get useful information out of a database, but there’s a whole lot more to querying, including the ability to save and re-use queries, and to create queries that prompt the user for information. We’ll examine queries in more detail in the next tutorial.