INDEX

Page Date of Date of Remarks

SL Name of Experiment
no. Experiment Remarks

1 Installing oracle/ MYSQL

Creating Entity-Relationship Diagram using

2
case tools.

Data types in SQL and usage of various Data

3
Definition Language commands

To implement constraints on database and

4
table.

5 To Manipulate the Operations on the table

To implement the concept of Joins and

6
grouping of Data

7 To implement the concept of SubQueries

To implement the concept of Indexes and

8
views

9 To implement the basics of PL/SQL

To implement the concept of Cursor and

10
Trigger
 EXPERIMENT NO. 1
Name of Experiment: Installing oracle/ MYSQL

Course Name: DBMS Lab Course Code: BCS-551

Branch: CSE Semester: V

Date of Experiment: Faculty: Mr. Badal Bhushan

Practical : 1

Objective: Installing oracle/ MYSQL

MySQL is one of the most popular relational database management software that is widely used in today's industry.
It provides multi-user access support with various storage engines. It is backed by Oracle Company. In this section,
we are going to learn how we can download and install MySQL for beginners.

Prerequisites
The following requirements should be available in your system to work with MySQL:

MySQL Setup Software

Microsoft .NET Framework 4.5.2
Microsoft Visual C++ Redistributable for Visual Studio 2019
RAM 4 GB (6 GB recommended) Download MySQL Follow
these steps:

Step 1: Go to the official website of MySQL and download the community server edition software. Here, you will see
the option to choose the Operating System, such as Windows.

Step 2: Next, there are two options available to download the setup. Choose the version number for the MySQL
community server, which you want. If you have good internet connectivity, then choose the
mysql-installerwebcounity. Otherwise, choose the other one.
Installing MySQL on Windows

Step 1: After downloading the setup, unzip it anywhere and double click the MSI installer .exe file.

Step 2: In the next wizard, choose the Setup Type. There are several types available, and you need to choose the
appropriate option to install MySQL product and features. Here, we are going to select the Full option and click on
the Next button.

Step 3: Once we click on the Next button, it may give information about some features that may fail to install on your
system due to a lack of requirements. We can resolve them by clicking on the Execute button that will install all
requirements automatically or can skip them. Now, click on the Next button.

Step 4: In the next wizard, we will see a dialog box that asks for our confirmation of a few products not getting
installed. Here, we have to click on the Yes button.

After clicking on the Yes button, we will see the list of the products which are going to be installed. So, if we need all
products, click on the Execute button.
Step 5: Once we click on the Execute button, it will download and install all the products. After completing the
installation, click on the Next button.

Step 6: In the next wizard, we need to configure the MySQL Server and Router. Here, I am not going to configure the
Router because there is no need to use it with MySQL. We are going to show you how to configure the server only.
Now, click on the Next button.

Step 7: As soon as you will click on the Next button, you can see the screen below. Here, we have to configure the
MySQL Server. Now, choose the Standalone MySQL Server/Classic MySQL Replication option and click on Next. Here,
you can also choose the InnoDB Cluster based on your needs.
Step 8: In the next screen, the system will ask you to choose the Config Type and other connectivity options. Here,
we are going to select the Config Type as 'Development Machine' and Connectivity as TCP/IP, and Port Number is
3306, then click on Next.

Step 9: Now, select the Authentication Method and click on Next. Here, I am going to select the first option.

Step 10: The next screen will ask you to mention the MySQL Root Password. After filling the password details, click
on the Next button.

Step 11: The next screen will ask you to configure the Windows Service to start the server. Keep the default setup
and click on the Next button.
Step 12: In the next wizard, the system will ask you to apply the Server Configuration. If you agree with this
configuration, click on the Execute button.

Step 13: Once the configuration has completed, you will get the screen below. Now, click on the Finish button to
continue.

Step 14: In the next screen, you can see that the Product Configuration is completed. Keep the default setting and
click on the Next-> Finish button to complete the MySQL package installation.
Step 15: In the next wizard, we can choose to configure the Router. So click on Next->Finish and then click the Next
button.

Step 16: In the next wizard, we will see the Connect to Server option. Here, we have to mention the root password,
which we had set in the previous steps.

In this screen, it is also required to check about the connection is successful or not by clicking on the Check button. If
the connection is successful, click on the Execute button. Now, the configuration is complete, click on Next.
Step 17: In the next wizard, select the applied configurations and click on the Execute button.

