1. Write a procedure named "getUnenrolledStudents" that takes in no parameters. The procedure should retrieve all the students who are not enrolled in any course and display their information.
2. Write a procedure named "updateStudentAge" that takes in parameters for "studentID" and "newAge." The procedure should update the age of the student with the given ID in the "Students" table.
3. Create a procedure named "deleteStudent" that takes in a parameter for "studentID." The procedure should delete the student record with the given ID from the "Students" table and all his enrollment information.
4. Write a procedure named "getCourseStudents" that takes in a parameter for "courseID." The procedure should retrieve all the students enrolled in the course with the specified courseID and display their names and majors.
5. Create a procedure named "getStudentInfo" that takes in a parameter for "studentID." The procedure should retrieve the student's information, including their name, age, rollNo, major, and the courses they are enrolled in, with their respective departments, and display them.
6. Write a procedure named "getMostPopularCourse" that takes in an optional parameter for "department." The procedure should retrieve the course with the most enrollments in the specified department and display the course's information, including the course name, instructor, and the number of students enrolled in the course, and display them. If no department is specified, ‘CS’ should be taken as default value.
7. Create a procedure named "calculateCourseGPA" that takes an input parameter for "courseID" and an output parameter for "averageGPA." The procedure should calculate the average GPA of all the students enrolled in the specified course and store the result in the output parameter.
8. ALTER the above procedure and display the average GPA of all the students in the "Students" table.
9. Write a procedure named "getCourseEnrollmentCount" that takes in a parameter for "courseID." The procedure should retrieve the total number of students enrolled in the course with the specified courseID and display the count.
10. Create a procedure named "getCourseWithoutGrades" that retrieves all the courses that have no grades recorded in the "Grades" table and displays their details.
Solution:
CREATE TABLE Studentss (
studentID INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50),
age INT,
rollNo VARCHAR(50),
major VARCHAR(50)
);
-- Create the Courses table
CREATE TABLE Courses (
courseID INT IDENTITY(1,1) PRIMARY KEY ,
courseName VARCHAR(50),
instructor VARCHAR(50),
department VARCHAR(50),
creditHour INT
);
-- Create the Enrollments table
CREATE TABLE Enrollments (
enrollmentID INT IDENTITY(1,1) PRIMARY KEY ,
studentID INT,
courseID INT,
FOREIGN KEY (studentID) REFERENCES Studentss(studentID),
FOREIGN KEY (courseID) REFERENCES Courses(courseID)
);
-- Create the Grades table
CREATE TABLE Grades (
gradeID INT IDENTITY(1,1) PRIMARY KEY ,
enrollmentID INT,
grade DECIMAL(4,2),
FOREIGN KEY (enrollmentID) REFERENCES Enrollments(enrollmentID)
);
-- Inserting records into the Students table
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Giselle Collette', 20,
'l201234', 'Computer Science');
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Emily Davis', 22, 'l212342',
'Data Science');
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Kaeya Alberich', 21,
'l203451', 'Mathematics');
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Florence Nightingale', 23,
'l203452', 'Data Science');
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Waver Velvet', 21, 'l224324',
'Data Science');
INSERT INTO Studentss (name, age, rollNo, major) VALUES ('Benedict Blue', 21, 'l214984',
'Computer Science');
-- Inserting records into the Courses table
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES
('Database Systems', 'Prof. Smith', 'CS', 4);
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES ('Web
Development', 'Prof. Jonathan', 'CS', 4);
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES ('Theory
of Automata', 'Prof. Williams', 'CS', 3);
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES
('Machine Learning', 'Prof. Williams', 'CS', 3);
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES
('Discrete Structures', 'Prof. Horace', 'CS', 3);
INSERT INTO Courses (courseName, instructor, department, creditHour) VALUES
('Numeric Computing', 'Prof. Sarah', 'MTH', 3);
-- Inserting records into the Enrollments table
INSERT INTO Enrollments (studentID, courseID) VALUES (1, 1);
INSERT INTO Enrollments (studentID, courseID) VALUES (2, 1);
INSERT INTO Enrollments (studentID, courseID) VALUES (2, 2);
INSERT INTO Enrollments (studentID, courseID) VALUES (3, 3);
INSERT INTO Enrollments (studentID, courseID) VALUES (5, 4);
INSERT INTO Enrollments (studentID, courseID) VALUES (5, 3);
INSERT INTO Enrollments (studentID, courseID) VALUES (5, 6);
INSERT INTO Enrollments (studentID, courseID) VALUES (6, 1);
-- Inserting records into the Grades table
INSERT INTO Grades (enrollmentID, grade) VALUES (1, 3.3);
INSERT INTO Grades (enrollmentID, grade) VALUES (2, 2.7);
INSERT INTO Grades (enrollmentID, grade) VALUES (3, 2.3);
INSERT INTO Grades (enrollmentID, grade) VALUES (4, 4);
INSERT INTO Grades (enrollmentID, grade) VALUES (5, 3.3);
INSERT INTO Grades (enrollmentID, grade) VALUES (6, 3.7);
INSERT INTO Grades (enrollmentID, grade) VALUES (7, 3);
INSERT INTO Grades (enrollmentID, grade) VALUES (8, 3.7);
--1--
CREATE PROCEDURE getUnenrolledStudents
AS
BEGIN
SELECT studentID, name FROM Studentss
WHERE studentID NOT IN (SELECT studentID FROM Enrollments);
END;
EXEC getUnenrolledStudents;
--2--
CREATE PROCEDURE updateStudentAge
@studentID INT, @newAge INT
AS
BEGIN
UPDATE Studentss
SET age = @newAge
WHERE studentID = @studentID;
END;
EXEC updateStudentAge @studentID = 4, @newAge = 25;
--3--
CREATE PROCEDURE deleteStudent
@studentID INT
AS
BEGIN
DELETE FROM Grades
WHERE enrollmentID IN (SELECT enrollmentID FROM Enrollments WHERE studentID = @studentID);
DELETE FROM Enrollments
WHERE studentID = @studentID;
DELETE FROM Studentss
WHERE studentID = @studentID;
END;
EXEC deleteStudent @studentID = 6;
--4--
CREATE PROCEDURE getCourseStudents
@courseID INT
AS
BEGIN
SELECT Studentss.name, Studentss.major, Enrollments.courseID
FROM Studentss
JOIN Enrollments ON Studentss.studentID = Enrollments.studentID
WHERE Enrollments.courseID = @courseID;
END;
EXEC getCourseStudents @courseID = 4;
--5--
CREATE PROCEDURE getStudentInfo
@studentID INT
AS
BEGIN
SELECT Studentss.name, Studentss.age, Studentss.rollNo, Studentss.major, Courses.courseName, Courses.department
FROM Studentss
JOIN Enrollments ON Studentss.studentID = Enrollments.studentID
JOIN Courses ON Enrollments.courseID = Courses.courseID
WHERE Studentss.studentID = @studentID;
END;
EXEC getStudentInfo @studentID = 5;
--6--
CREATE PROCEDURE getMostPopularCourse
@department VARCHAR(50) = 'CS'
AS
BEGIN
SELECT TOP 1 Courses.courseName, Courses.instructor, COUNT(*) AS EnrollmentCount
FROM Courses
JOIN Enrollments ON Courses.courseID = Enrollments.courseID
WHERE Courses.department = @department
GROUP BY Courses.courseName, Courses.instructor
ORDER BY EnrollmentCount DESC;
END;
EXEC getMostPopularCourse @department = 'CS';
--7--
CREATE PROCEDURE calculateCourseGPA
@courseID INT,
@averageGPA FLOAT OUTPUT
AS
BEGIN
SELECT @averageGPA =
((SUM(Grades.grade * Courses.creditHour)) / SUM(Courses.creditHour))
FROM Grades
JOIN Enrollments ON Grades.enrollmentID = Enrollments.enrollmentID
JOIN Courses ON Enrollments.courseID = Courses.courseID
WHERE Enrollments.courseID = @courseID;
END;
DECLARE @avgGPA FLOAT;
EXEC calculateCourseGPA @courseID = 1, @averageGPA = @avgGPA OUTPUT;
SELECT @avgGPA AS AverageGPA;
--8--
ALTER PROCEDURE calculateCourseGPA
@averageGPA FLOAT OUTPUT
AS
BEGIN
SELECT @averageGPA = AVG(grade)
FROM Grades;
END;
DECLARE @avgGPA FLOAT;
EXEC calculateCourseGPA @averageGPA = @avgGPA OUTPUT;
SELECT @avgGPA AS AverageGPA;
--9--
CREATE PROCEDURE getCourseEnrollmentCount
@courseID INT
AS
BEGIN
SELECT COUNT(*) AS EnrollmentCount
FROM Enrollments
WHERE courseID = @courseID;
END;
EXEC getCourseEnrollmentCount @courseID = 1;
--10--
CREATE PROCEDURE getCourseWithoutGrades
AS
BEGIN
SELECT Courses.courseName, Courses.instructor, Courses.department
FROM Courses
LEFT JOIN Enrollments ON Courses.courseID = Enrollments.courseID
LEFT JOIN Grades ON Enrollments.enrollmentID = Grades.enrollmentID
WHERE Grades.enrollmentID IS NULL;
END;
EXEC getCourseWithoutGrades;
