Search & Select Data in Databases | IGCSE Information and Communication Technology Preparation - Year 11 PDF Download

Introduction

• Searching and selecting data in databases is commonly carried out through queries, which can be based on one or multiple criteria.

Using a Single Criterion to Select Subsets of Data

• One way to select specific data is by using a single criterion. For example, you might need to retrieve all customers from a particular city, like selecting all customers from London.
• To achieve this:
  • Navigate to the Query Design View.
  • Include the table you wish to query.
  • Drag the field you want to query into the QBE grid. For example, if you're searching for customers from a specific city, drag the City field.
  • Under the Criteria row for this field, input the value you are searching for (e.g., 'London').

Using Multiple Criteria to Select Subsets of Data

• You can apply multiple conditions to filter data in databases. For example, you might need to find all customers from a specific city who made a purchase in the last month.
• To retrieve customers from London who made a purchase in the last 30 days:
  • Begin a new query and specify the City field with 'London' as the filter criterion.
  • Add another field you wish to query to the query-by-example (QBE) grid. For instance, if you're interested in customers who made a purchase in the last month, drag the LastPurchaseDate field.
  • In the Criteria row under this field, input Date()-30.

Using Operators to Perform Searches

• AND: This operator returns true only if both specified conditions are satisfied.
• OR: It returns true if at least one of the specified conditions is met.
• NOT: This operator returns true if the specified condition is not met.
• LIKE: It returns true if the value matches a certain pattern, often used with wildcards for flexibility.
• Comparison Operators: These include >, <, =,="">=, <=,>< />, and they evaluate the comparison between values and return true if the condition holds.

Using Wildcards to Perform Searches

• Definition: Wildcards are utilized with the LIKE operator in database queries to search for patterns.
• Common Wildcard Characters:
  • % - Represents zero, one, or multiple characters
  • _ - Represents a single character
• Example: To retrieve all customers whose names begin with 'J':
  • Begin a new query and place the field you wish to query onto the QBE grid. For instance, if you seek customers whose names begin with 'J', drag the Name field.
  • In the Criteria row beneath this field, input J*.