Query design

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which list shows the parts of a query design?

A Table, fields to show, criteria, sort order

B Title, font, colour, page border

2. When is a query design written?

A After the query has been run and finished

B Before the query is built

3. A query design says: Table: Students. Which part of the design is this?

A The sort order

B The table that holds the data

4. Which part of a query design decides which columns appear in the results?

A Fields to show

B Sort order

5. Which is a criteria in a query design?

A Year_Level = 11

B Sort by Surname A to Z

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Table: Students		ANames the table the query will take its records from
2Fields: FirstName, Year		BCriteria: Year = 11
3Criteria filter the records so only matching ones are shown		CThe sort line uses a filter condition. It should name the sort direction, such as Sort: Year ascending, and the condition belongs in the criteria.
4Sort order arranges the results from smallest to largest or A to Z		DThe table line lists fields instead of a table name. It should be something like Table: Students, and the fields belong on a separate Fields line.
5Table: Name, Year, Score Criteria: Score > 80		ELists the fields that will be shown in the query results
6Table: Students Fields: Name, Year Sort: Year > 10		FSort: Surname ascending

Definitions

Query design

Fill in the Blanks

Complete each line of code by filling in the missing parts.

_____: Students Fields: Name, Year Sort: Name ascending

Table: Students ______: Name, Year Sort: Name ascending

Table: Students Fields: Name, Year ________: Year = 11

Table: Students Criteria: Year __ 11 Sort: Name ascending

Table: Products Fields: Name, Price Sort: Price __________

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Goal: Show cheap products

B Table: Products

C Fields: Products

D Criteria: Price < 10

E Sort: Price lowest first

Question 2

A Goal: Show Year 11 students

B Table: Teachers

C Fields: Name, Year

D Criteria: Year = 11

E Sort: Name A-Z

Question 3

A Table: Books

B Fields: Title Author

C Criteria: Genre = 'Fantasy'

D Sort: Title A-Z

Question 4

A Goal: Top scores first

B Table: Results

C Fields: Student, Score

D Sort: Score lowest first

Question 5

A Table: Customers

B Fields: OrderID, Total

C Criteria: Total > 100

D Sort: Total highest first

Answer Key

Query design

Section A: Quick Questions

A — Table, fields to show, criteria, sort order

B — Before the query is built

B — The table that holds the data

A — Fields to show

A — Year_Level = 11

Section B: Match the Pairs

Table: Students → Names the table the query will take its records from

Fields: FirstName, Year → Lists the fields that will be shown in the query results

Criteria filter the records so only matching ones are shown → Criteria: Year = 11

Sort order arranges the results from smallest to largest or A to Z → Sort: Surname ascending

Table: Name, Year, Score Criteria: Score > 80 → The table line lists fields instead of a table name. It should be something like Table: Students, and the fields belong on a separate Fields line.

Table: Students Fields: Name, Year Sort: Year > 10 → The sort line uses a filter condition. It should name the sort direction, such as Sort: Year ascending, and the condition belongs in the criteria.

Section C: Definitions

Query design — A plan for a database query that lists the table, the fields to show, the criteria used to filter records and the sort order, written before the query is built.

Section D: Fill in the Blanks

Table: Students
Fields: Name, Year
Sort: Name ascending

Table: Students
Fields: Name, Year
Sort: Name ascending

Table: Students
Fields: Name, Year
Criteria: Year = 11

Table: Students
Criteria: Year >= 11
Sort: Name ascending

Table: Products
Fields: Name, Price
Sort: Price descending

Section E: Spot the Error

C — The Fields line must list the field names to show, such as Name, Price. Products is the table name, which belongs on the Table line.

B — The goal is about students, so the query design must name the Students table, not Teachers.

B — Fields in a query design are separated by commas. It should be Fields: Title, Author.

D — The goal is to show top scores first, so the sort order must be highest first, not lowest first.

A — The fields OrderID and Total come from the Orders table. The query design must name Orders as the table.