Advanced Database Management - SSS 3 Note

ADVANCED DATABASE MANAGEMENT

Subject: Digital Technologies
Class: SSS 3
Term: Second Term
Week: 2
Topic: Advanced Database Management
Duration: 80 Minutes (Double Period)

Learning Objectives

By the end of the lesson, students should be able to:

1. Explain the meaning and purpose of SQL.
2. Identify common SQL commands.
3. Write simple SQL queries to retrieve database records.
4. Use conditions to filter records.
5. Sort query results and select specific fields.
6. Explain how SQL queries can be used to prepare simple database reports.

1. Introduction to Database Management

A database is an organised collection of related information stored so that it can be accessed, managed and updated efficiently. Databases are used in schools, hospitals, banks, businesses, libraries and many other organisations.

For example, a school database may store students' names, admission numbers, classes and examination scores. Instead of searching through many paper records, an authorised user can retrieve the required information using database software.

Database management refers to the activities involved in storing, organising, retrieving, updating and maintaining data in a database.

Example of a Student Database Table

StudentID	Name	Class	Score
101	Ada	SSS 3	85
102	Chidi	SSS 3	72
103	Fatima	SSS 2	91
104	Emeka	SSS 3	64

This table contains four records. Each row represents one student, while each column represents a field such as StudentID, Name, Class or Score.

2. Introduction to SQL

SQL stands for Structured Query Language. It is a language used to create, retrieve, manipulate and manage data in relational database systems.

SQL enables users to communicate with a database by writing instructions called queries or commands. These instructions can retrieve information, add new records, change existing data and remove records when authorised.

Figure 1: SQL allows users to request information from a database.

Uses of SQL

• Retrieving records from database tables.
• Filtering information according to specified conditions.
• Adding new records to a database.
• Updating existing records.
• Deleting records when authorised.
• Organising information for reports and analysis.

3. Basic SQL Commands and Queries

An SQL command is an instruction used to perform an operation on a database. The commands used depend on the task being carried out.

3.1 SELECT

The SELECT command retrieves information from a database table.

SELECT * FROM Students;

This query retrieves all columns and records from the table named Students. The asterisk (*) means all columns.

To retrieve only students' names and scores, use:

SELECT Name, Score
FROM Students;

3.2 INSERT INTO

The INSERT INTO command adds a new record to a table.

INSERT INTO Students (StudentID, Name, Class, Score)
VALUES (105, 'Grace', 'SSS 3', 78);

This example adds a student record with ID 105, the name Grace, class SSS 3 and score 78. The table and column names must match the database structure.

3.3 UPDATE

The UPDATE command changes existing data in a table.

UPDATE Students
SET Score = 80
WHERE StudentID = 105;

This query changes Grace's score to 80 for the record with StudentID 105.

Important: An UPDATE statement should normally include an appropriate WHERE condition. Without it, every record in the table may be updated.

3.4 DELETE

The DELETE command removes records from a table.

DELETE FROM Students
WHERE StudentID = 105;

This statement deletes the record with StudentID 105.

Database safety: DELETE and UPDATE commands can change important information. Before running them, check the target records and ensure that you have permission to make the change.

3.5 CREATE TABLE

The CREATE TABLE command creates a new table in a database.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(20),
    Score INT
);

In this example:

• StudentID stores a student's identification number.
• INT indicates an integer data type.
• VARCHAR(50) stores text with a maximum declared length of 50 characters.
• PRIMARY KEY identifies each record uniquely.

Summary of Basic SQL Commands

Command	Purpose
SELECT	Retrieves data.
INSERT INTO	Adds new records.
UPDATE	Changes existing records.
DELETE	Removes records.
CREATE TABLE	Creates a table.

4. Retrieving Database Records

Retrieving data means requesting and obtaining information stored in a database. SQL uses the SELECT statement for this purpose.

4.1 Retrieving All Records

SELECT * FROM Students;

This retrieves all columns and rows from the Students table.

4.2 Retrieving Specific Fields

SELECT Name, Class
FROM Students;

This returns only the Name and Class columns.

4.3 Using Column Aliases

