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)