REAL TIME DATA ANALYSIS IN DBEAVER

Introduction
This practical exercise focused on designing and managing a relational database for Greenwood Academy using SQL. The objective was to demonstrate an understanding of Data Definition Language (DDL), Data Manipulation Language (DML), filtering techniques, aggregate functions, and conditional logic using SQL. Throughout the exercise, a complete database structure was created, populated with data, modified where necessary, and queried to retrieve meaningful information.
The practical provided hands-on experience with the essential SQL commands required to build and maintain a relational database management system.

Database and Schema Creation

The first task involved creating a dedicated schema named greenwood_academy. A schema serves as a logical container that organizes database objects such as tables, views, indexes, and constraints.
The search path was then configured so that all subsequent database objects would automatically be created within the greenwood_academy schema.

2. Creating Database Tables

Three relational tables were created to store the school's information.
Students Table
The students table stores personal information about every student enrolled at Greenwood Academy. The table includes:
• Student ID (Primary Key)
• First Name
• Last Name
• Gender
• Date of Birth
• Class
• City

The Student ID uniquely identifies every student in the database.

Subjects Table
The subjects table stores information about all academic subjects offered by the school.
The table contains:
• Subject ID (Primary Key)
• Subject Name
• Department
• Teacher Name
• Credit Hours
The Subject Name was defined as UNIQUE, ensuring that duplicate subject names cannot be entered into the database.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

SELECT * FROM students;

---

Exam Results Table

The exam_results table records examination performance for students.
The table stores:
• Result ID (Primary Key)
• Student ID
• Subject ID
• Marks
• Exam Date
• Grade
This table acts as the transactional table that links students to the subjects they have taken.

1. Modifying Existing Tables Database requirements often change over time. SQL provides the ALTER TABLE command to modify existing database structures. Several structural changes were performed during the exercise.

Adding a New Column

A new column named phone_number was added to the students table to accommodate student contact information.
This demonstrated how SQL allows database administrators to extend existing tables without affecting the stored data.

Renaming a Column

The credits column in the subjects table was renamed to credit_hours.
Renaming columns improves database readability while preserving existing records.

Removing a Column

After further review, the school decided that storing phone numbers was unnecessary.
The phone_number column was therefore removed using the ALTER TABLE DROP

COLUMN command.

These activities demonstrated the flexibility of SQL in adapting database structures to changing organizational requirements..

4. Inserting Data

Once the database structure had been completed, records were inserted into each table.
The following datasets were successfully inserted:
• 10 student records
• 10 subject records
• 10 examination result records
The INSERT INTO statement was used to populate each table with realistic school data.
Following the insertion process, SELECT statements and COUNT() functions were executed to verify that all records had been successfully stored.

