DEV Community

Cover image for MASTERING SQL ON ORACLE LIVE SQL ( COLLEGE STUDENT AND COURSE MANGEMENT SYSTEM )
Rokesh . S
Rokesh . S

Posted on

MASTERING SQL ON ORACLE LIVE SQL ( COLLEGE STUDENT AND COURSE MANGEMENT SYSTEM )

#ai #productivity #webdev

🚀 Mastering SQL on Oracle Live SQL: A Step-by-Step Guide

Working with SQL is a fundamental skill for developers and data professionals. Recently, I explored Oracle Live SQL, a web-based platform for writing and executing SQL scripts without installing any local database. In this post, I’ll walk you through the steps I took — from creating tables to writing queries and procedures.

✅ 1. Creating Tables with Constraints

First, I created multiple tables:

  • STUDENTS
  • COURSES
  • ENROLLMENTS

Here’s an example of creating the Enrollments table with foreign keys referencing Students and Courses:

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

This ensures referential integrity between students, courses, and enrollments.

✅ 2. Inserting Data

Next, I inserted sample data into the Students table:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-05-10','YYYY-MM-DD'), 'alice@univ.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob Smith', 'Mechanical', TO_DATE('2001-11-15','YYYY-MM-DD'), 'bob@univ.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Clara Adams', 'Physics', TO_DATE('2003-02-20','YYYY-MM-DD'), 'clara@univ.edu');
Enter fullscreen mode Exit fullscreen mode

The TO_DATE function ensures proper date formatting for Oracle DB.

✅ 3. Altering Table Structure

What if you need to add a new column later? You can use ALTER TABLE:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);
Enter fullscreen mode Exit fullscreen mode

This adds a phone number column to the existing table.

✅ 4. Aggregating Data with GROUP BY and HAVING

To get departments with more than two students, I used:

SELECT Dept, COUNT(*) AS Student_Count
FROM Students
GROUP BY Dept
HAVING COUNT(*) > 2;
Enter fullscreen mode Exit fullscreen mode

This query filters out departments having fewer than three students.

✅ 5. Creating a Stored Procedure

Finally, I created a stored procedure to update student grades:

CREATE OR REPLACE PROCEDURE UpdateGrade (
    p_StudentID IN NUMBER,
    p_CourseID IN NUMBER,
    p_NewGrade IN CHAR
) AS
BEGIN
    UPDATE Enrollments
    SET Grade = p_NewGrade
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;

    COMMIT;
END;
Enter fullscreen mode Exit fullscreen mode

This makes updating grades easy and reusable.

🔑 Key Learnings:

✔ Use foreign keys for data integrity.
✔ Use ALTER TABLE for schema changes without losing data.
✔ GROUP BY and HAVING are powerful for aggregations.
✔ Stored procedures automate repetitive tasks.

💬 Have you tried Oracle Live SQL or created similar setups? Share your experience in the comments!





Top comments (0)