lOMoARcPSD|55762870

Unit 2 notes - eswar

Btech (Visvodaya Engineering College)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Aanchal Sharma ([email protected])
 lOMoARcPSD|55762870

UNIT- II

Relational Algebra and Calculus: Relational Algebra - Selection and Projection, Set
operations, Renaming, Joins, Division, Examples of Algebra Queries, Relational calculus -
Tuple relational Calculus - Domain relational calculus - Expressive Power of Algebra and
calculus.
Form of Basic SQL Query - Examples of Basic SQL Queries, Introduction to Nested Queries,
Correlated Nested Queries, Set - Comparison Operators, Aggregate Operators, NULL values -
Comparison using Null values - Logical connectives - AND, OR and NOT - Impact on SQL
Constructs, Outer Joins, Disallowing NULL values, Complex Integrity Constraints in SQL
Triggers and Active Data bases.

Relational Algebra and Calculus

Relational database systems are expected to be equipped with a query language that can
assist its users to query the database instances. There are two kinds of query languages −
relational algebra and relational calculus.

The relational algebra is a procedural query language. It consists of a set of operations that take
one or two relations as input and produce a new relation as their result. The fundamental
operations in the relational algebra are select, project, union, set difference, Cartesian
product, and rename. In addition to the fundamental operations, there are several other
operations namely, set intersection, natural join, division, and assignment.
Fundamental Operations The select, project, and rename operations are called unary
operations, because they operate on one relation. The other three operations operate on pairs of
relations and are, therefore, called binary operations. Various operations are shown as follows:

UNIT – II / 1
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Select Operation (σ) :

Relational algebra includes operators to select rows from a relation and to project columns (π).
These operations allow us to manipulate data in a single relation. It selects tuples that satisfy the
given predicate from a relation. i.e The selection operator σ allows us to extract rows

from a relation

Notation : σp (r)
Where σ stands for selection predicate and r stands for relation. p is prepositional logic formula
which may use connectors like and, or, and not. These terms may use relational operators like −
=, ≠, ≥, < , >, ≤.
Ex 1 : To select those tuples of the loan relation where the branch is “Perryridge,”
Ans : σ branch-name = “Perryridge” (loan)
Ex 2 : Selects tuples from books where subject is 'database'.

σsubject = "database"(Books)

Ex 3 : Selects tuples from books where subject is 'database' and 'price' is 450.
σsubject = "database" and price = "450"(Books)
Ex 4 : Selects tuples from books where subject is 'database' and 'price' is
450 or those books published after 2010.

σsubject = "database" and price = "450" or year > "2010"(Books)

Let us consider the following schemas

UNIT – II / 2
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Project Operation (∏) : Projection is the operation of selecting certain attributes from a
relation R to form a new relation. i.e The projection operator π allows us to extract

columns from a relation

It projects column(s) that satisfy a given predicate.

Notation − ∏A1, A2, An (r)
Where A1, A2 , An are attribute names of relation r.
Duplicate rows are automatically eliminated, as relation is a set.
For example −

Ex 1 : Selects columns named as subject and author from the relation Books

∏subject, author (Books)

Union Operation (∪) :

It return a relation consist of all rows appearing in either or both of two specifying relations. i.e
RUS returns a relation containing all tupples that occur in either R or S both.
Here, R and S must be union compatible and schema of the result is defined to be identical to the
schema of R.

For Union operation to be valid, the following conditions must hold

UNIT – II / 3
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

 r, and s must have the same number of attributes.

 Attribute domains must be compatible.

 Duplicate tuples are automatically eliminated.

Ex1 : Select the names of the authors who have either written a book or an article or both.

∏ author (Books) ∪ ∏ author (Articles)

Set Difference (−)

R-S returns a relation instance containing all tuples that occur in R but not in S. The
relations R and S must be union-compatible, and the schema of the result is defined to be
identical to the schema of R.

Notation : r − s

Ex2 : Provides the name of authors who have written books but not articles
∏ author (Books) − ∏ author (Articles)

Cartesian Product (Χ)

R x S returns a relation instance whose schema contains all the fields of R followed by S

Notation : r Χ s

Rename Operation (ρ)

The rename operation allows us to rename the output relation. 'rename' operation is denoted with

small Greek letter rho ρ.

Notation − ρ x (E)
Where the result of expression E is saved with name of x.
Additional operations are −

