Advanced Database Management – SS3 Digital Technologies Lesson Note (Second Term)

A database is an organised digital filing cabinet that stores data in tables. Each table is a grid of rows (records) and columns (fields). SQL (Structured Query Language) is the language used to add, read, change and delete data. Reports turn the results of queries into answers people can use to make decisions.

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?

Imagine a school with 2,000 students. If you kept all their names, grades and birthdays in a giant pile of papers, finding one student’s record would take hours.

A database is like a very organised digital filing cabinet. Inside this cabinet, we have tables.

A table looks like a grid (similar to Excel). It has two parts:

• Rows (records): These go across. Each row represents one person or thing (for example, one student).
• Columns (fields): These go down. Each column represents a specific detail (for example, “Phone Number” or “Admission Date”).

To talk to this filing cabinet, we need a language. That language is SQL (Structured Query Language). Think of SQL as the “remote control” for your data.

What are the four basic commands (CRUD)?

In every database, there are four main things you will do. Programmers call these CRUD:

1. C for Create (INSERT): Adding a new student to the list.
2. R for Read (SELECT): Looking up information that is already there.
3. U for Update (UPDATE): Changing a student’s address if they move house.
4. D for Delete (DELETE): Removing a record if a student leaves the school.

The golden rule of SQL: Always be careful with the DELETE command. If you do not tell the computer which person to delete, it might delete everyone in the whole school.

How do you write a query with SELECT?

A query is a question. When you write a query, you are asking the database to show you specific data.

The most common command is SELECT. It usually follows this pattern:

SELECT [What you want] FROM [Which table] WHERE [Special condition]

Real-world example: To get a list of all students in SS3, you would write:

SELECT Name, Class FROM StudentTable WHERE Class = ‘SS3’;

The asterisk (*): If you want to see every column in a table, use the star symbol:

SELECT * FROM StudentTable;

How do you filter and sort data?

Sometimes a list is too long. We need to narrow it down or put it in order.

• WHERE: This is your filter. You can use it to find people older than 15 (Age > 15) or people whose name starts with ‘A’.
• ORDER BY: This is your “sort” button. You can sort names alphabetically (A-Z) or sort prices from highest to lowest.
• AND / OR: You can combine rules. For example: “Find students who are in SS3 AND play football.”

Example:

SELECT Name FROM Students WHERE Class = ‘SS3’ ORDER BY Name ASC;

This gives us a neatly alphabetised list of the senior class.

How do you generate reports?

A report is the final result of your queries, formatted so that a person (like a Principal or a Manager) can read it and make decisions.

Why do we need reports?

• Summary: Instead of seeing 1,000 sales, a report shows you the “Total Sales for January.”
• Grouping: You can group data together. For example, “How many students are in each house (Red, Blue, Green)?”
• Calculations: SQL can do the maths for you using “aggregate functions”:
  1. SUM: Adds numbers up.
  2. AVG: Finds the average.
  3. COUNT: Tells you how many items are in the list.

Key terms checklist

• Table: Where the data lives.
• SQL: The language used to ask questions.
• 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

1. Write a query that shows every column in StudentTable.
2. Explain the job of ORDER BY and of WHERE, with one example of each.
3. Explain what a report is and give two reasons why a school Principal would need one.