ACARA Digital Technologies Years 7–8 — Structured Data and Queries Hangman

The 40 key terms

1structure

How data is organised. Structured data fits rows and columns with headings, like a table. Unstructured data, like free text, images or audio, has no fixed layout and is harder to analyse.

2attribute

One fact that describes an object, such as a pet's name, colour or age. Each attribute holds a single piece of information.

3event attributes

The facts that describe an event: what happened, the date, the time, the place and who took part, like a race at a sports carnival or a bus trip.

4database table

A database table stores data in columns called fields and rows called records. A single-table database keeps all its data in this one table.

5field and record

A field is a column and a record is one data row. The heading row is not a record. A table with 4 fields and 6 records has 4 columns and 7 rows including the heading.

6record

One row in a table that stores one object. Each attribute of the object becomes a field, so one pet's Name, Species and Age fill one record, and every record uses the same fields.

7event record

One occurrence of an event stored as a single row, where each attribute of the event, such as Date, Winner or Time, becomes a field.

8database

A database gives every record the same fields and data types and lets you query large amounts of data; a spreadsheet is a flexible grid.

9table, query, form, report

The four database objects: a table stores records, a query finds records, a form enters records and a report presents them.

10string

Text made of a sequence of characters (single letters, digits or symbols), written in quotes, like 'Sam' or '0412345678'. Phone numbers are strings.

11integer

A whole number with no decimal part, positive or negative, used for counts such as the number of students in a class (e.g. 25, 0 or -3).

12float

A floating point number is a number with a decimal part, such as 1.75 or 19.95. It is used for measurements like height and for money, unlike an integer, which has no decimals.

13Date/Time

A data type that stores a calendar date and/or time of day, such as 25/03/2025 or 14:30, so values can be sorted in order and used to calculate the duration between two dates.

14Boolean

A data type with only two possible values: true or false (yes or no). Used for yes/no fields, TRUE and FALSE in a spreadsheet, and the result of a comparison such as 5>3.

15character field

A character field holds exactly one character, like a grade A-E or an M/F code. A text field holds a string of any length, like a name or a postcode.

16data type

A setting on a database field that decides what kind of value it accepts, such as text, integer, date or yes/no, so wrong entries are rejected. It is a first form of validation.

18table design

Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

19primary key

A field whose value is different for every record, such as a student ID or an AutoNumber, so each record can be identified. Names make a poor key because two people can share one.

21Create table

Make a new table by adding fields in design view, then type each record into its own row in datasheet view. Save the table so your data is kept.

22table structure

The plan for one table: list each field the scenario needs, give it a data type and any validation, and choose a primary key so the table can hold every record.

23query

A saved question asked of a table. It shows only the fields and records that answer it, as a new view, and leaves the original table unchanged.

24SQL

Structured Query Language: the standard language for asking a database for data, and for adding, changing or deleting it. Each statement ends with a semicolon.

25SELECT name, age FROM students;

SELECT lists the fields to show and FROM names the table. This returns the name and age columns for every record in students. SELECT * shows all columns. End with a semicolon.

26SELECT Name, Score FROM Students

A query that lists the fields to show. Only Name and Score appear, in that order, so other columns are left out. SELECT * shows every field.

27SELECT lastName, firstName FROM students;

The output has only the fields named, as columns in the order written after SELECT, with a heading row of field names and one row per record kept (all records if there is no WHERE).

28SELECT * FROM students;

Type an SQL statement in the tool's query window, run it, then read the results table. If an error shows, read the message and fix the statement.

29WHERE

An SQL clause that keeps only the rows meeting a condition, such as WHERE age >= 15 or WHERE town = 'Perth'. Text goes in quotes, and AND or OR can combine conditions.

30WHERE Score > 80

A criterion that limits a query to records where the condition is true, so only Score values above 80 are shown. Compare with =, <>, <, >, <=, >=. Text goes in quotes.

31WHERE Town = 'Leeds'

The value in a WHERE condition must match the field's data type: text and characters go in quotes, numbers do not, and Boolean fields use TRUE or FALSE, e.g. Member = TRUE.

32AND / OR

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

33LIKE '%at%'

LIKE in a WHERE clause matches part of a text value. The % wildcard stands for any characters, so '%at%' finds text containing at, 'S%' starts with S and '%s' ends with s.

34ORDER BY

Sorts the rows a SELECT query returns by a chosen field. It sorts A to Z or lowest to highest by default (ASC), or the reverse if you add DESC after the field name.

35ORDER BY Score DESC

Sorts query results by the Score field from highest to lowest. Use ASC for lowest to highest. Add a second field after a comma to sort ties, like ORDER BY Score DESC, Name.

37SELECT FROM WHERE ORDER BY

The order of clauses in a full SQL query: SELECT, FROM, WHERE, then ORDER BY. WHERE filters the rows first, then ORDER BY sorts the rows that are left.

38WHERE, ORDER BY, SELECT

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

39SELECT WHERE ORDER BY

Read a query one clause at a time: SELECT names which fields are shown, WHERE picks which records, and ORDER BY sets the order the results appear in.

40SELECT fields FROM table WHERE condition ORDER BY field DESC;

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

41SQL error

A mistake in an SQL script, such as text without quotes, > instead of >=, AND instead of OR, clauses in the wrong order, a missing table after FROM or a missing semicolon.

42Test a query

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

43COUNT(*)

SQL function that counts records. SELECT COUNT(*) FROM Students; outputs one row with the number of records in the table. It counts rows, whereas SUM adds up values.