IB Computer Science · A3 · Databases
A3.3 Database programming
Write basic SQL statements to select, insert, update and delete data, explain joins and transactions, and describe how to prevent SQL injection.
1/6SQL is the language for working with relational databases
- SQL is the language for working with relational databases
- SELECT retrieves the rows and columns you ask for
- INSERT, UPDATE and DELETE change the stored data
- A join combines rows from related tables
- A transaction makes several changes succeed or fail together
- Parameterised queries stop SQL injection
- Check your recall
- Practice: 10 questions
Part 1 of 6: SQL is the language for working with relational databases
A DBMS needs instructions in a language it understands. SQL is the standard language for relational databases. It stands for Structured Query Language.
A query is a request sent to the DBMS. Each request is written as an SQL statement, which ends with a semicolon. Keywords are written in capitals by convention.
SQL is declarative. You describe the result you want, and the DBMS decides how to find it. The examples use a table called Student, whose primary key is StudentID.
The Student table used in the examples
- StudentID
- 1
- Name
- Ana
- Year
- 12
- Mark
- 78
- StudentID
- 2
- Name
- Ben
- Year
- 13
- Mark
- 64
- StudentID
- 3
- Name
- Chloe
- Year
- 12
- Mark
- 91
- StudentID
- 4
- Name
- Dev
- Year
- 13
- Mark
- 55
Key terms · tap one for its definition
SQL
SQL is the standard language used to define, query and change data in a relational database.
query
A query is a request sent to a DBMS to retrieve data, or sometimes to change it.
SQL statement
An SQL statement is a complete SQL command, ending in a semicolon, that the DBMS can execute.
Part 2 of 6: SELECT retrieves the rows and columns you ask for
Now use the Student table to ask a question. A clause is one part of a SELECT statement that starts with a keyword. The main clauses are listed here.
- SELECT lists the columns to show, and a star means all columns.
- FROM names the table to read.
- WHERE keeps only the rows that meet a condition.
- ORDER BY sorts the result, with DESC for descending order.
A condition is a test that is true or false for each row, such as Year = 12. Join conditions with AND or OR.
Worked example
Write a SELECT statement
- Choose the columns: SELECT Name, Mark
- Name the table: FROM Student
- Keep Year 12 only: WHERE Year = 12
- Sort the result: ORDER BY Mark DESC;
Answer: SELECT Name, Mark FROM Student WHERE Year = 12 ORDER BY Mark DESC; returns Chloe 91 and then Ana 78.
Key terms · tap one for its definition
clause
A clause is one part of an SQL statement that begins with a keyword, such as FROM, WHERE or ORDER BY.
condition
A condition is a test, such as Year = 12, that is either true or false for each row.

Quick check
One question on the part you just read
Easy
Show answer and explanation
Answer: A · Worked explanation
Part 3 of 6: INSERT, UPDATE and DELETE change the stored data
SELECT only reads data. Three other statements change it, and each acts on one table.
- INSERT adds a new record.
- INSERT INTO Student (StudentID, Name, Year, Mark) VALUES (5, 'Eli', 12, 70);
- UPDATE changes values in existing records.
- UPDATE Student SET Mark = 82 WHERE StudentID = 1;
- DELETE removes records.
- DELETE FROM Student WHERE StudentID = 4;
In UPDATE and DELETE, the WHERE clause selects which records are affected. These statements alter the stored tables at once. Check the WHERE condition before running them, because outside a transaction there is no simple undo.
Part 4 of 6: A join combines rows from related tables
Data split across tables must often be brought back together. A join combines rows from two tables where their key values match. The match is usually a foreign key equal to a primary key.
Take a Member table with MemberID, Name and ClubID, where ClubID is a foreign key. A Club table holds 10 Chess and 20 Drama.
The Member table
- MemberID
- 1
- Name
- Ana
- ClubID
- 10
- MemberID
- 2
- Name
- Ben
- ClubID
- 20
- MemberID
- 3
- Name
- Chloe
- ClubID
- 10
- MemberID
- 4
- Name
- Dev
- ClubID
- 30
Worked example
Join two tables
- Start from Member: SELECT Member.Name, Club.ClubName FROM Member
- Add the join: INNER JOIN Club
- Match the keys: ON Member.ClubID = Club.ClubID;
- Dev’s ClubID of 30 has no match in Club, so Dev is left out.
Answer: The result has three rows: Ana Chess, Ben Drama and Chloe Chess.
That was an inner join, which returns only rows with a match. A left join keeps every row of the first table. It shows NULL, meaning no value, where nothing matches.
Key terms · tap one for its definition
join
A join is an operation that combines rows from two or more tables where their key values match.
inner join
An inner join is a join that returns only the rows that have a matching key value in both tables.
left join
A left join is a join that returns every row of the first table, with NULL where the second table has no match.

