Search & Select Data in Databases
Searching and selecting data in databases is typically done using queries. These queries can be based on a single criterion or multiple criteria.
Using a Single Criterion to Select Subsets of Data
- You can use a single criterion to select specific data. For example, you might want to select all customers from a specific city
- E.g. to return all customers from London:
- Open the Query Design View
- Add the table you want to query
- Drag the field you want to query to the QBE grid. For instance, if you're looking for customers from a specific city, drag the
Cityfield - In the
Criteriarow under this field, type the value you're looking for (e.g., 'London')
Using Multiple Criteria to Select Subsets of Data
- You can also use multiple criteria to select data. For instance, you might want to select all customers from a specific city who have also purchased in the last month
- E.g. to return all customers from London who purchased in the last 30 days:
- Follow the steps above to start a new query and add the
Cityfield with 'London' as the criteria - Drag another field you want to query to the QBE grid. For example, if you're looking for customers who purchased in the last month, drag the
LastPurchaseDatefield - In the
Criteriarow under this field, typeDate()-30
Using Operators to Perform Searches
- AND - Returns true if both conditions are met
- OR - Returns true if at least one condition is met
- NOT - Returns true if the condition is not met
- LIKE - Returns true if the value matches a pattern (used with wildcards)
- >, <, =, >=, <=, <> - These are comparison operators. They return true if the comparison between the values is correct
Using Wildcards to Perform Searches
- Wildcards are used with the LIKE operator to search for patterns. The most common wildcard characters are:
%- Represents zero, one, or multiple characters_- Represents a single character
- E.g. to return all customers whose names start with 'J':
- Start a new query and drag the field you want to query to the QBE grid. For example, if you're looking for customers whose names start with 'J', drag the
Namefield - In the
Criteriarow under this field, typeJ*
Exam Tip
- Remember, the exact steps and symbols used for wildcards may vary depending on the specific DBMS and its version. In Microsoft Access, the asterisk (*) is used as the wildcard character to represent multiple characters, while the question mark (?) represents a single character
- When referring to field names from the exam question, make sure you copy it exactly the way it appears in the question
- Make sure you give the information asked for in the question and not a different field
Worked example
A student is setting up a database of information about volcanoes for a Geography project. The following is part of the database.
Name_of_volcano |
Country |
Height |
State |
Last_eruption |
Volcano_type |
| Use | Japan | 731 | Active | 2001 | Stratovolcano |
| Tor Zawar | Pakistan | 2237 | Dormant | 2010 | Fissure |
| Datong | China | 1882 | Extinct | 450 | Cinder Cone |
| Changbaishan | China | 2744 | Active | 1903 | Stratovolcano |
| Stromboli | Italy | 926 | Active | 2016 | Stratovolcano |
| Tengchong | China | 2865 | Dormant | 1609 | Pyroclastic cone |
| Wudalianchi | China | 597 | Dormant | 1721 | Multi-coned |
Operators such as AND, OR, NOT, LIKE, >, >=, <, <=, =, <> can be used to search the volcano database. The search criteria for all the dormant volcanoes with a height of less than 1000 metres would look like this:
State = “Dormant” AND Height < 1000
Use only the given operators and data to:
a. write down the search criteria that will produce a list of all the volcanoes that are not extinct in China that also last erupted before the year 1900.
[6]
State = NOT ‘Extinct’ AND Country = ‘China’ AND Last_eruption < 1900
State = – 1 mark or State <> [1]
NOT ‘Extinct’ – 1 mark or <> ‘Extinct’ [1]
AND Country [1]
= ‘China’ [1]
AND Last_eruption [1]
< 1900 [1]
b. write down the names of the volcanoes that match the requirements of part (a).
[2]
Tengchong [1]
Wudalianchi [1]
c. The data is sorted into ascending order of height. Write down the name of the volcano which would now be in the first record.
[1]
Wudalianchi [1]
