DBMS

Trigger and Stored Procedure  Tables Setup CREATE TABLE Department (     D_id INT PRIMARY KEY,     D_name VARCHAR(50) ); CREATE TABLE Student (     S_id INT PRIMARY KEY,     S_name VARCHAR(50),     D_id INT,     FOREIGN KEY (D_id) REFERENCES Department(D_id) ); CREATE TABLE Subject (     Sub_id INT PRIMARY KEY,     Sub_name VARCHAR(50) ); CREATE TABLE Marks (     S_id INT,     Sub_id INT,     Marks INT,     FOREIGN KEY (S_id) REFERENCES Student(S_id),     FOREIGN KEY (Sub_id) REFERENCES Subject(Sub_id) ); Stored Procedure Example 👉 Example Task: Find average marks of a student. DELIMITER $$ CREATE PROCEDURE Get_AvgMarks (IN student_id INT) BEGIN     SELECT S.S_name, AVG(M.Marks) AS Average_Marks     FROM Marks M     JOIN Student S ON M.S_id = S.S_id     WHERE M.S_id = student_id     GROUP BY S.S_name; EN...

Consider the following schema Part A CREATE TABLE Accounts ( AccNo INT PRIMARY KEY, Name VARCHAR ( 50 ), Balance DECIMAL ( 10 , 2 ) ); INSERT INTO Accounts VALUES ( 101 , 'Raj' , 10000 ), ( 102 , 'Meena' , 15000 ( 103 , 'Karan' , 20000 ); Q1. Start a transaction where: Raj transfers 2000 to Meena. After the transfer, commit the transaction. Show the balances of all accounts. START TRANSACTION; UPDATE Accounts SET Balance = Balance - 2000 WHERE AccNo = 101; -- Raj UPDATE Accounts SET Balance = Balance + 2000 WHERE AccNo = 102; -- Meena COMMIT; SELECT * FROM Accounts; Q2. Start a transaction where: Raj transfers 5000 to Karan. Before committing, you realize Raj doesn’t have sufficient balance and rollback the transaction. Show the balances of all accounts after rollback. START TRANSACTION; UPDATE Accounts SET Balance = Balance - 5000 WHERE AccNo = 101; -- Raj UPDATE Accounts SET Balance = Balance + 5000 WHERE AccNo = 103; -- Karan ROLLBACK;...