Cover lesson · Science · Year 7 · Beginner

Database queries for planets

DCF: Data and computational thinking → Data and information literacy

Name: Class: Date:

Cover teacher — you need no subject knowledge

Print: this sheet, one per pupil. The whole database is printed on it as Table 1, so no computers are needed — pupils can answer every query by reading the table. If computers are available, the same eight records are in solar-system-data.csv and pupils can sort and filter for real. All the answers are on the example sheet.

Run: 5 min fields, records and data types · 20 min the six queries · 10 min the three spreadsheet functions · 10 min write two queries of your own and swap with a partner · 5 min the "surprising answer" question at the end.

Collect: this sheet. If computers were used, ask pupils to leave the file sorted by Number_of_Moons, largest first.

What you are doing

Table 1 is a database of the eight planets. Each row is a record and each column is a field. You are going to answer six questions by sorting and filtering it — the two things every database does — and then write two queries of your own. Every answer must come out of the table, not out of your head.

Table 1: the planets database

Planet Diameter_km Distance_from_Sun_million_km Orbital_Period_days Number_of_Moons Mean_Temperature_C Has_Rings
Mercury4,87957.988.00167No
Venus12,104108.2224.70464No
Earth12,756149.6365.2115No
Mars6,792228.0687.02-65No
Jupiter142,984778.54,33195-110Yes
Saturn120,5361,432.010,747274-140Yes
Uranus51,1182,867.030,58928-195Yes
Neptune49,5284,515.059,80016-200Yes

Source: NASA Planetary Fact Sheet. Moon counts are the confirmed totals — astronomers are still finding new ones, so this field changes.

Step by step

  1. 5 min — Fill in the "About this database" box: how many fields, how many records, and what kind of data each field holds.
  2. 20 min — Answer the six queries. For a filter, list every record that matches — not just the first one you spot.
  3. 10 min — Work out the three function answers. Show your working if you are doing it on paper.
  4. 10 min — Write two queries of your own, with the answers, and swap with a partner to check.
  5. 5 min — Answer the last question about the surprising result.

About this database

How many fields (columns)?
How many records (rows)?
Which field holds text?
Which field can only ever be Yes or No?
Which field contains negative numbers, and why does that matter when you sort it?
Which field would make the best unique key for a record?

The six queries

No. Query Answer
1Sort by Diameter_km, smallest first. Which planet is first and which is last?
2Filter Number_of_Moons > 2. How many records match, and which are they?
3Filter Distance_from_Sun_million_km < 250 AND Mean_Temperature_C > 0. Which planets match?
4Sort by Orbital_Period_days, largest first. Which planet has the longest year, and how many days is it?
5Search for the planet with the shortest year. Name it and give the value.
6Filter Has_Rings = Yes AND Number_of_Moons < 30. Which planets match?

Three functions

If the moon counts sit in cells E2 to E9 of a spreadsheet, these three formulae give three different "averages" and totals. Work out each answer from Table 1.

=SUM(E2:E9)
=AVERAGE(E2:E9)
=MEDIAN(E2:E9)
The mean and the median are very different. Which one describes a typical planet better, and why?

My own queries

My query (say sort, filter or search, and name the field) The answer from Table 1

One surprising answer

Sort the table by Distance_from_Sun_million_km, closest first, then look at Mean_Temperature_C. The hottest planet is not the closest one. Which planet is hottest, and write one sentence about what that tells you about drawing conclusions from a single field.

Success criteria

If you finish early