GoalPlan a query with fields and criteria, and work out which records it selects.
New words
query · a search that selects the records and fields you ask for from a databasecriteria · the conditions that a record must meet to be selectedcondition · something that is checked and is either TRUE or FALSEcomparison operator · a symbol that compares two values, such as >, < or =
Explanation
A database table can hold thousands of records. A query searches the table and shows only the records that you ask for. The criteria say what a record must be like to be chosen. Each record is checked against a condition, such as Pages > 200, and the answer is always TRUE or FALSE. Criteria use comparison operators: = (equal to), > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to) and <> (not equal to). Before building a query, plan it in a design table with its description, the table, the fields to show and the criteria. Show only the fields that are needed, because some data is private.
BookID
Title
Genre
Pages
Year
OnLoan
1
River Maps
Adventure
210
2018
Yes
2
The Quiet Kite
Story
96
2020
No
3
Space Atlas
Science
320
2021
Yes
4
Tiny Machines
Science
148
2019
No
5
Desert Wind
Adventure
275
2016
No
6
The Baking Club
Story
184
2022
Yes
7
Rock and Rain
Science
412
2015
No
8
Night Train
Adventure
350
2020
Yes
Worked examples
Query description
Table
Fields
Criteria
Titles of all Science books
Books
Title, Genre
Genre = Science
The library wants the titles of all the Science books. The design table says which table to search, which fields to show and the criteria. The query checks Genre = Science for each of the eight records. Three records are TRUE.
Title
Genre
Space Atlas
Science
Tiny Machines
Science
Rock and Rain
Science
Here is what the query returns. The Genre field is shown, so you can check that every record meets the condition. Records with Adventure or Story give FALSE and are not shown.
Pages in record
Pages > 200
Pages >= 210
210
TRUE
TRUE
96
FALSE
FALSE
320
TRUE
TRUE
The comparison operators give TRUE or FALSE for each record. The operator >= includes the number itself, but > does not. Look at the pages of three books (210, 96 and 320) and test two conditions.
Class activity
Human database (no computer). Write eight cards for a pretend table of animals at a zoo: Name, Type (bird, reptile or mammal), Age and whether it eats meat (Yes or No). Lay them out. A partner writes a query design on a slip, such as ‘names of all animals with Age > 5’. You select the cards that meet the condition and read out the fields. Then swap, and make a query that uses <, <= or <>. Count your results before checking the cards a second time. Use invented animals only.
Practice
1
Fill in the criteria and say which titles are selected: the titles and pages of all books with more than 250 pages.
Query description
Table
Fields
Criteria
Titles and pages of long books
Books
?
?
Fields: Title, Pages. Criteria: Pages > 250. Selected: Space Atlas (320), Desert Wind (275), Rock and Rain (412) and Night Train (350).
2
Look at the Books table at the top of this lesson. Which titles does each query select? (a) OnLoan = No (b) Year < 2020
(a) The Quiet Kite, Tiny Machines, Desert Wind and Rock and Rain. (b) River Maps (2018), Tiny Machines (2019), Desert Wind (2016) and Rock and Rain (2015). The Year 2020 itself is not less than 2020.
3
Design a query that lists the titles of the books that are on loan. Complete the design table and list the results.
Query description
Table
Fields
Criteria
Titles of books on loan
Books
?
?
Fields: Title, OnLoan. Criteria: OnLoan = Yes. Results: River Maps, Space Atlas, The Baking Club and Night Train.
4
For each page count, say TRUE or FALSE for the condition Pages >= 275.
Pages
Pages >= 275
210
?
275
?
96
?
350
?
184
?
210 FALSE, 275 TRUE, 96 FALSE, 350 TRUE, 184 FALSE. The number 275 is TRUE because >= includes the number itself.