This content originally appeared on DEV Community and was authored by Boopathy.S
INTRODUCTION:
In this blog we are going to create a college student and course management system .Managing students, courses, and enrollments is a common requirement in colleges and universities. In this blog, we’ll build a Student and Course Management System step by step using Oracle SQL.
we will learn how to:
Create relational tables (Students, Courses, and Enrollments)
Insert sample data
Alter tables and add constraints
Run queries to retrieve meaningful results
Step 1: Create the Students table
We’ll start by creating a Students table to store student information.
sql
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);
Explanation:
StudentID: Primary key, ensures uniqueness.
Name: Student name (mandatory field).
Dept: Department of the student.
DOB: Date of Birth.
Email: Must be unique for every student.
Step 2: Create the Courses table
Now, let’s create a table to store various courses.
sql
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);
We also want to make sure credits are valid. Let’s add a constraint:
sql
ALTER TABLE Courses ADD CHECK (Credits BETWEEN 1 AND 5);
Step 3: Create the Enrollments table
The Enrollments table connects students and courses.
sql
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);
This establishes foreign key relationships, ensuring only valid students and valid courses can be enrolled.
Step 4: Insert Sample Data
Insert a few students:
sql
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'JAI', 'Computer Science', TO_DATE('2006-03-15', 'YYYY-MM-DD'), 'jai.cs@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'MATHI', 'Electrical Engineering', TO_DATE('2006-11-22', 'YYYY-MM-DD'), 'mathi.ee@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'PRIAN', 'Mechanical Engineering', TO_DATE('2006-07-09', 'YYYY-MM-DD'), 'prian.mech@example.com');
Insert courses:
sql
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'Database Systems', 3);
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'Operating Systems', 4);
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'Data Structures', 5);
Don’t forget to commit your changes:
sql
COMMIT;
Step 5: Alter Table (Add Phone Number)
Let’s add a new column for student phone numbers:
sql
ALTER TABLE Students ADD PhoneNo VARCHAR2(10);
Step 6: Run Useful Queries
- Display student names in uppercase with email length sql SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;
- Show all courses with credits sql SELECT CourseID, CourseName, Credits FROM Courses;
- Display all students sql SELECT StudentID, Name, Dept, DOB, Email, PhoneNo FROM Students;
Finally:
We have successfully built a College Student and Course Management System using Oracle SQL.
Thank You @santhoshnc sir for guiding me.
This content originally appeared on DEV Community and was authored by Boopathy.S
Boopathy.S | Sciencx (2025-08-21T14:33:06+00:00) College Student & Course Management System. Retrieved from https://www.scien.cx/2025/08/21/college-student-course-management-system/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.