UNIT – II / 4
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

 Set intersection
 Assignment
 Natural join

Set intersection : R ∩ S' returns a relation instance containing all tuples that occur in both R and
S. The relations Rand S must be union-compatible.

Assignment : It provides a convenient way to express complex queries. Assignment must always
be made to a temporary relation variable.

Joins : The join operation is one of the most useful operations in relational algebra and the
most commonly used way to combine information from two or more relations. Although a join
can be defined as a cross-product followed by selections and projections. It is denoted by ⋈.

There are TWO types of Joins

1. Inner Join 2. Outer Joins

Inner Join can be classified into three ways

1) Condition Join or Theta Join 2) Equi Join 3) Natural Join

Outer Join can be classified as

1) Left outer Join 2) Right outer Join 3) Full outer Join

Condition Join or Theta Join : Theta join combines tuples from different relations provided
they satisfy the theta condition. The join condition is denoted by the symbol θ.

Notation : R1 ⋈θ R2
or

R1 and R2 are relations having attributes (A1, A2, .., An) and (B1, B2,.. ,Bn) such that the
attributes don’t have anything in common, that is R1 ∩ R2 = Φ.

Theta join can use all kinds of comparison operators.

Student
SID Name Std
101 Alex 10
102 Maria 11
UNIT – II / 5
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Subjects
Class Subject
10 Math
10 English
11 Music
11 Sports
Ex : STUDENT ⋈Student.Std = Subject.Class SUBJECT
Student_detail
SID Name Std Class Subject
101 Alex 10 10 Math
101 Alex 10 10 English
102 Maria 11 11 Music
102 Maria 11 11 Sports

Equijoin

When Theta join uses only equality comparison operator, it is said to be equijoin. The above
example corresponds to equijoin.

Natural Join ( ⋈)
It is a binary operator that is written as (R ⋈S) where R and S are relations. The result of the
natural join is the set of all combinations of tuples in R and S that are equal on their common
attribute names. For an example consider the tables Employee and Dept and their natural join:
Employee
Name EmpId DeptName
Dept
DeptName Manager ⋈
Employee Dept
Harry 3415 Finance Finance George DeptNam
Name EmpId Manager
Sally 2241 Sales Sales Harriet e
George 3401 Finance Production Charles Harry 3415 Finance George
Harriet 2202 Sales Sally 2241 Sales Harriet
George 3401 Finance George
Harriet 2202 Sales Harriet

UNIT – II / 6
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Note : The Default join operation used in the join is Natural Join. But Natural Join operation
results in some loss of Information.

Outer Joins

Theta Join, Equijoin, and Natural Join are called inner joins. An inner join includes only
those tuples with matching attributes and the rest are discarded in the resulting relation.
Therefore, we need to use outer joins to include all the tuples from the participating relations in
the resulting relation. There are three kinds of outer joins − left outer join, right outer join,
and full outer join.

Left Outer Join(R S)

All the tuples from the Left relation, R, are included in the resulting relation. If there are
tuples in R without any matching tuple in the Right relation S, then the S-attributes of the
resulting relation are made NULL.

Right Outer Join: ( R S)

All the tuples from the Right relation, S, are included in the resulting relation. If there are tuples
in S without any matching tuple in R, then the R-attributes of resulting relation are made NULL.

Full Outer Join: ( R S)

All the tuples from both participating relations are included in the resulting relation. If there are
no matching tuples for both relations, their respective unmatched attributes are made NULL.

Employee

Employee_name Street City

Coyote Toon Hollywood

Rabbit Tunnel Carrotvile

UNIT – II / 7
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Smith Revolver Death Valley

Williams Seaview Seattle

Works

Employee_name Branch Name Salary

Coyote Mesa 1500

Rabbit Mesa 1300

Gates Redmond 5300

Williams Redmond 1500

Result of Employee works

Employee_name Street City Branch Name Salary

Coyote Toon Hollywood Mesa 1500

Rabbit Tunnel Carrotvile Mesa 1300

Williams Seaview Seattle Redmond 1500

NULL NULL
Smith Revolver Death Valley

.Result of Employee works

UNIT – II / 8
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Employee_name Street City Branch Name Salary

Coyote Toon Hollywood Mesa 1500

Rabbit Tunnel Carrotvile Mesa 1300

