Scribd
Upload
6 views2 pages

Student Result Triggers in SQL

The document outlines a database assignment involving the creation of a StudentResultsDB with tables for Students, Subjects, and Results. It includes triggers for validating marks before insertion and calculating pass/fail status after results are updated. Additionally, sample data is inserted into the tables, demonstrating the functionality of the triggers.

Uploaded by

Akashdeep Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Student Result Triggers in SQL

The document outlines a database assignment involving the creation of a StudentResultsDB with tables for Students, Subjects, and Results. It includes triggers for validating marks before insertion and calculating pass/fail status after results are updated. Additionally, sample data is inserted into the tables, demonstrating the functionality of the triggers.

Uploaded by

Akashdeep Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

‭LAB Assignment-4‬

‭Triggers using High level language extension such as Display‬


‭student result and invalid condition.‬

‭CODE:‬
‭ REATE DATABASE StudentResultsDB;‬
C
‭USE StudentResultsDB;‬

‭CREATE TABLE Students (‬


‭StudentID INT PRIMARY KEY,‬
‭FirstName VARCHAR(50),‬
‭LastName VARCHAR(50),‬
‭DateOfBirth DATE‬
‭);‬

‭CREATE TABLE Subjects (‬


‭SubjectID INT PRIMARY KEY,‬
‭SubjectName VARCHAR(100),‬
‭MaximumMarks INT‬
‭);‬

‭CREATE TABLE Results (‬


‭ResultID INT PRIMARY KEY,‬
‭StudentID INT,‬
‭SubjectID INT,‬
‭MarksObtained INT,‬
‭FOREIGN KEY (StudentID) REFERENCES Students(StudentID),‬
‭FOREIGN KEY (SubjectID) REFERENCES Subjects(SubjectID)‬
‭);‬

‭DELIMITER $$‬

‭ REATE TRIGGER Before_Result_Insert‬


C
‭BEFORE INSERT ON Results‬
‭FOR EACH ROW‬
‭BEGIN‬
‭DECLARE max_marks INT;‬

‭ ELECT MaximumMarks INTO max_marks‬


S
‭FROM Subjects‬
‭WHERE SubjectID = [Link];‬

‭IF [Link] < 0 OR [Link] > max_marks THEN‬


‭SIGNAL SQLSTATE '45000'‬
‭SET MESSAGE_TEXT = 'Invalid Marks: Marks must be between 0 and the maximum marks for the‬
‭subject.';‬
‭END IF;‬
‭END$$‬

‭ ELIMITER ;‬
D
‭DELIMITER $$‬

‭Harshdeep Singh (23124036)‬


‭ REATE TRIGGER After_Result_Change‬
C
‭AFTER INSERT ON Results‬
‭FOR EACH ROW‬
‭BEGIN‬
‭DECLARE total_marks INT;‬
‭DECLARE max_marks INT;‬
‭DECLARE pass_fail VARCHAR(10);‬

‭ ELECT SUM(MarksObtained) INTO total_marks‬


S
‭FROM Results‬
‭WHERE StudentID = [Link];‬

‭ ELECT SUM(MaximumMarks) INTO max_marks‬


S
‭FROM Subjects s‬
‭INNER JOIN Results r ON [Link] = [Link]‬
‭WHERE [Link] = [Link];‬

‭IF (total_marks / max_marks) * 100 >= 40 THEN‬


‭SET pass_fail = 'Pass';‬
‭ELSE‬
‭SET pass_fail = 'Fail';‬
‭END IF;‬

‭SELECT CONCAT('Student ', [Link], ' has Total Marks: ', total_marks, ' and has ', pass_fail) AS‬
‭ esult_Status;‬
R
‭END$$‬

‭DELIMITER ;‬

I‭NSERT INTO Students (StudentID, FirstName, LastName, DateOfBirth)‬


‭VALUES (1, 'Rohit', 'Brar', '2005-05-01'), (2, 'Jassi', 'Sidhu', '2004-10-12');‬

I‭NSERT INTO Subjects (SubjectID, SubjectName, MaximumMarks)‬


‭VALUES (1, 'Math', 100), (2, 'Physics', 100), (3, 'Chemistry', 100);‬

I‭NSERT INTO Results (ResultID, StudentID, SubjectID, MarksObtained)‬


‭VALUES (1, 1, 1, 80), (2, 1, 2, 91), (3, 1, 3, 76), (4, 2, 1, 82), (5, 2, 2, 88), (6, 2, 3, 71);‬

I‭NSERT INTO Results (ResultID, StudentID, SubjectID, MarksObtained)‬


‭VALUES (4, 2, 1, 105);‬

‭Harshdeep Singh (23124036)‬

You might also like