A3.3 Database programming: notes and practice

What you need to know

Our summary of the topic in our own words, not the IB guide. Points marked HL are for Higher Level only.

• Distinguish the SQL sublanguages DDL, DML, DCL and TCL and place a given statement in the right one
• Write DDL statements to create and alter a table
• Write a single-table SELECT query using WHERE, ORDER BY and DISTINCT
• Write a query that joins two tables on a key
• Predict the result set a given query returns from given tables
• Write INSERT, UPDATE and DELETE statements, and explain the consequence of omitting a WHERE clause
• Use the aggregate functions COUNT, SUM, AVG, MIN and MAX in a queryHL
• Use GROUP BY to aggregate per group, and filter the groupsHL
• Explain what a database view is and why views are used for convenience and access controlHL
• Explain what a transaction is and why some operations must succeed or fail as a unitHL
• State the ACID properties and explain what each one guaranteesHL
• Explain the role of COMMIT and ROLLBACKHL

Practice questions

Original questions written for the 2027 course, marked as you go. Easiest first.

• SQL UPDATE on library loan recordsWritten · Core
• SQL SELECT and JOIN on library loansWritten · Stretch
• Database transactions for sending creditWritten · Stretch
• SQL GROUP BY on canteen salesWritten · Stretch

Exam tips for A3.3

• Before writing a query, decide which tables you need and how they join.
• An UPDATE or DELETE without WHERE changes every row. This is a classic trap.
• Predict the result of a query by working through the rows one at a time, the way a trace table does for code.