Step 18: After completing the above step, we will get the following screen. Here, click on the Finish button.

Step 19: Now, the MySQL installation is complete. Click on the Finish button.
Verify MySQL installation

Once MySQL has been successfully installed, the base tables have been initialized, and the server has been started,
you can verify its working via some simple tests.

Open your MySQL Command Line Client; it should have appeared with a mysql> prompt. If you have set any
password, write your password here. Now, you are connected to the MySQL server, and you can execute all the SQL
command at mysql> prompt as follows:

For example: Check the already created databases with show databases command:
 EXPERIMENT NO. 2
Name of Experiment: Creating Entity-Relationship Diagram using case tools.
Course Name: DBMS Lab Course Code: BCS-551

Branch: CSE Semester: V

Date of Experiment: Faculty: Mr. Badal Bhushan

Practical : 2

Objective :- Creating Entity-Relationship Diagram using case tools.

An Entity–relationship model (ER model) describes the structure of a database with the help of a diagram,
which is known as Entity Relationship Diagram (ER Diagram). An ER model is a design or blueprint of a
database that can later be implemented as a database.

Ellipse– It represents attribute in the ER Model.

Dashed Ellipses– It denotes derived attributes.

Diamond– It represents relationship between entity and attribute.

Double Diamonds– It represents weak relationships.

Double lines– It indicates total participation of an entity in a relationship set.

Double Rectangle– It represents weak entity set.

Doubles Ellipses– It represents multivalued attributes.

Line– It links attribute(s) to entity set(s) and entity set(s) to relationship set(s).

Multiple ellipses connected to single ellipse using lines– It represents composite attribute Ellipse with line
inside it– It represents single values attributes

Rectangle– It represents entity in the ER Model.

Database - bank.
Tables - account, branch, customer, loan, transaction details.
 Made with Xodo PDF Reader and Editor

EXPERIMENTNO.4
Name of Experiment :To implement constrains
on database and table
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :-4

 Objective: To implement constraints on database and

tabe

Data constraints: Besides the cell name, cell length and cell data type there are other parameters
i.e. other data constrains that can be passed to the DBA at check creation time. The constraints can
either be placed at column level or at the table level.

i. Column Level Constraints: If the constraints are defined along with the column definition, it
is called a column level constraint.

ii. Table Level Constraints: If the data constraint attached to a specify cell in a table reference
the contents of another cell in the table then the user will have to use table level constraints.

Null Value Concepts:- while creating tables if a row locks a data value for particular column that
value is said to be null . Column of any data types may contain null values unless the column was
defined as not null when the table was created.

Syntax:
Column_name datatype(size)NOTNULL
Example:-

Create table STUDENT1 (rollno number(10),name varchar2(20) not null,Branch varchar2(5));

Insert into STUDENT values (101,’’,’CSE’);

 Made with Xodo PDF Reader and Editor

OUTPUT:-

Primary Key: primary key is one or more columns is a table used to uniquely identity each row in the
table. Primary key values must not be null and must be unique across the column. A multicolumn primary
key is called composite primary key.

Syntax

OUTPUT:-

Default value concept: At the line of cell creation a default value can be assigned to it. When the
user is loading a record with values and leaves this cell empty, the DBA will automatically load
this cell with the default value specified. The data type of the default value should match the data
type of the column .

Syntax:

Create table tablename

(columnname datatype (size) default value,….);

Foreign Key Concept : Foreign key represents relationship between tables. A foreign key is column
whose values are derived from the primary key of the same of some other table . the existence of
foreign key implies that the table with foreign key is related to the primary key table from which
 Made with Xodo PDF Reader and Editor

the foreign key is derived .A foreign key must have corresponding primary key value in the
primary key table to have meaning.

Foreign key as a column constraint

Syntax :

Create table table name

(columnname datatype (size) references another table name);

Foreign key as a table constraint:

Example:- At column level:

Create table DEPARTMENT(Dno number(10),Drame varchar2(20),Dlocation varchar2(20));
Create table EMPLOYMENT (Eno number (10) primary key,Ename varchar2(20),Eaddress
varcahar2(20);,Dno number(5)reference Department(dno));
insert into DEPARTMENT values(1,’sales’,Patiala’);
insert into DEPARTMENT values(213,’ABC’,’Chandighard,I);
insert into DEPARTMENT values(214,’DEF’,Sirhind’,2);

