COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM.

In this blog we are Building a Simple Student-Course Database in SQL (with Oracle)

Step 1: Create the Students Table

We start by creating a Students table with fields for student ID, name, department, date of birth, and email.
We also make StudentID the primary key and Email unique.

CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

Step 2: Create the Courses Table

The Courses table stores course details.
We also add a check constraint to ensure that course credits are always between 1 and 5.

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

Step 3: Create the Enrollments Table

This table links students and courses. Each enrollment has an ID, references a student, references a course, and stores a grade.

CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

Step 4: Insert Sample Data

Let’s add some students and courses. Notice how we use TO_DATE to store proper date values.

-- Insert students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Lily', 'Herbalism', TO_DATE('2005-07-22', 'YYYY-MM-DD'), 'lilybloom.hb@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');

-- Insert courses
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);

We also add a PhoneNo column to the Students table:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

Step 5: Run Some Queries

Now that our data is ready, let’s try some queries.

Display student names in uppercase with email length
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;
Display all courses
SELECT CourseID, CourseName, Credits
FROM Courses;
Display all students
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Conclusion

We just built a mini student-course enrollment database with Oracle SQL.
Along the way, we learned how to:

✅ Create tables with constraints
✅ Insert data with proper formats
✅ Alter tables to add new columns
✅ Run basic queries to fetch information