COLLEGE STUDENT AND COURSES MANAGEMENT SYSTEMS

I am build a basic College student and Student-Course Enrollment system using Oracle SQL.
we are performing the below operations:

• Create tables
• Insert data
• Alter tables
• Add constraints
• Run simple queries

Step 1: Create Tables

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

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

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

Step 2: Insert Sample Data
sql
Copy code
-- Insert Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Divi', 'CB', TO_DATE('3/15/2007', 'MM-DD-YYYY'), '[e-mail]');

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

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Diksha', 'CB', TO_DATE('7/9/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);

-- Commit changes
COMMIT;

Step 3: Modify Table and Add Constraint
sql
Copy code
-- Add phone number to Students
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

-- Add check constraint to Courses
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

Step 4: Query the Data
sql
Copy code
-- Show student names in uppercase and email length
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;

-- Show all courses
SELECT CourseID, CourseName, Credits
FROM Courses;

Show all students
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Summary
In this post, we built a simple database using Oracle SQL:

Created 3 tables

Inserted sample data

Added a column and constraint

Ran a few basic queries




Thank you @santhoshnc for guiding and supporting me!!