G.C.E. A/L ICT Unit 9.2: Python Databases, Searching & Sorting Complete A/L ICT Unit 9 notes covering Python SQLite database management

💻 G.C.E. A/L ICT – Unit 9: Programming

Database Management, Searching & Sorting Algorithms

📚 About this guide: Complete, exam-focused notes for G.C.E. Advanced Level ICT Unit 9. Covers Python SQLite Database Management (SELECT, INSERT, UPDATE, DELETE), Sequential Search, and Bubble Sort with theory, Python code examples, step-by-step sorting passes, and model exam questions.

Definitions

Important Database Terms

• Database: An organized collection of related data.
• Table: A structure used to store data in rows and columns.
• Record: A complete row of information (e.g., 101 | Amal | 18 | 75).
• Field: A column representing one type of information (e.g., Name).
• Primary Key: A field that uniquely identifies each record (e.g., StudentID).

SQL Basics

The 4 Main SQL Operations (S-I-U-D)

Operation	SQL Command	Purpose
Retrieve	SELECT	Get data
Add	INSERT	Add a new record
Modify	UPDATE	Change existing data
Delete	DELETE	Remove data

🎯 Memory Trick: SELECT = See | INSERT = Add | UPDATE = Change | DELETE = Remove

Setup

Connecting & Creating a Cursor

import sqlite3

# 1. Connect to database
conn = sqlite3.connect("school.db")

# 2. Create a cursor
cursor = conn.cursor()
          

Why a cursor? It is used to execute SQL statements and retrieve results from the database.

Retrieve

SELECT & Fetching Data

# Retrieve all records
cursor.execute("SELECT * FROM Student")
rows = cursor.fetchall()  # Gets ALL remaining records

for row in rows:
    print(row)

# OR retrieve just one record
row = cursor.fetchone()
print(row)
          

Modify

INSERT, UPDATE, DELETE & commit()

# INSERT with Python variables (using ? placeholders)
cursor.execute("INSERT INTO Student VALUES (?, ?, ?, ?)", 
               (106, "Ravi", 18, 88))

# UPDATE specific record
cursor.execute("UPDATE Student SET Marks = 90 WHERE StudentID = 102")

# DELETE specific record
cursor.execute("DELETE FROM Student WHERE StudentID = 103")

# ⭐ ALWAYS save changes!
conn.commit()
conn.close()
          

⚠️ Critical Exam Point: Always use conn.commit() after INSERT, UPDATE, or DELETE to save changes. Also, always use a WHERE clause in UPDATE/DELETE, otherwise you will modify or delete all records!

Searching

Sequential (Linear) Search

Theory: Examines elements one by one from the beginning until the required value is found or all elements are checked.

numbers = [10, 25, 30, 45, 60]
search = 45
found = False

for i in range(len(numbers)):
    if numbers[i] == search:
        print("Found at position", i)
        found = True
        break

if not found:
    print("Not found")
          

Sorting

Bubble Sort

Theory: Repeatedly compares adjacent elements and swaps them if they are in the wrong order. Larger elements "bubble" to the end.

numbers = [5, 3, 8, 4, 2]
n = len(numbers)

for i in range(n - 1):
    for j in range(n - 1 - i):
        if numbers[j] > numbers[j + 1]:
            # Swap elements
            numbers[j], numbers[j + 1] = numbers[j + 1], numbers[j]

print(numbers) # Output: [2, 3, 4, 5, 8]
          

🎯 Why n - 1 - i? After each pass, the largest unsorted element reaches its correct final position at the end. Therefore, we don't need to compare it again, reducing the number of comparisons in each subsequent pass.

Comparison

Sequential Search vs. Bubble Sort

Feature	Sequential Search	Bubble Sort
Purpose	Find data	Arrange data
Mechanism	Checks elements sequentially	Compares adjacent elements
Data Change	Does not change data order	Changes the order of data

📌 A/L Exam Practice Questions

Q1

Write an SQL statement to retrieve names of students with marks > 80.

SELECT Name FROM Student WHERE Marks > 80;

Q2

Write an SQL statement to change the marks of StudentID 5 to 95.

UPDATE Student SET Marks = 95 WHERE StudentID = 5;

Q3

What searching technique examines data from the first element toward the last?

Answer: Sequential search.

Q4

What is the purpose of if numbers[j] > numbers[j + 1]: in Bubble Sort?

Answer: It compares two adjacent elements to determine if they need to be swapped to achieve ascending order.

⭐ Memorize vs. Understand

🧠 MEMORIZE:
SELECT → Retrieve | INSERT → Add | UPDATE → Modify | DELETE → Delete
fetchall() → all records | fetchone() → one record | commit() → save changes

💡 UNDERSTAND:
1. How Python connects to a database and executes SQL.
2. How sequential search checks items one by one.
3. How bubble sort compares adjacent values, swaps them, and why comparisons decrease each pass (n - 1 - i).

❓ Frequently Asked Questions (FAQ)

Q: What is a Primary Key in a database? A: A Primary Key is a field that uniquely identifies each record in a database table. For example, a StudentID ensures that no two students have the same identifier.

Q: What is the difference between Sequential Search and Bubble Sort? A: Sequential Search is a searching algorithm used to find a specific item by checking elements one by one from the beginning. Bubble Sort is a sorting algorithm that repeatedly compares adjacent elements and swaps them if they are in the wrong order to arrange data.

Q: Why is conn.commit() used in Python database programming? A: The conn.commit() method is used to save changes made to the database. It is required after executing INSERT, UPDATE, or DELETE statements to permanently apply the modifications.

Q: What are the four main SQL operations? A: The four main SQL operations are: SELECT (Retrieve data), INSERT (Add a new record), UPDATE (Modify existing data), and DELETE (Remove data).

Q: Why does the inner loop in Bubble Sort use range(n - 1 - i)? A: After each pass, the largest unsorted element "bubbles" to its correct final position at the end of the list. Therefore, we don't need to compare it again, reducing the number of comparisons in each subsequent pass.