Teachers' Portal  /  A-Level Computer Science 9618  /  Databases  /  SQL data manipulation language
Lesson pack · A-Level Computer Science 9618 · 8.3 · AS Level

SQL data manipulation language

Query and maintain. With the tables built, SQL's data manipulation language does the daily work: totals and averages per group, questions that need two linked tables at once, and the inserts, updates and deletes that keep the data current.

Student lesson Lab, full screen
At a glance

The shape of the lesson

Topic
Databases
Syllabus reference
Cambridge International AS and A Level Computer Science 9618, 8.3
Level
AS Level
Duration
45 minutes, lab-led; stretches to a 60 minute block (see the contingency notes)
Position in topic
Lesson 6 of 6 in Databases; follows SQL data definition language
Central model
Fig. 1: INNER JOIN pairs each loan with the member whose MemberID matches
Cooperative structure
Connect-Extend-Challenge
Assessment
Stage 1 check and the exam-style plenary from the student lesson, plus the worksheet as the exit task
Learning objectives

By the end of the lesson, learners can

Section by section
  • Totals per group: Every example in this lesson uses the library tables below.
  • Joining two tables: In the lab's relational database tab, run the first example: an INNER JOIN of MEMBERSHIP and STUDENT.
  • INSERT, UPDATE and DELETE: In the lab's DDL and DML tab, run "Insert records", "Update records" and "Delete records" and watch the changed rows highlight.
Key points to leave on the board
  • Order: SELECT, FROM, (INNER JOIN ... ON), WHERE, GROUP BY, ORDER BY.
  • Aggregates: SUM, COUNT, AVG; every other SELECTed field must be in GROUP BY.
  • Joins: ON foreign key = primary key; use Table.Field names.
  • Maintenance: INSERT INTO ... VALUES; UPDATE ... SET ... WHERE; DELETE FROM ... WHERE.
The core visual model

One picture every idea returns to

A query uses SELECT fields FROM table, an optional WHERE condition, GROUP BY to make one output row per group, and ORDER BY to sort, in that order. The aggregate functions SUM, COUNT and AVG work on a whole column, or on each group when GROUP BY is used. INNER JOIN ... ON combines two tables, outputting a row for each pair of records whose linking fields match. Data is maintained with INSERT INTO, UPDATE ... SET ... WHERE and DELETE FROM ... WHERE.

INNER JOIN ... ON Loan.MemberID = Member.MemberID LOAN (not returned)MEMBER LoanIDMemberID L02M02 L03M01 L05M04 L06M01 MemberIDFirstName M01Aisha M02Ben M04Dev LoanIDFirstName L02Ben L03Aisha L05Dev L06Aisha one output row per matching pair
Fig. 1: INNER JOIN pairs each loan with the member whose MemberID matches
Lesson sequence

Forty-five minutes, phase by phase

TimePhaseWhat happens in the roomGrouping
0 to 5 minHook: predictOpen with the question in the title, Query and maintain.. With the tables built, SQL's data manipulation language does the daily work: totals and averages per group, questions that need two linked tables at once, and the inserts, updates and deletes that keep the data current. Put the figure on the board: it is the one picture every idea in the lesson returns to.Whole class, then pairs
5 to 13 minTeach and check: Totals per groupEvery example in this lesson uses the library tables below. Model the worked example AVG per group on the board, one line at a time. Your turn: Write a query to output the total price of all the SciFi books, and give its output.Whole class, then pairs
13 to 21 minTeach and check: Joining two tablesIn the lab's relational database tab, run the first example: an INNER JOIN of MEMBERSHIP and STUDENT. Project tab 4 of the lab (INNER JOIN on linked tables) and let a student drive it while the class predicts. Model the worked example Join and group on the board, one line at a time. Your turn: Write a query to output the Title and LoanDate of every book borrowed by member M01, earliest first.Pairs
21 to 30 minTeach and check: INSERT, UPDATE and DELETEIn the lab's DDL and DML tab, run "Insert records", "Update records" and "Delete records" and watch the changed rows highlight. Project tab 6 of the lab (DML: insert, update and delete) and let a student drive it while the class predicts. Model the worked example Write the maintenance script on the board, one line at a time.Connect-Extend-Challenge
30 to 36 minCheckThe four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong.Individual, then whole class
36 to 43 minPlenaryThe exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording.Individual, random call
43 to 45 minExit and homeworkSet the worksheet below (6 questions, 20 marks). Learners self-assess against the objectives before they leave.Individual
Timing and contingency

Protect the parts that carry the learning

The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.

Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.

Cut first if the build overruns: the last check in INSERT, UPDATE and DELETE, never the check or the plenary.

In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.

Running the cooperative task

Connect-Extend-Challenge

After the new idea, each student writes three lines: how it connects to what they knew, how it extends their thinking, and what still challenges them. Pairs compare, then the challenges become the plenary.

The full structure, with its variations and the other twelve, is on the cooperative-learning page.

Examiner traps to pre-empt

What to head off, and how

