This content originally appeared on DEV Community and was authored by SASHMITHA G 24CB054
Building a Simple Student Management Schema in Oracle SQL
In this post, I will walk you through a straightforward example of creating a small database schema for managing students, courses, and enrollments using Oracle SQL. This example covers table creation, constraints, data insertion, and simple queries — perfect for beginners or those looking to refresh their SQL basics.
Step 1: Creating the Tables
The schema consists of three tables: Students, Courses, and Enrollments.
Students table stores information about each student such as their ID, name, department, date of birth, email, and later a phone number.
Courses table keeps details about different courses including their ID, name, and credits.
Enrollments bridges students and courses, showing which student is enrolled in which course and the grade they received.
Here is the SQL to create these tables with appropriate primary keys and foreign key relationships:
sql
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);
Step 2: Inserting Sample Data
Next, we add some sample student records. Notice how the date format is handled using TO_DATE with the date format YYYY-MM-DD. Also, emails are kept unique as per table constraint:
sql
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Aruna', 'Electrical Engineering', TO_DATE('2005-11-22', 'YYYY-MM-DD'), 'aruna.ee@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Navya', 'Mechanical Engineering', TO_DATE('2008-07-09', 'YYYY-MM-DD'), 'navya.mech@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Preetha', 'Computer Science', TO_DATE('2007-03-15', 'YYYY-MM-DD'), 'preetha.cs@example.com');
Step 3: Altering Tables to Add New Features
We then extend the Students table to include a phone number column:
sql
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);
And add a constraint on the Courses table to ensure the credits are between 1 and 5:
sql
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);
This ensures data integrity by restricting invalid credit values.
Step 4: Adding Courses
Courses are inserted similarly, specifying their IDs, names, and credits. The credits must comply with our check constraint:
sql
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'DBMS', 3);
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'OS', 4);
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'Data Structures', 5);
Step 5: Committing the Changes
Since Oracle requires explicit commits to make the transactions permanent, we run:
sql
COMMIT;
Step 6: Querying the Data
Finally, we run some queries to retrieve useful information:
Display student names in uppercase and the length of their emails:
sql
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;
List all available courses along with their credits:
sql
SELECT CourseID, CourseName, Credits
FROM Courses;
Show all student details including the newly added phone number:
sql
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;
Conclusion
This simple example covers core SQL concepts like table creation, constraints, relationships, data insertion, date formatting, table alteration, and querying in Oracle SQL. It’s a solid base for building more complex educational management systems or practicing SQL skills.
Thank you @santhoshnc sir for guiding me.
This content originally appeared on DEV Community and was authored by SASHMITHA G 24CB054
SASHMITHA G 24CB054 | Sciencx (2025-08-21T14:46:41+00:00) COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM. Retrieved from https://www.scien.cx/2025/08/21/college-student-and-course-management-system/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.