TK Study
HomeMemoriseFlashcardsNotesMaster answersPast questionsMultiple choiceMock examPractice & marker

Software Engineering › Notes › Python and SQL you must be able to write

Python and SQL you must be able to write

Python basics: input, selection, loops, strings

python
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

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

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

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

python
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'

python
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])

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

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

Functions and modules

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

print(calc_area(3, 4))      # 12

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

python
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

File handling

python
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")

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

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

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

Python OOP: classes, inheritance, polymorphism, encapsulation

python
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())

python
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

python
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

python
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

Secure Python: validation and parameterised SQL

python
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")

python
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")

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

Python with sqlite3 and parameterised queries

python
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

python
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()

python
# 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

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

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

Flask routes, sessions, templates (back-end web extension)

python
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}"

python
@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")

python
@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"))

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

Worked Python: unique random numbers in a grid (2025 Q26 style)

python
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")

python
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)

Common coding patterns

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

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

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

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

python
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]

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

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"

SQL: HSC syntax and must-know queries

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

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

sql
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

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

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

sql
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);

Part 2. School notes (Canvas, 12SENX2) by syllabus dot point

Corrections are marked in the text. Anything not marked is the school wording.

Python reference (school notes)

Core Built-ins
len(x)              # length of iterable
max(x), min(x)      # largest / smallest
sum(x)              # sum of numbers
sorted(x, key=..., reverse=True)   # returns sorted list
all(x)              # True if all elements truthy
any(x)              # True if any element truthy
range(start, stop, step)   # sequence generator
type(x)             # object type
int(x), float(x), str(x), complex(x)   # type conversions
list(), tuple(), set(), dict()        # convert/construct
zip(a, b)           # pair elements
enumerate(x)        # (index, value) pairs
bin(n), hex(n)      # convert to binary/hex string
ord(c), chr(i)      # char ↔ Unicode code point
Strings
s.upper(), s.lower()        # case change
s.isupper(), s.islower()    # case checks
s.isdigit(), s.isalpha(), s.isalnum(), s.isdecimal(), s.isnumeric()
s.startswith(sub), s.endswith(sub)   # prefix/suffix check
s.count(sub)                # count occurrences
s.find(sub), s.index(sub)   # find substring (-1 vs error)
s.replace(old, new)         # replace substring
s.strip(chars), s.lstrip(), s.rstrip()  # remove whitespace/chars
s.split(sep)                # split into list
sep.join(list)              # join list → string
len(s)                      # string length
s[0], s[-1]                 # indexing
s[a:b:c]                    # slicing with step
Lists
l.append(x)        # add single element
l.extend(iterable) # add multiple elements
l.insert(i, x)     # insert at index
l.remove(x)        # remove first occurrence
l.pop(i)           # remove & return element (default last)
l.count(x)         # count occurrences
l.index(x)         # find index of element
l.sort(key=..., reverse=True)   # sort in place
l.reverse()        # reverse list
del l[i]           # delete element
list(reversed(l))  # reversed copy
list(zip(a, b))    # zip back into tuples
Tuples
t = (1, 2, 3)      # tuple creation
x, y = (1, 2)      # unpacking
t.count(x), t.index(x)   # count / index
Sets
s.add(x), s.update(iterable)       # add elements
s.remove(x), s.discard(x)          # remove (discard = safe)
s | t          # union
s & t          # intersection
s - t          # difference
s ^ t          # symmetric difference
Dictionaries
d.get(key, default)        # safe retrieval
d.keys(), d.values(), d.items()   # views
d.update({k:v})            # add/update multiple
d.pop(key), d.popitem()    # remove & return
d.clear()                  # empty dictionary
d[k] = v                   # add/update key
del d[k]                   # delete key
for k in d: print(k)            # keys
for v in d.values(): print(v)   # values
for k, v in d.items(): print(k, v)   # key + value pairs
File Handling
f = open("file.txt", "r")   # open file
f.read(), f.readline(), f.readlines()   # read content
f.write("text"), f.writelines(list)     # write content
f.close()                   # close file
with open("file.txt") as f: # auto-close
data = f.read()
User I/O
name = input("Enter name: ")     # input from user
print(a, b, sep="*", end="\\")   # custom print
Errors & Exceptions
try:
except ValueError:
except:
else:
finally:
Math / Random / Useful Modules
import math
math.pi
math.sqrt(16)
math.ceil(x)
math.floor(x)
import random
random.random()   #random float between 0 and 1
random.randint(a, b)   # random int
random.choice(list)    # random element

Part 3. Where this has been examined (see ../../question_bank.md)

ID Marks Source Topic / type
hsc2025-q9 1 2025 HSC ORM and SQL (matching table (tick which approach))
hsc2025-q19 3 2025 HSC Developing secure code (Python) (Python)
hsc2025-q21 4 2025 HSC SQL queries (SQL)
hsc2025-q26 5 2025 HSC Design, construct and implement a solution (Python) (Python)
sample-q17 3 Sample Exam SQL (join, filter, order) (SQL)
sample-q18b 3 Sample Exam Developing secure code (Python input validation) (Python (function))
sample-q21a 1 Sample Exam 2D arrays (Python) (multiple choice (Python))
sample-q21b 5 Sample Exam Python: subroutines, lists, loops (Python (subroutines, 2D list))
sample-q22a 2 Sample Exam SQL (GROUP BY) (SQL (fill the blanks))
fam-q2 3 Familiarisation SQL (multi-select (SQL))
fam-q9b 3 Familiarisation Developing secure code (Python input validation) (Python (function))
fam-q12a 2 Familiarisation SQL (table (SQL output))
fam-q12b 3 Familiarisation SQL (joins, AS) (SQL (three tables, aliases))
fam-q13 6 Familiarisation Python: lists, loops, selection (Python)
← Year 11 content (Programming fundamentals, OOP, Mechatronics)Pseudocode, flowcharts and diagram rules →