Trap learners fall intoTeaching move that pre-empts it
Every field in the SELECT list that is not inside SUM, COUNT or AVG must appear in the GROUP BY.SELECT Title, COUNT(BookID)... GROUP BY Genre is wrong.
An INNER JOIN only outputs records that have a match.A book that has never been lent does not appear in a join of BOOK and LOAN at all.
UPDATE or DELETE without a WHERE clause changes or deletes every record in the table.Always check the condition, and use the primary key when only one record should change.
Worksheet

SQL data manipulation language: worksheet

6 questions, 20 marks. Written in the style of the papers; no past paper question is reproduced.

NameClassDate
Q1 · trace[3 marks]

Using the MEMBER table in this lesson, give the output of:

SELECT FormGroup, COUNT(MemberID)
FROM Member
GROUP BY FormGroup
ORDER BY FormGroup;
Q2 · write[4 marks]

Write an SQL query to output the Title and Author of every SciFi book in the BOOK table, most expensive first. Give the output.

Q3 · write[4 marks]

Write an SQL query to output the total price of all the books that are currently out on loan (not returned). Give the output.

Q4 · write[4 marks]

Write SQL statements to: (a) add member M05, Eli Brown, in form group 12B; (b) record that loan L05 has been returned; (c) delete every loan that has been returned.

Q5 · analysis[3 marks]

A student wants the average price of each genre and writes:

SELECT Genre, AVG(Price)
FROM Book
ORDER BY Genre
GROUP BY Genre;

Identify the error and give the output of the corrected query.

Q6 · trace[2 marks]

The statement INSERT INTO Book VALUES ('B106', 'Wonder', 'Palacio', 'Adventure', 9.00); is run. Then the query SELECT AVG(Price) FROM Book WHERE Genre = 'Adventure'; is run. Give the output and show your working.

Answer key and marking notes
Q1 · 3 marks
  • 12A 2
  • 12B 1
  • 13C 1
Q2 · 4 marks
  • SELECT Title, Author FROM Book
  • WHERE Genre = 'SciFi'
  • ORDER BY Price DESC;
  • Output: Neuromancer Gibson; Dune Herbert.
Q3 · 4 marks
  • SELECT SUM(Book.Price) FROM Book
  • INNER JOIN Loan ON Book.BookID = Loan.BookID
  • WHERE Loan.Returned = FALSE;
  • Output 32.00 (Neuromancer 10.50 + Emma 6.00 + Dune 8.50 + Rebecca 7.00).
Q4 · 4 marks
  • (a) INSERT INTO Member VALUES ('M05', 'Eli', 'Brown', '12B');
  • (b) UPDATE Loan SET Returned = TRUE
  • WHERE LoanID = 'L05';
  • (c) DELETE FROM Loan WHERE Returned = TRUE;
Q5 · 3 marks
  • GROUP BY must come before ORDER BY.
  • Corrected: ... FROM Book GROUP BY Genre ORDER BY Genre;
  • Output: Adventure 12.00; Classic 6.50; SciFi 9.50.
Q6 · 2 marks
  • The Adventure books are now Holes 12.00 and Wonder 9.00.
  • (12.00 + 9.00) / 2 = 10.50.
Quiz

6 multiple-choice questions on this lesson

From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.

Assign in the appRun as a class gameTopic question bank
Q11 mark

Using the LOAN table, what is the output of the query below?

LOAN
LoanIDBookIDMemberIDLoanDateReturned
L01B101M012026-09-01TRUE
L02B103M022026-09-02FALSE
L03B102M012026-09-03FALSE
L04B105M032026-09-03TRUE
L05B101M042026-09-05FALSE
L06B104M012026-09-06FALSE
SELECT COUNT(LoanID)
FROM Loan
WHERE Returned = FALSE;

  • A4
  • B2
  • C6
  • D3
Q21 mark

Using the BOOK table, what is the output of the query below?

BOOK
BookIDTitleAuthorGenrePrice
B101DuneHerbertSciFi8.50
B102EmmaAustenClassic6.00
B103NeuromancerGibsonSciFi10.50
B104Rebeccadu MaurierClassic7.00
B105HolesSacharAdventure12.00
SELECT SUM(Price)
FROM Book
WHERE Genre = 'Classic';

  • A13.00
  • B6.50
  • C2
  • D19.00
Q31 mark

Using the MEMBER table, what is the output of the query below?

MEMBER
MemberIDFirstNameSurnameFormGroup
M01AishaKhan12A
M02BenOkafor12B
M03ChloeWang12A
M04DevPatel13C
SELECT Surname
FROM Member
WHERE FormGroup = '12A'
ORDER BY Surname DESC;

  • AWang, then Khan
  • BKhan, then Wang
  • CChloe, then Aisha
  • DWang, Patel, Okafor, then Khan
Q41 mark

Loan L03 has just been returned. Which statement records this correctly?

  • AUPDATE Loan SET Returned = TRUE WHERE LoanID = 'L03';
  • BUPDATE Loan SET Returned = TRUE;
  • CINSERT INTO Loan (Returned) VALUES (TRUE) WHERE LoanID = 'L03';
  • DALTER TABLE Loan SET Returned = TRUE WHERE LoanID = 'L03';
