AccountsISC Class 12 · Database Management System

SQL UPDATE, DELETE and Aggregate Functions: ISC Class 12 Accounts DBMS

Updated 17 October 2026 · Draft, not yet fact-checked

UPDATE table SET column = value WHERE condition changes existing values; DELETE FROM table WHERE condition removes rows; without WHERE both act on every row. The aggregate functions SUM, AVG, COUNT, MAX and MIN take a column over the selected rows and return a single value. They ignore NULL values, except COUNT(*), which counts rows.

Notice that NULL is skipped: COUNT(*) counts 4 rows but COUNT(Balance) counts 3, and AVG is 1200 divided by 3, not by 4.

Illustration drawn for QuestionBuddy.

Understand SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM

Updating and deleting data. After SELECT, the ISC syllabus lists the commands Update and Delete and the functions Avg, Count, Max, Min, Sum. The tables used here are the Stock and Staff tables of the previous topic page, plus two small tables, Debtor and Supplier, that contain missing (NULL) values. Every example starts from the original data.

UPDATE

UPDATE table_name SET column = expression WHERE condition;

Several columns can be set together, separated by commas. The expression may use the old value, as in Salary = Salary + 5000. Without WHERE, every row is updated.

DELETE

DELETE FROM table_name WHERE condition;

This removes the rows that meet the condition. Without WHERE, all rows are deleted (the empty table remains). A DELETE cannot be limited to a column; it removes whole rows. Some systems also have TRUNCATE, which empties a table without any condition; it is outside the ISC command list.

Aggregate (group) functions work on a column of the selected rows:

SUM(column): total; AVG(column): average; MAX(column): largest; MIN(column): smallest.
COUNT(*): number of rows; COUNT(column): number of rows in which that column is not NULL.

They can be combined with WHERE, which selects the rows first: SELECT AVG(Rate) FROM Stock WHERE Category = 'Electronics';

NULL values. NULL means "no value" and is not zero. SUM, AVG, MAX, MIN and COUNT(column) ignore NULLs. So AVG is the total divided by the number of non-NULL values, not by the number of rows.

Order of work. WHERE picks rows; the function then summarises them; the answer is one row.

Precautions. Write the WHERE clause first and test it with a SELECT before running UPDATE or DELETE.

SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM: key facts

UPDATE: UPDATE table
SET column = value
WHERE condition;; No WHERE means every row changes.
DELETE: DELETE FROM table | WHERE condition;; Removes whole rows.
Aggregate functions: SUM total
AVG average
MAX largest
MIN smallest
COUNT number; Return one value for the selected rows.
Average with NULLs: SUM of values | divided by Number of non-NULL values; NULLs are left out of both.
COUNT forms: COUNT of all columns (written with a star) counts rows | COUNT(column) counts non-NULL entries; They can differ.

How to answer SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM questions

Do these questions in the order the database would.

1Apply the WHERE condition to select the rows.
2For UPDATE, change the stated columns on those rows only; show the new table.
3For DELETE, remove those rows and list what remains.
4For functions, compute over the surviving rows, ignoring NULL values.
5Write the result with its column heading, one row.

SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

A salary revision

Suppose the Staff table has Accounts salaries 42,000, 55,000 and 36,000, and management gives ₹5,000 more to Accounts staff only.

Write the UPDATE and give the new Accounts salaries.

Try it first, then show the solutionSolution

Statement: UPDATE Staff SET Salary = Salary + 5000 WHERE Dept = 'Accounts';
New salaries: 47,000, 60,000 and 41,000; other departments are unchanged.

Answer:

UPDATE Staff SET Salary = Salary + 5000 WHERE Dept = 'Accounts';

Accounts salaries become 47,000, 60,000 and 41,000.

Memory hook: WHERE decides who is changed.

Tricks to remember SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM

Short memory aids. Say each one aloud once, then use it on a question the same day.

No WHERE, no mercy
UPDATE or DELETE without WHERE affects every row.
Use it when: Avoiding data loss.
Five functions: S-A-M-M-C
Sum, Average, Max, Min, Count.
Use it when: Listing the functions.
NULL is not zero
Aggregates skip NULL, so AVG divides by fewer.
Use it when: Predicting averages.

SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM: solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

Example 1: Largest value · 1 mark · MCQBoard level

The query SELECT MAX(Salary) FROM Staff; returns:
(a) 55000
(b) 30000
(c) 248000
(d) 6

Try it first, then show the solutionSolution

MAX picks the largest salary among 42000, 38000, 55000, 30000, 47000, 36000.

Answer: (a) 55000

