Advanced Database Management SS3 Digital Technologies Lesson Note

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 organized digital filing cabinet. Inside this cabinet, we have Tables.

• A Table looks like a grid (similar to Excel).
• Rows (Records): These go across. Each row represents one person or thing (e.g., one student).
• Columns (Fields): These go down. Each column represents a specific detail (e.g., “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.

The Four Basic Commands (CRUD)

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

1. C – Create (INSERT): Adding a new student to the list.
2. R – Read (SELECT): Looking up information that is already there.
3. U – Update (UPDATE): Changing a student’s address if they move house.
4. D – 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 don’t tell the computer which person to delete, it might delete everyone in the whole school!

Writing Your First Query (The SELECT Statement)

A Query is just a fancy word for 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: If you want a list of all students in SS3, you would write: SELECT Name, Class FROM StudentTable WHERE Class = ‘SS3’;

The Asterisk (*): If you are lazy and want to see every column in a table, you use the star symbol: SELECT * FROM StudentTable;

Filtering and Sorting (The “Search” Power)

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 alphabetized list of the senior class.)

Generating Reports (Turning Data into Answers)

A Report is the final result of your queries, formatted so a human (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 math 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.

Summary 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 organized answer that helps people make decisions.