Python and SQL you must be able to write

Input and output —

name = input("Enter your name: ")      # input() always returns a string
age = int(input("Enter age: "))        # convert to a number
print("Hello", name, "you are", age)
print(f"Hello {name}")                 # f-string

Selection —

if mark >= 90:
    grade = "A"
elif mark >= 80:
    grade = "B"
else:
    grade = "C"

for loop —

for i in range(1, 6):          # 1,2,3,4,5 (stop is excluded)
    print(i)
for item in my_list:
    print(item)

while loop —

count = 0
while count < 5:
    count = count + 1

Strings —

s = "software"
s[:3]            # 'sof'  (first 3 characters, slicing)
s[-1]            # 'e'    (last character)
len(s)           # 8
s.upper()        # 'SOFTWARE'
"war" in s       # True
s.replace("soft", "hard")
", ".join(["a", "b"])   # 'a, b'

Lists —

nums = [4, 8, 15]
nums.append(16)
nums.remove(8)
total = sum(nums)
biggest = max(nums)
nums.sort()
for i in range(len(nums)):
    print(i, nums[i])

Dictionary —

ages = {"Sam": 17, "Alex": 18}
ages["Jo"] = 16
for key in ages:
    print(key, ages[key])

Global and local —

A variable created inside a function is local to that function. A variable created outside is global; to change a global inside a function use the global keyword. Pass values in as parameters and use return instead of globals where possible.

total = 0
def add(n):
    global total
    total = total + n

Function with parameters and return —

def calc_area(width, height):
    area = width * height
    return area

print(calc_area(3, 4))      # 12

Calling after defining (2025 marker note) — Markers noted students wrote functions but never called them. Define the function, then call it, and print or use the returned value.

Modules —

import random
import math
n = random.randint(1, 25)        # inclusive both ends
pick = random.choice(["a", "b"])
r = math.sqrt(16)                # 4.0

Exceptions —

try:
    number = int(input("Number: "))
    result = 10 / number
except ValueError:
    print("Please enter a whole number")
except ZeroDivisionError:
    print("Cannot divide by zero")
else:
    print(result)
finally:
    print("Done")           # always runs

Write and append —

with open("scores.txt", "w") as f:     # "w" overwrites, "a" appends
    f.write("Sam,85\n")
with open("scores.txt", "a") as f:
    f.write("Alex,90\n")

Read line by line (sequential file) —

with open("scores.txt", "r") as f:
    for line in f:
        name, mark = line.strip().split(",")
        print(name, int(mark))

Read all —

with open("scores.txt") as f:
    text = f.read()          # whole file as a string
    # f.readlines() gives a list of lines

Missing file —

try:
    open("missing.txt")
except FileNotFoundError:
    print("File not found")

Why with — The with statement closes the file automatically, even if an error occurs (good memory and exception management).

Class with constructor and methods —

class Person:
    def __init__(self, name, age):
        self.name = name        # attributes
        self.age = age

    def greet(self):
        return "Hi, I am " + self.name

p = Person("Sam", 17)
print(p.greet())

Encapsulation (private attribute, getter and setter) —

class Account:
    def __init__(self, owner, balance):
        self.owner = owner
        self.__balance = balance          # double underscore = private

    def get_balance(self):
        return self.__balance

    def deposit(self, amount):
        if amount > 0:                    # validation protects the data
            self.__balance += amount

Inheritance —

class Student(Person):                    # Student inherits from Person
    def __init__(self, name, age, student_id):
        super().__init__(name, age)       # call the parent constructor
        self.student_id = student_id

    def greet(self):                      # overriding
        return "Hi, I am student " + self.name

Polymorphism —

class Dog:
    def speak(self):
        return "Woof"
class Cat:
    def speak(self):
        return "Meow"

for animal in [Dog(), Cat()]:
    print(animal.speak())      # same method name, different behaviour

Class diagram link (2025 Q22) — Draw boxes with three parts (name, attributes, methods); an arrow from subclass to superclass shows inheritance; write the inherited items only in the parent.

Message passing — One object calls a method on another, e.g. order.add_item(item); the call is the message.

Input validation —

def get_age():
    while True:
        text = input("Age (0-120): ")
        if text.isdigit() and 0 <= int(text) <= 120:
            return int(text)
        print("Invalid age, try again")

Password check using first 3 letters + 123 (2025 Q19) —

uName = input("What is your username? ")
password = input("What is your password? ")
expected = uName[:3] + "123"
if password == expected:
    print("Welcome!")
