DEV Community

Cover image for College Student & Course Management System
PRIAN S S 24CB042
PRIAN S S 24CB042

Posted on

College Student & Course Management System

Introduction

Databases play a vital role in organizing and managing information in today’s digital world. SQL (Structured Query Language) is the standard language used to interact with relational databases. With SQL, we can create tables, insert and update records, enforce rules, and extract meaningful insights from data.

In this blog, we explore how SQL is applied using Students and Courses tables. We will learn how to:

Create and modify tables

Insert and manage records

Use constraints to maintain data integrity

Transform data with built-in functions

Summarize data using GROUP BY

Filter groups using the HAVING clause

By the end, you’ll understand not only the SQL commands but also the theory behind them — giving you both practical skills and conceptual clarity.

Working with Students and Courses Tables in Oracle SQL

In this post, let’s walk through some common SQL operations using two tables: Students and Courses. We’ll cover how to insert values, alter tables, add constraints, and run useful queries to analyze data.

1.Creating the Students Table

We start by creating a table to store student details. Each student has an ID, name, department, date of birth, and email.
For example, our Students table keeps details about learners such as their ID, name, department, date of birth, and email.
_CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE

);_

2. Inserting Sample Student Records

Next, we’ll insert a few students, each belonging to a different department.

`INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'Computer Science', TO_DATE('2005-03-15', 'YYYY-MM-DD'), 'arun.cs@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Meena R', 'Electrical Engineering', TO_DATE('2004-11-22', 'YYYY-MM-DD'), 'meena.ee@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Vignesh S', 'Mechanical Engineering', TO_DATE('2005-07-09', 'YYYY-MM-DD'), 'vignesh.mech@example.com');`

3.Altering the Students Table

Suppose we also want to store phone numbers. We can add a new column PhoneNo with 10 characters:

_ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

_
*4. Creating the Courses Table and Adding Constraints
*

Let’s say we also have a Courses table with credits. To make sure credit values are always between 1 and 5, we add a CHECK constraint.

_ALTER TABLE Courses
ADD CONSTRAINT chk_credits_limit
CHECK (Credits BETWEEN 1 AND 5);_

5. Displaying Student Names in Uppercase with Email Length

We can use SQL functions to transform and analyze data. For example:

_SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;_

6.Working with Course Credits

To see all the courses with their credit values, we run:

_SELECT CourseID, CourseName, Credits
FROM Courses;

_
And if we want to calculate useful insights like the average credits or the total number of students, we can use:

_SELECT
(SELECT AVG(Credits) FROM Courses) AS Avg_Credits,
(SELECT COUNT(StudentID) FROM Students) AS Total_Students
FROM dual;_

GROUP BY with HAVING in SQL

GROUP BY → Groups rows that have the same values in a column.

Aggregate functions like COUNT, SUM, AVG, MAX, MIN are applied to each group.

HAVING → Works like WHERE, but it filters groups instead of individual rows.
1. Students Count by Department

_SELECT Dept, COUNT(StudentID) AS Total_Students
FROM Students
GROUP BY Dept;_

Conclusion

Working with SQL is not just about writing queries — it is about understanding how data can be structured, managed, and analyzed effectively. Through our Students and Courses example, we saw how to:

Create and modify tables to match real-world needs

Insert meaningful records into the database

Enforce rules with constraints to protect data integrity

Transform and analyze data using built-in SQL functions

Summarize information with the GROUP BY clause

Apply conditions on aggregated results using the HAVING clause





THANKYOU @santhoshnc FOR GUIDING AND SUPPORTING US !!!!

Top comments (0)