SQL Queries : College Student & Course Management System

Introduction

In today’s digital era, databases play a crucial role in managing and organizing information efficiently. SQL (Structured Query Language) is the backbone of relational databases, enabling us to create, modify, and retrieve data in a structured way.

This blog demonstrates a College Student & Course Management System using Oracle LiveSQL. Through a set of 10 SQL tasks — including DDL, DML, ALTER, constraints, functions, aggregate operations, JOINs, GROUP BY, views, and stored procedures — we will explore how to design and query relational databases effectively.Each query is explained with its purpose, making it easy for beginners to learn and practice SQL.

Use Case

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

Courses table
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Enrollments table (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

1. DDL – Create Faculty Table

CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

DDL (Data Definition Language) commands are used to define and manage database objects such as tables, views, and indexes.

Creates a new table Faculty with FacultyID as the primary key, FacultyName as NOT NULL, and Email as unique.

2. DML – Insert 3 Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Amit Sharma', 'Computer Science', TO_DATE('2002-05-12','YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Verma', 'Electronics', TO_DATE('2001-08-23','YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Mehta', 'Mechanical', TO_DATE('2000-11-05','YYYY-MM-DD'), '[e-mail]');

DML (Data Manipulation Language) is used to insert, update, delete, and query records in a database.

Inserts 3 student records into the Students table with different departments.

3. ALTER TABLE – Add Phone Number

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

ALTER is used to modify an existing table structure, such as adding or deleting columns, or changing constraints.

Adds a new column PhoneNo to the Students table to store 10-digit phone numbers.

4. Defining Constraints – Limit Credits (1 to 5)

ALTER TABLE Courses
MODIFY (Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5));

Constraints ensure data integrity by restricting the values allowed in a column.

Ensures the Credits column in the Courses table only accepts values between 1 and 5.

5. SELECT with Functions – Uppercase & Email Length

SELECT Name,
UPPER(Name) AS UpperCase_Name,
LENGTH(Email) AS Email_Length
FROM Students;

SQL Functions help in manipulating and formatting data during queries.

Displays each student’s name, their name in uppercase, and the length of their email address.

6. Aggregate Functions – Avg Credits & Total Students

SELECT AVG(Credits) AS Avg_Credits
FROM Courses;

SELECT COUNT(*) AS Total_Students
FROM Students;

Aggregate functions perform calculations on multiple rows and return a single value.

Finds the average number of credits across all courses and the total number of students.

7. JOIN Operation – Student with Courses

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

JOIN is used to combine rows from multiple tables based on related columns.

Shows the list of students along with the courses they are enrolled in and their grades.

8. GROUP BY with HAVING – Students per Department

SELECT Dept, COUNT() AS Student_Count FROM Students
GROUP BY Dept HAVING COUNT() > 1;

GROUP BY groups rows with the same values, and HAVING filters groups based on conditions.

Groups students by department and displays only departments having more than 1 student.

9. View – StudentCoursesView

CREATE OR REPLACE 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;

A View is a virtual table created from a SQL query that simplifies complex queries.

Creates a view that shows student names, their enrolled courses, and grades.

10. Stored Procedure – UpdateGrade

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;

A Stored Procedure is a set of SQL statements stored in the database that can be executed when needed.

This procedure updates a student’s grade in the Enrollments table when given StudentID, CourseID, and the new grade.

Conclusion

This project shows how SQL commands like DDL, DML, ALTER, constraints, functions, joins, group by, views, and stored procedures work together to build and manage a College Student & Course Management System. By practicing these queries in Oracle LiveSQL, beginners can easily understand database design, data handling, and querying for real-world applications.