Relational databases and SQL queries in HSC Information Processes and Technology
“Describe the organisation of a relational database into tables, records, fields, keys and relationships, and construct SQL queries to retrieve and manipulate data”
A relational database stores data in tables. Each table holds records (rows) made of fields (columns). A primary key uniquely identifies each record; a foreign key in one table refers to the primary key of another, creating a relationship between the tables. SQL retrieves and manipulates this data: SELECT chooses fields, FROM names the table, WHERE filters records, ORDER BY sorts, and a join combines tables on matching keys. Normalisation removes redundancy by splitting data into related tables so each fact is stored once.
Jump to a section
Legacy course. This page covers HSC Information Processes and Technology (IPT), which NESA has replaced. The last IPT HSC exam was held in 2024; current students study Enterprise Computing 11-12 or Software Engineering 11-12 (first HSC exams in 2025). See the IPT hub for what carries over, or our HSC Software Engineering hub. Use this page as background only.
What this dot point is asking
NESA wants you to describe how a relational database is organised and to read and write SQL that retrieves and manipulates data. You need the vocabulary (table, record, field, key, relationship), an understanding of how tables link through keys, and the ability to construct a SELECT query with conditions, sorting and joins.
The answer
The relational model
A relational database organises data into two-dimensional tables, also called relations. Each table describes one kind of thing, for example Students or Subjects. A row in the table is a record, representing one instance (one student). A column is a field, representing one attribute (the student's surname). Every field has a data type, such as text, number, date or Boolean.
Keys and relationships
A primary key is a field (or combination of fields) whose value is unique for every record, so it identifies that record without ambiguity. A StudentID is a typical primary key. A foreign key is a field in one table that holds the primary key value of a record in another table. By matching a foreign key to a primary key, the database links related records, which is what makes the model relational. For example, an Enrolments table can hold a StudentID foreign key and a SubjectID foreign key, connecting students to subjects.
Relationships come in three kinds: one to one, one to many, and many to many. A many to many relationship (students to subjects) is implemented with a linking table that holds the two foreign keys, because a single field cannot store a list.
Schemas and data dictionaries
The schema is the design of the database: the tables, their fields, the data types and the relationships. A data dictionary documents each field with its name, type, size, and validation rules. Entity relationship diagrams draw the tables as boxes and the relationships as connecting lines, showing the structure at a glance.
Normalisation
Normalisation is the process of organising fields into tables so that each piece of data is stored once. Without it, repeating a customer's address on every order wastes space and risks inconsistency when the address changes in one place but not another. Normalisation splits data into related tables linked by keys, removing this redundancy and the update anomalies it causes.
Retrieving data with SQL
SQL (Structured Query Language) is the standard language for querying relational databases. A basic retrieval has three clauses:
- SELECT lists the fields you want returned.
- FROM names the table.
- WHERE filters the records to those meeting a condition.
For example, to list the names of students in Year 12:
SELECT FirstName, Surname FROM Student WHERE Year = 12;
ORDER BY sorts the result, ascending by default or descending with DESC. Comparison operators (=, >, <, >=, <=, <>) and the logical operators AND, OR and NOT build richer conditions. LIKE with wildcards matches patterns in text.
Joining tables
When the data you need spans two tables, a join combines them on a matching key. To list each student with the names of the subjects they take, you join Student to Subject through the Enrolments linking table, matching StudentID to StudentID and SubjectID to SubjectID. The join is the SQL expression of a relationship.
Manipulating data
Beyond retrieval, SQL updates the stored data. INSERT adds a new record, UPDATE changes fields in existing records (usually with a WHERE clause so it does not change every row), and DELETE removes records. These map onto the processing and storing information processes.
A Library database has a Member table (MemberID primary key, Name, JoinDate) and a Loan table (LoanID primary key, MemberID foreign key, BookTitle, DueDate). Write a query to list the names of members with a loan due before 1 June 2026, sorted by name.
SELECT Member.Name, Loan.BookTitle, Loan.DueDate
FROM Member, Loan
WHERE Member.MemberID = Loan.MemberID
AND Loan.DueDate < '2026-06-01'
ORDER BY Member.Name;
The WHERE clause does two jobs: it joins the tables by matching MemberID, and it filters to overdue loans.
Confusing a record with a field. A record is a whole row (one member); a field is one column (the JoinDate). Swapping these terms loses easy marks.
Forgetting the join condition. Querying two tables without matching the keys returns every combination of rows (a cross product), not the related records.
Leaving WHERE off an UPDATE or DELETE. Without a condition these statements change or remove every record in the table, which is almost never intended.
Calling any foreign key a primary key. The primary key uniquely identifies records in its own table; a foreign key points to a primary key in another table.
Describing normalisation as deleting data. It removes duplication by splitting tables, but no real information is lost; the data is reconnected through keys.
Exam-style questions
Questions in the style of NESA exam questions on this dot point, each with a worked answer. They are written by ExamExplained unless tagged "Past paper"; the year shows the paper a question is modelled on.
2021 HSC-style3 marksA booking table for a graduation dinner has fields including Student_ID, First_Name, Last_Name and No_of_guests. Write a SQL statement to list the names in alphabetical order (A-Z) by Last_Name of the students who have more than one guest. Use SELECT, FROM, WHERE and ORDER BY.Show worked answer →
For 3 marks you need a correct, complete SQL statement using all four keywords. A model answer is:
SELECT First_Name, Last_Name
FROM Booking
WHERE No_of_guests > 1
ORDER BY Last_Name ASC
Mark-by-mark logic:
SELECT First_Name, Last_Name names the fields to display (the students' names).
FROM Booking names the table the data comes from.
WHERE No_of_guests > 1 filters to only students with more than one guest.
ORDER BY Last_Name ASC sorts the results alphabetically A to Z by surname (ASC is ascending; it is the default but stating it is safest).
Markers reward correct keyword order, the right comparison (> 1, not >= 1), and sorting on Last_Name.
2020 HSC-style3 marksAn online games store is converting a flat-file database (Game ID, Game name, Release date, Cost, Publisher ID, Publisher, Developer ID, Developer) into three tables. Construct a schema to represent the three tables in the relational database, clearly identifying primary keys, foreign keys and relationships.Show worked answer →
For 3 marks construct a normalised schema with three tables, key fields underlined or marked, and relationships shown. A model schema is:
Game (GameID [primary key], GameName, ReleaseDate, Cost, PublisherID [foreign key], DeveloperID [foreign key])
Publisher (PublisherID [primary key], PublisherName)
Developer (DeveloperID [primary key], DeveloperName)
Relationships: one Publisher has many Games (one to many), and one Developer has many Games (one to many). PublisherID and DeveloperID in the Game table are foreign keys that link back to the Publisher and Developer tables.
This removes the repetition of publisher and developer names in the flat file. Markers reward correct primary keys, the foreign keys in the Game table, and the one to many relationships.
2020 HSC-style3 marksDesign a query using a structured query language (SQL) to display games that have been released from March 2018 to March 2020. List the results in alphabetical order by game name.Show worked answer →
For 3 marks write a complete SQL query that filters on a date range and sorts the output. A model answer is:
SELECT GameName, ReleaseDate
FROM Game
WHERE ReleaseDate >= '01/03/2018' AND ReleaseDate <= '31/03/2020'
ORDER BY GameName ASC
Mark-by-mark logic:
SELECT GameName (and ReleaseDate to show the value) names the output fields.
FROM Game names the table.
WHERE ReleaseDate >= '01/03/2018' AND ReleaseDate <= '31/03/2020' uses AND with two conditions so only records inside the date range are returned. Using OR here would return everything and is the common trap.
ORDER BY GameName ASC sorts alphabetically by game name.
Markers reward the AND range condition and the alphabetical sort on the name field.