VCE Spreadsheets, SQL & Visualisation Definitions Worksheet: Relational database structure and Querying data with SQL (Modules 45–54)
Name:
Date:
Class:
✎
Key terms
Write what each term means, in your own words.
1
Import CSV
2
validation rule
3
CHECK (age BETWEEN 0 AND 120)
4
query
5
SELECT name, age FROM students;
6
WHERE
7
AND / OR
8
Like "S*"
9
ORDER BY
10
SELECT AVG(score) FROM results;
Answer Key
VCE Spreadsheets, SQL & Visualisation · Modules 45–54 · Student wording will vary for definitions
1Import CSV — Fills a new database table from a CSV file. The first row becomes the field names, and you choose a data type for each field, such as text or number, and check them after the import.
2validation rule — A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.
3CHECK (age BETWEEN 0 AND 120) — A constraint is a rule on a column. This CHECK rejects any record with an age outside 0 to 120. NOT NULL (must have a value) and UNIQUE (no repeats) are constraints too.
4query — 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.
5SELECT 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.
6WHERE — 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.
7AND / 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.
8Like "S*" — A query criterion where the * wildcard stands for any characters, so it finds text that starts with S. Like "*s" ends with s and Like "*at*" contains at.
9ORDER 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.
10SELECT AVG(score) FROM results; — An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.