COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM.

In this blog we are going to Building a Simple Student-Course Management System with Oracle SQL
Managing student enrollments and course information is a common task for educational institutions and developers building academic applications. We'll create tables for students, courses, and enrollments, insert sample data, and explore some interesting queries.

Step 1: Creating the Tables
We start by creating three tables—Students, Courses, and Enrollments. Each table is designed with appropriate data types, constraints, and relationships.

sql
-- 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)
);
The Students table contains the student's name, department, date of birth, email (unique), and an ID as a primary key.

The Courses table has course details including credits.

The Enrollments table records which student is enrolled in which course, along with their grades. It establishes foreign key relationships to ensure referential integrity.

Step 2: Adding Data Integrity Features
To improve data quality, let’s add a phone number column to Students and a check constraint on the Credits column in Courses:

sql
-- Add PhoneNo column to Students
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

-- Add check constraint for Credits in Courses (must be 1 to 5)
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);
Step 3: Inserting Sample Data
Insert some sample student and course data to work with:

sql
-- Insert students with different departments
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (2, 'TOM HOLLAND', 'ECE', TO_DATE('11/22/2005', 'MM/DD/YYYY'), '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (3, 'ANDREW', 'IT', TO_DATE('7/9/2008', 'MM/DD/YYYY'), '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'EMMA', 'CSBS', TO_DATE('3/15/2007', 'MM/DD/YYYY'), '[e-mail]');

-- Insert courses with credits
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (101, 'DE', 3);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (102, 'CE', 4);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (103, 'Data science', 5);

-- Commit changes
COMMIT;
Step 4: Querying Data with Useful Functions
Some simple queries to analyze the data:

sql
-- Display student names in uppercase and length of emails
SELECT UPPER(Name) AS Student_Name, LENGTH(Email) AS Email_Length FROM Students;

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

-- Display all student details, including the new PhoneNo column
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo FROM Students;

Conclusion
This simple database management system covers the core concepts like table creation with constraints, foreign keys for relationships, data insertion, and basic querying in Oracle SQL.

Thank you @santhoshnc sir for guiding me.