Information and Communication Technology (0417) | Manipulate data

Database Management: Manipulate Data (18.2)

Hello! Databases are not just places to store information; they are powerful tools for making that information useful. This chapter is all about giving instructions to your database so it can calculate new values, put data in order, and find exactly the specific records you need.

Think of this as learning how to talk to your database and make it do the hard work for you!

1. Performing Calculations in Databases

Data manipulation often starts with maths. We use calculations to generate useful information that wasn't directly entered into the table.

1.1 Calculated Fields and Controls

When you design a database report or a query, you can instruct the system to perform a calculation on existing fields. The result is placed in a new area called a calculated field (in queries) or a calculated control (in forms/reports).

Example: If you have a field for [Price] and a field for [Quantity], you can create a new calculated field called [Total Cost].

Formula: [Total Cost] = [Price] * [Quantity]

1.2 Formulae vs. Functions

We use two main tools to perform calculations:

Formulae:

These involve basic arithmetic operations applied to field names or values.

• Addition: +
• Subtraction: -
• Multiplication: *
• Division: /

Functions:

These are pre-built commands that perform complex or repetitive tasks quickly, usually involving a whole column of data. You need to know these key aggregate functions:

• SUM: Calculates the total of all values in a numeric field. (E.g., SUM of all sales.)
• AVERAGE (AVG): Calculates the mean or average value of the numbers in a field.
• MAXIMUM (MAX): Finds the largest value in a numeric field. (E.g., the highest exam score.)
• MINIMUM (MIN): Finds the smallest value in a numeric field. (E.g., the cheapest item price.)
• COUNT: Counts the total number of records that meet certain criteria. (E.g., COUNT how many students are in Class A.)

2. Sorting Data (Ordering Records)

Sorting means arranging the data in a particular sequence so it is easier to read and analyse.

2.1 Ascending and Descending Order

• Ascending Order: Arranges data from the smallest to the largest value.
  • Numbers: 1, 2, 3...
  • Text: A, B, C...
  • Dates: Oldest to Newest.
• Descending Order: Arranges data from the largest to the smallest value.
  • Numbers: ...3, 2, 1
  • Text: Z, Y, X...
  • Dates: Newest to Oldest.

2.2 Sorting using Multiple Criteria

Sometimes, simply sorting by one field isn't enough. You often need to sort by multiple criteria.

The database sorts by the primary sort key first, and then, if any records have the same value for the primary key, it uses the secondary sort key to break the tie.

Example: If you sort a student list:

1. Sort by [Class] in Ascending order (Primary Key).
2. Then sort by [Surname] in Ascending order (Secondary Key).

Result: All students in Class 1A will be grouped together, and within that group, their names will be sorted alphabetically by surname.

3. Searching and Selecting Data using Queries

A query is essentially a question you ask the database. It allows you to select a subset (a small, filtered group) of records that meet specific requirements, known as criteria.

3.1 Setting up Selection Criteria

Criteria are the rules you set to decide which records should be displayed.

Example: Find all customers whose [City] is 'London' AND whose [Balance] is over 500.

Don't worry if this seems tricky at first; practice is key! The main challenge is choosing the right operator.

3.2 Comparison Operators

These operators compare a field value to a criteria value (like a number or a piece of text).

• = (Equals): Finds records where the field is exactly equal to the criteria.
• > (Greater Than): Finds records larger than the criteria. (E.g., >100)
• < (Less Than): Finds records smaller than the criteria. (E.g., <50)
• >= (Greater Than or Equal To)
• <= (Less Than or Equal To)
• <> (Not Equal To): Finds records where the field is not equal to the criteria.

Did you know? You often do not need to type the "=" sign; most database software assumes you mean equals unless you specify otherwise (e.g., if you just type 'London' in the criteria row, it assumes =[London]).

4. Logical and Special Operators in Queries

When you need to use more than one criterion, you use logical operators (AND, OR, NOT) to connect them.

4.1 The AND Operator (The Strict Rule)

The AND operator is used when a record must satisfy ALL criteria to be selected.

Analogy: You need to be tall AND fast to join the basketball team. If you are only tall but not fast, you are excluded.

In the database, criteria linked by AND are usually placed in the same row of the query design grid.

Example Criteria: [Gender] = 'Male' AND [Age] >= 18
Only selects males who are 18 or older.

4.2 The OR Operator (The Flexible Rule)

The OR operator is used when a record must satisfy AT LEAST ONE of the criteria to be selected.

Analogy: You can pay with a credit card OR cash. If you have either one, you can pay.

In the database, criteria linked by OR are usually placed in different rows (often labelled 'Criteria' and 'or') of the query design grid.

Example Criteria: [Department] = 'Sales' OR [Department] = 'Marketing'
Selects employees who work in Sales, or employees who work in Marketing, or both (though an employee usually only has one department).

4.3 The NOT Operator (The Exclusion Rule)

The NOT operator is used to exclude records that meet a certain criterion.

Example Criteria: NOT [Country] = 'USA'
Selects all records where the country is anything except USA.

4.4 The LIKE Operator and Wildcards

The LIKE operator is used when you are searching for partial matches within text fields. You use wildcards with the LIKE operator.

A wildcard is a special character that represents one or more unknown characters. The specific wildcard characters depend on the software you use, but the two most common are:

• * (Asterisk or star): Represents any number of characters (zero or more).
• ? (Question Mark): Represents a single character.

Example uses of Wildcards:

• Criteria: LIKE "S*"
  Meaning: Find all entries that start with the letter 'S' (e.g., Smith, Sanchez, Silver).
• Criteria: LIKE "*ton"
  Meaning: Find all entries that end with 'ton' (e.g., Newton, Brighton, Dalton).
• Criteria: LIKE "R??a"
  Meaning: Find four-letter words that start with 'R' and end with 'a' (e.g., Rita, Rosa).
• Criteria: LIKE "*@cambridge.org"
  Meaning: Finds all email addresses belonging to the cambridge.org domain.