At level:
Create table DEPARTMENT(Dno number(10),Drame varchar2(20),Dlocation varchar2(20));
Create table EMPLOYMENT (Eno number (10) primary key,Ename varchar2(20),Eaddress
varcahar2(20);,Dno number(5),
Foreign key(dno) refernces DEPARTMENT(dno));
insert into DEPARTMENT values(1,’sale’,’Patiala’);
insert into DEPARTMENT values(213,’ABC’,’Chandighard,I);
insert into DEPARTMENT values(214,’DEF’,Sirhind’,2);

OUTPUT :
 Made with Xodo PDF Reader and Editor

Check Integrity Constraints: Use the check constraints when you need to enforce integrity rules
that can be evaluated based on a logical expression following are a few examples of appropriate
check constraints.

• A check constraints name column of the client_master so that the name is entered in upper case.

• A check constraint on the client_no column of the client _master so that no client_no value starts
with ‘c’

Syntax:

Create table tablename

(columnname datatype (size) CONSTRAINT constraintname)

Check (expression));
 Made with Xodo PDF Reader and Editor

EXPERIMENTNO.5
Name of Experiment :To Manipulate the Operations
on the table
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :-5

Objective:- To Manipulate the Operations on the table.

DML (DATA Manipulation Language)-

It includes commands to insert tuples into, delete tuples from and modify tuples in the database.
Data manipulation includes:

• The retrieval of information stored in the database.

• The insertion of new information into the database.

• The deletion of information from the database.

• The modification of information stored by the appropriate data model. There are
basically two types.

(i) Procedural DML:- require a user to specify what data are needed and how to
get those data.

(ii) Non Procedural DML : require a user to specify what data are needed
without specifying how to get those data.

Updating the content of a table:

In creation situation we may wish to change a value in table without changing all values in the tuple . For
this purpose the update statement can be used.

Update table name

 Made with Xodo PDF Reader and Editor

Set columnname = expression, columnname =expression…… Where

columnname = expression;

Deletion Operation:-

A delete query is expressed in much the same way as Query. We can delete whole tuple ( rows)

we can delete values on only particulars attributes.

Deletion of all rows

Syntax:

Syntax of DELETE Command

1. DELETE FROM Table_Name WHERE condition;
Examples of DELETE Command
Example 1: This example describes how to delete a single record from the table.
Let's take a Product table consisting of the following records:

Product_Id Product_Name Product_Price Product_Quantity

P101 Chips 20 20

P102 Chocolates 60 40

P103 Maggi 75 5

P201 Biscuits 80 20

P203 Namkeen 40 50

Suppose, you want to delete that product from the Product table whose Product_Id is P203. To do this, you
have to write the following DML DELETE command:
Advertisement

DELETE FROM Product WHERE Product_Id = 'P202' ;

Range Searching: Between operation is used for range searching. Pattern

Searching:

The most commonly used operation on string is pattern matching using the operation ‘like’ we

describe patterns by using two special characters.

• Percent (%) ; the % character matches any substring we consider the following
 Made with Xodo PDF Reader and Editor

examples.

• ‘Perry %’ matches any string beginning with perry

• ‘% idge % matches any string containing’ idge as substring.

• ‘ - - - ‘ matches any string exactly three characters.

• ‘ - - - % matches any string of at least of three characters.

Oracle functions:

Sorting of data in table Syntax:

Select columnname, columnname

From table

Order by columnname;

Aggregate function -Functions are used to manipulate data items and return result. function follow the
format of function _name (argument1, argument2 ..) .An arrangement is user defined variable or constant.
The structure of function is such that it accepts zero or more arguments.

Examples:

Avg return average value of n

Because an aggregate function operates on a set of values, it is often used with the GROUP BY clause of
the SELECT statement. The GROUP BY clause divides the result set into groups of values and the
aggregate function returns a single value for each group.

The following are the commonly used SQL aggregate functions:

 AVG() – returns the average of a set.

 COUNT() – returns the number of items in a set.

 MAX() – returns the maximum value in a set.

 MIN() – returns the minimum value in a set

 SUM() – returns the sum of all or distinct values in a set Except for
the COUNT() function, SQL aggregate functions ignore null.
Syntax:

Avg ([distinct/all]n)

Min return minimum value of expr.

Syntax:

MIN((distinct/all )expr)

Count Returns the no of rows where expr is not null

Syntax:
 Made with Xodo PDF Reader and Editor

Count ([distinct/all)expr]

Count (*) Returns the no rows in the table, including duplicates and those with nulls. Max

Return max value of expr

Syntax:

Max ([distinct/all]expr)

Sum Returns sum of values of n

Syntax:

Sum ([distinct/all]n)

Aggregate Function Example

Queries
--Count the number of employees
SELECT COUNT(*) AS TotalEmployees FROM Employee;

-- Calculate the total salary

SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Find the average salary

SELECT AVG(Salary) AS AverageSalary FROM Employee;

-- Get the highest salary

SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Determine the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;

Output:-
TotalEmployees
6
TotalSalary
3120
AverageSalary
624
HighestSalary
802
LowestSalary
403
 Made with Xodo PDF Reader and Editor

EXPERIMENTNO.3
Name of Experiment :Data types in SQL and usage
of various data definition language commands
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :-3

Objective: Data types in SQL and usage of various Data Definition Language

commands Introduction about SQL.

SQL (Structured Query Language) is a nonprocedural language, you specify what you want, not how to
get it. A block structured format of English key words is used in this Query language. It has the following
components.

DDL (Data Definition Language)-

The SQL DDL provides command for defining relation schemas, deleting relations and modifying relation
schema.

Data Definition Language-

The SQL DDL allows specification of not only a set of relations but also information about each

relation, including-

• Schema for each relation

• The domain of values associated with each attribute.

• The integrity constraints.

• The set of indices to be maintained for each relation.

• The security and authorization information for each relation.

• The physical storage structure of each relation on disk.

Domain types in SQL-
 Made with Xodo PDF Reader and Editor

The SQL standard supports a variety of built in domain types, including-

• Char (n)- A fixed length character length string with user specified length .

• Varchar (n)- A variable character length string with user specified maximum length n.

• Int- An integer.

• Small integer- A small integer.

• Numeric (p, d)-A Fixed point number with user defined precision.

• Real, double precision- Floating point and double precision floating point
numbers with machine dependent precision.

• Float (n)- A floating point number, with precision of at least n digits.

• Date- A calendar date containing a (four digit) year, month and day of the month.

• Time- The time of day, in hours, minutes and seconds Eg. Time ’09:30:00’.

• Number- Number is used to store numbers (fixed or floating point).

DDL statement for creating a table-

Syntax-

Create table tablename:-

Creating a table from a table-

Syntax-
 Made with Xodo PDF Reader and Editor

Insertion of data into tables-

Syntax-

OUTPUT:-
 Made with Xodo PDF Reader and Editor

Inserting data into a table from another table:

Syntax-

INSERT INTO tablename

SELECT columnname, columnname, …….

FROM tablename;

Insertion of selected data into a table from another table:

Syntax-

Retrieving of data from the tables-

Syntax-

Elimination of duplicates from the select statement-

Syntax-
 Made with Xodo PDF Reader and Editor

Selecting a data set from table data-

Syntax-
 EXPERIMENTNO.6
Name of Experiment :To Implement the concept of Joins and grouping of data
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :- 6
Objective:- To implement the concept of Joins and grouping of data.

Joint Multiple Table (Equi Join): Some times we require to treat more than one table as though
manipulate data from all the tables as though the tables were not separate object but one single entity. To
achieve this we have to join tables.Tables are joined on column that have dame data type and data with in
tables.

The tables that have to be joined are specified in the FROM clause and the joining attributes in the WHERE
clause.

Algorithm for JOIN in SQL:

1. Cartesian product of tables (specified in the FROM clause)

2. Selection of rows that match (predicate in the WHERE clause)

3. Project column specified in the SELECT clause.

1. Cartesian product:-

Consider two table student and course

Select B.*,P.*

FROM student B, course P;

Example:
2. INNER JOIN:

3. LEFT OUTER JOIN:

LEFT OUTER JOIN = Cartesian product + selection but include rows from the left table which are
unmatched pat nulls in the values of attributes belonging to the second table Exam:

Select B.*,P*

FROM student B left join course p ON

B.course # P.course #;
4. RIGHT OUTER JOIN:

RIGHT OUTER JOIN = Cartesian product + selection but include rows from right table which are unmatched
Exam:

Select B.*,P.* From student B RIGHT JOIN course P B.course#

= P course # ;
5. FULL OUTER JOIN

Exam

Select B.*,P.*

From student B FULL JOIN course P On

B.course # = P course # ;
Grouping Data From Tables:

There are circumstances where we would like to apply the aggregate function not only to a single set of
tuples, but also to a group of sets of tuples, we specify this wish in SQL using the group by clause. The
attribute or attributes given in the group by clause are used to form group. Tuples with the same value on all
attributes in the group by clause are placed in one group.

Syntax:

SELECT columnname, columnname

FROM tablename

GROUP BY columnname;

At times it is useful to state a condition that applies to groups rather than to tuples. For example we might
be interested in only those branches where the average account balance is more than 1200. This condition
does not apply to a single tuple, rather it applies to each group constructed by the GROUP BY clause. To
express such Questionry, we use the having clause of SQL. SQL applies predicates in the having may be
used.

Syntax:

SELECT columnname, columnname

FROM tablename

GROUP BY columnname;

HAVING searchcondition;
 EXPERIMENTNO.7
Name of Experiment :To Implement the concept of
subquries
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :- 7

Objective:- To implement the concept of SubQueries.

SubQueries:- A subQuery is a form of an SQL statement that appears inside another SQL statement.
It also termed as nested Query. The statement containing a subQuery called a parent statement.
The rows returned by the subQuery are used by the following statement. It can be used by the
following commands:

1. To insert records in the target table.

2. To create tables and insert records in this table.

3. To update records in the target table.

4. To create view.

5. To provide values for the condition in the WHERE , HAVING IN ,

SELECT,UPDATE, and DELETE statements.

Exam:-

Creating clientmaster table from oldclient_master, table

Create table client_master

AS SELECT * FROM oldclient_master;

Using the Union, Intersect and Minus Clause:

Union Clause:

The user can put together multiple Queries and combine their output using the union

clause . The union clause merges the output of two or more Queries into a single set of

rows and column. The final output of union clause will be

Output: = Records only in Query one + records only in Query two + A single set of

records with is common in the both Queries.

Syntax:
 Name of Experiment :To Implement the basics of PL/SQL

Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :- 9

Objective:- To implement the basics of PL/SQL.

Introduction – PL/SQL bridges the gap between database technology and procedural programming
languages. It can be thought of as a development tool that extends the facilities of Oracles SQL database
language. Via PL/SQL you can insert, delete, update and retrieve table data as well as writing loops or
branching to another block of code.

PL/SQL Block structure-

DECLARE

Declarations of memory variables used later

BEGIN

SQL executable statements for manipulating table data.

EXCEPTIONS

SQL and/or PL.SQL code to handle errors. END;

Displaying user Messages on the screen – Any programming tool requires a method through which
messages can be displayed to the user. Dbms_output is a package that includes a number of procedure and
functions that accumulate information in a buffer so that it can be retrieved later. These functions can also
be used to display message to the user.

put_line: put a piece of information in the buffer followed by a end of line marker. It can also be used to
display message to the user.

Setting the server output on:

SET SERVER OUTPUT ON:

Example: Write the following code in the PL/SQL block to display message to user

DBMS_OUTPUT.PUT_LINE(‘Display user message’);

Conditional control in PL/SQL-
The WHILE LOOP:

Syntax:

The FOR LOOP statement:

Syntax:

Example:-
 OUTPUT:-

The GOTO statement: The goto statement allows you to change the flow of control within a PL/SQL Block.

Syntax:-

Example:-

Output:-
 EXPERIMENTNO.8
Name of Experiment :To Implement the concept of Indexes and views
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical :-8
Objective:- To implement the concept of Indexes and views

Indexes- An index is an ordered list of content of a column or group of columns in a table. An index created
on the single column of the table is called simple index. When multiple table columns are included in the
index it is called composite index.

Creating an Index for a table:-

Syntax (Simple)

Composite Index:-

CREATE INDEX index_name

ON tablename(columnname,columnname);

Creating an UniQuestion Index:-

Dropping Indexes:-

An index can be dropped by using DROP INDEX

SYNTAX:-

Views:-