Example 2: Three function queries · 3 marks · Short answerBoard level

Give the output of: (a) SELECT SUM(Qty) FROM Stock; (b) SELECT AVG(Rate) FROM Stock WHERE Category = 'Electronics'; (c) SELECT COUNT(*) FROM Staff WHERE Dept = 'Accounts'; (3 marks)

Try it first, then show the solutionSolution

(a) 200 + 150 + 40 + 25 + 10 + 5 + 30 = 460.
(b) Electronics rates 8500 and 350: (8500 + 350) ÷ 2 = 4425.
(c) Anil, Chetan and Fiza: 3.

Answer: (a) 460; (b) 4425; (c) 3.

Example 3: UPDATE and DELETE in sequence · 6 marks · Long answerHarder board level

Starting from the original tables, run in order: (1) UPDATE Staff SET Salary = Salary + 5000 WHERE Dept = 'Accounts'; (2) UPDATE Stock SET Rate = Rate + 5 WHERE Category = 'Stationery'; (3) DELETE FROM Stock WHERE Qty < 10; Show the Staff table after (1), the Stock table after (3), and the output of SELECT COUNT(*) AS Items, SUM(Qty) AS TotalQty, MAX(Rate) AS MaxRate, MIN(Rate) AS MinRate FROM Stock; (6 marks)

Try it first, then show the solutionSolution

(1) Accounts salaries rise by 5000: Anil 47000, Chetan 60000, Fiza 41000; others unchanged.
(2) Stationery rates become Pen 15, Notebook 50, Stapler 125.
(3) Qty below 10: only the Printer (5) is deleted; Table has exactly 10 and stays.
Functions on the remaining 6 rows: Items 6; total Qty 200 + 150 + 40 + 25 + 10 + 30 = 455; MaxRate 4500; MinRate 15.

Answer: Staff after (1):

Name	Salary
Anil	47000
Beena	38000
Chetan	60000
Deepa	30000
Eshan	47000
Fiza	41000

Stock after (3):

ItemName	Qty	Rate
Pen	200	15
Notebook	150	50
Stapler	40	125
Chair	25	1800
Table	10	4500
Mouse	30	350

Functions:

Items	TotalQty	MaxRate	MinRate
6	455	4500	15

Example 4: UPDATE with and without WHERE · 3 marks · Short answerHarder board level

The total of the Salary column in Staff is ₹2,48,000. Find the total after (a) UPDATE Staff SET Salary = Salary + 1000; and (b) UPDATE Staff SET Salary = Salary + 1000 WHERE Dept = 'Stores'; Starting from the original table each time. State which statement a manager intending to raise only Stores salaries should have used. (3 marks)

Try it first, then show the solutionSolution

(a) All 6 rows rise by 1000: 2,48,000 + 6,000 = ₹2,54,000.
(b) Only Deepa (Stores) rises: 2,48,000 + 1,000 = ₹2,49,000.
Statement (b) is the correct one for the manager.

Answer: (a) ₹2,54,000; (b) ₹2,49,000. The manager should use (b).

Example 5: COUNT and AVG with NULL values · 3 marks · Short answerHOTS / competency

The Debtor table is: Asha, Pune, 13000; Bilal, Nagpur, NULL; Chitra, Pune, 8000; Dev, Nagpur, 21000; Esha, Pune, NULL. A student says AVG(Balance) is 42000 ÷ 5 = 8400. Give the output of SELECT COUNT(*), COUNT(Balance), SUM(Balance), AVG(Balance) FROM Debtor; and explain the student's error. (3 marks)

Try it first, then show the solutionSolution

COUNT(*) counts all 5 rows; COUNT(Balance) counts the 3 non-NULL balances.
SUM(Balance) = 13000 + 8000 + 21000 = 42000.
AVG ignores NULL: 42000 ÷ 3 = 14000, not 42000 ÷ 5.

Answer:

TotalRows	Balances	Total	Average
5	3	42000	14000

The student divided by 5 rows instead of the 3 non-NULL values.

Where students lose marks in SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM

The slips examiners see most often, and what to write instead.

