ITA U3O2 KK15 - functions and techniques to retrieve required information through searching, sorting, filtering and querying data sets

Queries / Finds

Called 'finding' in Filemaker, a RDBMS can perform powerful searches using AND and OR logic, often using Query By Example where the desired data is entered into the field(s) where it should be found.

Filemaker and Access allow similar search techniques. In the search below, the RDBMS will find all records of people who have a surname starting with G and an age less than 17. Note that search terms in the same row are ANDed.

More powerful searches can be easily built...

In this example, three search terms are used. The DBMS will now find records of ...

• All people whose Surname begins with 'G' AND Age is less than 17
• OR All people whose surname begins with K and ends with G AND have an age of 16 or more
• OR All people whose age is less than 5.

Notice how each new row of criteria is OR'ed with the other rows, so the records found by each row are all included.

Since some searches (queries, finds) are frequently used (e.g. video rental customers whose DVD is overdue for return), searches can be stored for later reuse. This makes multi-step searches quick and simple even for inexperienced database users.

SORTING

Sorting is usually performed before producing output to organise data into an order where it's easy to find values (e.g. a name in a long list) or significant records (e.g. the ones with extraordinary values, such as the biggest or smallest). RDBMS can sort by several fields in a hierarchical way. e.g. One could sort by SURNAME, then by FIRSTNAME, then by MIDDLEINITIAL. In this way, the most important field is sorted first; where surnames are the same, records are then sorted by first name; where surnames and firstnames are the same, records are then sorted by middle initial. (This is how phone books are sorted - check it out.)

Data can be sorted differently depending on what the user needs to find. For example, a database programmer might sort records by ID, but office workers may need the same records sorted by name.

FILTERING

Users can specify what records to show by indicating what contents they're looking for. This can be done is a few ways in Access.

This is filtering in Excel

Searching

A simple search (often triggered by a CTRL+F keystroke) lets you find text or data in databases, spreadsheets, text documents. Some searches let you search multiple documents at once (sometimes called grep).

More powerful tools let you use regular expressions that act like wildcards in search terms to find variants of the value being hunted.