CREATE TABLE Faculty (
    FacultyID NUMBER PRIMARY KEY,
    FacultyName VARCHAR2(50) NOT NULL,
    Dept VARCHAR2(30),
    Email VARCHAR2(50) UNIQUE
);
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-14', 'arun.k@college.edu');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', DATE '2002-08-20', 'priya.s@college.edu');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Verma', 'MECH', DATE '2001-12-10', 'rahul.v@college.edu');
ALTER TABLE Students
ADD PhoneNo NUMBER(10);
ALTER TABLE Courses
MODIFY (Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5));
SELECT Name AS StudentName,
       UPPER(Name) AS UpperCaseName,
       LENGTH(Email) AS EmailLength
FROM Students;
SELECT AVG(Credits) AS AvgCredits,
       (SELECT COUNT(*) FROM Students) AS TotalStudents
FROM Courses;
SELECT s.Name AS StudentName,
       c.CourseName,
       e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;
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;
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 Course
 

 
    
Top comments (0)