Logical data is how we want to see the current data in our database. Physical data is how this data is actually placed
in our database. Views are masks placed upon tables. This allows the programmer to develop a method via which we
can display predetermined data to users according to our desire. Views may be created fore the following reasons:

1. The DBA stores the views as a definition only. Hence there is no duplication of data.

2. Simplifies Queries.

3. Can be Queried as a base table itself.

4. Provides data security.

5. Avoids data redundancy.
Creation of Views:- Syntax:-

Renaming the columns of a view:-

Syntax:-

Selecting a data set from a view-

Syntax:-
 Destroying a view-

Syntax:-

Example:-

OUTPUT:-
 EXPERIMENTNO.10
Name of Experiment :To Implement the concept of Cursor and Trigger
Course Name: DBMS Lab CourseCode:BCS-551

Branch:CSE Semester:V

DateofExperiment: Faculty:Mr.Badal Bhushan

Practical No:- 10

Objective:- To implement the concept of Cursor and Trigger.

Cursor– We have seen how oracle executes an SQL statement. Oracle DBA uses a work area for its internal
processing. This work area is private to SQL’s operation and is called a cursor. The data that is stored in
the cursor is called the Active Data set. The size of the cursor in memory is the size required to hold the
number of rows in the Active Data Set.

Explicit Cursor- You can explicitly declare a cursor to process the rows individually. A cursor declared
by the user is called Explicit Cursor. For Queries that return more than one row, You must declare a cursor
explicitly.

The data that is stored in the cursor is called the Active Data set. The size of the cursor in memory is the size
required to hold the number of rows in the Active

Why use an Explicit Cursor- Cursor can be used when the user wants to process data one row at a time.

Explicit Cursor Management- The steps involved in declaring a cursor and manipulating data

in the active data set are:-

• Declare a cursor that specifies the SQL select statement that you want to process.

• Open the Cursor.

• Fetch the data from the cursor one row at a time.

• Close the cursor.

Explicit Cursor Attributes- Oracle provides certain attributes/ cursor variables to control the execution
of the cursor. Whenever any cursor(explicit or implicit) is opened and used Oracle creates a set of four
system variables via which Oracle keeps track of the ‘Current’ status of the cursor.

• Declare a cursor that specifies the SQL select statement that you want to process.

• Open the Cursor.

• Fetch the data from the cursor one row at a time.

• Close the cursor.

How to Declare the Cursor:-

How to Open the Cursor:-

Fetching a record From the Cursor:-

The fetch statement retrieves the rows from the active set to the variables one at a time. Each time a fetch is
executed. The focus of the DBA cursor advances to the next row in the Active set. One can make use of
any loop structure(Loop-End Loop along with While,For) to fetch the records from the cursor into variable
one row at a time.

The General Syntax to Fetch the records from the cursor is as follows:-

Database Triggers:-

Database triggers are procedures that are stored in the database and are implicitly executed(fired) when the
contents of a table are changed. Use of Da tabase Triggers:- Database triggers support Oracle to provide a
highly customized database management system. Some of the uses to which the database triggers can be
put to customize management information in Oracle are as follows:-

• A Trigger can permit DML statements against a table any if they are issued, during regular
business hours or on predetermined weekdays.

• A trigger can also be used to keep an audit trail of a table along with the operation
performed and the time on which the operation was performed.

• It can be used to prevent invalid transactions.

How to apply DataBase Triggers:- A

trigger has three basic parts:-

1. A triggering event or statement.

2. A trigger restriction

3. A trigger action.

Types of Triggers:-

Using the various options , four types of triggers can be created:-

1. Before Statement Trigger:- Before executing the triggering statement, the

trigger action is executed.

2. Before Row Trigger:- Before modifying the each row affected by the triggering
statement and before appropriate integrity constraints, the trigger is executed if

the trigger restriction either evaluated to TRUE or was not included.’

3. After Statement Trigger:- After executing the triggering statement and applying any

deferred integrity constraints, the trigger action is executed.

4. After row Trigger:- After modifying each row affected by the triggering statement

and possibly applying appropriate integrity constraints, the trigger action is executed

for the current row if the trigger restriction either evaluates to TRUE or was not

included.

Syntax For Creating Trigger:-

The syntax for Creating the Trigger is as follows:-

Example:-
OUTPUT:-

How to Delete a Trigger:-

The syntax for Deleting the Trigger is as follows:-

Example :-