COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM

1.Create Faculty Table (DDL)

This command creates a table named Faculty to store faculty details. It contains four attributes: FacultyID, FacultyName, Dept, and Email. FacultyID is defined as the primary key to uniquely identify each faculty, and Email is set as unique to prevent duplicate entries.
Command:
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

2.Insert Data into Students Table (DML)

This command inserts sample student records into the Students table. Each record includes StudentID, Name, Department, Date of Birth, and Email. These entries are used to populate the database with initial data for testing queries.
Command:
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'Ravi Kumar', 'CSE', DATE '2002-05-14', '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (2, 'Anita Sharma', 'ECE', DATE '2001-11-22', '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (3, 'Suresh Mehta', 'IT', DATE '2003-01-09', '[e-mail]');

3. Alter Table – Add Column

This command modifies the existing Students table by adding a new column named PhoneNo. This column is used to store the phone number of students and allows a maximum of 10 digits.
Command:
ALTER TABLE Students ADD PhoneNo NUMBER(10);

4. Add Constraints to Courses Table

This command adds a CHECK constraint to the Courses table. The constraint ensures that the Credits value of any course must be between 1 and 5. This prevents invalid data entry and maintains data integrity.
Command:
ALTER TABLE Courses ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

5. Select with Functions

This query uses SQL functions to manipulate and analyze data. It converts student names into uppercase and calculates the length of each student’s email ID. Such functions help in formatting and analyzing textual data.
Command:
SELECT UPPER(Name) AS StudentName_Upper, LENGTH(Email) AS Email_Length FROM Students;

6. Aggregate Functions

These queries use aggregate functions to summarize data. The first query calculates the average credits from the Courses table, while the second counts the total number of unique students enrolled in courses using COUNT(DISTINCT ...).
Command:
SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(DISTINCT StudentID) AS TotalStudents FROM Enrollments;

7. Join Operation

This query demonstrates the use of JOINs across three tables: Students, Enrollments, and Courses. It retrieves a list of students along with the courses they are enrolled in and their corresponding grades. This helps in combining data from multiple tables.
Command:
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;

8. Group By with Having

This query uses GROUP BY to count the number of students in each department. The HAVING clause is applied to filter results, displaying only those departments that have more than 2 students. It helps in generating summarized reports with conditions.
Command:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

9. Create View

This command creates a database view named StudentCoursesView. A view is a virtual table that simplifies complex queries. This view displays each student along with the courses they are enrolled in and their grades, making future queries easier.
Command:
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;

10. Stored Procedure – UpdateGrade

This command creates a stored procedure called UpdateGrade. A stored procedure is a reusable set of SQL statements. This one updates a student’s grade in the Enrollments table for a specific course. It improves database efficiency and reusability.
Command:
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;