STUDENT MANAGEMENT SYSTEM

🚀 Creating SQL Queries in Oracle LiveSQL for a DBMS Assignment

In this blog, I’ll walk you through how I created and executed SQL queries for my DBMS Assignment using Oracle LiveSQL. This will cover everything from table creation to running queries and verifying results with screenshots.

🔑 Step 1: Setting up the Tables

The assignment required creating three core tables: Students, Courses, and Enrollments. I logged into Oracle LiveSQL and used the SQL Worksheet.

Here’s an example for the Enrollments table:

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

✅ After running the command, Oracle confirmed that the table was created successfully.

✍️ Step 2: Inserting Data into Students

Next, I inserted sample data into the Students table. This helps later when running queries like SELECT, JOIN, and GROUP BY.

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice', 'CSE', DATE '2003-05-12', 'alice@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob', 'ECE', DATE '2002-11-23', 'bob@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Charlie', 'MECH', DATE '2004-02-01', 'charlie@example.com');

Each INSERT executed successfully, and I verified it by checking the Script Output.

🛠 Step 3: Altering the Students Table

The assignment asked to add a PhoneNo column. I used:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

✅ The column was added successfully.

🔍 Step 4: Running Queries

Now for the fun part! I ran multiple queries to meet assignment requirements.

a) Select with Functions
SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength
FROM Students;

This returned student names in uppercase and the length of their email IDs.

b) Join Operation

To display students with their enrolled courses and grades:

SELECT s.Name AS StudentName, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

At this stage, since no enrollment data was added, the result was empty. (In practice, I would insert course and enrollment data first.)

📸 Step 5: Screenshots for Submission

Throughout the process, I took screenshots of:

Table creation