Topics from the IB Computer Science syllabus.

When working with relational databases, it is often necessary to retrieve data that is spread across more than one table. SQL supports this through queries that combine tables and apply conditions to control which data is returned and how it is presented. At this level, queries are expected to include joins, relational operators, filtering, pattern matching, and ordering data.

This section explains these required features conceptually, focusing on what each feature achieves rather than detailed syntax

SECTION 1 | QUERIES OVERVIEW

Joining tables
In a relational database, tables are linked using common fields, usually a primary key in one table and a foreign key in another. A query that retrieves data from two tables must use a JOIN to combine related records.

Joins allow SQL to:

Match rows from two tables based on a related field
Display information from both tables in a single result set
Avoid duplicating data across tables

Without joins, queries would be limited to a single table and could not reflect relationships such as customers and orders or students and classes.

Relational Operators
Relational operators are used to compare values within a query. They allow conditions to be applied based on numerical or logical relationships.
Common relational operators include:

Equal to (=)
Not equal to (!= or <>)
Greater than (>)
Less than (<)
Greater than or equal to (>=)
Less than or equal to (<=)

These operators are essential for controlling which records are selected when querying data from one or more tables.

Filtering Data
Filtering is the process of restricting the rows returned by a query so that only relevant data is displayed. Filtering is based on conditions applied to one or more fields.
Filtering allows a query to:

Select only records that meet specific criteria
Combine multiple conditions using logical operators
Reduce large datasets to meaningful results

When querying multiple tables, filtering can be applied to fields from either table after they have been joined.

Pattern Matching
Pattern matching allows queries to find text-based data that follows a specific pattern, rather than requiring an exact match.
This is particularly useful when:

Searching for values that start or end with certain characters
Matching partial strings
Handling user input where full values may not be known

Pattern matching increases query flexibility and is commonly used when working with names, email addresses, or codes stored as text.

Ordering Data
Ordering controls the sequence in which query results are displayed. By default, SQL does not guarantee any specific order, so explicit ordering is required when sorted output is needed.
Ordering allows results to be:

Sorted in ascending or descending order
Organised alphabetically or numerically
Presented in a meaningful and readable way

When querying joined tables, ordering can be applied to fields from either table in the result set.

Summary
To construct effective queries between two tables, SQL queries must be able to:

Join related tables to combine data
Use relational operators to compare values
Filter results to include only relevant records
Apply pattern matching for flexible text searches
Order data to control how results are displayed

These features work together to produce precise, meaningful query results from relational databases.

SECTION 2 | PRACTICE SCENARIO

This section includes a scenario and sample data for you to use to practice the SQL commands from later sections.

Scenario:
School Library DatabaseA secondary school uses a computerised library system to manage books and student borrowing.
The librarian wants to store information about:

Books available in the library
Students who can borrow books
Loans that record which student has borrowed which book and when

The database will allow the librarian to:

Find all books by a specific author
Check which books are currently on loan
See which student borrowed a particular book
Count how many books each student has borrowed

Students will later use SQL queries to retrieve and manipulate this data.

Database Tables Overview
The database contains three tables:

Students – stores student details
Books – stores book details
Loans – links students to books they have borrowed

Primary and foreign keys are included so students can practise JOIN operations.

Table 1: Students (Sample Data)This table stores information about students registered with the library.
Fields

StudentID (Primary Key)
FirstName
LastName
YearGroup

Students table (sample data)

StudentID	FirstName	LastName	YearGroup
101	Aisha	Khan	11
102	Ben	Carter	10
103	Clara	Nguyen	12
104	Daniel	Smith	11

Table 2: Books (Sample Data)This table stores details of each book in the library.
Fields

BookID (Primary Key)
Title
Author
Genre
YearPublished

Books table (sample data)

BookID	Title	Author	Genre	YearPublished
B01	1984	George Orwell	Dystopian	1949
B02	To Kill a Mockingbird	Harper Lee	Fiction	1960
B03	The Hobbit	J.R.R. Tolkien	Fantasy	1937
B04	The Martian	Andy Weir	Science Fiction	2011

Table 3: Loans (Sample Data)This table records when a student borrows a book.
Fields

LoanID (Primary Key)
StudentID (Foreign Key → Students)
BookID (Foreign Key → Books)
LoanDate
ReturnDate (NULL if not yet returned)

Loans table (sample data)

