Advanced Database Management – SS3 Digital Technologies Lesson Note (Second Term)
Lesson note at a glance
- Class
- SS3
- Subject
- Digital Technologies
- Term
- Second Term
- Reading time
- 6 min
A database is an organised collection of data stored in tables so that it can be searched, changed and reported on quickly.
Behavioural objectives
By the end of the lesson, students should be able to:
- describe a database, a table, a row (record) and a column (field);
- name the four CRUD commands and the SQL command for each;
- write a simple SELECT query using FROM and WHERE;
- use WHERE, ORDER BY, AND and OR to filter and sort data;
- explain what a report is and state what SUM, AVG and COUNT do.
What is a database?
A database is an organised digital filing cabinet that stores data in tables.
- Problem it solves: a school with 2,000 students on loose papers would need hours to find one record.
- Table: a grid that holds data about one kind of thing, such as students.
- Record: one row of a table, such as one student.
- Field: one column of a table, such as “Phone Number” or “Admission Date”.
- Primary key: a field with a different value for every record, such as StudentID, used to identify one record exactly.
- Database management system (DBMS): software that creates and manages databases, such as MySQL, Oracle and Microsoft Access.
Where are databases used?
- Schools: student names, classes and scores.
- Banks: customer accounts and transactions.
- Shops: stock items, prices and sales.
- Hospitals: patient records and appointments.
What are rows and columns in a table?
A table is a grid made of rows (records) that go across and columns (fields) that go down.
- Row (record): goes across and holds every detail about one person or thing.
- Column (field): goes down and holds one type of detail for everyone.
| Feature | Row (record) | Column (field) |
|---|---|---|
| Direction | Goes across | Goes down |
| Holds | All details of one item | One kind of detail for all items |
| Example | Everything about Ada | The Class of every student |
What is SQL?
SQL (Structured Query Language) is the language used to add, read, change and delete data in a database.
- Remote control idea: SQL is the remote control for your data.
- Statement: one SQL instruction, usually ended with a semicolon (;).
- Text values: written inside single quotes, such as ‘SS3’.
- Keywords: words such as SELECT, FROM and WHERE, usually written in capital letters.
What are the four basic commands (CRUD)?
CRUD stands for Create, Read, Update and Delete, the four main jobs done on any database.
| Letter | Operation | SQL command | Example use |
|---|---|---|---|
| C | Create | INSERT | Add a new student to the list |
| R | Read | SELECT | Look up information already stored |
| U | Update | UPDATE | Change an address after a student moves house |
| D | Delete | DELETE | Remove a record when a student leaves |
What does each command look like?
- INSERT: INSERT INTO StudentTable (Name, Class) VALUES (‘Ada’, ‘SS3’);
- SELECT: SELECT Name FROM StudentTable;
- UPDATE: UPDATE StudentTable SET Class = ‘SS3’ WHERE StudentID = 5;
- DELETE: DELETE FROM StudentTable WHERE StudentID = 5;
What is the golden rule of SQL?
- Be careful with DELETE: if you do not say which record to remove, the computer may delete every record in the table.
- Always use WHERE: add WHERE to DELETE and UPDATE so only the right record changes.
- Check first: run a SELECT with the same WHERE to see which records will be affected.
How do you write a query with SELECT?
A query is a question that asks the database to show specific data, and SELECT is the command used to write it.
Pattern: SELECT [what you want] FROM [which table] WHERE [special condition];
- SELECT: names the columns you want to see.
- FROM: names the table that holds the data.
- WHERE: gives a condition that records must meet. It is optional.
What are some SELECT examples?
- All SS3 students: SELECT Name, Class FROM StudentTable WHERE Class = ‘SS3’;
- Every column (asterisk): SELECT * FROM StudentTable; The star (*) means all columns.
How do you filter and sort data?
You filter data with WHERE and sort it with ORDER BY, and you combine rules with AND and OR.
What does WHERE do?
- WHERE: the filter. It keeps only records that match a condition, such as Age > 15.
- LIKE: matches patterns. WHERE Name LIKE ‘A%’ finds names that start with A.
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | Class = ‘SS3’ |
| > | Greater than | Age > 15 |
| < | Less than | Age < 15 |
| <> | Not equal to | Class <> ‘SS3’ |
What does ORDER BY do?
- ORDER BY: the sort button. It puts results in order.
- ASC: ascending order, such as A to Z or lowest to highest.
- DESC: descending order, such as highest price to lowest price.
What do AND and OR do?
- AND: both conditions must be true. Class = ‘SS3’ AND Age > 15.
- OR: at least one condition must be true. Class = ‘SS3’ OR Class = ‘SS2’.
Example of filtering and sorting together
- Query: SELECT Name FROM Students WHERE Class = ‘SS3’ ORDER BY Name ASC;
- Result: a neatly alphabetised list of the SS3 students.
How do you generate reports?
A report is the formatted result of queries, set out so that a person such as a Principal or Manager can read it and make decisions.
Why do we need reports?
- Summary: instead of 1,000 sales, a report shows “Total Sales for January”.
- Grouping: data can be grouped, such as the number of students in each house (Red, Blue, Green).
- Calculations: SQL does the maths for you using aggregate functions.
What are aggregate functions?
Aggregate functions calculate one result from many values in a column.
| Function | What it does | Example |
|---|---|---|
| SUM | Adds numbers up | SELECT SUM(Amount) FROM Sales; |
| AVG | Finds the average | SELECT AVG(Score) FROM Results; |
| COUNT | Counts how many items are in the list | SELECT COUNT(*) FROM StudentTable; |
- GROUP BY: groups records before counting or adding. SELECT House, COUNT(*) FROM StudentTable GROUP BY House;
How is a report produced?
- Decide the question the reader needs answered.
- Write a query that selects the needed data.
- Filter and sort the data with WHERE and ORDER BY.
- Use SUM, AVG, COUNT or GROUP BY to summarise it.
- Present the result in a clear layout with a title and headings.
What are the key terms?
- Table: where the data lives.
- SQL: the language used to ask questions of a database.
- SELECT: the command to see data.
- WHERE: the command to filter data.
- Report: the organised answer that helps people make decisions.
Evaluation
- What is the difference between a row and a column in a table?
- List the four CRUD operations and the SQL command for each.
- Why must you be careful when using the DELETE command?
- Write a query that shows the Name and Class of all students in SS3 from StudentTable.
- State what SUM, AVG and COUNT do.
Assignment
A school keeps a table called StudentTable with the fields Name, Class and Age. Write a query that shows every column, a second query that lists the names of SS3 students in A to Z order, and two reasons why a Principal would need a report from this data.
This term's Digital Technologies topics
SS3 Second Term · 7 topics

