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
2

OR, NOT, wildcards and two tables

Computing 9: Topic 2.3, pp. 217–256
GoalCombine criteria with OR and NOT, use the wildcard *, and write a query that takes fields from two linked tables.
New words
OR · a record is found if it meets the conditions in at least one rowNOT · a record is found only if it does not match the valuewildcard · a symbol, such as *, that stands for any characters in a text searchrelationship · the link between two tables that lets one query use fields from both
Explanation

Criteria in the same row are joined by AND. Criteria in different rows are joined by OR: the second condition goes in the row marked or. A record is found if it satisfies every condition in at least one row. NOT turns a text condition round, so NOT "Road" finds every type except Road. The wildcard * stands for any characters, including none. T* finds text beginning with T, and *e finds text ending in e. A query can also take fields from two tables when a relationship links them. The grid then has a Table row that tells where each field lives, and the linked key makes the right records meet.

WantedWhere the conditions goResult size
A and Bsame Criteria rowusually fewer records
A or BA in Criteria, B in the or rowusually more records
not ANOT before the value
starts with T"T*" in the field
Worked examples
FieldModelTypeGears
TableBikesBikesBikes
Sort
Showyesyesyes
Criteria"Folding"
or>=27
OR or AND? The first grid has Type = "Folding" in the Criteria row and Gears >= 27 in the or row. A bike is found if it is a Folding bike or has at least 27 gears: Falcon (27 gears), Nimbus (6) and Tulip (3), 3 records. The second grid puts both conditions in the same row. No Folding bike has 27 or more gears, so it finds 0 records.
FieldModelTypeAvailable
TableBikesBikesBikes
Sort
Showyesyesno
CriteriaNOT "Road""Yes"
or
NOT and the wildcard. The first query lists the available bikes that are not Road bikes: Trailer, Tiger, Nimbus, Ridge, Tulip and Titan, 6 records. A second query, with Model "T*" and Type "Road" in the same row, first finds the models that begin with T: Tornado, Trailer, Tiger, Tempo, Tulip and Titan. Only Tempo is a Road bike, so 1 record is found. The pattern "*e" would find Breeze and Ridge.
FieldModelTownAvailable
TableBikesBranchesBikes
Sort
Showyesyesno
CriteriaNOT "Lakeside""Yes"
or
Two tables. Bikes holds the Branch_ID of the branch where each bike is kept, and Branches holds the Town for each Branch_ID. The Branches table holds B1 Rivermouth, B2 Hillton, B3 Lakeside and B4 Eastport. The grid takes Model from Bikes and Town from Branches, and the relationship through Branch_ID joins them. Available bikes not kept in Lakeside are 7: Breeze (Rivermouth), Trailer (Rivermouth), Tiger (Eastport), Nimbus (Hillton), Tempo (Hillton), Ridge (Eastport) and Tulip (Rivermouth).
Class activity

Human query (no computer). Write eight pupils’ first names with a favourite sport and a yes/no for ‘plays in a team’ on cards. Place the cards on the floor. A caller reads one query, such as ‘sport is football OR name starts with A’, and the team of players picks up the matching cards. Count the cards. Then change OR into AND, run it again and compare the counts. Say which query finds more cards and why.

Practice
1
The Orchard table is shown. A query has Fruit = "Pear" in the Criteria row and Height_m > 4.5 in the or row. Which Tree_IDs are found?
Tree_IDFruitHeight_mYear_plantedHealthy
1Apple3.52019Yes
2Pear2.82021Yes
3Plum4.12018No
4Apple4.62017Yes
5Cherry3.22020Yes
6Pear5.02016No
2
The Instruments table is shown. Which instruments are found by Name "G*", and which by Name "*n"?
NameSection
Guitarstrings
Gongpercussion
Flutewind
Drumpercussion
Hornbrass
Violinstrings
3
Write the criteria (with the field each belongs to) for: Bikes that are not Road bikes, cost less than 15 and have a Model beginning with T. Which of the bikes in the table are found?
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
4
A Clubs table holds C1 Chess, C2 Art and C3 Choir. The Pupils table is shown and linked by Club_ID. A query shows Name and Club_name with the criterion Club_name NOT "Art". List the results.
Pupil_IDNameClub_ID
P1AliC1
P2MiaC2
P3LeoC1
P4AnaC3
P5OmarC2