COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM.

In this blog we are going to creating tables, inserting data, and running some useful queries with Oracle SQL.

Step 1: Create the Students Table
We’ll start by creating a table for students. This table holds student details along with a unique email ID.

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

StudentID is the primary key (unique for each student).

Email must be unique.

We’ll add phone numbers later.

Step 2: Create the Courses Table
Next, we build a table for courses:

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

Credits will later have a constraint (between 1 and 5).

Step 3: Create the Enrollments Table
Since students can enroll in multiple courses, we need a join table:

sql
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);
Here:
Enrollments connects StudentID ↔ CourseID.

We can also store the student’s grade.

Step 4: Insert Sample Data
Let’s add a few student records:

sql
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'DIVYA','AIDS', TO_DATE('11/22/2007', 'MM/DD/YYYY'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'DIKSHA', 'IT', TO_DATE('7/9/2008', 'MM/DD/YYYY'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'HARINI', 'Computer Science', TO_DATE('3/15/2007', 'MM/DD/YYYY'), '[e-mail]');

Step 5: Altering the Table (Adding Columns + Constraints)
Suppose we forgot to add phone numbers. Let’s fix that:

sql
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);
Also, ensure course credits are between 1 and 5:

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

Step 6: Insert Courses
sql
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'MACHINE LEARNING', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'OPERATING SYSTEMS', 4);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'Data Structures', 5);

Step 7: Committing Data
Always save your changes:

sql
COMMIT;

Step 8: Querying the Database
Now, let’s run some useful queries:

Show student names in uppercase & length of their email address:

sql
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;
Check available courses:

sql
SELECT CourseID, CourseName, Credits
FROM Courses;
View all student details:

sql
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

we have created a database which includes the insertion of data,altering
and showing the output.

THANK YOU SIR @santhoshnc for supporting and guiding me.