INSERT INTO students (
    student_id,
    first_name,
    last_name,
    gender,
    date_of_birth,
    class,
    city
)
VALUES
(1, 'Amina', 'Wanjiku', 'F', '2008-03-12', 'Form 3', 'Nairobi'),
(2, 'Brian', 'Ochieng', 'M', '2007-07-25', 'Form 4', 'Mombasa'),
(3, 'Cynthia', 'Mutua', 'F', '2008-11-05', 'Form 3', 'Kisumu'),
(4, 'David', 'Kamau', 'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5, 'Esther', 'Akinyi', 'F', '2009-06-30', 'Form 2', 'Nakuru'),
(6, 'Felix', 'Otieno', 'M', '2009-09-14', 'Form 2', 'Eldoret'),
(7, 'Grace', 'Mwangi', 'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8, 'Hassan', 'Abdi', 'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9, 'Ivy', 'Chebet', 'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James', 'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');


INSERT INTO subjects (
    subject_id,
    subject_name,
    department,
    teacher_name,
    credit_hours
)
VALUES
(1, 'Mathematics', 'Sciences', 'Mr. Njoroge', 4),
(2, 'English', 'Languages', 'Ms. Adhiambo', 3),
(3, 'Biology', 'Sciences', 'Ms. Otieno', 4),
(4, 'History', 'Humanities', 'Mr. Waweru', 3),
(5, 'Kiswahili', 'Languages', 'Ms. Nduta', 3),
(6, 'Physics', 'Sciences', 'Mr. Kamande', 4),
(7, 'Geography', 'Humanities', 'Ms. Chebet', 3),
(8, 'Chemistry', 'Sciences', 'Ms. Muthoni', 4),
(9, 'Computer Studies', 'Sciences', 'Mr. Oduya', 3),
(10, 'Business Studies', 'Humanities', 'Ms. Wangari', 3);

INSERT INTO exam_results (
    result_id,
    student_id,
    subject_id,
    marks,
    exam_date,
    grade
)
VALUES
(1, 1, 1, 78, '2024-03-15', 'B'),
(2, 1, 2, 85, '2024-03-16', 'A'),
(3, 2, 1, 92, '2024-03-15', 'A'),
(4, 2, 3, 55, '2024-03-17', 'C'),
(5, 3, 2, 49, '2024-03-16', 'D'),
(6, 3, 4, 71, '2024-03-18', 'B'),
(7, 4, 1, 88, '2024-03-15', 'A'),
(8, 4, 6, 63, '2024-03-19', 'C'),
(9, 5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');

5. Updating Existing Records

Data stored in databases occasionally requires correction.
Two update operations were performed.
The first update changed Esther Akinyi's city from Nakuru to Nairobi after the student relocated.
The second update corrected an examination mark from 49 to 59 after it had been entered incorrectly.
These corrections demonstrated the use of the SQL UPDATE statement together with the WHERE clause to modify only the intended record.

**

6. Deleting Records

**
The school later cancelled one examination result.
Using the DELETE statement, the examination record with result_id = 9 was permanently removed from the exam_results table.
After deletion, verification queries confirmed that the record had been successfully removed.
This exercise demonstrated the proper use of SQL DELETE statements together with filtering conditions.

The data retrieval phase focused on demonstrating how SQL can be used to extract meaningful information from a relational database through the use of SELECT statements. A variety of queries were developed to retrieve specific records based on predefined conditions. These included identifying students enrolled in particular classes, filtering students by gender and city of residence, retrieving subjects offered within a specific department, displaying examination results that met certain performance thresholds, and selecting records that fell within defined date and mark ranges. Additional queries were written to identify students based on the initial letters of their names and to retrieve subjects containing specific keywords. Each query was designed to answer a particular business or academic question, illustrating the practical role of SQL in transforming raw data into useful information for decision-making.
To achieve these results, several SQL filtering operators were applied, including WHERE, AND, OR, BETWEEN, IN, NOT IN, and LIKE. These operators enabled precise filtering of records by combining multiple conditions, searching for values within specified ranges, matching text patterns, and excluding unwanted records. The exercise demonstrated how SQL provides flexible and efficient mechanisms for querying relational databases, allowing users to retrieve exactly the information they require without modifying the underlying data. Collectively, these techniques highlighted the importance of data filtering in database management and emphasized SQL's capability to support accurate reporting, data analysis, and informed decision-making within an educational information system.

**

8. Aggregate Functions

**
Aggregate functions summarize information contained within a database.
The COUNT() function was used to determine:
• The total number of students currently enrolled in Form 3.
• The total number of examination results with marks greater than or equal to 70.
Aggregate functions are valuable in reporting, statistical analysis, and business intelligence because they transform large datasets into concise summaries.

1. Using CASE WHEN Statements The CASE WHEN statement enables SQL to categorize data dynamically. Two practical examples were completed. Performance Classification Students' examination marks were classified into: • Distinction • Merit • Pass • Fail The classification was generated without changing the underlying data, simply by creating a calculated output column named performance. ________________________________________ Student Level Classification Students were also categorized according to their class level. Those in: • Form 3 • Form 4 were labelled Senior, while students in: • Form 1 • Form 2 were labelled Junior. The calculated column was named student_level. These exercises demonstrated how SQL can generate meaningful business information directly during data retrieval.

**

10. Challenges Encountered

**
During the practical session, one common PostgreSQL error was encountered:
ERROR: relation "students" does not exist
The problem occurred because PostgreSQL was searching for the table in the wrong schema.
The issue was resolved by:
• Verifying the current schema.
• Checking the location of the table using the information_schema.tables view.
• Setting the search path to the greenwood_academy schema.
• Referencing the table using its fully qualified name where necessary.
This troubleshooting exercise reinforced the importance of understanding schemas and database object locations when working with PostgreSQL.

**

Conclusion

**
This practical exercise successfully demonstrated the core principles of SQL database development and management. A fully functional school database was designed, populated, modified, and queried using industry-standard SQL commands.
The exercise provided practical experience in creating tables, modifying database structures, inserting records, updating information, deleting records, filtering data, summarizing results using aggregate functions, and generating dynamic classifications using CASE expressions.
Overall, the practical strengthened understanding of relational database concepts and highlighted the importance of SQL as a fundamental tool for storing, organizing, managing, and analyzing structured data. These skills form a strong foundation for more advanced topics such as database optimization, normalization, stored procedures, and business intelligence tools such as Power BI.