COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM.

Creating a Simple Student-Course Database in Oracle SQL

In this post,we are going to know about how to create a basic Student-Course Enrollment System using Oracle SQL. This includes creating tables, inserting sample data, applying constraints, and running a few useful queries.

Step 1: Creating the Tables

We’ll start by creating three core tables: Students, Courses, and Enrollments.

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

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

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

Step 2: Inserting Sample Students

Here, we insert 3 sample students from different departments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'raj Kumar', 'csbs', TO_DATE('2005-03-15', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'harini R', 'mechanical Engineering', TO_DATE('2004-11-22', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'laksita S', 'civil Engineering', TO_DATE('2005-07-09', 'YYYY-MM-DD'), '[e-mail]');

Step 3: Modifying the Table

We now add a PhoneNo column to the Students table:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

Step 4: Adding Constraints

Ensure that the Credits for courses are between 1 and 5:

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

Step 5: Inserting Sample Courses

Let's add a few courses to our database:

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'cyber security', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'machine learning', 4);

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

COMMIT;

Step 6: Running Sample Queries

1. Display Student Names in Uppercase and Email Length:
   SELECT
   UPPER(Name) AS Student_Name,
   LENGTH(Email) AS Email_Length
   FROM Students;

2. Display All Courses with Credits:
   SELECT CourseID, CourseName, Credits
   FROM Courses;

3. Display All Student Details:
   SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
   FROM Students;

THANK YOU @santhoshnc sir. For guiding me .