Manipulate data Revision Notes

Sorting data means arranging it in a specific order to make it easier to analyse or find information. You can sort data in ascending order (from smallest to largest, A to Z, or earliest to latest) or descending order (largest to smallest, Z to A, or latest to earliest).

Sorting can be done based on one criterion, such as sorting a list of students by their marks in ascending order. For example, if you have marks: 75, 88, 62, sorting ascending gives 62, 75, 88.

You can also sort by multiple criteria. For instance, first sort a list of employees by department (alphabetically), then within each department sort by salary (descending). This layered sorting helps organise complex data logically.

• Ascending order: smallest to largest, A to Z, earliest to latest.
• Descending order: largest to smallest, Z to A, latest to earliest.
• Multiple criteria sorting: primary, secondary, tertiary keys.
• Example: Sort sales data by region (A-Z), then by sales amount (high to low).

Searching data means selecting specific records that meet certain conditions. Criteria define these conditions, and operators help combine or specify them.

Logical operators include AND, OR, and NOT. AND requires all conditions to be true; OR requires at least one condition to be true; NOT excludes records matching a condition.

Comparison operators compare values: > (greater than), < (less than), = (equal to), >= (greater than or equal to), <= (less than or equal to), and <> (not equal to). These allow precise filtering. For example, searching for sales > 1000 finds all sales above 1000 units.

• AND: all criteria must be met.
• OR: any one criterion must be met.
• NOT: excludes specified criteria.
• Comparison operators: >, <, =, >=, <=, <> for numerical or text comparisons.
• Example: Find products where price >= 50 AND stock < 20.

Wildcards are special characters that represent one or more unknown characters in text searches, allowing flexible pattern matching.

Common wildcards include * (asterisk) representing any number of characters, and ? (question mark) representing a single character.

For example, searching for 'S*' finds all entries starting with 'S' (e.g., 'Sales', 'Software'). Searching for 'Jo?n' finds 'John' or 'Joan'. Wildcards help when exact text is unknown or when searching for variations.

• * matches any number of characters.
• ? matches exactly one character.
• Useful for partial matches or unknown text.
• Example: Search for customer names starting with 'A*' to find 'Alice', 'Alan', 'Amanda'.

Imagine a spreadsheet listing books with columns: Title, Author, Year, and Price.

To sort by Year ascending, select the Year column and sort ascending to see oldest books first.

To find books priced less than 20, search with criteria Price < 20 using the comparison operator <. This filters the list to cheaper books only, useful for budget decisions.

• Sort by Year ascending to organise chronologically.
• Search Price < 20 to find affordable books.
• Combine criteria: Author = 'Smith' AND Price <= 15 to find cheap books by Smith.
• Use wildcards: Title = 'Data*' to find all books starting with 'Data'.