DEV Community

ABITHA N 24CB001
ABITHA N 24CB001

Posted on

Student-Course Management Database

`In this blog post,I created a simple Student-Course Management Database, step by step, using Oracle Live SQL.What This Project Includes
 *Creating STUDENTS, COURSES, and ENROLLMENTS tables
*Inserting data
Adding constraints (e.g., PRIMARY KEY, CHECK)
*Querying data with SELECT
*Altering tables to add new columns

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

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

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


This gives us three connected tables:
STUDENTS with student details
COURSES listing the available courses
ENROLLMENTS to track who enrolled in what course and their grades

✍️ Step 2: Inserting Student Data
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'HARINI', 'Computer Science', TO_DATE('3/15/2007', 'MM/DD/YYYY'), 'harini16cs@gmail.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'DIVYA', 'AIDS', TO_DATE('11/22/2007', 'MM/DD/YYYY'), 'divya12ad@gmail.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'DIKSHA', 'IT', TO_DATE('7/9/2008', 'MM/DD/YYYY'), 'diksha11it@gmail.com');

🧠 Step 3: Modifying Tables – Add a Phone Number Column
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);


This command added a new column to store student phone numbers.

🛡️ Step 4: Adding Constraints – Validating Credits
ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

This ensures that no course is added with 0 or more than 5 credits — a simple yet important data validation rule.

📘 Step 5: Inserting Courses
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'MACHINE LEARNING', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'OPERATING SYSTEMS', 4);

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

🔍 Step 6: Querying Data

Let’s get all student information including the new PhoneNo column:

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

Output Example:

STUDENTID   NAME    DEPT    DOB EMAIL   PHONENO
1   HARINI  Computer Science    3/15/2007   harini16cs@gmail.com
    (null)
2   DIVYA   AIDS    11/22/2007  divya12ad@gmail.com
    (null)
3   DIKSHA  IT  7/9/2008    diksha11it@gmail.com
    (null)

Note: I haven’t added phone numbers yet, so they appear as null.`
Enter fullscreen mode Exit fullscreen mode





Top comments (0)