else:
    print("Incorrect password entered")

Hashing a password (hashlib) —

import hashlib
hashed = hashlib.sha256("secret".encode()).hexdigest()

Caution — Real systems should use a salt and a slow password hash (bcrypt, argon2); plain SHA-256 is shown only as the idea of hashing. Never store plain-text passwords.

Why parameterised queries — Putting user input straight into SQL text lets an attacker change the query (SQL injection). The ? placeholder sends the value separately as DATA, so it can never be run as SQL.

Connect, create, insert (parameterised) —

import sqlite3
conn = sqlite3.connect("school.db")
cur = conn.cursor()
cur.execute("""CREATE TABLE IF NOT EXISTS Student (
                   StudentID INTEGER PRIMARY KEY,
                   Name TEXT NOT NULL,
                   Mark INTEGER)""")
cur.execute("INSERT INTO Student (Name, Mark) VALUES (?, ?)", ("Sam", 85))
conn.commit()          # save the changes

Select —

cur.execute("SELECT Name, Mark FROM Student WHERE Mark >= ?", (80,))
for name, mark in cur.fetchall():
    print(name, mark)
row = cur.fetchone()   # one row or None
conn.close()

SQL injection (bad) —

# NEVER do this: user_input is joined into the SQL text
cur.execute("SELECT * FROM Student WHERE Name = '" + user_input + "'")
# input  x' OR '1'='1  returns every row

SQL injection (safe) —

cur.execute("SELECT * FROM Student WHERE Name = ?", (user_input,))

Update and delete —

cur.execute("UPDATE Student SET Mark = ? WHERE StudentID = ?", (90, 1))
cur.execute("DELETE FROM Student WHERE StudentID = ?", (1,))
conn.commit()

CRUD — Create (INSERT), Read (SELECT), Update (UPDATE), Delete (DELETE).

Note — Flask is not named in the syllabus. It is the most common Python back-end example, so know the ideas: routing, request data, sessions, templates, parameterised queries.

Minimal app and routes —

from flask import Flask, render_template, request, redirect, url_for, session

app = Flask(__name__)
app.secret_key = "change-me"          # needed to sign the session cookie

@app.route("/")
def home():
    return render_template("home.html", title="Home")

@app.route("/item/<int:item_id>")
def item(item_id):
    return f"Item {item_id}"

Form handling (GET and POST) —

@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        username = request.form["username"]
        password = request.form["password"]
        if check_user(username, password):      # your own function
            session["user"] = username          # store in the session
            return redirect(url_for("dashboard"))
        return render_template("login.html", error="Invalid login")
    return render_template("login.html")

Protecting a page with the session —

@app.route("/dashboard")
def dashboard():
    if "user" not in session:
        return redirect(url_for("login"))
    return render_template("dashboard.html", user=session["user"])

@app.route("/logout")
def logout():
    session.clear()                            # end the session
    return redirect(url_for("login"))

Jinja2 template —

<h1>Welcome {{ user }}</h1>
{% for item in items %}
  <p>{{ item.name }}</p>
{% endfor %}

Template escaping — Jinja2 escapes {{ }} output by default, which helps stop XSS. Do not mark user input as safe.

Session idea — Flask stores session data in a signed cookie. Security: set a strong secret key, keep little data in it, clear it on logout, use HTTPS and secure cookie settings.

Route returns — A route function returns a string, rendered template, redirect or JSON (jsonify).

Task —

Generate 15 unique random numbers from 1 to 25, then print a 5 by 5 grid marking an X where the position number is in the list. (NESA 2025 Q26 sample answer, tidied.)

import random

rand_list = []
for x in range(15):
    next_int = random.randint(1, 25)
    while next_int in rand_list:          # keep numbers unique
        next_int = random.randint(1, 25)
    rand_list.append(next_int)

pos = 0
while pos < 25:
    if pos in rand_list:
        print("| X", end=" ")
    else:
        print("|  ", end=" ")
    pos = pos + 1
    if pos % 5 == 0:
        print("|\n")

Note on the NESA sample — The sample tests positions 0 to 24 against numbers 1 to 25, so number 25 can never print and 0 never exists; markers still accepted it. Safer: test pos + 1 in rand_list.

Alternative using the library — rand_list = random.sample(range(1, 26), 15) creates 15 unique numbers in one call (an inbuilt Python function; the MG criteria reward inbuilt functions).

