Skip to page content

Database Management System — Part 2: SQL, Aggregate Functions and DBMS in Business

Beta

Lesson

Asking questions of the data

Part 2 — SQL statements, aggregate functions, and the database at work in debtors, bank reconciliation and asset accounting.

In this lesson you will

Write and read SELECT queries with WHERE, AND and OR, using brackets where needed
Write UPDATE and DELETE statements and state how many records they affect
Use COUNT, SUM, AVG, MAX and MIN to summarise accounting data
Explain how a database supports debtors and creditors, bank reconciliation and the asset register

Part 2 at a glance

Concept

Monal’s Invoices table

Imagined data. InvNo is the primary key; CustCode is a foreign key to Customers.
InvNo	InvDate	CustCode	Amount
501	2026-04-03	C01	48000
502	2026-04-05	C02	36000
503	2026-04-09	C03	52000
504	2026-04-12	C01	30000
505	2026-04-15	C05	75000
506	2026-04-18	C04	22000
507	2026-04-22	C02	41000
508	2026-04-26	C06	18000
509	2026-04-28	C05	64000
510	2026-04-30	C03	27000

Concept

The SQL statements in your syllabus

Text and dates go in single quotes; numbers do not. A statement ends with a semicolon.
Statement / clause	What it does	Form
SELECT … FROM	Shows chosen fields (or * for all) of a table	`SELECT InvNo, Amount FROM Invoices;`
WHERE	Keeps only the records that meet a condition	`WHERE Amount > 40000`
Comparison	Equal, not equal, less, greater, at most, at least	`= <> < > <= >=`
AND	Both conditions must be true	`City = 'Vidisha' AND Amount > 40000`
OR	At least one condition must be true	`CustCode = 'C02' OR CustCode = 'C05'`
UPDATE … SET … WHERE	Changes values in the records that meet the condition	`UPDATE Customers SET CreditLimit = 90000 WHERE CustCode = 'C04';`
DELETE FROM … WHERE	Removes the records that meet the condition	`DELETE FROM Invoices WHERE InvNo = 506;`