A column alias gives a column a temporary display name in the query result.

SELECT Name, Score AS ExaminationScore
FROM Students;

The column containing the score is displayed using the heading ExaminationScore in the result.

5. Filtering Database Records

Filtering means selecting only records that satisfy a particular condition. SQL uses the WHERE clause to specify that condition.

5.1 Filtering with a Comparison

To find students who scored more than 70:

SELECT Name, Score
FROM Students
WHERE Score > 70;

Using the sample table, the query returns Ada, Chidi and Fatima because their scores are greater than 70.

5.2 Common Comparison Operators

Operator	Meaning	Example
=	Equal to	WHERE Class = 'SSS 3'
>	Greater than	WHERE Score > 70
<	Less than	WHERE Score < 50
>=	Greater than or equal to	WHERE Score >= 50
<=	Less than or equal to	WHERE Score <= 40
<>	Not equal to in standard SQL	WHERE Class <> 'SSS 2'

5.3 Filtering with AND

The AND operator returns records when all specified conditions are true.

SELECT Name, Score
FROM Students
WHERE Class = 'SSS 3' AND Score >= 70;

This query returns students who are in SSS 3 and scored at least 70.

5.4 Filtering with OR

The OR operator returns records when at least one of the specified conditions is true.

SELECT Name, Class
FROM Students
WHERE Class = 'SSS 2' OR Score > 80;

This query returns students who are in SSS 2 or scored more than 80.

5.5 Filtering a Range with BETWEEN

The BETWEEN operator selects values within a range, including both endpoints.

SELECT Name, Score
FROM Students
WHERE Score BETWEEN 70 AND 90;

This selects students whose scores are from 70 to 90 inclusive.

5.6 Searching Text with LIKE

The LIKE operator searches for a pattern in text. The percent sign (%) represents zero or more characters in a pattern.

SELECT Name
FROM Students
WHERE Name LIKE 'A%';

This example finds names that begin with the letter A, such as Ada.

6. Sorting Query Results

The ORDER BY clause arranges query results in a specified order.

6.1 Ascending Order

SELECT Name, Score
FROM Students
ORDER BY Score ASC;

ASC sorts scores from the lowest to the highest. It is also the default sort order when no direction is specified.

6.2 Descending Order

SELECT Name, Score
FROM Students
ORDER BY Score DESC;

DESC sorts scores from the highest to the lowest.

6.3 Combining Filtering and Sorting

SELECT Name, Score
FROM Students
WHERE Score >= 70
ORDER BY Score DESC;

This query selects students with scores of at least 70 and displays them from the highest score to the lowest.

7. Generating Simple Database Reports

A database report is an organised presentation of selected information from a database for reading, monitoring, decision-making or record keeping.

Reports can help schools identify high-performing students, list students by class, summarise examination results and prepare useful information for teachers or administrators.

7.1 Selecting Records for a Report

Suppose a teacher needs a report of students who scored at least 70.

SELECT StudentID, Name, Class, Score
FROM Students
WHERE Score >= 70
ORDER BY Score DESC;

The query retrieves the relevant records and arranges them by score from highest to lowest. The output can then be used as the basis of a simple report.

7.2 Using Aggregate Functions

SQL aggregate functions perform calculations on a group of records.

Function	Purpose	Example
COUNT()	Counts records or non-null values.	COUNT(*)
AVG()	Calculates an average.	AVG(Score)
MAX()	Finds the highest value.	MAX(Score)
MIN()	Finds the lowest value.	MIN(Score)
SUM()	Adds numeric values.	SUM(Score)

7.3 Counting Students

SELECT COUNT(*) AS TotalStudents
FROM Students;

This query returns the number of records in the Students table. If the table contains four student records, the result is 4.

7.4 Calculating the Average Score

SELECT AVG(Score) AS AverageScore
FROM Students;

This query calculates the average of the values in the Score column, excluding null scores.

7.5 Finding the Highest and Lowest Scores

SELECT MAX(Score) AS HighestScore,
       MIN(Score) AS LowestScore
FROM Students;

The query returns the highest and lowest scores stored in the table.

7.6 Grouping Report Results

