COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

In this blog we are going to learn about the college student and the course management system of database management system

Getting Started with SQL: Creating a Simple Student Course Enrollment System

Create relational tables for students, courses, and enrollments
Add constraints for data validation
Insert sample records
Perform basic SQL queries

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

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

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

Here:

Defining primary keys for each table to uniquely identify records.
Using foreign keys in Enrollments to connect students with their courses.
Enforcing a UNIQUE constraint on student email addresses to prevent duplicates.

sql
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Aishu', 'MI', TO_DATE('11/20/2006', 'MM-DD-YYYY'), 'aishu.cb@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Sam', 'ME', TO_DATE('3/19/2007', 'MM-DD-YYYY'), 'sam.cb@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Sivanya', 'EEE', TO_DATE('11/22/2007', 'MM-DD-YYYY'), 'sivanya.cb@example.com');

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

sql
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

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

sql
COMMIT;

sql
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;

sql
SELECT CourseID, CourseName, Credits
FROM Courses;

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

This simple SQL-based system is a great starting point for understanding database design. With just a few tables and queries, you can build a scalable foundation for student-course management.