Williams Seaview Seattle Redmond 1500

NULL NULL
Gates Redmond 5300

.Result of Employee works

Employee_name Street City Branch Name Salary

Coyote Toon Hollywood Mesa 1500

Rabbit Tunnel Carrotvile Mesa 1300

Williams Seaview Seattle Redmond 1500

NULL NULL
Gates Redmond 5300
NULL NULL
Smith Revolver Death Valley

Division : The division is a binary operation that is written as R ÷ S. The result consists of the
restrictions of tuples in R to the attribute names unique to R, i.e., in the header of R but not in the
header of S, for which it holds that all their combinations with tuples in S are present in R.

Ex 1 :

Completed DBProject Completed ÷ DBProject

Student Task Task Student
Fred Database1 Database1 Fred
Fred Database2 Database2 Sarah
Fred Compiler1

UNIT – II / 9
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Eugene Database1
Eugene Compiler1
Sarah Database1
Sarah Database2

Ex 2 :

More Examples of Algebra Queries

Relational Calculus : In contrast to Relational Algebra, Relational Calculus is a non-

procedural query language, that is, it tells what to do but never explains how to do it.

Relational calculus exists in two forms −

UNIT – II / 10
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

1) Tuple Relational Calculus (TRC)

2) Domain Relational Calculus (DRC)

Tuple Relational Calculus (TRC) : Filtering variable ranges over tuples

Notation − {T | Condition}
Returns all tuples T that satisfies a condition.
For example − { T.name | Author(T) AND T.article = 'database' }
Output − Returns tuples with 'name' from Author who has written article on 'database'.
TRC can be quantified. We can use Existential (∃) and Universal Quantifiers (∀).
For example −

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

Output − The above query will yield the same result as the previous one.

Domain Relational Calculus (DRC)

