Sorting and filtering a database table

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. A database table has a Score field. You want the highest score at the top. Which sort do you use?

A Descending (big to small)

B Ascending (small to big)

2. You sort a Surname field in ascending order. Which surname comes first?

A Adams

B Zhang

3. You want to see only the records where Year Level is 8 and hide all the others. What do you use?

A Sort

B Filter

4. What happens to the records that do not match when you filter a table?

A They are hidden from view

B They are deleted forever

5. Which is true about sorting a table by one field?

A Only the records that match are kept

B Every record stays, but they are put in a new order

Match the Pairs

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

Left column		Right column
1Sort Ascending on Age		AA filter hides every record that does not match. Blue team records are hidden when filtering on Red. Remove the filter to see all records again.
2Filter by Selection: Year = 8		BSort Descending on Score
3Sort the Players table so the highest score is at the top		CAscending sorts A to Z, not Z to A, and this sort should use one field only. Use Sort Descending on Surname to get Z to A.
4Show only the students who are in class 7B		DShows only the records where the Year field is 8 and hides all the others
5Sort Ascending on Surname and Age at the same time to put names in Z to A order		EPuts the records in order from the smallest age to the biggest age
6Filter by Selection: Team = Red, then expect the Blue team records to still be shown		FFilter by Selection: Class = 7B

Definitions

sort and filter

Fill in the Blanks

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

____ by Name, Ascending

Fields: Name, Score Sort by Score, __________

Pets table: Name, Age, Type ______ Type = "Cat"

Pets table: Name, Age, Type Filter Type _ "Dog"

Goal: youngest pet first Sort by ___, Ascending

Spot the Error

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

Question 1

A SELECT * FROM Pets

B WHERE Type = Dog

C ORDER BY Age;

Question 2

A SELECT Name, Age FROM Pets

B WHERE Type = 'Cat'

C ORDER Age ASC;

Question 3

A SELECT * Games

B WHERE Year = 8

C ORDER BY Score DESC;

Question 4

A -- Show oldest pets first

B SELECT * FROM Pets

C ORDER BY Age ASC;

Question 5

A SELECT * FROM Books

B WHERE Genre = 'Sci-Fi

C ORDER BY Title ASC;

Answer Key

Sorting and filtering a database table

Section A: Quick Questions

A — Descending (big to small)

A — Adams

B — Filter

A — They are hidden from view

B — Every record stays, but they are put in a new order

Section B: Match the Pairs

Sort Ascending on Age → Puts the records in order from the smallest age to the biggest age

Filter by Selection: Year = 8 → Shows only the records where the Year field is 8 and hides all the others

Sort the Players table so the highest score is at the top → Sort Descending on Score

Show only the students who are in class 7B → Filter by Selection: Class = 7B

Sort Ascending on Surname and Age at the same time to put names in Z to A order → Ascending sorts A to Z, not Z to A, and this sort should use one field only. Use Sort Descending on Surname to get Z to A.

Filter by Selection: Team = Red, then expect the Blue team records to still be shown → A filter hides every record that does not match. Blue team records are hidden when filtering on Red. Remove the filter to see all records again.

Section C: Definitions

sort and filter — Sort puts records in order by one field, either ascending (A to Z, small to big) or descending. Filter shows only the records that match a chosen value and hides the rest.

Section D: Fill in the Blanks

Sort by Name, Ascending

Fields: Name, Score
Sort by Score, Descending

Pets table: Name, Age, Type
Filter Type = "Cat"

Pets table: Name, Age, Type
Filter Type = "Dog"

Goal: youngest pet first
Sort by Age, Ascending

Section E: Spot the Error

B — The filter value is text, so it needs quotes. Without them the database looks for a field called Dog. It should be WHERE Type = 'Dog'.

C — Sorting uses the words ORDER BY. The BY is missing. It should be ORDER BY Age ASC;

A — The table name must come after the word FROM. It should be SELECT * FROM Games.

C — ASC (ascending) puts the smallest ages first, so the youngest pets would appear first. To show the oldest first you need descending: ORDER BY Age DESC;

B — The text value is missing its closing quote. It should be WHERE Genre = 'Sci-Fi'