Mistake: Writing UPDATE or DELETE without WHERE
Forgetting the condition.
Wrong working
DELETE FROM Stock;
Right working
DELETE FROM Stock WHERE Qty < 10;
Do this: Add the condition and check with SELECT first.
Usually costs: 2 marks.
Mistake: Counting NULL in an average
Dividing by the number of rows.
Wrong working
AVG = 42000 ÷ 5 = 8400
Right working
AVG = 42000 ÷ 3 = 14000
Do this: Aggregates skip NULLs.
Usually costs: 1-2 marks.
Mistake: Mixing up COUNT(*) and COUNT(column)
Treating them as identical.
Wrong working
COUNT(Balance) = 5
Right working
COUNT(Balance) = 3; COUNT(*) = 5
Do this: COUNT(column) skips NULLs.
Usually costs: 1 mark.
Mistake: Applying the function before the WHERE
Summarising all rows.
Wrong working
AVG(Rate) for Electronics taken over all 7 items
Right working
(8500 + 350) ÷ 2 = 4425
Do this: WHERE selects rows first.
Usually costs: 1-2 marks.

Exam tips

Show the table after each UPDATE or DELETE if the question runs several statements.
Name the result column in function queries (use AS or write the heading).
Mention NULL handling when the data contain missing values.
Check boundary rows in conditions such as Qty < 10.

Practice questions on SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

Q1 · 1 mark · MCQBoard level
The command DELETE FROM Staff; (with no condition) will:
(a) delete no rows
(b) delete only the first row
(c) delete all rows but keep the table
(d) delete the table's column names
Hint
No WHERE means every row.
Answer
(c) delete all rows but keep the table
Q2 · 3 marks · Short answerBoard level
Write SQL statements for the Stock table to: (i) increase the Rate of Chair by 200; (ii) remove the items whose Qty is 5. Give the new rate of Chair and the number of rows left. (3 marks)
Hint
Two statements, each with WHERE.
Answer
UPDATE Stock SET Rate = Rate + 200 WHERE ItemName = 'Chair'; DELETE FROM Stock WHERE Qty = 5; The Chair rate becomes 2000 and 6 rows remain (the Printer is removed).
Q3 · 3 marks · Short answerHarder board level
From the original Stock table, give the output of: (i) SELECT COUNT(*) FROM Stock WHERE Rate > 1000; (ii) SELECT MAX(Qty) FROM Stock WHERE Category = 'Stationery'; (iii) SELECT MIN(Rate) FROM Stock WHERE Category = 'Furniture'; (3 marks)
Hint
WHERE first, then the function.
Answer
(i) 3 (Chair, Table, Printer); (ii) 200; (iii) 1800.
Q4 · 6 marks · Long answerHarder board level
On the original Staff table run: UPDATE Staff SET Salary = Salary + 2000 WHERE Dept = 'Sales'; then DELETE FROM Staff WHERE Dept = 'Stores'; Give the output of SELECT COUNT(*), SUM(Salary), AVG(Salary), MAX(Salary), MIN(Salary) FROM Staff; and show the working. (6 marks)
Hint
Sales salaries become 40000 and 49000.
Answer
After the update, Salaries are 42000, 40000, 55000, 30000, 49000, 36000; the Stores row (Deepa, 30000) is deleted, leaving 42000, 40000, 55000, 49000, 36000. COUNT = 5; SUM = 2,22,000; AVG = 2,22,000 ÷ 5 = 44,400; MAX = 55,000; MIN = 36,000.
Q5 · 3 marks · Short answerHOTS / competency
The Supplier table has Kiran 5000, Lakshmi NULL, Manoj 7000, Neha NULL in the column Dues. A clerk reports "average dues ₹3,000". Give COUNT(*), COUNT(Dues), SUM(Dues) and AVG(Dues), and say whether the clerk is right. (3 marks)
Hint
How many non-NULL values?
Answer
COUNT(*) = 4; COUNT(Dues) = 2; SUM(Dues) = 12,000; AVG(Dues) = 12,000 ÷ 2 = 6,000. The clerk divided by 4 and got 3,000, which is wrong because AVG ignores NULLs.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1.Which command changes existing values?Show answer
UPDATE.
2.What happens if DELETE has no WHERE?Show answer
All rows are deleted.
3.Name the five functions in the syllabus.Show answer
AVG, COUNT, MAX, MIN, SUM.
4.Does AVG include NULL values?Show answer
No, they are ignored.
5.COUNT(*) counts what?Show answer
The number of rows.

SQL: UPDATE, DELETE and the Functions AVG, COUNT, MAX, MIN, SUM: frequently asked questions

What is the difference between DELETE and TRUNCATE?

DELETE removes the rows chosen by a WHERE condition (or all rows without it); TRUNCATE empties the whole table with no condition. Only DELETE is in the basic ISC command list.

Can UPDATE change more than one column?

Yes, separate the assignments with commas.

Do functions need WHERE?

No. Without it they work on all rows.

Is the output of a function query one row?

Yes, a single value (or one value per function listed).