SQL, data import, ODBC, ACID and data management issues: WACE Computer Science Unit 4
“Write SQL to create, query and modify relational data (including joins, aggregate functions and grouping), import data, explain database connectivity (such as ODBC) and ACID transactions, and discuss the ethical, security and legal issues of managing data”
Use SQL to define tables with keys and constraints, query with SELECT, WHERE, ORDER BY, JOIN, GROUP BY, aggregates and HAVING, and change data with INSERT, UPDATE and DELETE. Clean data before importing, connect applications through interfaces such as ODBC, protect integrity with ACID transactions, and manage data ethically, securely and legally.
Jump to a section
What this dot point is asking
You need to write SQL confidently, understand how data is imported and how applications connect to databases, explain ACID transactions, and discuss the ethical, security and legal issues of managing data.
The answer
SQL essentials
Defining tables
CREATE TABLE STUDENT (
StudentID INTEGER PRIMARY KEY,
FirstName TEXT NOT NULL,
Surname TEXT NOT NULL,
YearGroup INTEGER CHECK (YearGroup BETWEEN 7 AND 12)
);
Foreign keys: FOREIGN KEY (CourseCode) REFERENCES COURSE(CourseCode).
Querying
- SELECT columns FROM table WHERE condition ORDER BY column ASC or DESC.
- Operators: =, <>, <, >, BETWEEN, IN, LIKE with wildcards (%), IS NULL, AND, OR, NOT.
- Joins: INNER JOIN ... ON matching keys; LEFT JOIN keeps all rows from the left table.
- Aggregates: COUNT, SUM, AVG, MIN, MAX with GROUP BY; HAVING filters groups.
Changing data
- INSERT INTO table (columns) VALUES (...);
- UPDATE table SET column = value WHERE condition;
- DELETE FROM table WHERE condition; (always check the WHERE clause).
Importing data
Data is often imported from CSV or spreadsheets. Clean it first (consistent formats, no duplicates, valid keys), map columns to fields, check data types and constraints, and verify row counts after import.
Connectivity and transactions
- ODBC (Open Database Connectivity) and similar APIs let applications in different languages connect to databases through drivers, using connection strings and credentials.
- Transactions group operations into one unit of work with ACID properties:
- Atomicity: all or nothing.
- Consistency: valid state to valid state.
- Isolation: concurrent transactions do not interfere.
- Durability: committed changes persist after failures.
Ethical, security and legal issues
- Privacy: collect only what is needed, protect it (APP 11), and handle breaches under the Notifiable Data Breaches scheme.
- Security: access control, encryption, backups, parameterised queries to stop SQL injection, audit logs.
- Accuracy and fairness: poor data quality can harm people (wrong records, biased decisions).
- Ownership and consent: respect who owns data and what people agreed to.
Find students with no enrolments (to follow up):
SELECT STUDENT.StudentID, STUDENT.Surname
FROM STUDENT
LEFT JOIN ENROLMENT ON STUDENT.StudentID = ENROLMENT.StudentID
WHERE ENROLMENT.StudentID IS NULL;
The LEFT JOIN keeps every student; those without a matching enrolment have NULL in the enrolment columns, so the WHERE clause selects them.
- Using WHERE with aggregates
- Use HAVING for conditions on groups.
- UPDATE or DELETE without WHERE
- It changes every row.
- Forgetting the join condition
- It produces a huge, wrong result (a Cartesian product).
Practice questions
Original practice questions graded from foundation to exam level, each with a full worked solution. Try them before revealing the solution.
foundation3 marksUsing STUDENT(StudentID, FirstName, Surname, YearGroup), write SQL to list the first names and surnames of Year 12 students in surname order.Show worked solution →
SELECT FirstName, Surname
FROM STUDENT
WHERE YearGroup = 12
ORDER BY Surname;
Marking guide: 1 mark for SELECT and FROM, 1 mark for WHERE, 1 mark for ORDER BY.
core4 marksUsing COURSE(CourseCode, CourseTitle, TeacherID) and ENROLMENT(StudentID, CourseCode, Grade), write SQL to show each course title and the number of students enrolled, only for courses with more than 20 students, largest first.Show worked solution →
SELECT COURSE.CourseTitle, COUNT(ENROLMENT.StudentID) AS NumStudents
FROM COURSE
INNER JOIN ENROLMENT ON COURSE.CourseCode = ENROLMENT.CourseCode
GROUP BY COURSE.CourseTitle
HAVING COUNT(ENROLMENT.StudentID) > 20
ORDER BY NumStudents DESC;
Marking guide: 1 mark for the join, 1 mark for COUNT with GROUP BY, 1 mark for HAVING, 1 mark for ORDER BY DESC.
exam6 marksA bank transfers 500 dollars from account A to account B. Explain each ACID property in this context, and write SQL for the transfer as a transaction.Show worked solution →
BEGIN TRANSACTION;
UPDATE ACCOUNT SET Balance = Balance - 500 WHERE AccountID = 'A';
UPDATE ACCOUNT SET Balance = Balance + 500 WHERE AccountID = 'B';
COMMIT;
- Atomicity: both updates happen or neither does; if the second fails, the first is rolled back, so money is not lost.
- Consistency: the database moves from one valid state to another (total money unchanged, constraints such as no negative balances respected).
- Isolation: another transaction reading the accounts mid-transfer does not see the half-finished state.
- Durability: once committed, the transfer survives a crash or power failure.
Marking guide: 2 marks for correct SQL transaction, 1 mark per ACID property explained in context (4 marks).
core5 marksA school library stores loans in LOAN(LoanID, BookID, MemberID, LoanDate, DaysAllowed). BOOK(BookID, Title) and MEMBER(MemberID, Name) already exist, with integer primary keys. (a) Write a CREATE TABLE statement for LOAN in which LoanID is the primary key, BookID and MemberID are foreign keys to BOOK and MEMBER, LoanDate must always be entered, and DaysAllowed must be between 7 and 28. (4 marks) (b) Write an INSERT statement that records loan 1001 of book 52 to member 7 on 2026-10-05 for 14 days. (1 mark)Show worked solution →
(a)
CREATE TABLE LOAN (
LoanID INTEGER PRIMARY KEY,
BookID INTEGER NOT NULL,
MemberID INTEGER NOT NULL,
LoanDate DATE NOT NULL,
DaysAllowed INTEGER CHECK (DaysAllowed BETWEEN 7 AND 28),
FOREIGN KEY (BookID) REFERENCES BOOK(BookID),
FOREIGN KEY (MemberID) REFERENCES MEMBER(MemberID)
);
(b)
INSERT INTO LOAN (LoanID, BookID, MemberID, LoanDate, DaysAllowed)
VALUES (1001, 52, 7, '2026-10-05', 14);
With these constraints the database rejects a loan for a book or member that does not exist (referential integrity) and a DaysAllowed value such as 60 (domain integrity).
Marking guide: (a) 1 mark for sensible data types and the PRIMARY KEY, 1 mark for both FOREIGN KEY ... REFERENCES clauses, 1 mark for NOT NULL on LoanDate, 1 mark for the CHECK constraint on DaysAllowed (4 marks). (b) 1 mark for a correct INSERT with values in the right order. Total 5 marks.
exam5 marksA school's homework app connects to its database through ODBC. The login code builds its query by joining text: "SELECT * FROM USERS WHERE Username = '" + username + "' AND Password = '" + password + "'". (a) Explain the role of ODBC in connecting the app to the database. (2 marks) (b) A user types nobody as the username and ' OR '1'='1 as the password. Show the query that is produced and explain why it lets the user log in. (2 marks) (c) State how the query should be written to prevent this attack. (1 mark)Show worked solution →
(a) ODBC (Open Database Connectivity) is a standard interface between an application and a database. The app sends SQL through the ODBC interface, and a driver for the particular database system translates the requests, so the same app code can work with different database systems. The app connects using a connection string that identifies the data source and credentials.
(b) The query produced is:
SELECT * FROM USERS WHERE Username = 'nobody' AND Password = '' OR '1'='1'
This is SQL injection: the quote in the input closes the password string, and the typed text becomes part of the SQL code. AND is evaluated before OR, so the condition is (Username = 'nobody' AND Password = '') OR '1'='1'. Because '1'='1' is always true, every row of USERS is returned and the app treats the login as successful.
(c) Use a parameterised query, for example SELECT * FROM USERS WHERE Username = ? AND Password = ?, with the username and password passed as parameters. The input is then treated only as data, never as SQL code, so the same input simply matches no user.
Marking guide: (a) 1 mark for ODBC as a standard interface between application and database, 1 mark for drivers or connection strings allowing different database systems. (b) 1 mark for the correct resulting query, 1 mark for explaining that the always true condition returns every row. (c) 1 mark for parameterised queries treating input as data. Total 5 marks.
exam14 marksA small online plant nursery uses these tables: CUSTOMER(CustomerID, Name, Suburb), PRODUCT(ProductID, ProductName, Category, Price), ORDERS(OrderID, CustomerID, OrderDate) and ORDER_LINE(OrderID, ProductID, Qty). ORDER_LINE has the composite primary key (OrderID, ProductID). Sample rows: PRODUCT (10, Lemon Tree, Fruit tree, 45.00), (11, Basil Seedling, Herb, 4.50), (13, Kangaroo Paw, Native, 18.00). Write SQL for each of the following. (a) List the name and price of every product in the Native category that costs less than 20 dollars, cheapest first. (2 marks) (b) Show each suburb and the number of orders placed by customers in that suburb. (3 marks) (c) List the ID and name of customers who have never placed an order. (3 marks) (d) Show each OrderID with the total value of the order (quantity multiplied by price), only for orders worth more than 100 dollars, highest total first. (4 marks) (e) Increase the price of every product in the Herb category by 10 per cent. Explain what would happen if the WHERE clause were left out. (2 marks)Show worked solution →
(a)
SELECT ProductName, Price
FROM PRODUCT
WHERE Category = 'Native' AND Price < 20
ORDER BY Price ASC;
(b)
SELECT CUSTOMER.Suburb, COUNT(ORDERS.OrderID) AS NumOrders
FROM CUSTOMER
INNER JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID
GROUP BY CUSTOMER.Suburb;
The join links each order to its customer's suburb, and GROUP BY produces one count per suburb. A LEFT JOIN from CUSTOMER is also accepted; it additionally lists suburbs with 0 orders.
(c)
SELECT CUSTOMER.CustomerID, CUSTOMER.Name
FROM CUSTOMER
LEFT JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.OrderID IS NULL;
The LEFT JOIN keeps every customer; customers with no matching order have NULL in the ORDERS columns, so the WHERE clause selects them. An INNER JOIN would not work because it drops those customers.
(d)
SELECT ORDER_LINE.OrderID, SUM(ORDER_LINE.Qty * PRODUCT.Price) AS OrderTotal
FROM ORDER_LINE
INNER JOIN PRODUCT ON ORDER_LINE.ProductID = PRODUCT.ProductID
GROUP BY ORDER_LINE.OrderID
HAVING SUM(ORDER_LINE.Qty * PRODUCT.Price) > 100
ORDER BY OrderTotal DESC;
HAVING is needed (not WHERE) because the condition is on the aggregated total of each group.
(e)
UPDATE PRODUCT
SET Price = Price * 1.1
WHERE Category = 'Herb';
Without the WHERE clause the UPDATE changes every row, so every product's price would rise by 10 per cent, not just the herbs.
Marking guide: (a) 1 mark for SELECT, FROM and the combined WHERE condition, 1 mark for ORDER BY Price. (b) 1 mark for the join on CustomerID, 1 mark for COUNT, 1 mark for GROUP BY Suburb. (c) 1 mark for LEFT JOIN, 1 mark for the correct join condition, 1 mark for WHERE ... IS NULL. (d) 1 mark for the join to PRODUCT, 1 mark for SUM(Qty * Price), 1 mark for GROUP BY with HAVING > 100, 1 mark for ORDER BY DESC. (e) 1 mark for a correct UPDATE with SET and WHERE, 1 mark for explaining that every row would change. Total 14 marks.
exam18 marksA physiotherapy clinic is replacing its spreadsheets with a database and a booking app. The tables are PATIENT(PatientID, FirstName, Surname, Phone, DateOfBirth), SLOT(SlotID, PhysioID, SlotStart, IsBooked) and APPOINTMENT(ApptID, PatientID, SlotID, Reason, DurationMin). IsBooked is 0 for a free slot and 1 for a taken slot. (a) Write a CREATE TABLE statement for APPOINTMENT with a primary key, foreign keys to PATIENT and SLOT, Reason required, and DurationMin between 15 and 90. (4 marks) (b) Existing patient details are in a CSV file exported from the spreadsheet. Describe three steps the clinic should take to import this data correctly. (3 marks) (c) Explain how the booking app, written in a different language from the database system, can connect to the database. (2 marks) (d) Booking a slot requires marking the slot as taken and inserting the appointment. Write SQL for booking slot 305 for patient 501 as a single transaction, and explain atomicity and isolation for this booking, including when two receptionists try to book slot 305 at the same moment. (4 marks) (e) Discuss the ethical, security and legal issues the clinic must manage when storing patient data. (5 marks)Show worked solution →
(a)
CREATE TABLE APPOINTMENT (
ApptID INTEGER PRIMARY KEY,
PatientID INTEGER NOT NULL,
SlotID INTEGER NOT NULL,
Reason TEXT NOT NULL,
DurationMin INTEGER CHECK (DurationMin BETWEEN 15 AND 90),
FOREIGN KEY (PatientID) REFERENCES PATIENT(PatientID),
FOREIGN KEY (SlotID) REFERENCES SLOT(SlotID)
);
(b) Any three of:
- Clean the data first: make formats consistent (for example all dates in one format, phone numbers without spaces) and remove duplicate patients.
- Check keys: every row needs a unique, non-null PatientID.
- Map columns to fields and check each value matches the field's data type and constraints.
- Verify after import: compare the number of rows imported with the number in the CSV file and spot check records.
(c) Through a standard connectivity interface such as ODBC. The app sends SQL through the interface, and a driver for the clinic's database system translates it, so an app in any language can use the database. The app connects with a connection string naming the data source and credentials (which should be stored securely, not in plain text in the code).
(d)
BEGIN TRANSACTION;
UPDATE SLOT SET IsBooked = 1 WHERE SlotID = 305 AND IsBooked = 0;
INSERT INTO APPOINTMENT (ApptID, PatientID, SlotID, Reason, DurationMin)
VALUES (9001, 501, 305, 'Knee rehabilitation', 45);
COMMIT;
- Atomicity: both statements succeed or neither does. If the insert fails (for example the PatientID does not exist), the whole transaction is rolled back, undoing the slot update, so a slot is never marked taken without an appointment, or the reverse. If the UPDATE changes no rows because the slot is already taken, the app should roll back instead of committing.
- Isolation: the two receptionists' transactions do not interfere. One transaction marks slot 305 as taken first; the other does not see a half-finished state and, when it runs, finds IsBooked is already 1, so its update changes no rows and it is rolled back. The slot cannot be double booked.
(e) A strong answer covers ethical, security and legal points, for example:
- Privacy (legal and ethical): collect only the patient data needed for treatment and bookings, and protect it as APP 11 requires. If a data breach occurs it must be handled under the Notifiable Data Breaches scheme.
- Security: role-based access control so staff see only what their job needs, encryption of stored data and connections, regular backups, parameterised queries in the booking app to stop SQL injection, and audit logs recording who viewed or changed records.
- Accuracy and fairness: wrong or out of date records (such as a wrong phone number or date of birth) can harm patients, so data must be kept accurate and up to date.
- Ownership and consent: patients' data should be used only for the purposes they agreed to, for example not for marketing without consent.
Marking guide: (a) 1 mark for the PRIMARY KEY and data types, 1 mark for both foreign keys, 1 mark for NOT NULL on Reason, 1 mark for the CHECK on DurationMin (4 marks). (b) 1 mark per valid import step (3 marks). (c) 1 mark for ODBC or a similar interface with drivers, 1 mark for the connection string and credentials (2 marks). (d) 2 marks for a correct transaction containing both statements, 1 mark for atomicity in context, 1 mark for isolation with the two receptionists (4 marks). (e) 1 mark for privacy with APP 11, 1 mark for the Notifiable Data Breaches scheme, 2 marks for two distinct security measures, 1 mark for an accuracy or consent issue explained in context (5 marks). Total 18 marks.