Module II

1. List different types of Triggers.

triggers are database objects that automatically execute when a specified event occurs on a
table.
Types of Triggers in MySQL
1. BEFORE INSERT Trigger
o Executes before a new record is inserted into a table.
o Used to validate or modify data before insertion.
2. AFTER INSERT Trigger
o Executes after a new record is inserted into a table.
o Commonly used for logging or updating another table.
3. BEFORE UPDATE Trigger
o Executes before an existing record is updated.
o Used to check or modify values before updating.
4. AFTER UPDATE Trigger
o Executes after an existing record is updated.
o Used for audit logs or updating related tables.
5. BEFORE DELETE Trigger
o Executes before a record is deleted from a table.
o Used to check conditions or save backup data.
6. AFTER DELETE Trigger
o Executes after a record is deleted from a table.

2. Write a SQL query to find departments where the maximum salary is greater than 80,000
SELECT department_id, MAX(salary) AS max_salary
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 80000;
3. Explain any 2 types of datatypes used in SQL.
In SQL, data types define the type of data that can be stored in a table column.
1. Numeric Data Type
 Used to store numbers such as integers or decimal values.
 Common numeric types: INT, FLOAT, DECIMAL.
Example:
CREATE TABLE Employee (
Emp_ID INT,
Salary DECIMAL(10,2)
);
 INT stores whole numbers.
 DECIMAL(10,2) stores numbers with two decimal places.
2. Character (String) Data Type
 Used to store text or characters such as names or addresses.
 Common types: CHAR, VARCHAR, TEXT.
Example:
CREATE TABLE Student (
Name VARCHAR(50),
City CHAR(20)
);
 VARCHAR(50) stores variable-length text up to 50 characters.
 CHAR(20) stores fixed-length characters.

4. Differentiate between DDL and DML.

.
DDL (Data Definition DML (Data Manipulation
Feature
Language) Language)

Used to define or modify the

Used to manipulate the
Definition structure of database objects like
data stored in the tables.
tables.

Purpose Creates or changes database Inserts, updates, deletes, or

 schema. retrieves data.

Examples of CREATE, ALTER, DROP, INSERT, UPDATE,

Commands TRUNCATE DELETE, SELECT

Changes the structure of the Changes the data inside the

Effect
database. tables.

Usually cannot be rolled back Can be rolled back using

Rollback
once executed. transactions.

. . .
5. Write an SQL query to find the average salary of employees in each department,
considering only departments where the average salary is greater than 50,000
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

Part B
Operator Meaning

= Equal to

!= or <> Not equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

LOGICAL OPERATORS
Operator Meaning

AND Both conditions must be true

OR At least one condition must be true

NOT Reverses the condition

[Link] MySQL, you can create triggers on the BORROW table to enforce the given
conditions.
1️⃣ Trigger: Do not issue book if not available
DELIMITER $$

CREATE TRIGGER check_book_availability

BEFORE INSERT ON BORROW
FOR EACH ROW
BEGIN
DECLARE status INT;

SELECT available INTO status

FROM BOOK
WHERE book_id = NEW.book_id;

IF status = 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Book is not available';
END IF;
END$$

DELIMITER ;
2️⃣ Trigger: On issue → mark book unavailable
DELIMITER $$

CREATE TRIGGER mark_unavailable

AFTER INSERT ON BORROW
FOR EACH ROW
BEGIN
UPDATE BOOK
SET available = 0
WHERE book_id = NEW.book_id;
END$$

DELIMITER ;

3️⃣ Trigger: On return → calculate fine (₹10 per day)

DELIMITER $$

CREATE TRIGGER calculate_fine

BEFORE UPDATE ON BORROW
FOR EACH ROW
BEGIN
IF NEW.return_date IS NOT NULL THEN
SET [Link] =
GREATEST(DATEDIFF(NEW.return_date, NEW.due_date),0) * 10;
END IF;
END$$

DELIMITER ;

4️⃣ Trigger: On return → mark book available

DELIMITER $$
CREATE TRIGGER mark_available
AFTER UPDATE ON BORROW
FOR EACH ROW
BEGIN
IF NEW.return_date IS NOT NULL THEN
UPDATE BOOK
SET available = 1
WHERE book_id = NEW.book_id;
END IF;
END$$

Illustrate aggregate functions with an example

Function Description

COUNT() Counts number of rows

SUM() Calculates total of a column

AVG() Finds the average value

MAX() Finds the highest value

MIN() Finds the lowest value

SELECT COUNT(*) FROM Employee;

SELECT SUM(Salary) FROM Employee;
SELECT AVG(Salary) FROM Employee;
SELECT MAX(Salary) FROM Employee;
SELECT MIN(Salary) FROM Employee;

Write a stored procedure to:

(Example Calculation)
 HRA = 20% of Basic
 DA = 10% of Basic
 PF = 5% of Basic
 Net Salary = Basic + HRA + DA – PF
 DELIMITER $$

CREATE PROCEDURE GeneratePayroll(IN empid INT)

 BEGIN

DECLARE basic_salary DECIMAL(10,2);

 DECLARE hra_amt DECIMAL(10,2);

 DECLARE da_amt DECIMAL(10,2);
 DECLARE pf_amt DECIMAL(10,2);
 DECLARE net_sal DECIMAL(10,2);
 DECLARE cnt INT;


 -- Check duplicate payroll

 SELECT COUNT(*) INTO cnt
 FROM PAYROLL
 WHERE emp_id = empid;


 IF cnt > 0 THEN

 SIGNAL SQLSTATE '45000'
 SET MESSAGE_TEXT = 'Payroll already generated for this employee';
 ELSE


 -- Get basic salary