LoanID	StudentID	BookID	LoanDate	ReturnDate
L001	101	B03	2025-01-10	2025-01-24
L002	102	B01	2025-01-12	NULL
L003	104	B04	2025-01-15	NULL

SECTION 3 | SQL COMMANDS PRACTICE

This section is designed to allow you to use the sample data and scenario above to practice the SQL methods explained. using Pyhton and SQL with SQLite.

Imports

sqlite3 lets Python send SQL commands to an SQLite database.
datetime (optional) can help generate dates, but you can also use strings like "2025-01-12".

Connection and cursor

A connection (conn) links your Python program to the database file.
A cursor (cur) is used to run SQL statements and fetch results.
You should enable foreign keys in SQLite using PRAGMA foreign_keys = ON; (SQLite does not enforce them by default).

CREATE TABLE IF NOT EXISTS

CREATE TABLE defines a new table (its fields and data types).
IF NOT EXISTS prevents an error if the table already exists.

General syntax
CREATE TABLE IF NOT EXISTS TableName (
FieldName DataType,
...
);

Relating to the scenario
The library needs three tables:

Students to store student details
Books to store book details
Loans to record borrowing events (links Students to Books)

Write SQL to create the Students table with:

StudentID as a primary key
FirstName, LastName, YearGroup

SELECT (with FROM)

SELECT chooses which fields to display.
FROM chooses which table to read from.

General syntax
SELECT field1, field2 FROM TableName;

The librarian may want to list student names from the Students table. Retrieve the first and last names of all students.

DISTINCT

DISTINCT removes duplicates from the output.

General syntax
SELECT DISTINCT fieldName FROM TableName;

Multiple books may share the same genre, but the librarian might want each genre only once. List all different genres in the Books table (no repeats).

WHERE (filtering)

WHERE filters records so only rows matching a condition are returned.

General syntax
SELECT ... FROM TableName WHERE condition;

The librarian might search for books published after a certain year. Find all books published after 1950.

AND

AND combines conditions; both must be true.

General syntax
WHERE condition1 AND condition2

The librarian may want fantasy books before a certain year. Find Fantasy books published before 1950.

OR combines conditions; at least one must be true.

General syntax
WHERE condition1 OR condition2

Relating to the scenario
The librarian wants books that are either Fiction or Fantasy. Retrieve all books where genre is Fiction OR Fantasy.

NOT

NOT reverses a condition.

General syntax
WHERE NOT condition

Relating to the scenario
The librarian wants books that are not Fiction. List all books that are NOT Fiction.

BETWEEN

BETWEEN checks whether a value lies within a range (inclusive).

General syntax
WHERE field BETWEEN low AND high

The librarian wants books published between two years. Find books published between 1930 and 1970 (inclusive).

ORDER BY (ASC and DESC)

ORDER BY sorts results.

ASC ascending (default)
DESC descending

General syntax
ORDER BY field ASC ORDER BY field DESC

The librarian may want students sorted by year group. Display students ordered by year group from highest to lowest.

LIKE with % wildcard

LIKE matches patterns in text.

% means “any sequence of characters”

General syntax
WHERE field LIKE 'pattern'

The librarian wants book titles containing a word like “The”. Find all book titles containing “The”.

JOIN

JOIN combines related tables, usually using keys.

General syntax
SELECT ... FROM TableA JOIN TableB ON TableA.key = TableB.key;

Loans link students to books. A join lets you display a readable “who borrowed what” list. Show each student’s name and the title of the book they borrowed.

GROUP BY

GROUP BY groups rows with the same value, commonly used with aggregates like COUNT().

General syntax
SELECT field, COUNT(*) FROM Table GROUP BY field;

In the library system:

A student may appear multiple times in the Loans table
GROUP BY can be used to list each student who has borrowed a book, without repeating the same student multiple times

List each student ID and first name of students who have borrowed at least one book, with no duplicates.

HAVING
HAVING filters grouped results (after GROUP BY).

WHERE filters rows
HAVING filters groups

General syntax
GROUP BY field HAVING COUNT(*) > value;

The librarian wants only students who have borrowed multiple books. Show students who have borrowed 2 or more books.

DATABASE FUNDAMENTALS | DATABASE PROGRAMMING

SECTION 1 | QUERIES OVERVIEW

SECTION 2 | PRACTICE SCENARIO

SECTION 3 | SQL COMMANDS PRACTICE

Check your understanding of SQL DDL and DML