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

Ask the database

Week 19 · Unit 7.4: The power of data · 3 lessons
2

Queries and criteria

Computing 7: Unit 7.4, pp. 112–149
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.

BookIDTitleGenrePagesYearOnLoan
1River MapsAdventure2102018Yes
2The Quiet KiteStory962020No
3Space AtlasScience3202021Yes
4Tiny MachinesScience1482019No
5Desert WindAdventure2752016No
6The Baking ClubStory1842022Yes
7Rock and RainScience4122015No
8Night TrainAdventure3502020Yes
Worked examples
Query descriptionTableFieldsCriteria
Titles of all Science booksBooksTitle, GenreGenre = 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.
TitleGenre
Space AtlasScience
Tiny MachinesScience
Rock and RainScience
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 recordPages > 200Pages >= 210
210TRUETRUE
96FALSEFALSE
320TRUETRUE
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 descriptionTableFieldsCriteria
Titles and pages of long booksBooks??
2
Look at the Books table at the top of this lesson. Which titles does each query select? (a) OnLoan = No (b) Year < 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 descriptionTableFieldsCriteria
Titles of books on loanBooks??
4
For each page count, say TRUE or FALSE for the condition Pages >= 275.
PagesPages >= 275
210?
275?
96?
350?
184?