Scribe logoScribeNotes & practice
Sign inStart free
  1. Notes
  2. ›IB Computer Science
  3. ›A3 Databases
  4. ›A3.3 Database programming

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.

  • 6 min read
  • 6 parts
  • 10 questions
  • Draft
Start a Quick 5 on this topic→Jump to the 10 questions
1/6SQL is the language for working with relational databases
  1. 1SQL is the language for working with relational databases
  2. 2SELECT retrieves the rows and columns you ask for
  3. 3INSERT, UPDATE and DELETE change the stored data
  4. 4A join combines rows from related tables
  5. 5A transaction makes several changes succeed or fail together
  6. 6Parameterised queries stop SQL injection
  7. Check your recall
  8. Practice: 10 questions
1

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

StudentIDNameYearMark
1Ana1278
2Ben1364
3Chloe1291
4Dev1355
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 1 done

2

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

Using the Student table, list the names and marks of Year 12 students, with the highest mark first.
  1. 1
    Choose the columns: SELECT Name, Mark
  2. 2
    Name the table: FROM Student
  3. 3
    Keep Year 12 only: WHERE Year = 12
  4. 4
    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.

Part 2 done

Quick check

One question on the part you just read

Easy

Identify the SQL statement that removes records from a table.
Show answer and explanation

Answer: A · Worked explanation

DELETE removes the records that meet its WHERE condition. SELECT only reads data and leaves the table unchanged. INSERT adds a new record. UPDATE changes values in existing records but does not remove the records themselves. Use DELETE with WHERE to remove only chosen rows.
3

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.

Common mistake

Forgetting WHERE in an UPDATE or DELETE changes or removes every row, not just the one you meant.

Part 3 done

4

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

MemberIDNameClubID
1Ana10
2Ben20
3Chloe10
4Dev30
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

List each member’s name with their club name, using the Member and Club tables.
  1. 1
    Start from Member: SELECT Member.Name, Club.ClubName FROM Member
  2. 2
    Add the join: INNER JOIN Club
  3. 3
    Match the keys: ON Member.ClubID = Club.ClubID;
  4. 4
    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.

Part 4 done

Quick check

One question on the part you just read

Easy

Identify the clause that sorts the rows returned by a query.
Show answer and explanation

Answer: D · Worked explanation

ORDER BY sorts the result, ascending by default or descending with DESC. WHERE filters rows by a condition but does not change their order. FROM names the table being read. SELECT chooses the columns to show, so it affects which columns appear and not the sequence of the rows.
5

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.

Go deeperThe four ACID properties

Reliable transactions are described by four properties, known together as ACID. Atomicity means all steps happen or none do. Consistency means a transaction takes the database from one valid state to another.

Isolation means that transactions running at the same time do not interfere with each other. Durability means that once a transaction is committed, its changes survive a crash or power loss.

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 5 done

6

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.

Exam tip

Name the measure and say why it works: parameterised queries treat input as data, not as code.

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.

Note read

Check your recall[15 marks]

6 written questions. Answer each one in your head or on paper, then open the mark scheme.

  1. 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. 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. 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. 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. 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. 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.

3.

Easy·State[1 mark]

State what SQL injection is.
Show answer and explanation

Answer: B · Worked explanation

SQL injection happens when typed input is run as SQL, which lets an attacker alter a query. Adding a record is the job of INSERT and has nothing to do with attacks. A transaction is undone by a rollback, which is a deliberate instruction. A join combines rows by matching keys.
4.

Medium·Determine[1 mark]

The Student table holds Ana (Mark 78), Ben (64), Chloe (91) and Dev (55). Determine the names returned by SELECT Name FROM Student WHERE Mark > 70;
Show answer and explanation

Answer: C · Worked explanation

The condition Mark > 70 is true for Ana with 78 and Chloe with 91. Ben with 64 and Dev with 55 fail it. Returning Chloe only would apply a limit of 80 or more. Ben and Dev are the rows that the condition rejects, and all four names would appear only if WHERE were missing.
5.

Medium·Predict[1 mark]

A technician runs UPDATE Student SET Year = 13; with no WHERE clause. Predict the effect.
Show answer and explanation

