DEV Community

DEEPAK KUMAR B 24CB007
DEEPAK KUMAR B 24CB007

Posted on

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
);
Enter fullscreen mode Exit fullscreen mode

โœ… 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);
Enter fullscreen mode Exit fullscreen mode

โœ… 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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

๐ŸŽฏ 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!





Top comments (0)