Skip to content

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
Start of this note, 2 of 7
Take this term’s notes offline
Reading here is free. Download all 7 SS3 Digital Technologies Second term notes as PDF to print and Word to edit.
Download all three terms, ₦2,000
Instant download. Secure payment by Paystack. Card, bank transfer, USSD or OPay. Payment stuck? WhatsApp us.

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:

Advertisement
  1. describe a database, a table, a row (record) and a column (field);
  2. name the four CRUD commands and the SQL command for each;
  3. write a simple SELECT query using FROM and WHERE;
  4. use WHERE, ORDER BY, AND and OR to filter and sort data;
  5. 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.
Name Class Age Ada SS3 17 Emeka SS2 16 Bisi SS3 17 Record (row) Top row: field names. Each column is a field.
A student table. The green box marks one record (row). Based on standard ICT texts.
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
Advertisement

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.
Advertisement

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.
Advertisement

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?

  1. Decide the question the reader needs answered.
  2. Write a query that selects the needed data.
  3. Filter and sort the data with WHERE and ORDER BY.
  4. Use SUM, AVG, COUNT or GROUP BY to summarise it.
  5. 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

  1. What is the difference between a row and a column in a table?
  2. List the four CRUD operations and the SQL command for each.
  3. Why must you be careful when using the DELETE command?
  4. Write a query that shows the Name and Class of all students in SS3 from StudentTable.
  5. 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

  1. Advanced Web Design
  2. Advanced Database Management
  3. Networking III
  4. Digital Project Management
  5. Emerging Tech III
  6. Revision of SSCE Past Questions I
  7. Revision of SSCE Past Questions II
Advertisement

Lesson Notes for Other Classes

Print it or edit it in Word

Every note is free to read online. A PDF or Word copy of one subject costs ₦1,000 for one term, or ₦2,000 for all three terms.

Sign in first, then choose your class and subject at checkout.