In DRC, the filtering variable uses the domain of attributes instead of entire tuple values
Notation −
{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where a1, a2 are attributes and P stands for formulae built by inner attributes.
For example −

{< article, page, subject > | ∈ TutorialsPoint ∧ subject = 'database'}

Output − Yields Article, Page, and Subject from the relation TutorialsPoint, where subject is
database.

UNIT – II / 11
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Just like TRC, DRC can also be written using existential and universal quantifiers. DRC also
involves relational operators.

EXPRESSIVE POWER OF ALGEBRA AND CALCULUS

It is possible to write syntactically correct calculus queries that have an

infinite number of Touples. Such queries are called unsafe.

– e.g.,

i.e all touples of S such that S is not in sailors. So S is obviously Infinite.

It is known that every query that can be expressed in relational algebra can
be expressed as a safe query in DRC / TRC; the converse is also true.

The expressive power of relational algebra is often used as a metric of how powerful a relational
database query language is. If a query language can express all the queries that we can express in
relational algebra, it is said to be relationally complete. A practical query language is expected to
be relationally complete; in addition, commercial query languages typically support features
that allow us to express some queries that cannot be expressed in relational algebra.

THE FORM OF A BASIC SQL QUERY

The basic form of an SQL query is as follows:

SELECT [DISTINCT] select-list
FROM from-list
WHERE < Condition >

UNIT – II / 12
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Every query must have a SELECT clause, which specifies columns to be retained in the
result, and a FROM clause, which specifies a cross-product of tables. The optional WHERE
clause specifies selection conditions on the tables mentioned in the FROM clause.
• The from-list in the FROM clause is a list of table names. A table name can be followed
by a range variable; a range variable is particularly useful when the same table name appears
more than once in the from-list.
• The select-list is a list of column names of tables named in the from-list. Column names
can be prefixed by a range variable.
• The condition in the WHERE clause is a boolean combination (i.e., an expression using
the logical connectives AND, OR, and NOT)
• The DISTINCT keyword is optional. It indicates that the table computed as an answer
to this query should not contain duplicates, that is, two copies of the same row. The default is
that duplicates are not eliminated.

UNIT – II / 13
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Ex 1 : Find the' names and ages of all sailors.

SELECT DISTINCT S.sname, S.age FROM Sailors S
The answer is a set of rows, each of which is a pair (sname, age). If two or more sailors
have the same name and age, the answer still contains just one pair with that name and age. This
query is equivalent to applying the projection operator of relational algebra.

UNIT – II / 14
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

UNION, INTERSECT, AND EXCEPT :

SQL provides three set-manipulation constructs. SQL supports these operations under the names
UNION, INTERSECT, and EXCEPT. Set operators are used to join the results of two (or more)
SELECT statements.

UNIT – II / 15
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

UNION is used to combine the results of two or more Select statements. However it will
eliminate duplicate rows from its result set. In case of union, number of columns and data type
must be same in both the tables.

Example of UNION

The First table,

ID Name
1 abhi
2 adam

The Second table,

ID Name
2 adam
3 Chester

Union SQL query will be,

select * from First

UNION
select * from second

The result table will look like,

ID NAME
1 abhi
2 adam
3 Chester

Union All

This operation is similar to Union. But it also shows the duplicate rows.

Union All query will be like,

select * from First

UNION ALL
select * from second

UNIT – II / 16
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

The result table will look like,

ID NAME
1 abhi
2 adam
2 adam
3 Chester

Intersect

Intersect operation is used to combine two SELECT statements, but it only retuns the records
which are common from both SELECT statements. In case of Intersect the number of columns
and data type must be same. MySQL does not support INTERSECT operator.

Intersect query will be,

select * from First

INTERSECT
select * from second

The result table will look like

ID NAME
2 adam

Minus

Minus operation combines result of two Select statements and return only those result which
belongs to first set of result. MySQL does not support INTERSECT operator. MINUS and
EXCEPT are exact synonyms.

Minus query will be,

The result table will look like, select * from First

MINUS
select * from second

UNIT – II / 17
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

ID NAME
1 abhi

NESTED QUERIES

A nested query is a query that has another query embedded within it; the embedded query
is called a sub query. i.e Subquery or Inner query or Nested query is a query in a query. A
subquery typically appears within the WHERE clause of a query. Subqueries can sometimes
appear in the FROM clause or the HAVING clause
 The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or
inside another subquery.
 A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
 You can use the comparison operators, such as >, <, or =. The comparison operator can also
be
a multiple-row operator, such as IN, ANY, or ALL.
 A subquery is also called an inner query or inner select, while the statement containing a
subquery is also called an outer query or outer select.
 The inner query executes first before its parent query so that the results of inner query can be
passed to the outer query.

Correlated Subquery

A query is called correlated subquery when both the inner query and the outer query are
interdependent. For every row processed by the inner query, the outer query is processed as well.
The inner query depends on the outer query before it can be processed.

Aggregate Operators : An aggregate function is a function that derives a single value

from a set of values from a column. Aggregate functions must be used with SELECT or
HAVING clauses.

Common aggregate functions include

Function Description
AVG(column) Returns the average value of a column
COUNT(column) Returns the number of rows (without a NULL value) of a column

UNIT – II / 18
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

COUNT(*) Returns the number of selected rows

COUNT(DISTINCT column) Returns the number of distinct results
MAX(column) Returns the highest value of a column
MIN(column) Returns the lowest value of a column
SUM(column) Returns the sum of a column

Ex : List the sum salary of all employees dept wise.

SQL > Select deptno,sum(sal) from emp group by deptno;

Ex : Display the average salary dept wise

SQL > Select deptno,avg(sal) from emp group by deptno;.

Ex : Display the maximum salary in each department.

SQL > Select deptno,max(sal) from emp group by deptno;

Ex : Display the minimum salary in each department

SQL > Select deptno,min(sal) from emp group by deptno;

Ex : List the number of employees working in each department.

SQL > Select deptno,count(*) from emp group by deptno;

Ex : Display total salary department wise where the dept wise total salary is above 5000.

SQL > select deptno,sum(sal) from emp group by deptno having sum(sal) >= 5000;

Ex : List the deptname and average salary of them.

SQL > Select dname,avg(sal) from emp,dept

where emp.deptno=dept.deptno group by dname;

Ex : List the deptname and sum of salary of them.

SQL > Select dname,sum(sal) from emp,dept

where emp.deptno=dept.deptno group by dname;

EX : Display the details of the employee whose salary is maximum.

SQL > Select * from emp where sal = (select max(sal) from emp);

Ex : Find second maximum salary

UNIT – II / 19
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

SQL > Select max(sal) from emp where

sal < (select max(sal) from emp);

Ex : Find the Third highest salary.

SQL > Select max(sal) from emp where

sal < (select max(sal) from emp where
sal < (select max(sal) from emp));

Ex :Display the top 5 salaries from employees table.

SQL > Select sal from (select sal from emp order sal desc) where rownum <= 5;

Ex : To find the employees whose salary is equal to the salary of at least one employee in
department of id 300?

SQL > SELECT EMPLOYEE_ID, SALARY

FROM EMPLOYEES
WHERE SALARY IN
( SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 300);

Ex : To find the employees whose salary is greater than at least on employee in department of id
500?

Sql > SELECT EMPLOYEE_ID, SALARY

FROM EMPLOYEES
WHERE SALARY > ANY
( SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 500);

Ex : Write a query to find the employees whose salary is less than the salary of all employees in
department of id 100?

SQL > SELECT EMPLOYEE_ID, SALARY

FROM EMPLOYEES
WHERE SALARY < ALL
( SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100 );
Ex : Write a query to find the employees whose manager and department should match with the
employee of id 20 or 30?

UNIT – II / 20
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

SQL > SELECT EMPLOYEE_ID, MANAGER_ID,

DEPARTMENT_ID
FROM EMPLOYEES
WHERE (MANAGER_ID,DEPARTMENT_ID) IN
( SELECT MANAGER_ID,
DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (20,30) );

Ex . Write a query to list the department names which have at least one employee?

SQL > SELECT DEPARTMENT_ID,

DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE EXISTS
(
SELECT 1
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

Ex : Write a query to find the departments which do not have employees at all?

SQL > SELECT DEPARTMENT_ID,

DEPARTMENT_NAME
FROM DEPARTMENTS D
WHERE NOT EXISTS
(
SELECT 1
FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)

NULL VALUES
The SQL NULL is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.

A field with a NULL value is a field with no value. It is very important to understand that a
NULL value is different than a zero value or a field that contains spaces.

A field with a NULL value is one that has been left blank during record creation.

UNIT – II / 21
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

Logical Connectives AND, OR, and NOT

There are three Logical Operators namely, AND, OR, and NOT. These operators
compare two conditions at a time to determine whether a row can be selected for the output.
When retrieving data using a SELECT statement, you can use logical operators in the WHERE
clause, which allows you to combine more than one condition.

Logical
Description
Operators
For the row to be selected at least one of
OR
the conditions must be true.
For a row to be selected all the specified
AND
conditions must be true.
For a row to be selected the specified
NOT
condition must be false.

TRIGGERS AND ACTIVE DATABASES

A trigger is a procedure that is automatically invoked by the DBMS in response to
specified changes to the database, and is typically specified by the DBA. A database that has a
set of associated triggers is called an active database. A trigger description contains three parts:
 Event: A change to the database that activates the trigger.
 Condition: A query or test that is run when the trigger is activated.
 Action: A procedure that is executed when the trigger is activated and its condition is
true.

Trigger is like a ‘Daemon that monitors a data base, and is executed when the data base is
modified in a way that matches the event specification
A data base that has a set of associated triggers is called an active data base.

Triggers can be written for the following purposes:

 Generating some derived column values automatically

 Enforcing referential integrity

 Event logging and storing information on table access

UNIT – II / 22
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

 Imposing security authorizations

 Preventing invalid transactions

The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;

CUSTOMERS table we had created previously

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+

The following program creates a row level trigger for the customers table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This
trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE

UNIT – II / 23
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])

 lOMoARcPSD|55762870

sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

When the above code is executed at SQL prompt, it produces the following result:

Trigger created.

Let us perform some DML operations on the CUSTOMERS table. Here is one INSERT
statement, which will create a new record in the table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

When a record is created in CUSTOMERS table, above create trigger display_salary_changes

will be fired and it will display the following result:

Old salary:
New salary: 7500
Salary difference:

Because this is a new record so old salary is not available and above result is coming as null.
Now, let us perform one more DML operation on the CUSTOMERS table. Here is one UPDATE
statement, which will update an existing record in the table:

UPDATE customers
SET salary = salary + 500
WHERE id = 2;

When a record is updated in CUSTOMERS table, above create trigger display_salary_changes

will be fired and it will display the following result:

Old salary: 1500

New salary: 2000
Salary difference: 500

UNIT – II / 24
DBMS NOTES (R15) VTA – CSE

Downloaded by Aanchal Sharma ([email protected])