IB Computer Science HL · Year 2 · Lesson 46

SQL languages, queries and updates

Construct two-table SQL using the complete required command set, then explain data updates and indexed-column maintenance.

Paper 150 minutesA3.3.1 · A3.3.2 · A3.3.3

Retrieve first, attempt the task, then reveal.

Course home IA hub 2027 case study

Targets

Declarative database programming

2 / 11

outline DDL versus DML;
construct JOIN queries with relational operators, filtering, pattern matching and ordering;
use SELECT, DISTINCT, FROM, WHERE, BETWEEN, ORDER BY, GROUP BY, HAVING, ASC, DESC, JOIN, LIKE %, AND, OR and NOT;
explain INSERT INTO, UPDATE SET and DELETE plus indexed-column update cost.

A3.3.1A3.3.2A3.3.3

Page 2 of 11

Learn + retrieve

Rapid Recall Deck

3 / 11

Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.

Recall deck answers

What is DDL? Data Definition Language: SQL statements that define/change structures.
What is DML? Data Manipulation Language: SQL statements that retrieve/change stored data.
What does SELECT do? Chooses columns/expressions to return.
What does DISTINCT do? Removes duplicate result rows.
What does WHERE do? Filters rows before grouping.
What does BETWEEN do? Tests whether a value lies in a specified range.
What does LIKE with % do? Pattern matching where % represents any sequence of characters.
What does ORDER BY do? Sorts results using ASC or DESC.
What does GROUP BY do? Forms groups of rows for aggregation.
What does HAVING do? Filters groups after GROUP BY.
What does JOIN do? Combines related rows from two tables using a join condition.
Which Boolean operators are required in SQL conditions? AND, OR and NOT, along with relational comparisons.
Which update statements are required? INSERT INTO, UPDATE ... SET and DELETE.

Page 3 of 11

A3.3.1–2

DDL/DML + simple query

4 / 11

DDL or DML?

PATIENT(PatID, PatientName, PatientDOB)
DOCTOR(DocID, DoctorName)
VISIT(VisitID, PatID, DocID, VisitDate, Treatment, Cost)

Outline the difference between Data Definition Language (DDL) and Data Manipulation Language (DML). Then classify these actions: create the VISIT table; add a column; retrieve visits; insert a visit; update a visit; delete a visit.

DDL defines or changes database structures such as tables and columns. DML retrieves or changes the data stored in those structures. Create VISIT and add a column are DDL; retrieve, insert, update and delete rows are DML.

BETWEEN + ORDER BY

Using PATIENT(PatID, PatientName, PatientDOB), construct a query that returns PatientName for dates of birth between '2005-01-01' and '2007-12-31', ordered by patient name ascending.

SELECT PatientName
FROM PATIENT
WHERE PatientDOB BETWEEN '2005-01-01' AND '2007-12-31'
ORDER BY PatientName ASC;

Page 4 of 11

A3.3.2 · Construct

Pattern matching

5 / 11

DISTINCT + LIKE syntax drill

For this isolated syntax drill, assume a simplified table VISIT_LOG(VisitID, PatientName, Treatment). Construct a query that returns each PatientName only once when Treatment contains the word therapy anywhere in the text.

Duplicate removal — choose the keyword that returns each name once.

Pattern match — use LIKE with wildcard characters around therapy.

SELECT DISTINCT PatientName
FROM VISIT_LOG
WHERE Treatment LIKE '%therapy%';

The next page repeats the same requirement using the normalized PATIENT + VISIT schema and therefore requires a JOIN.

Page 5 of 11

JOIN

Use relationship keys

6 / 11

Two-table therapy query

Using PATIENT(PatID, PatientName, PatientDOB) and VISIT(VisitID, PatID, DocID, VisitDate, Treatment, Cost), construct a JOIN query that returns each patient name once when the treatment contains therapy.

SELECT DISTINCT P.PatientName
FROM PATIENT AS P
JOIN VISIT AS V
  ON P.PatID = V.PatID
WHERE V.Treatment LIKE '%therapy%';

Page 6 of 11

Conditions

Relational + Boolean filters

7 / 11

BETWEEN + AND/OR/NOT

Using PATIENT and VISIT joined by PatID, return patient name, visit date and cost for visits costing 100–500; exclude treatments containing trial; accept treatments containing scan OR therapy; order newest visit first.

SELECT P.PatientName, V.VisitDate, V.Cost
FROM PATIENT AS P
JOIN VISIT AS V ON P.PatID = V.PatID
WHERE V.Cost BETWEEN 100 AND 500
  AND NOT V.Treatment LIKE '%trial%'
  AND (V.Treatment LIKE '%scan%' OR V.Treatment LIKE '%therapy%')
ORDER BY V.VisitDate DESC;

Page 7 of 11

Grouped queries

GROUP BY + HAVING

8 / 11

Filter groups

Using VISIT(VisitID, PatID, DocID, VisitDate, Treatment, Cost), construct a query that lists each DocID and its visit count only for doctors with at least 5 visits, busiest first.

SELECT DocID, COUNT(*) AS VisitCount
FROM VISIT
GROUP BY DocID
HAVING COUNT(*) >= 5
ORDER BY VisitCount DESC;

WHERE filters rows before grouping; HAVING filters groups after aggregation.

Page 8 of 11

A3.3.3

Update stored data

9 / 11

INSERT

INSERT INTO PATIENT (PatID, PatientName, PatientDOB)
VALUES (105, 'Amina Noor', '2006-04-12');

UPDATE

UPDATE VISIT
SET Cost = 140
WHERE VisitID = 9001;

DELETE

DELETE FROM VISIT
WHERE VisitID = 9001;

A missing/incorrect WHERE condition can change far more rows than intended.

Page 9 of 11

Indexes

Writes have maintenance costs

10 / 11

Indexed-column update

PatientDOB is indexed and a migration changes millions of DOB values. Explain the cost.

The DB must update the rows and index entries/order. Large changes add write/maintenance cost, can fragment index structures and may require indexes to be rebuilt or reorganized after significant modifications.

Page 10 of 11

Homework

Finish the learning cycle

11 / 11

Case study / exam preparation

Post-IA focus: add one useful 2027 case-study research note and prioritize cumulative exam retrieval.

Task complete

Paper 1 — short practice

Write one JOIN+LIKE+DISTINCT query, one BETWEEN+ORDER BY query, one GROUP BY+HAVING query, then INSERT/UPDATE/DELETE examples.

Practice complete

Designed by David Xu

Educators and students are welcome to use these materials for non-commercial teaching and learning with attribution. Please share the original link when possible. Reposting, redistributing modified copies, removing attribution, or commercial use requires prior permission.

End of Lesson 46.