AdvertisementYour brand could be hereYour brand hereOn every Darslab page · students, parents and teachersinfo@darslab.comAsk for pricesPricesinfo@darslab.com
☰ Year plan · Computing

Ask smart questions

Week 19 · 2.3 Creating databases · 3 lessons
1

Ask the database a question

Computing 9: Topic 2.3, pp. 217–256
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_IDModelTypeGearsPriceAvailableBranch_ID
1BreezeRoad2112.00YesB1
2TornadoMountain1815.50NoB2
3TrailerMountain2416.00YesB1
4CometRoad149.50YesB3
5TigerMountain2114.00YesB4
6FalconRoad2718.00NoB3
7NimbusFolding68.50YesB2
8TempoRoad1811.00YesB2
9RidgeMountain2417.50YesB4
10TulipFolding37.00YesB1
11ZephyrRoad2113.00NoB4
12TitanMountain2115.00YesB3
Worked examples
FieldModelTypePriceAvailable
TableBikesBikesBikesBikes
Sort
Showyesnoyesno
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.
FieldModelTypePriceAvailable
TableBikesBikesBikesBikes
Sort
Showyesnoyesno
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.
FieldModelTypePriceAvailable
TableBikesBikesBikesBikes
Sortascending
Showyesnoyesno
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_IDFruitHeight_mYear_plantedHealthy
1Apple3.52019Yes
2Pear2.82021Yes
3Plum4.12018No
4Apple4.62017Yes
5Cherry3.22020Yes
6Pear5.02016No
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.
FieldFruitHeight_mYear_plantedHealthy
TableOrchardOrchardOrchardOrchard
Showyesnonono
Criteria???
or
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_IDFruitHeight_mYear_plantedHealthy
1Apple3.52019Yes
2Pear2.82021Yes
3Plum4.12018No
4Apple4.62017Yes
5Cherry3.22020Yes
6Pear5.02016No
4
Explain why the Show box of Type is unticked in the first example, and what would change if it were ticked.