GoalRead and fill in a query grid: choose fields, hide fields, set criteria with comparison operators, combine criteria with AND, and sort the results.
New words
query · a question you ask a database; it shows only the records and fields that answer itcriteria · the conditions a record must meet to be shown, written in the Criteria row of the query gridcomparison operator · a symbol such as =, <, >, <= or >= that compares a field with a valueascending / descending · sorted from smallest to largest or A to Z (ascending), or the other way round (descending)
Explanation
A query is a question to the database. You build it in a query grid with one column for each field you use. The Table row says where the field comes from (the Bikes table is shown above). The Show row decides whether the field appears in the results. A field can be used for a criterion and still be hidden. Criteria use comparison operators, and a text value is written in double quotation marks. When criteria are in the same row, a record must meet all of them, so this is an AND search. The Sort row puts the results in ascending or descending order. Running a query never changes the stored table, and the records found are listed in the order of the table unless you sort them.
Bike_ID
Model
Type
Gears
Price
Available
Branch_ID
1
Breeze
Road
21
12.00
Yes
B1
2
Tornado
Mountain
18
15.50
No
B2
3
Trailer
Mountain
24
16.00
Yes
B1
4
Comet
Road
14
9.50
Yes
B3
5
Tiger
Mountain
21
14.00
Yes
B4
6
Falcon
Road
27
18.00
No
B3
7
Nimbus
Folding
6
8.50
Yes
B2
8
Tempo
Road
18
11.00
Yes
B2
9
Ridge
Mountain
24
17.50
Yes
B4
10
Tulip
Folding
3
7.00
Yes
B1
11
Zephyr
Road
21
13.00
No
B4
12
Titan
Mountain
21
15.00
Yes
B3
Worked examples
Field
Model
Type
Price
Available
Table
Bikes
Bikes
Bikes
Bikes
Sort
Show
yes
no
yes
no
Criteria
"Mountain"
or
A bike-hire shop keeps the Bikes table shown above. The query shows Model and Price for the Mountain bikes. Type is in the grid, but its Show box is no, because the criterion needs Type and the results do not. The records found are Tornado 15.50, Trailer 16.00, Tiger 14.00, Ridge 17.50 and Titan 15.00: 5 records.
Field
Model
Type
Price
Available
Table
Bikes
Bikes
Bikes
Bikes
Sort
Show
yes
no
yes
no
Criteria
"Mountain"
<16
"Yes"
or
Two more criteria in the same row make it an AND search. The bike must be a Mountain bike, available, and cost less than 16. Out of the 5 Mountain bikes, Tornado is not available and Trailer costs exactly 16, which is not less than 16. Only Tiger 14.00 and Titan 15.00 are found.
Field
Model
Type
Price
Available
Table
Bikes
Bikes
Bikes
Bikes
Sort
ascending
Show
yes
no
yes
no
Criteria
"Mountain"
"Yes"
or
Sorting. The grid now keeps Mountain bikes that are available and sorts Price ascending. Without a sort the records appear in the order of the table: Trailer, Tiger, Ridge, Titan. With ascending Price they appear as Tiger 14.00, Titan 15.00, Trailer 16.00 and Ridge 17.50. Descending would give Ridge first.
Class activity
Question cards (no computer). Copy the Bikes table onto squared paper, a row for each bike. Take turns to write a query on a card using only the grid rows Field, Show, Sort and Criteria. Your partner finds the matching records on the paper table with a pencil and writes the results in order. Try one query with two criteria in the same row and see which records drop out. (If you have a computer or tablet and an adult, you may type the table into a spreadsheet and use its filter.)
Practice
1
The Orchard table is shown. A query shows Fruit and Height_m, with the criteria Height_m > 3 and Healthy = "Yes" in the same row, sorted by Height_m descending. List the results in order.
Tree_ID
Fruit
Height_m
Year_planted
Healthy
1
Apple
3.5
2019
Yes
2
Pear
2.8
2021
Yes
3
Plum
4.1
2018
No
4
Apple
4.6
2017
Yes
5
Cherry
3.2
2020
Yes
6
Pear
5.0
2016
No
Taller than 3 m: trees 1 (3.5), 3 (4.1), 4 (4.6), 5 (3.2) and 6 (5.0). Of these, the healthy ones are trees 1, 4 and 5. In descending order of height the results are Apple 4.6, Apple 3.5, Cherry 3.2.
2
Complete the grid so that the query lists the Fruit of healthy trees planted after 2018 that are less than 4 m tall. Only Fruit is shown.
Field
Fruit
Height_m
Year_planted
Healthy
Table
Orchard
Orchard
Orchard
Orchard
Show
yes
no
no
no
Criteria
?
?
?
or
Healthy: "Yes". Year_planted: >2018. Height_m: <4. All three go in the same Criteria row, and Show is yes only for Fruit. The query finds Apple (tree 1), Pear (tree 2) and Cherry (tree 5).
3
In the Orchard table of the first task, which tree or trees does this query find: Fruit = "Pear" and Healthy = "No" in the same row?
Tree_ID
Fruit
Height_m
Year_planted
Healthy
1
Apple
3.5
2019
Yes
2
Pear
2.8
2021
Yes
3
Plum
4.1
2018
No
4
Apple
4.6
2017
Yes
5
Cherry
3.2
2020
Yes
6
Pear
5.0
2016
No
Only tree 6. Tree 2 is a Pear but it is healthy, and tree 3 is not healthy but it is a Plum. Both criteria must be true.
4
Explain why the Show box of Type is unticked in the first example, and what would change if it were ticked.
The criterion on Type still works when Show is no, because the database uses the field to find the records. If Show were yes, a Type column (Mountain, Mountain, …) would also appear in the results, which is not wanted.