Answer: A · Worked explanation

Without WHERE, no condition limits the update, so it applies to every record in the table. A statement does not stop after the first row. WHERE is optional in UPDATE, which is why leaving it out is dangerous. Only Year 12 students would change if the statement included a condition such as Year = 12.
6.

Medium·Predict[1 mark]

Member has 4 rows with ClubID values 10, 20, 10 and 30. Club has 2 rows, with ClubID 10 and 20. Predict how many rows an inner join on ClubID returns.
Show answer and explanation

Answer: D · Worked explanation

The members with ClubID 10, 20 and 10 each match a club, giving 3 rows. The member with ClubID 30 has no match, so an inner join leaves that row out. The value 4 would be the count for a left join. The value 2 is the number of rows in Club, and 8 multiplies 4 by 2 as if every pairing were returned.
7.

Medium·Predict[1 mark]

A transaction subtracts 5 items from the stock of warehouse A, then the system fails before it adds 5 to warehouse B. Predict what the DBMS should do.
Show answer and explanation

Answer: B · Worked explanation

A transaction must either complete in full or have no effect, so the DBMS rolls back the unfinished work and the data returns to its earlier state. Committing only the first update would make 5 items vanish. Committing both is impossible because the second step never ran. Deleting records would destroy data.
8.

Hard·Identify[2 marks]

A developer builds a login query by joining typed text into the SQL string. Which pair of measures best reduces the risk of SQL injection?
Show answer and explanation

Answer: C · Worked explanation

Parameterised queries pass input as data, so it is never run as SQL, and limited permissions reduce the damage if an attack still succeeds. Longer names and commits do not stop input being run as code. Full error messages help attackers learn the structure. Sorting, left joins and capital letters have no effect on injection.
9.

Hard·Deduce[2 marks]

The Student table holds Ana (Year 12, Mark 78), Ben (Year 13, 64), Chloe (Year 12, 91) and Dev (Year 13, 55). Deduce the names returned by SELECT Name FROM Student WHERE Year = 13 OR Mark > 80;
Show answer and explanation

Answer: B · Worked explanation

OR returns a row when either condition is true. Ben and Dev are in Year 13, and Chloe has a Mark above 80, so three names are returned. Ana meets neither test. No rows would be returned if the word were AND, because no Year 13 student has a mark above 80. Ben and Dev alone ignore Chloe’s mark.
10.

Hard·Predict[2 marks]

A left join of Member with Club on ClubID includes Dev, whose ClubID is 30, and Club has no row for 30. Predict what is shown for Dev.
Show answer and explanation

Answer: A · Worked explanation

A left join keeps every row of the first table, and where the second table has no match it shows NULL. Leaving Dev out is what an inner join would do. The query is valid, so it does not fail. The value 30 is a key, not a club name, and Club has no name for it to show.

Keep going

Next topicA3.4 Alternative databases and warehousesPrevious topicA3.2 Database design

More in A3 · Databases

  1. A3.1Database fundamentalsNotes · 10 questions
  2. A3.2Database designNotes · 10 questions
  3. A3.3Database programmingNotes · 10 questions
  4. A3.4Alternative databases and warehousesNotes · 10 questions· HL only

All IB Computer Science topics →

Draft: written by Scribe and checked automatically, not yet by a subject reviewer.

Practice questions are original, written by Scribe and checked automatically for correctness.

Topic codes follow the official numbering. Not endorsed by IB Diploma Programme or any exam board.

On this page

0/6 read

  1. 1SQL is the language for working with relational databases
  2. 2SELECT retrieves the rows and columns you ask for
  3. 3INSERT, UPDATE and DELETE change the stored data
  4. 4A join combines rows from related tables
  5. 5A transaction makes several changes succeed or fail together
  6. 6Parameterised queries stop SQL injection
  7. Check your recall
  8. Practice: 10 questions
All coursesScribeHow it worksPricingPrivacyTerms

Notes and questions are written by Scribe in our own words. Topic codes follow the official course numbering so you can find your place. Not produced or endorsed by the IB, College Board, AQA, OCR, Pearson Edexcel or any exam board.

© 2026 Scribe