SELECT basic INTO basic_salary

FROM EMP
 WHERE emp_id = empid;
 -- Calculate salary components
 SET hra_amt = basic_salary * 0.20;
 SET da_amt = basic_salary * 0.10;
 SET pf_amt = basic_salary * 0.05;
 SET net_sal = basic_salary + hra_amt + da_amt - pf_amt;
 -- Insert payroll record
 INSERT INTO PAYROLL(emp_id, hra, da, pf, net_salary)
 VALUES(empid, hra_amt, da_amt, pf_amt, net_sal);
 END IF;
 END$$
 DELIMITER ;

[Link] SQL, constraints are rules applied to table columns to maintain accuracy and
integrity of data in a database.
Types of Constraints in SQL
1. NOT NULL
 Ensures that a column cannot have a NULL (empty) value.
Example:
CREATE TABLE Student (
SID INT,
Name VARCHAR(50) NOT NULL
);
Here, the Name column must always contain a value.

2. UNIQUE
 Ensures that all values in a column are different.
Example:
CREATE TABLE Employee (
Emp_ID INT UNIQUE,
Name VARCHAR(50)
 );
Here, Emp_ID cannot contain duplicate values.

3. PRIMARY KEY
 Uniquely identifies each record in a table.
 It is a combination of NOT NULL + UNIQUE.
Example:
CREATE TABLE Student (
SID INT PRIMARY KEY,
Name VARCHAR(50)
);
Each SID must be unique and cannot be NULL.

4. FOREIGN KEY
 Creates a relationship between two tables.
 Ensures that a value exists in another table.
Example:
CREATE TABLE Orders (
Order_ID INT,
SID INT,
FOREIGN KEY (SID) REFERENCES Student(SID)
);
Here, SID must exist in the Student table.

5. CHECK
 Ensures that column values satisfy a specific condition.
Example:
CREATE TABLE Employee (
Emp_ID INT,
Salary INT CHECK (Salary > 0)
);
 Salary must always be greater than 0.

6. DEFAULT
 Assigns a default value if no value is provided.
Example:
CREATE TABLE Book (
Book_ID INT,
Status VARCHAR(20) DEFAULT 'Available'
);

[Link] – Definition
In SQL (or MySQL), a trigger is a database object that automatically executes when a
specified event (INSERT, UPDATE, DELETE) occurs on a table.
Uses of Triggers
Triggers are used to:
 Automatically validate data before inserting or updating.
 Maintain data integrity.
 Audit changes in tables.
 Automatically update related tables.
 Enforce business rules in a database.

Trigger for RESULT Table

Assume an audit table:
CREATE TABLE RESULT_AUDIT(
sid INT,
exam_id INT,
old_marks INT,
new_marks INT,
change_time TIMESTAMP
);
1️⃣ Trigger for Insert (Check Marks & Set PASS/FAIL)
DELIMITER $$

CREATE TRIGGER result_insert_trigger

BEFORE INSERT ON RESULT
FOR EACH ROW
BEGIN

IF [Link] < 0 OR [Link] > 100 THEN

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT='Marks must be between 0 and 100';
END IF;

IF [Link] >= 40 THEN

SET [Link]='PASS';
ELSE
SET [Link]='FAIL';
END IF;

END$$

DELIMITER ;

2️⃣ Trigger for Update (Prevent FINAL update + Audit)

DELIMITER $$

CREATE TRIGGER result_update_trigger

BEFORE UPDATE ON RESULT
FOR EACH ROW
BEGIN
 IF [Link]='FINAL' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT='Marks cannot be updated after FINAL';
END IF;

IF [Link] < 0 OR [Link] > 100 THEN

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT='Marks must be between 0 and 100';
END IF;

IF [Link] >= 40 THEN

SET [Link]='PASS';
ELSE
SET [Link]='FAIL';
END IF;

END$$

DELIMITER ;

3️⃣ Trigger to Record Audit Log

DELIMITER $$

CREATE TRIGGER result_audit_trigger

AFTER UPDATE ON RESULT
FOR EACH ROW
BEGIN

INSERT INTO RESULT_AUDIT

VALUES([Link], OLD.exam_id, [Link], [Link], NOW());
 END$$

DELIMITER ;

19. In SQL, joins are used to combine rows from two or more tables based on a
related column between them.
Types of SQL Joins
1. INNER JOIN
 Returns only matching records from both tables.
Example
SELECT [Link], [Link]
FROM Student
INNER JOIN Course
ON [Link] = [Link];
This query shows only students who have matching course records.

2. LEFT JOIN (LEFT OUTER JOIN)

 Returns all records from the left table and matching records from the right table.
 If there is no match, NULL values appear.
Example
SELECT [Link], [Link]
FROM Student
LEFT JOIN Course
ON [Link] = [Link];

3. RIGHT JOIN (RIGHT OUTER JOIN)

 Returns all records from the right table and matching records from the left table.
Example
SELECT [Link], [Link]
FROM Student
RIGHT JOIN Course
 ON [Link] = [Link];

4. FULL JOIN (FULL OUTER JOIN)

 Returns all records from both tables, whether they match or not.
Example
SELECT [Link], [Link]
FROM Student
FULL JOIN Course
ON [Link] = [Link];

QUESTION NOS 8,17,18 refer from Our PPT.