USE school_quality_analysis
CREATE TABLE Schools (
SchoolID INT PRIMARY KEY,
SchoolName VARCHAR(100),
Location VARCHAR(50),
TotalTeachers INT
);
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Age INT,
Gender VARCHAR(10),
GradeLevel VARCHAR(50),
SchoolID INT,
PerformanceScore DECIMAL(5, 2),
FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);
CREATE TABLE Teachers (
TeacherID INT PRIMARY KEY,
TeacherName VARCHAR(100),
Gender VARCHAR(10),
Subject VARCHAR(50),
SchoolID INT,
FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);
CREATE TABLE Resources (
ResourceID INT PRIMARY KEY,
ResourceType VARCHAR(100),
Quantity INT,
SchoolID INT,
FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);
CREATE TABLE PerformanceMetrics (
MetricID INT PRIMARY KEY,
MetricType VARCHAR(100),
MetricValue DECIMAL(5,2),
SchoolID INT,
FOREIGN KEY (SchoolID) REFERENCES Schools(SchoolID)
);
INSERT INTO Schools (SchoolID, SchoolName, Location, TotalTeachers)
VALUES
(1, 'God Is Good High', 'Urban', 25),
(2, 'Sunshine Elementary', 'Rural', 10),
(3, 'Nairobi Academy', 'Urban', 30),
(4, 'Runda School', 'Rural', 15);
ALTER TABLE Students
ADD GradeLevel VARCHAR(30);
INSERT INTO Students (StudentID, StudentName, Age, Gender, GradeLevel, SchoolID,
PerformanceScore)
VALUES
(1, 'Alice Kimani', 14, 'Female', 'Grade 8', 1, 85.5),
(2, 'Polo Smith', 10, 'Male', 'Grade 5', 2, 78.0),
(3, 'Cathy Aoko', 13, 'Female', 'Grade 7', 1, 92.3),
(4, 'David Seree', 9, 'Male', 'Grade 4', 2, 80.1),
(5, 'Eva Cado', 15, 'Female', 'Grade 9', 3, 88.9),
(6, 'Frank Ireri', 11, 'Male', 'Grade 6', 4, 74.5);
ALTER TABLE Teachers
ADD YearsExperience INT;
INSERT INTO Teachers (TeacherID, TeacherName, Subject, Gender, YearsExperience,
SchoolID)
VALUES
(1, 'Mary HadaLittleLamb', 'Mathematics', 'Female', 8, 1),
(2, 'John DaBaptist', 'Science', 'Male', 5, 2),
(3, 'Sarah WaAbraham', 'English', 'Female', 10, 3),
(4, 'Michael Martinez', 'History', 'Male', 6, 4),
(5, 'Laura Robinson', 'Physical Education', 'Female', 7, 1),
(6, 'Peter Kenneth', 'Art', 'Male', 4, 3),
(7, Makori Ben', 'Home Science', 'Male', 2, 5);
INSERT INTO Resources (ResourceID, ResourceType, Quantity, SchoolID)
VALUES
(1, 'Textbooks', 200, 1),
(2, 'Computers', 50, 2),
(3, 'Lab Equipment', 25, 3),
(4, 'Library Books', 100, 4),
(5, 'Sports Equipment', 60, 1),
(6, 'Computers', 20, 4);
INSERT INTO PerformanceMetrics (MetricID, MetricType, MetricValue, SchoolID)
VALUES
(1, 'Graduation Rate', 90.0, 1),
(2, 'Literacy Rate', 85.0, 2),
(3, 'Attendance Rate', 95.0, 3),
(4, 'Graduation Rate', 78.0, 4),
(5, 'Literacy Rate', 88.5, 1),
(6, 'Attendance Rate', 80.0, 2);
SELECT * FROM Students;
SELECT Location, AVG(PerformanceScore) AS AveragePerformance
FROM Students
JOIN Schools ON Students.SchoolID = Schools.SchoolID
GROUP BY Location;
