COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM.

In this blog we are going To college student and course management system database management system.

In this blog, we’ll learn how to create a simple Student-Course Database in Oracle SQL. This example is perfect for beginners who want hands-on practice with SQL commands, constraints, and queries.

Step 1: Creating the Students Table

We start by creating the Students table. It will store student details such as ID, Name, Department, Date of Birth, and Email.

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

Explanation:

PRIMARY KEY → Ensures each student has a unique ID.

NOT NULL → The Name cannot be empty.

UNIQUE → The Email must be different for each student.

Step 2: Creating the Courses Table

Next, we create a Courses table to store available courses.

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

Explanation:

CourseID uniquely identifies each course.

Credits will later have a check constraint to ensure valid values.

Step 3: Creating the Enrollments Table

To link students and courses, we create an Enrollments table.

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

Explanation:

StudentID references the Students table.

CourseID references the Courses table.

This creates relationships between tables (foreign keys).

Step 4: Inserting Sample Data

Now let’s insert some students and courses.

Insert Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Sakthi', 'CB', TO_DATE('11/22/2006', 'MM-DD-YYYY'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Varsha', 'IT', TO_DATE('03/15/2007', 'MM-DD-YYYY'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Divi', 'CS', TO_DATE('11/22/2006', 'MM-DD-YYYY'), '[e-mail]');

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

Step 5: Altering Tables

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

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

Step 6: Commit Changes

In Oracle, always commit after inserting or updating records.

COMMIT;

Step 7: Running Queries

Now let’s fetch some useful results from our database.

Display student names in uppercase with email length
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;

Display all courses with credits
SELECT CourseID, CourseName, Credits
FROM Courses;

Display all student details
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Conclusion

In this tutorial, we:

Created Students, Courses, and Enrollments tables.

Learned about primary keys, foreign keys, unique constraints, and check constraints.

Inserted sample data and queried results.

Thank You @santhoshnc sir for guiding me.