STUDENT MANAGENT SYSTEM

🚀 My SQL Learning Journey with Oracle Live SQL

Recently, I started practicing DBMS & SQL concepts using Oracle Live SQL. I wanted to share my progress so far — from creating tables to writing queries, altering schema, and even building views and stored procedures.

🏗️ Step 1: Creating a Table

The first thing I did was create a STUDENTS table with constraints like PRIMARY KEY, NOT NULL, and UNIQUE:

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

✅ Output: Table STUDENTS created successfully!

✏️ Step 2: Altering a Table

Later, I realized I also needed to store students’ phone numbers. Using ALTER TABLE, I added a new column:

ALTER TABLE Students
ADD PhoneNo VARCHAR(10);

✅ Output: Table STUDENTS altered.

👀 Step 3: Creating a View

To simplify data access, I created a view that joins Students, Enrollments, and Courses:

CREATE VIEW StudentCoursesView AS
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;

Now I can query StudentCoursesView directly without repeating the joins.

⚡ Step 4: Writing a Stored Procedure

I also experimented with stored procedures. For example, updating a student’s grade in a course:

CREATE PROCEDURE UpdateGrade (
    p_StudentID INT,
    p_CourseID INT,
    p_NewGrade VARCHAR2
)
AS
BEGIN
    UPDATE Enrollments
    SET Grade = p_NewGrade
    WHERE StudentID = p_StudentID AND CourseID = p_CourseID;
END;

This lets me update grades with a single procedure call.

📊 Step 5: Running Aggregate Queries

Finally, I tried aggregate queries like average credits or total students:

SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(*) AS TotalStudents FROM Students;

🎯 Key Learnings

DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE
DCL: GRANT, REVOKE
TCL: COMMIT, ROLLBACK, SAVEPOINT

Practicing these in Oracle Live SQL helped me understand not just syntax but real-world database management.

💡 Next, I plan to practice joins, subqueries, transactions, and indexing.

👉 Do you also use Oracle Live SQL or another tool for learning databases? Share your thoughts below!

DEV Community