`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.`
For further actions, you may consider blocking this person and/or reporting abuse
Top comments (0)