What earns the marks (5) — Creates a list of unique random numbers, processes the list correctly, uses selection AND looping, correctly formatted output, uses inbuilt Python functions. (Marker note: call the function after defining it.)

Two-dimensional list —

grid = [[0] * 5 for _ in range(5)]     # 5 x 5 of zeros
grid[2][3] = 1                         # row 2, column 3
for row in grid:
    print(row)

Total and average —

total = 0
for m in marks:
    total = total + m
average = total / len(marks)

Find the maximum (without max) —

highest = marks[0]
for m in marks:
    if m > highest:
        highest = m

Linear search —

found = False
for i in range(len(items)):
    if items[i] == target:
        found = True
        position = i
        break

Counting —

count = 0
for word in words:
    if word == "yes":
        count += 1

Bubble sort —

for i in range(len(a) - 1):
    for j in range(len(a) - 1 - i):
        if a[j] > a[j + 1]:
            a[j], a[j + 1] = a[j + 1], a[j]

Validation loop —

mark = int(input("Mark 0-100: "))
while mark < 0 or mark > 100:
    mark = int(input("Invalid. Mark 0-100: "))

RiskRating (2025 Q17 in Python) —

total = 0
for score in scores:           # 10 items
    total = total + score
if total > 35:
    rating = "High"
elif total < 20:
    rating = "Low"
else:
    rating = "Medium"

HSC syntax (course spec) — SELECT fields or calculated values; FROM tables; WHERE search criteria; GROUP BY fields to group rows; ORDER BY the sequence of results (ASC or DESC). AS renames a field for display.

Operators allowed in WHERE — CONTAINS, DOES NOT CONTAIN, EQUALS, NOT EQUAL TO, GREATER THAN, GREATER THAN OR EQUAL TO, LESS THAN, LESS THAN OR EQUAL TO; logical AND, OR, NOT. Aggregates: SUM, AVG, COUNT, MAX, MIN.

Basic select —

SELECT Name, Release_date
FROM Games
WHERE Release_date >= '01/03/2022' AND Release_date <= '31/03/2023'
ORDER BY Name ASC

Marker note — Use quotation marks round text and date values. Show the whole statement and correct keyword order (SELECT, FROM, WHERE, GROUP BY, ORDER BY).

Group and count (2025 Q21 sample) —

SELECT Category, Status, COUNT(JobID) AS Jobs
FROM Issues
WHERE (Status = 'Pending' OR Status = 'Escalated')
GROUP BY Status, Category
ORDER BY Jobs DESC

Join (course-spec form) —

SELECT Developers.First_name, Developers.Last_name, SUM(Games.cost) AS Totalcost
FROM Games, Publishers, Developers
WHERE Publishers.Name = 'Games Inc'
  AND Publishers.Publisher_ID = Games.Publisher_ID
  AND Developers.Developer_ID = Games.Developer_ID
GROUP BY Developers.Developer_ID
ORDER BY Developers.Last_name DESC

JOIN keyword (also accepted in SQL) —

SELECT Student.Name, Subject.Title
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID
INNER JOIN Subject ON Enrolment.SubjectID = Subject.SubjectID

Join rule — Join tables by matching the primary key of one table to the foreign key in the other; without the matching condition you get every combination of rows.

Aggregates —

SELECT AVG(Mark) AS Average, MAX(Mark) AS Highest, MIN(Mark) AS Lowest
FROM Results
WHERE Subject = 'Software Engineering'

Insert, update, delete (CRUD) —

INSERT INTO Student (Name, Mark) VALUES ('Sam', 85);
UPDATE Student SET Mark = 90 WHERE StudentID = 1;
DELETE FROM Student WHERE StudentID = 1;
CREATE TABLE Student (StudentID INTEGER PRIMARY KEY, Name TEXT NOT NULL, Mark INTEGER);

Pattern search — Course spec uses CONTAINS. Most SQL uses LIKE: WHERE Name LIKE '%son%' (% = any characters).

WHERE v GROUP BY — WHERE filters rows before grouping. GROUP BY gathers rows with the same value so aggregates (COUNT, SUM) can be calculated for each group. Any non-aggregated field in SELECT should be in GROUP BY.

Primary and foreign key — Primary key uniquely identifies each row in a table. Foreign key is a field that holds the primary key of another table to create the relationship.

Relationships (ERD) — One-to-many (one customer, many orders) is stored by putting the foreign key on the 'many' side. Many-to-many needs a linking (junction) table, e.g. Enrolment between Student and Subject.