Scribd
Upload
7 views19 pages

Lecture 04 Advanced SQL

The document provides an overview of SQL cursors, including implicit and explicit types, their definitions, lifecycle, and examples. It also covers SQL functions, stored procedures, and triggers, detailing their uses, syntax, and examples. Additionally, it includes exercises for practicing SQL concepts related to employee management.

Uploaded by

anikahossain544
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
7 views19 pages

Lecture 04 Advanced SQL

The document provides an overview of SQL cursors, including implicit and explicit types, their definitions, lifecycle, and examples. It also covers SQL functions, stored procedures, and triggers, detailing their uses, syntax, and examples. Additionally, it includes exercises for practicing SQL concepts related to employee management.

Uploaded by

anikahossain544
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

Database System: Advanced SQL

Prof. Dr. Rafiqul Islam

February 14, 2026

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 1 / 19
SQL Cursor

Uses
A cursor in SQL is a database object used to process data one row at a
time, useful when row-by-row handling is needed instead of bulk
processing. It temporarily stores data for operations like SELECT,
UPDATE, or DELETE.

Types of Cursor
Implicit Cursor
Explicit Cursor

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 2 / 19
Implicit Cursor in SQL

Definition: Implicit cursors are automatically created by SQL for DML


operations (INSERT, UPDATE, DELETE).

Attributes:
SQL%FOUND – True if at least one row affected
SQL%NOTFOUND – True if no rows affected
SQL%ROWCOUNT – Number of rows affected
SQL%ISOPEN – Always false (implicit cursors close automatically)

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 3 / 19
Database Table

Sample Table (Before Update): Employee


EmpID Name Country Age Salary
1 John USA 23 30000
2 Michael Canada 21 45000
3 David United Kingdom 24 40000
4 Robert Germany 21 35000
5 James France 22 25000

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 4 / 19
Implicit Cursor

Example (PL/SQL)
DECLARE
total_rows NUMBER ;
BEGIN
UPDATE Employees
SET Salary = Salary + 1500;

total_rows := SQL % ROWCOUNT ;


