Scribd
Upload
5 views2 pages

SQL School Management System Guide

The document outlines a SQL practice project for a School Management System, detailing the creation of three tables: Students, Subjects, and Enrollments, along with sample data insertion. It includes several SQL queries for tasks such as listing students, showing enrollments, calculating average ages, filtering students by grades, and identifying subjects with the most enrollments. Additionally, it specifies the creation of a view for top-performing students based on their grades.

Uploaded by

kiptoorooney6
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
5 views2 pages

SQL School Management System Guide

The document outlines a SQL practice project for a School Management System, detailing the creation of three tables: Students, Subjects, and Enrollments, along with sample data insertion. It includes several SQL queries for tasks such as listing students, showing enrollments, calculating average ages, filtering students by grades, and identifying subjects with the most enrollments. Additionally, it specifies the creation of a view for top-performing students based on their grades.

Uploaded by

kiptoorooney6
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Practice Project: School Management System

TABLES TO CREATE:

1. Students
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Gender VARCHAR(10),
Age INT,
Class VARCHAR(10)
);

2. Subjects
CREATE TABLE Subjects (
SubjectID INT PRIMARY KEY,
SubjectName VARCHAR(50)
);

3. Enrollments
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
SubjectID INT,
Grade CHAR(1),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)
);

INSERT SAMPLE DATA:

-- Students
INSERT INTO Students (StudentID, Name, Gender, Age, Class) VALUES
(1, 'Alice', 'Female', 14, '8A'),
(2, 'Brian', 'Male', 15, '8B'),
(3, 'Clara', 'Female', 13, '8A'),
(4, 'Daniel', 'Male', 14, '8C');

-- Subjects
INSERT INTO Subjects (SubjectID, SubjectName) VALUES
(1, 'Math'), (2, 'Science'), (3, 'English'), (4, 'History');

-- Enrollments
INSERT INTO Enrollments (StudentID, SubjectID, Grade) VALUES
(1, 1, 'A'), (1, 2, 'B'),
(2, 1, 'B'), (2, 3, 'A'),
(3, 3, 'C'), (3, 4, 'B'),
(4, 2, 'A'), (4, 4, 'A');
PRACTICE TASKS:

1. List all students and their classes.


SELECT Name, Class FROM Students;

2. Show subjects each student is enrolled in.


SELECT [Link], [Link]
FROM Enrollments e
JOIN Students s ON [Link] = [Link]
JOIN Subjects sub ON [Link] = [Link];

3. Get average age of students per class.


SELECT Class, AVG(Age) AS AvgAge FROM Students GROUP BY Class;

4. List students with grade 'A'.


SELECT [Link], [Link]
FROM Enrollments e
JOIN Students s ON [Link] = [Link]
JOIN Subjects sub ON [Link] = [Link]
WHERE [Link] = 'A';

5. Find subjects with the most enrolled students.


SELECT [Link], COUNT(*) AS Enrolled
FROM Enrollments e
JOIN Subjects sub ON [Link] = [Link]
GROUP BY [Link]
ORDER BY Enrolled DESC;

6. Create a view of top-performing students.


CREATE VIEW TopPerformers AS
SELECT [Link], [Link]
FROM Enrollments e
JOIN Students s ON [Link] = [Link]
JOIN Subjects sub ON [Link] = [Link]
WHERE [Link] = 'A';

You might also like