The GROUP BY clause groups records that have the same value in a specified column. It can be used to calculate summaries for each class.

SELECT Class, COUNT(*) AS NumberOfStudents,
       AVG(Score) AS AverageScore
FROM Students
GROUP BY Class;

This query produces one summary row for each class, showing the number of students and their average score.

Remember: SQL queries retrieve and organise the data. Depending on the database software, the result may be viewed on screen, exported to a spreadsheet or used by a reporting tool to create a formatted report.

8. Good Practices When Working with SQL

• Use clear and meaningful table and column names.
• Check the spelling of table and column names before running queries.
• Use WHERE carefully when updating or deleting records.
• Test queries with sample data before using them on important records.
• Keep backup copies of important databases.
• Only access or change information you are authorised to use.
• Protect confidential student, customer and organisational information.
• Check query results to ensure they are accurate and complete.

9. Practical Class Activity

Activity: Use the sample Students table from this lesson to write SQL queries.

1. Write a query to display all records from the Students table.
2. Write a query to display only the Name and Score columns.
3. Write a query to display students who scored more than 70.
4. Write a query to display students in SSS 3.
5. Write a query to arrange students by score from highest to lowest.
6. Write a query to count the total number of student records.
7. Write a query to calculate the average score.

Where database software is available, students should run the queries and compare their results. If a computer is unavailable, students can write the SQL statements in their notebooks and discuss the expected results.

10. Summary

SQL stands for Structured Query Language. It is used to create, retrieve and manipulate information in relational databases. Common SQL commands include SELECT, INSERT INTO, UPDATE, DELETE and CREATE TABLE.

The SELECT statement retrieves records, while the WHERE clause filters them according to specified conditions. ORDER BY arranges results in ascending or descending order. SQL aggregate functions such as COUNT, AVG, MAX, MIN and SUM help summarise information for simple database reports.

11. Evaluation Questions

1. What is SQL, and what is its full meaning?
2. State four uses of SQL in database management.
3. Explain the purpose of the SELECT command.
4. Differentiate between INSERT INTO and UPDATE.
5. What is the purpose of the WHERE clause?
6. Explain the difference between AND and OR in an SQL query.
7. What is the function of ORDER BY?
8. Explain the purpose of COUNT() and AVG().
9. What is a database report?
10. Write an SQL query to display the names of students whose scores are at least 70, arranged from highest to lowest.

12. Assignment

Using the sample Students table in this lesson, write SQL statements to perform the following tasks:

1. Display all records from the Students table.
2. Display the Name and Class columns only.
3. Find students whose scores are below 70.
4. Display records in ascending order of score.
5. Count the total number of students.
6. Calculate the average score of all students.
7. Write a query that groups the records by Class and counts the students in each class.

13. Interactive Revision Quiz

Choose one answer for each question and click Mark Quiz to see your score.

1. What does SQL stand for?

A. Standard Question List B. System Query Link C. Structured Query Language D. Simple Question Language

2. Which SQL command retrieves data from a table?

A. SELECT B. DELETE C. UPDATE D. DROP

3. Which clause is commonly used to filter records?

A. ORDER BY B. FROM C. SELECT D. WHERE

4. Which command adds a new record to a table?

A. SELECT B. INSERT INTO C. ORDER BY D. GROUP BY

5. What does ORDER BY Score DESC do?

A. Deletes scores B. Displays names only C. Arranges scores from highest to lowest D. Counts student records

6. Which SQL function counts records?

A. MAX() B. AVG() C. SUM() D. COUNT()

7. Which operator requires both conditions to be true?

A. AND B. OR C. LIKE D. DESC

8. Which command changes existing records?

A. SELECT B. UPDATE C. CREATE TABLE D. ORDER BY

9. What is the purpose of GROUP BY?

A. Deletes duplicate tables automatically B. Changes the database password C. Groups records with matching values for summarisation D. Sorts every result in descending order

10. Why should a WHERE clause be used carefully with UPDATE and DELETE?

A. It changes the font of a report B. It makes every query a report C. It prevents all database errors automatically D. Without a suitable condition, unintended records may be affected