How do you search a relational database with complex criteria and add calculations?
Searching a relational database using advanced functions (queries with multiple criteria, AND/OR logic, comparison and wildcard operators, sorting and multi-level sorting) and using calculations in queries and reports (calculated fields and summary totals).
An SQA Higher Administration and IT answer on searching a relational database with advanced functions, covering queries with multiple criteria, AND/OR logic, comparison and wildcard operators, sorting, and using calculations such as calculated fields and summary totals in queries and reports.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this key area is asking
A database is only useful if you can find the right data and calculate from it. The SQA expects you to search a relational database using advanced functions, queries with multiple criteria combined with AND/OR, comparison and wildcard operators, and sorting (including multi-level), and to use calculations in queries and reports, such as calculated fields and summary totals. These skills appear in the assignment (building working queries) and the question paper (explaining AND vs OR, or what a calculated field is).
Searching with advanced functions
Multiple criteria: AND and OR
- AND: every condition must be true, so the search is narrower. "Region = North AND Sales > 10000" returns only high-selling North records.
- OR: any condition may be true, so the search is wider. "Region = North OR Region = South" returns records from either region.
Operators, wildcards and sorting
- Comparison operators:
>,<,>=,<=,=,<>(not equal), and Between x And y for a range (for example dates in a month). - Wildcards:
*(any characters) and?(one character), for partial matches, for example*ltdfinds anything ending "ltd". - Sorting: order results ascending or descending; a multi-level sort orders by one field, then another (region, then name).
Using calculations in queries and reports
- Calculated field: derive a value, such as a line total (
[Quantity]*[Price]), VAT, or a full name from first and last name. - Summary totals: group and aggregate, such as total or average sales per region, or a count of orders per customer, often shown in a grouped report.
These keep the database efficient (you store only raw data and calculate the rest) and accurate (the figures always match the current data).
Examples in context
Example 1. Finding overdue accounts. An administrator queries a customer database for accounts where Balance > 0 AND DueDate < Today, sorts by Balance descending, and adds a calculated field for days overdue. The query returns exactly the overdue accounts, worst first, showing AND logic, comparison operators, sorting and a calculation.
Example 2. Sales by region report. A report groups orders by region and shows a summary total of sales for each, plus a calculated field for each line's value. Management sees totals per region without the database storing them, illustrating summary totals and calculated fields.
Try this
Q1. Explain the difference between using AND and OR in a database search. [2 marks]
- Cue. AND requires all conditions to be true, so it narrows the results (fewer records match); OR requires any one condition to be true, so it widens the results (more records match).
Q2. Describe what a calculated field is, with an example. [2 marks]
- Cue. A field whose value is worked out from other fields by a formula (for example
Total: [Quantity]*[Price]) rather than stored, recalculating automatically when the data changes.
Exam-style practice questions
Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
SQA Higher style4 marksDescribe the difference between using AND and using OR in a database search, with an example of each.Show worked answer →
Worth 4 marks. Describe each with an example, two marks each.
AND (about 2 marks). AND requires all conditions to be true, so it narrows the search, for example "Region = North AND Sales > 10000" returns only North records that also have sales over 10000. Fewer records match.
OR (about 2 marks). OR requires any one condition to be true, so it widens the search, for example "Region = North OR Region = South" returns records from either region. More records match.
SQA Higher style4 marksDescribe what a calculated field is and give an example of its use in a database.Show worked answer →
Worth 4 marks. Describe the calculated field and give a use.
Calculated field (about 2 marks). A field in a query or report whose value is worked out from other fields using a formula, rather than being typed in and stored, for example Total: [Quantity]*[Price]. It recalculates automatically when the data changes.
Use (about 2 marks). It is used to derive information the database does not store directly, for example calculating each order line's total from quantity and price, or VAT from a net amount, so reports show the figures without storing them.
Related dot points
- The structure of a relational database (tables, fields, records, data types, primary and foreign keys), and creating relationships between tables, with the advantages of a relational database over a flat-file one.
An SQA Higher Administration and IT answer on the structure of a relational database, covering tables, fields, records, data types, primary and foreign keys, creating relationships between tables, and the advantages of a relational database over a flat-file one.
- Creating and using forms for data entry, creating reports (including grouped reports with totals), printing database results in a range of formats, and exporting data to spreadsheet, word-processing and presentation applications.
An SQA Higher Administration and IT answer on creating and using database forms for data entry, creating reports including grouped reports with totals, printing results in a range of formats, and exporting data to spreadsheet, word-processing and presentation applications.
- Using spreadsheet functions, formulae and features at Higher level, including absolute and relative cell references, logical and lookup functions (IF, nested IF, COUNTIF, SUMIF, VLOOKUP), statistical functions, named ranges and conditional formatting.
An SQA Higher Administration and IT answer on spreadsheet functions, formulae and features, covering absolute and relative cell references, logical and lookup functions such as IF, nested IF, COUNTIF, SUMIF and VLOOKUP, statistical functions, named ranges and conditional formatting.
- Managing worksheets and workbooks, consolidating data across sheets, creating dynamic links between software applications, and importing and exporting spreadsheet data from and to external sources.
An SQA Higher Administration and IT answer on managing worksheets and workbooks, consolidating data across sheets, creating dynamic links between applications, and importing and exporting spreadsheet data to and from external sources.
- Creating and formatting charts from spreadsheet data (choosing an appropriate chart type and adding titles, labels and legends), and printing in a range of views and selections (formulae view, gridlines and headings, fit to page, selected ranges).
An SQA Higher Administration and IT answer on creating and formatting charts from spreadsheet data, choosing an appropriate chart type, and printing in a range of views and selections such as formulae view, gridlines, fit to page and selected ranges.