dbms_output . put_line ( total_rows || ’ rows updated
END ;

Output
5 rows updated.

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 5 / 19
Explicit Cursor in SQL

Definition:
Explicit cursors are user-defined and provide full control over declaration,
opening, fetching, closing, and deallocation.

Lifecycle:
1 Declare cursor with query - DECLARE cursor name CURSOR FOR
select statement;
2 Open cursor - OPEN cursor name;
3 Fetch rows one by one - FETCH cursor name INTO variable list;
4 Close cursor - CLOSE cursor name;
5 Deallocate cursor

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 6 / 19
Explicit Cursor

Uses
- Process query results row by row - Useful for complex operations where
set-based SQL is insufficient

Syntax
DECLARE cursor_name CURSOR FOR SELECT query ;
OPEN cursor_name ;
FETCH NEXT FROM cursor_name INTO variable_list ;
CLOSE cursor_name ;
DEALLOCATE cursor_name ;

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 7 / 19
Explicit Cursor in SQL

Example:
DECLARE emp_cursor CURSOR FOR
SELECT Name , Salary FROM Employees ;

OPEN emp_cursor ;

FETCH NEXT FROM emp_cursor INTO @Name , @Salary ;


WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ’ Name : ’ + @Name +
’ , Salary : ’ + CAST ( @Salary AS VARCHAR );
FETCH NEXT FROM emp_cursor INTO @Name , @Salary ;
END ;

CLOSE emp_cursor ;
DEALLOCATE emp_cursor ;

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 8 / 19
Sample Output (Row-by-Row):

Output
EmpID Name Salary
1 John 30000
2 Michael 45000
3 David 40000
4 Robert 35000
5 James 25000

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 9 / 19
SQL Cursor

Uses
- Process query results row by row - Useful for complex operations where
set-based SQL is insufficient

Example
DECLARE cur CURSOR FOR
SELECT name FROM students ;
OPEN cur ;
FETCH NEXT FROM cur INTO @studentName ;
-- process each row
CLOSE cur ;
DEALLOCATE cur ;

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 10 / 19
SQL Functions

Uses
- Encapsulate reusable logic - Return a single value (scalar) or a table

Syntax
CREATE OR REPLACE FUNCTION function_name ( parameter_list
RETURNS return_data_type
BEGIN
-- declare local variables if needed
DECLARE variable_name data_type ;

-- SQL statements to compute the result


-- e . g . , SELECT ... INTO variable_name

RETURN variable_name ;
END ;

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 11 / 19
SQL Functions

Example
CREATE FUNCTION getTotalMarks ( sid INT )
RETURNS INT
BEGIN
DECLARE total INT ;
SELECT SUM ( marks ) INTO total
FROM exam WHERE student_id = sid ;
RETURN total ;
END ;

Example Call
SELECT getTotalMarks (101);

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 12 / 19
SQL Functions

Example-2
CREATE FUNCTION getAverageMarks ( sid INT )
RETURNS DECIMAL (5 ,2)
BEGIN
DECLARE avgMarks DECIMAL (5 ,2);
SELECT AVG ( marks ) INTO avgMarks
FROM exam
WHERE student_id = sid ;
RETURN avgMarks ;
END ;

Example Call
SELECT getAverageMarks (101);

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 13 / 19
Stored Procedures

Uses
- Group multiple SQL statements - Support parameters (IN, OUT,
IN/OUT) - Improve modularity and performance

Syntax
CREATE PROCEDURE procedure_name ( parameter_list )
BEGIN
-- SQL statements
END ;

Example Call
CALL addStudent (105 , ’ Rahim ’ );

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 14 / 19
Stored Procedures

Example-1
CREATE PROCEDURE addStudent (
IN sid INT , IN sname VARCHAR (50))
BEGIN
INSERT INTO students ( id , name )
VALUES ( sid , sname );
END ;

Example Call
CALL addStudent (105 , ’ Rahim ’ );

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 15 / 19
Stored Procedures

Example-2
CREATE PROCEDURE getStudentMarks ( IN sid INT )
BEGIN
SELECT student_id , subject , marks
FROM exam
WHERE student_id = sid ;
END ;

Example Call
CALL getStudentMarks (101);

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 16 / 19
Triggers
Uses
- Automatically execute logic on events - Enforce business rules - Maintain
audit logs

Syntax
CREATE TRIGGER beforeInsertExam
BEFORE INSERT ON exam
FOR EACH ROW
BEGIN
IF NEW . marks < 0 THEN
SET NEW . marks = 0;
END IF ;
END ;

Example
Ensures no negative marks are inserted.
Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 17 / 19
Sample Table Data
Students Table
CREATE TABLE students (
id INT PRIMARY KEY ,
name VARCHAR (50)
);

INSERT INTO students VALUES


(101 , ’ Rahim ’ ) ,(102 , ’ Karim ’ ) ,(103 , ’ Ayesha ’ );

Exam Table
CREATE TABLE exam (
exam_id INT AUTO_INCREMENT PRIMARY KEY ,
student_id INT ,
marks INT ,
FOREIGN KEY ( student_id ) REFERENCES students ( id )
);
Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 18 / 19
Exercises: Employee Table

Practice Tasks
Create a function to return the annual salary of an employee.
Write a function to return the full name of an employee.
Write a procedure to update the salary of an employee.
Create a procedure to list all employees in a given department.
Write a cursor to calculate the total salary of all employees.
Write a cursor to print the names of employees earning more than
50,000.
Create a trigger to log salary changes into a SalaryLog table.
Create a trigger to prevent the deletion of employees from the
Manager department.

Prof. Dr. Rafiqul Islam Database System: Advanced SQL February 14, 2026 19 / 19

You might also like