Q51 mark

Using the BOOK and LOAN tables, what is the output of the query below?

BOOK
BookIDTitleAuthorGenrePrice
B101DuneHerbertSciFi8.50
B102EmmaAustenClassic6.00
B103NeuromancerGibsonSciFi10.50
B104Rebeccadu MaurierClassic7.00
B105HolesSacharAdventure12.00
LOAN
LoanIDBookIDMemberIDLoanDateReturned
L01B101M012026-09-01TRUE
L02B103M022026-09-02FALSE
L03B102M012026-09-03FALSE
L04B105M032026-09-03TRUE
L05B101M042026-09-05FALSE
L06B104M012026-09-06FALSE
SELECT Loan.MemberID, SUM(Book.Price)
FROM Book
INNER JOIN Loan ON Book.BookID = Loan.BookID
GROUP BY Loan.MemberID
ORDER BY Loan.MemberID;

  • AM01 21.50; M02 10.50; M03 12.00; M04 8.50
  • BM01 13.00; M02 10.50; M04 8.50
  • CM01 3; M02 1; M03 1; M04 1
  • DM01 7.17; M02 10.50; M03 12.00; M04 8.50
Q61 mark

Using the BOOK and LOAN tables, these statements are run in order. What is the output of the SELECT?

BOOK
BookIDTitleAuthorGenrePrice
B101DuneHerbertSciFi8.50
B102EmmaAustenClassic6.00
B103NeuromancerGibsonSciFi10.50
B104Rebeccadu MaurierClassic7.00
B105HolesSacharAdventure12.00
LOAN
LoanIDBookIDMemberIDLoanDateReturned
L01B101M012026-09-01TRUE
L02B103M022026-09-02FALSE
L03B102M012026-09-03FALSE
L04B105M032026-09-03TRUE
L05B101M042026-09-05FALSE
L06B104M012026-09-06FALSE
INSERT INTO Loan
VALUES ('L07', 'B102', 'M03', '2026-09-08', FALSE);

SELECT Book.Title, COUNT(Loan.LoanID)
FROM Book
INNER JOIN Loan ON Book.BookID = Loan.BookID
WHERE Loan.Returned = FALSE
GROUP BY Book.Title
ORDER BY Book.Title;

  • ADune 1; Emma 2; Neuromancer 1; Rebecca 1
  • BDune 2; Emma 2; Holes 1; Neuromancer 1; Rebecca 1
  • CDune 1; Emma 1; Neuromancer 1; Rebecca 1
  • DDune 1; Emma 2; Holes 0; Neuromancer 1; Rebecca 1
Quiz key
Q1: A

L02, L03, L05 and L06 are not yet returned, so COUNT gives 4. 2 is the number of loans that have been returned (L01 and L04), 6 counts every loan by ignoring the WHERE clause, and 3 is the number of loans made by member M01.

Q2: A

The Classic books are Emma 6.00 and Rebecca 7.00, and SUM adds them: 13.00. 6.50 is their average (AVG), 2 is how many there are (COUNT), and 19.00 is the total for the SciFi books.

Q3: A

Only Aisha Khan and Chloe Wang are in 12A, and DESC sorts the surnames in reverse alphabetical order: Wang, then Khan. Khan then Wang is ascending order. The query selects Surname, not FirstName, and listing all four surnames ignores the WHERE clause.

Q4: A

UPDATE ... SET ... WHERE changes the field in only the records that meet the condition, here the single loan L03. Without the WHERE clause every loan would be marked as returned. INSERT INTO adds a new record and cannot take a WHERE clause, and ALTER TABLE changes the structure, not stored values.

Q5: A

M01 borrowed Dune 8.50, Emma 6.00 and Rebecca 7.00, total 21.50; M02 Neuromancer 10.50; M03 Holes 12.00; M04 Dune 8.50. M01 13.00 with no M03 row is what you get by counting only loans not yet returned, but the query has no WHERE clause. 3, 1, 1, 1 is what COUNT would give, and 7.17 is M01's average, which AVG would give.

Q6: A

After the insert, the loans not returned are L02 (Neuromancer), L03 and L07 (both Emma), L05 (Dune) and L06 (Rebecca), giving Dune 1, Emma 2, Neuromancer 1, Rebecca 1. The second option ignores the WHERE clause. The third forgets the new loan L07. Holes has no unreturned loan, so it has no rows after WHERE and does not appear at all, rather than appearing with 0.

Lab and demo

What to project

The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.

Tab 4INNER JOIN on linked tables Tab 6DML: insert, update and delete
Slides

Slides for this lesson

A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.

Also for this topic

Everything else you can use

Student lessonSQL data manipulation language Practice pageWorked solutions, reveal one at a time Topic question bankEvery MCQ for Databases Revision notesOne page per lesson, with the traps Notes PDFPrint the topic notes Topic planDatabases: every lesson in order
Original work by the TheLucidSTEM team. The worksheet items are written in the style of the papers; no past paper question is reproduced.
Previous lessonSQL data definition language