Quick check
One question on the part you just read
Easy
Show answer and explanation
Answer: D · Worked explanation
Part 5 of 6: A transaction makes several changes succeed or fail together
Joins only read data, but some tasks must change several things at once. Moving 5 items of stock between warehouses needs two updates. One subtracts 5 from the first stock count, and the other adds 5 to the second.
If the system fails between them, 5 items vanish. A transaction prevents this by grouping the updates into one unit. Either every step is saved or none is.
Saving the changes is a commit. Undoing them is a rollback. After a failure, the DBMS rolls back the unfinished transaction, so the data returns to its earlier state.
Key terms · tap one for its definition
transaction
A transaction is a sequence of database operations treated as a single unit that either completes in full or has no effect.
commit
A commit is the instruction that permanently saves all the changes made in a transaction.
rollback
A rollback is the instruction that cancels a transaction and restores the data to its state before the transaction began.
Part 6 of 6: Parameterised queries stop SQL injection
Queries often include text that users type. That creates a risk. SQL injection is an attack in which a user enters SQL code as input and the DBMS runs it.
It happens when a program joins typed text straight into the SQL string. An attacker can then change what the query does, and may read, change or delete data. Several measures reduce the risk.
- Use a parameterised query, which passes input as data and never as SQL text.
- Validate input by checking its type, length and allowed characters.
- Give each database account only the permissions it needs.
- Show users general error messages, not details of the database.
Key terms · tap one for its definition
SQL injection
SQL injection is an attack in which malicious SQL code is entered as input and then executed by the database.
parameterised query
A parameterised query is a query whose input values are supplied separately from the SQL text, so they cannot be run as code.
Check your recall[15 marks]
6 written questions. Answer each one in your head or on paper, then open the mark scheme.
- 1.Write down[3 marks]
Write down an SQL statement that lists the Name of every Year 13 student in the Student table.
Show mark schemeHide mark scheme
Model answer
SELECT Name FROM Student WHERE Year = 13;
- 1 mark for SELECT Name
- 1 mark for FROM Student
- 1 mark for WHERE Year = 13
- 2.Write down[3 marks]
Write down an SQL statement that changes the Mark of the student with StudentID 2 to 70.
Show mark schemeHide mark scheme
Model answer
UPDATE Student SET Mark = 70 WHERE StudentID = 2;
- 1 mark for UPDATE Student
- 1 mark for SET Mark = 70
- 1 mark for WHERE StudentID = 2
- 3.Explain[2 marks]
Explain why a join is needed to answer a question that uses data from two tables.
Show mark schemeHide mark scheme
Model answer
A relational database stores related facts in separate tables to avoid repetition. A join matches key values, usually a foreign key with a primary key, so rows from both tables can appear in one result.
- 1 mark for stating that the data is stored in separate tables
- 1 mark for explaining that a join matches key values to combine the rows
- 4.Describe[3 marks]
Describe how a transaction protects the data when stock is moved between two warehouses using two updates.
Show mark schemeHide mark scheme
Model answer
The two updates are grouped into one transaction. If both succeed, a commit saves them. If a failure happens between them, a rollback restores the data to its earlier state, so stock is not lost.
- 1 mark for stating that the updates are grouped as one transaction
- 1 mark for stating that a commit saves the changes when all succeed
- 1 mark for stating that a rollback restores the earlier state after a failure
- 5.Outline[2 marks]
Outline two ways to prevent SQL injection.
Show mark schemeHide mark scheme
Model answer
Use parameterised queries so that input is treated as data and not as SQL text. Validate input by checking its type, length and allowed characters.
- 1 mark for outlining one measure such as parameterised queries
- 1 mark for outlining a second measure such as validating input or limiting account permissions
- 6.Distinguish[2 marks]
Distinguish between a commit and a rollback.
Show mark schemeHide mark scheme
Model answer
A commit permanently saves the changes made in a transaction. A rollback cancels the transaction and restores the data to its state before it began.
- 1 mark for stating that a commit saves the changes permanently
- 1 mark for stating that a rollback cancels the changes and restores the earlier state

Practice this topic
10 quick-fire multiple-choice questions, easy to hard. Tap an answer to see if you're right and the worked explanation. (The written questions with mark schemes are under “Check your recall” above.)
0 right0 of 10 answered
Includes the 2 quick checks in the note above.
EasyState[1 mark]
Show answer and explanation
Answer: B · Worked explanation
MediumDetermine[1 mark]
Show answer and explanation
Answer: C · Worked explanation
MediumPredict[1 mark]
Show answer and explanation
Answer: A · Worked explanation
MediumPredict[1 mark]
Show answer and explanation
Answer: D · Worked explanation
MediumPredict[1 mark]
Show answer and explanation
Answer: B · Worked explanation
HardIdentify[2 marks]
Show answer and explanation
Answer: C · Worked explanation
HardDeduce[2 marks]
Show answer and explanation
Answer: B · Worked explanation
HardPredict[2 marks]
Show answer and explanation
Answer: A · Worked explanation