Dbms Lab Manual

1
INDEX
Sr. Name of Experiment Date Remark
No.
1 Introduction to SQL and installation of SQL Server /
Oracle
2 Data Types, Creating Tables, Retrieval of Rows

using Select Statement, Alter and Drop
Statements.
3 Working with Null Values, matching a Pattern,

Ordering the Result, Aggregate Functions, Grouping,
Update and Delete Statements.
4 Set Operators, Nested Queries, Joins, Sequences
5 Views, Indexes, Database Security and Privileges:

Grant and Revoke Commands, Commit and Rollback
Commands.
6 PL/SQL Architecture, Assignments and Expressions,

Writing PL/SQL Code, Referencing Non- SQL
parameters
7 Stored Procedures and Exception

Handling
8 Triggers and Cursor Management in PL/SQL.
COMPUTER SCIENCE ENGG. DEPT.

2
PRACTICAL –1
AIM:- Introduction to SQL and installation of SQL Server / Oracle
● Relational Database Management System

A relational database management system (RDBMS) is a database management system
(DBMS) that is based on the relational model as introduced by E. F. Codd, of IBM's San
Jose Research Laboratory. Many popular databases currently in use are based on the
relational database model.
● Relational databases are powerful because they require few assumptions about
how data is related or how it will be extracted from the database. As a result, the
same database can be viewed in many different ways.
● An important feature of relational systems is that a single database can be

spread across several tables. This differs from flat-file databases, in which each
database is self-contained in a single table.
● Almost all full-scale database systems are RDBMS's. Small database systems,
however, use other designs that provide less flexibility in posing queries.
● Oracle
In June 1970, Dr. E.F Codd, presented a Relational Model sponsored by IBM. The
language developed by IBM to manipulate data stored within Codd’s Model was
originally called as Structured English Query Language. Later it was called SQL.
● Oracle Corporation is a company that produces most widely used server based
multiuser RDBMS named Oracle
● The Oracle Database (commonly referred to as Oracle RDBMS or simply as

Oracle) is an object-relational database management systemproduced and
marketed by Oracle Corporation.
● Oracle Application Express

Oracle Application Express (Oracle APEX, previously named Oracle HTML DB) is a
software development environment based on the Oracle database.

3
● It allows a fast development cycle to be achieved to create web based

applications.
● It can be used for departmental-style applications with a dozen users, but can
also scale up to handle thousands of users.
⮚ Opening oracle
⮚ Oracle – opening an existing/new database

4
⮚ Oracle – Login
● Basic SQL Commands
DDL – Data Definition Language

The data definition language concept and name was first introduced in relation to the
Codasyl database model, where the schema of the database was written in a language
syntax describing the records, fields, and sets of the user data model. The term DDL is
also used in a generic sense to refer to any formal language for describing data or
information structures.
1. Create 2. Alter 3. Rename 4. Truncate5. Drop
DML - Data Manipulation Language

A data manipulation language (DML) is a family of syntax elements similar to a
computer programming language used for inserting, deleting and updating data in a
database. Performing read-only queries of data is sometimes also considered a
component of DML.
Manipulation of persistent database objects, e.g., tables or stored procedures, via the
SQL schema statements, rather than the data stored within them, is considered to be
part of a separate data definition language. In SQL these two categories are similar in
their detailed syntax, data types, expressions etc., but distinct in their overall function.
1. INSERT 2. UPDATE 3. DELETE 4. MERGE

5
PRACTICAL – 2
AIM: Data Types, Creating Tables, Retrieval of Rows using Select Statement,
Conditional Retrieval of Rows, Alter and Drop Statements.
Ans: SQL Database Data Types

DATA TYPES represents the type of data an object is holding. Data
Types are defined for columns of a table, local/global variables,
input/output arguments of procedures etc..
Each database system (MS SQL Server, MYSQL, DB2, Oracle etc.) have
its own long list of data types but several data types are common in
most of them. This article will list down common data types across
various database systems.
Numeric Data Type

Few numeric data type has syntax of data_type(x). Here x is meant for
precision value.
1. CREATE
Syntax
CREATE TABLE Tablename( { Fieldname Datatype [ (Size) ] Fieldtype [...] } [,...] ) ;
Description:
CREATE TABLE creates a new empty table in the selected database from the structure
description in the command.

6
2. ALTER
Syntax
ALTER TABLE table_name ADD ( column_1 column-definition);
ALTER TABLE table_name MODIFY (column_1 column_type);
Description
The ALTER TABLE statement allows you to rename an existing table. It can also be used
to add, modify, or drop a column from an existing table.
● Before using alter command
● After using alter command

7
3. DROP
Syntax
DROP TABLE table_name;
Description
DROP TABLE removes one or more tables. You must have the DROP privilege for each
table. All table data and the table definition are removed.
4. TRUNCATE
Syntax

8
TRUNCATE TABLE table_name;
Description
TRUNCATE TABLE empties a table completely. Logically, TRUNCATE TABLE is equivalent
to a DELETE statement that deletes all rows, but there are practical differences under
some circumstances.
Implementation of DML Statements
1. The INSERT Statement

The INSERT statement is used to add a new row of data to a table. The basic format of
an INSERT statement is as follows:
Syntax
INSERT
INTO <table>
(<column listings>)
VALUES (<column values>);

9
PRACTICAL – 3
AIM: Working with Null Values, Matching a Pattern from a Table,

Ordering the Result of a Query, Aggregate Functions, Grouping the
Result of a Query, Update and Delete Statements.
Ans: SQL NULL
SQL NULL value represents a blank value in a table. NULL value is used for
identifying any missing entries in a table.
One can use NOT NULL value to display table entries which are not NULL.
Syntax of NULL Value

SELECT column_name(s)
FROM table_name
Where column_name IS NULL;
FROM table_name
Where column_name IS NOT NULL;
SQL NULL STATEMENT Example:
Database table “Employee”
Employee ID Employee Name Age Gender Location Salary
1001 Henry 54 Male New York 100000
1002 Tina 36 Female Moscow 80000

10
1003 John 24 Male 40000
1004 Mile 31 Male London 70000
1005 Tara 26 Female 50000
1006 Sohpie 29 Female London 60000
Note: "Location" column in the "Employee" table above is optional. Therefore, if

one enters a data with no value for the "Location" column, the "Location" column
will be saved with a NULL value.
Now if one wants to display the entries whose location is left blank, then here is a
statement example.
SELECT * FROM Employee

WHERE Location IS NULL;
SQL NULL Statement Output:
The NULL statement will display the following results
1003 John 24 Male 40000
1005 Tara 26 Female 50000
1001 Henry 54 Male New York 100000
1002 Tina 36 Female Moscow 80000
1003 John 24 Male London 40000
1006 Sophie 29 Female London 60000

11
SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column
values match a specified pattern. It is useful when you want to
search rows to match a specific pattern, or when you do not know
the entire value. For this purpose we use a wildcard character '%'.
first_name last_name
------------- -------------
Stephen Fleming
Shekar Gowda
first_name last_name
------------- -------------
Rahul Sharma
first_name last_name age
------------- ------------- ------
Rahul Sharma 10
Anajali Bhagwat 12
Shekar Gowda 15
first_name last_name subject

12
------------- ------------- ----------
Anajali Bhagwat Maths
Shekar Gowda Maths
Rahul Sharma Science
Stephen Fleming Science
2. The DELETE Statement

The DELETE statement is used to remove one or more rows from a table. The basic
format of a DELETE statement is as follows:
Syntax
DELETE
FROM <table>
WHERE <one or more data conditions>;
● Before deleting record of Student with ID=103
● After deleting record of student with ID=103
3. The UPDATE Statement

The UPDATE statement is used to update existing records in a table.

13
Syntax
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
4.The MERGE Statement

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
WHEN NOT MATCHED THEN <insert_clause>;
● Employee table
● Bonuses Table

14
● Merge operation

15
PRACTICAL – 4
AIM: Set Operators, Nested Queries, Joins, and Sequences.
The set operators in oracle are UNION, UNION ALL, INTERSECT, MINUS. These set
operators allow us to combine more than one select statements and only one result set
will be returned.
● UNION: The UNION operator is used to combine the result-set of two or more
SELECT statements Tables of both the select statement must have the same
number of columns with similar data types. It eliminates duplicates.
Syntax:
SELECT column_name(s) FROM table_name1
UNION
● UNION ALL
UNION ALL selects all rows from all the select statements
UNION ALL output is not sorted.
Distinct keyword cannot be used in select statements.
Syntax:
UNION ALL
● INTERSECT allows combining results of two or more select queries. If a record

exists in one query and not in the other, it will be omitted from the INTERSECT
results.
Syntax:
select field1, field2, . field_n from tables INTERSECT select field1, field2, . field_n from
tables;
● MINUS returns all rows in the first query that are not returned in the second
query. Each statement must have the same number of fields in the result sets
with similar data types.
Syntax:

16
Select field1, field2, . field_n from tables

MINUS
select field1, field2, . field_n from tables;
EXAMPLES
Table 1: Stu_acc Table 2: Stu_info
● UNION UNION ALL
● INTERSECTION

17
● MINUS
SQL Subquery Example:

SELECT first_name, last_name, subject
FROM student_details
WHERE games NOT IN ('Cricket', 'Football');
Subquery output would be similar to:
first_name last_name subject
------------- ------------- ----------
Shekar Gowda Badminton
Priya Chandra Chess
2) Lets consider the student_details table which we have used

earlier. If you know the name of the students who are studying
science subject, you can get their id's by using this query below,
SELECT id, first_name

WHERE first_name IN ('Rahul', 'Stephen');

18
but, if you do not know their names, then to get their id's you
need to write the query in this manner,
SELECT id, first_name

WHERE first_name IN (SELECT first_name
WHERE subject= 'Science');
Subquery Output:
id first_name
-------- -------------
100 Rahul
102 Stephen
In the above sql statement, first the inner query is processed first
and then the outer query is processed.
SQL Subquery; INSERT Statement

3) Subquery can be used with an INSERT statement to add rows
of data from one or more tables to another table. Lets try to group
all the students who study Maths in a table 'maths_group'.
INSERT INTO maths_group(id, name)

SELECT id, first_name || ' ' || last_name
FROM student_details WHERE subject= 'Maths'
SQL Subquery; SELECT Statement
4) A subquery can be used in the SELECT statement as follows.
Lets use the product and order_items table defined in the
sql_joins section.

19
select p.product_name, p.supplier_name, (select

order_id from order_items where product_id = 101)
as order_id from product p where p.product_id =
101
product_name supplier_name order_id
------------------ ------------------ ----------
Television Onida 5103
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.
SELECT p.product_name FROM product p

WHERE p.product_id = (SELECT o.product_id FROM
order_items o
WHERE o.product_id = p.product_id);
Subquery Notes
Nested Subquery
1) You can nest as many queries you want but it is recommended
not to nest more than 16 subqueries in oracle
Non-Corelated Subquery
2) If a subquery is not dependent on the outer query it is called a
non-correlated subquery
Subquery Errors
3) Minimize subquery errors: Use drag and drop, copy and paste
to avoid running subqueries with spelling and database typos.

20
Watch your multiple field SELECT comma use, extra or to few

getting SQL error message "Incorrect syntax".
SQL Subquery Comments

Adding SQL Subquery comments are good habit (/* your
command comment */) which can save you time, clarify your
previous work .. results in less SQL headaches
JOIN
The JOIN keyword is used in an SQL statement to query data from two or more tables,
based on a relationship between certain columns in these tables. Tables in a database
are often related to each other with keys. A primary key is a column (or a combination
of columns) with a unique value for each row. Each primary key value must be unique
within the table. The purpose is to bind data together, across tables, without repeating
all of the data in every table.
❖ EQUI JOIN
Syntax
Select column_list
FROM table1, table2....
WHERE table1.column_name = table2.column_name;
Description
SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the
associated tables. An equal sign (=) is used as comparison operator in the where clause
to refer equality. You may also perform EQUI JOIN by using JOIN keyword followed by
ON keyword and then specifying names of the columns along with their associated
tables to check equality.
❖ CROSS JOIN
Syntax
Select *
From table1
Cross join table2

21
Description
In cross joins, each row from first table joins with all the rows of another table. If 1st
table contains x rows and y rows in 2nd one, the result set will be x*y rows.
❖ OUTER JOIN
● LEFT JOIN
Syntax
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Description
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if
there are no matches in the right table (table_name2).
● RIGHT JOIN
Syntax
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
Description
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even
if there are no matches in the left table (table_name1).

22
EXAMPLES
Table 1: Stu_acc Table 2: Stu_info
1. Equi Join

23
2. Cross Join
3. Outer Join
1. Left Outer Join 2. Right Outer Join

24
SQL SERVER: ORACLE: Sequence

Oracle uses different approach for generating unique identifiers.
An auto incremented filed with SEQUENCE object is created first
and this value is assigned to table’s column
CREATE SEQUENCE sequence_name

START WITH start_value
INCREMENT BY increment_value
INSERT INTO table_name(autoincrement_column,
column1, column2, ..)
VALUES(sequence_name.nextval, value1, value2, ..)
sequence_name - is the sequence to be created
start_value – is the start value of identifier
increment_value – is the increment value to which
previous value should be incremented
table_name – is the table name on which auto
increment value is needed
autoincrement_column– is the column whose value to
be auto generated
SQL SERVER: ORACLE Example
CREATE SEQUENCE sequence_employee
START WITH 1
INCREMENT BY 1
INSERT INTO Employee(EmployeeID, Name, ..)

VALUES(sequence_employee.nextval, “Test”, ..)
MS ACCESS: AUTO INCREMENT
column_name data_type constraint AUTOINCREMENT;

25
Example: MS AUTO INCREMENT
CREATE TABLE Employee

(
EmployeeID INT PRIMARY KEY AUTOINCREMENT,
Name VARCHAR(100) NOT NULL,
.
.
)
The default starting value of AUTOINCREMENT is 1 and will
increment by 1 for each record. To modify it alter the value as in
example below.

26
PRACTICAL – 5
AIM: Views, Indexes, Database Security and Privileges: Grant and

Revoke Commands, Commit and Rollback Commands.
Ans: SQL Views

A VIEW is a virtual table, through which a selective portion of the data from one or
more tables can be seen. Views do not contain data of their own. They are used to
restrict access to the database or to hide data complexity. A view is stored as a
SELECT statement in the database. DML operations on a view like INSERT, UPDATE,
DELETE affects the data in the original table upon which the view is based.
The Syntax to create a sql view is
CREATE VIEW view_name
AS
SELECT column_list
FROM table_name [WHERE condition];
● view_name is the name of the VIEW.
● The SELECT statement is used to define the columns and rows that you want to
display in the view.
For Example: to create a view on the product table the sql query would be like
CREATE VIEW view_product
AS
SELECT product_id, product_name
FROM product;
SQL Index
Index in sql is created on existing tables to retrieve the rows
quickly.
When there are thousands of records in a table, retrieving
information will take a long time. Therefore indexes are created on
columns which are accessed frequently, so that the information
can be retrieved quickly. Indexes can be created on a single

27
column or a group of columns. When a index is created, it first

sorts the data and then it assigns a ROWID for each row.
SQL GRANT Command

SQL GRANT is a command used to provide access or privileges on
the database objects to the users.
The Syntax for the GRANT command is:
GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];
● privilege_name is the access right or privilege granted to the
user. Some of the access rights are ALL, EXECUTE, and
SELECT.
● object_name is the name of an database object like TABLE,
VIEW, STORED PROC and SEQUENCE.
● user_name is the name of the user to whom an access right
is being granted.
● user_name is the name of the user to whom an access right
is being granted.
● PUBLIC is used to grant access rights to all users.
● ROLES are a set of privileges grouped together.
● WITH GRANT OPTION - allows a user to grant access rights
to other users.
For Example: GRANT SELECT ON employee TO user1; This
command grants a SELECT permission on employee table to
user1.You should use the WITH GRANT option carefully because
for example if you GRANT SELECT privilege on employee table to
user1 using the WITH GRANT option, then user1 can GRANT
SELECT privilege on employee table to another user, such as user2
etc. Later, if you REVOKE the SELECT privilege on employee from
user1, still user2 will have SELECT privilege on employee table.

28
SQL REVOKE Command:

The REVOKE command removes user access rights or privileges to
the database objects.
The Syntax for the REVOKE command is:
REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}
For Example: REVOKE SELECT ON employee FROM user1;This
command will REVOKE a SELECT privilege on employee table from
user1.When you REVOKE SELECT privilege on a table from a user,
the user will not be able to SELECT data from that table anymore.
However, if the user has received SELECT privileges on that table
from more than one users, he/she can SELECT from that table
until everyone who granted the permission revokes it. You cannot
REVOKE privileges if they were not initially granted by you.
Privileges and Roles:

Privileges: Privileges defines the access rights provided to a user
on a database object. There are two types of privileges.
1) System privileges - This allows the user to CREATE, ALTER,
or DROP database objects.
2) Object privileges - This allows the user to EXECUTE, SELECT,
INSERT, UPDATE, or DELETE data from database objects to which
the privileges apply.
Few CREATE system privileges are listed below:
System
Description
Privileges
allows users to create the specified object

CREATE object
in their own schema.

29
CREATE ANY allows users to create the specified object

object in any schema.
The above rules also apply for ALTER and DROP system
privileges.
Few of the object privileges are listed below:
Object
Description
Privileges
INSERT allows users to insert rows into a table.
allows users to select data from a database

SELECT
object.
UPDATE allows user to update data in a table.
allows user to execute a stored procedure or

EXECUTE
a function.
Roles: Roles are a collection of privileges or access rights. When

there are many users in a database it becomes difficult to grant or
revoke privileges to users. Therefore, if you define roles, you can
grant or revoke privileges to users, thereby automatically granting
or revoking privileges. You can either create Roles or use the
system roles pre-defined by oracle.
Some of the privileges granted to the system roles are as given
below:
To revoke a CREATE TABLE privilege from testing ROLE, you can
write:
REVOKE CREATE TABLE FROM testing;

The Syntax to drop a role from the database is as below:
DROP ROLE role_name;
For example: To drop a role called developer, you can write:
DROP ROLE testing;

30
Data Control Language (DCL)

These SQL commands are used for providing security to database
objects. These commands are GRANT and REVOKE.
SQL is followed by unique set of rules and guidelines called
Syntax. All the SQL statements start with any of the keywords like
SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE,
SHOW and the entire statements end with a semicolon (;).
Important point to be noted is that SQL is case insensitive, which
means SELECT and select have same meaning in SQL statements.


31
PRACTICAL – 6
AIM: PL/SQL Architecture, Assignments and Expressions, Writing

Introduction to PL/SQL
o PL/SQL stands for Procedural Language extension of SQL.
o PL/SQL is a combination of SQL along with the procedural features of
programming languages.
o It was developed by Oracle Corporation in the early 90’s to enhance the
capabilities of SQL.
The PL/SQL Engine:

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can
be stored in the client system (client-side) or in the database (server-side).
A Simple PL/SQL Block:

DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE.
This section is optional and is used to declare any placeholders like variables, constants,
records and cursors, which are used to manipulate data in the execution section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and
ends with END. This is a mandatory section and is the section where the program logic
is written to perform any task. The programmatic constructs like loops, conditional
statement and SQL statements form the part of execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION.
This section is optional. Any errors in the program can be handled in this section, so

32
that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions
that cannot be handled, the Block terminates abruptly with errors.
Every statement in the above three sections must end with a semicolon ; . PL/SQL
blocks can be nested within other PL/SQL blocks. Comments can be used to document
code.
IF-THEN-ELSE
Syntax
IF condition THEN
{...statements...}
ELSE
{...statements...}
END IF;
EXAMPLE
Table- Acc_no
Case 1 : If statement executed Case 2: Else statement executed

33
PRACTICAL – 7
AIM: Stored Procedures and Exception Handling.
Procedure
A procedure or in simple a proc is a named PL/SQL block which performs one or more
specific task. This is similar to a procedure in other programming languages.
A procedure has a header and a body. The header consists of the name of the
procedure and the parameters or variables passed to the procedure. The body consists
or declaration section, execution section and exception section similar to a general
PL/SQL Block.
There are three types of parameters that can be declared:
IN - The parameter can be referenced by the procedure or function. The value of the
parameter can not be overwritten by the procedure or function.
OUT - The parameter can not be referenced by the procedure or function, but the
value of the parameter can be overwritten by the procedure or function.
IN OUT - The parameter can be referenced by the procedure or function and the value
of the parameter can be overwritten by the procedure or function.
Syntax
CREATE [OR REPLACE] PROCEDURE procedure_name

[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
How to execute a Procedure?

There are two ways to execute a procedure.

34
1) From the SQL prompt.

EXECUTE [or EXEC] procedure_name;
2) Within another procedure – simply use the procedure name.

procedure_name;
Functions
A function is a named PL/SQL Block which is similar to a procedure. The major
difference between a procedure and a function is, a function must always return a
value, but a procedure may or may not return a value.
Syntax
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
1) Return Type: The header section defines the return type of the function. The return
datatype can be any of the oracle datatype like varchar, number etc.
2)The execution and exception section both should return a value which is of the
datatype defined in the header section.
How to execute a PL/SQL Function?

A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;
If ‘employee_name’ is of datatypevarchar we can store the name of the employee by
assigning the return type of the function to it.

35
2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;
3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.
EXAMPLES
1. PROCEDURE
o Before executing Procedure
o After executing Procedure
2. Function
o Creating the Function

36
o Executing the Function
PR
FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of
times. Iteration occurs between the start and end integer values given. The counter is
always incremented by 1. The loop exits when the counter reaches the value of the end
integer.
Syntax
FOR counter IN val1..val2
LOOP
statements;
END LOOP;
val1 - Start integer value.
val2 - End integer value.

37
FOR in REVERSE Loop

A FOR LOOP is used to execute a set of statements for a predetermined number of
times in reverse order.
Syntax
FOR counter IN REVERSE val1..val2
LOOP
statements;
END LOOP;
While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a
condition is true. The condition is evaluated at the beginning of each iteration. The
iteration continues until the condition becomes false.
Syntax
WHILE <condition>
LOOP statements;
END LOOP;
EXAMPLES
1. FOR LOOP
2. FOR LOOP in REVERSE

38
3. WHILE LOOP

39
PRACTICAL – 8
AIM: Triggers and Cursor Management in PL/SQL.
TRIGGERS
A trigger is a pl/sql block structure which is fired when a DML statements like Insert,
Delete, Update is executed on a database table. A trigger is triggered automatically
when an associated DML statement is executed.
Syntax
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)
BEGIN
--- sql statements
END;
CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with
the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the
trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used
to create a trigger on a view. before and after cannot be used to create a trigger on a
view.
{INSERT [OR] | UPDATE [OR] | DELETE} -This clause determines the triggering
event. More than one triggering events can be used together separated by OR keyword.
The trigger gets fired at all the specified triggering event.
[OF col_name] -This clause is used with update triggers. This clause is used when you
want to trigger an event only when a specific column is updated.

40
CREATE [OR REPLACE ] TRIGGER trigger_name -This clause creates a trigger with the
given name or overwrites an existing trigger with the same name.
[ON table_name] - This clause identifies the name of the table or view to which the
trigger is associated.
[REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and
new values of the data being changed. By default, you reference the values as
:old.column_name or :new.column_name. The reference names can also be changed
from old (or new) to any other user-defined name. You cannot reference old values
when inserting a record, or new values when deleting a record, because they do not
exist.
[FOR EACH ROW] - This clause is used to determine whether a trigger must fire when
each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql
statement is executed(i.e.statement level Trigger).
WHEN (condition) -This clause is valid only for row level triggers. The trigger is fired
only for rows that satisfy the condition specified.
Types of PL/SQL Triggers

There are two types of triggers based on the which level it is triggered.
1) Row level trigger -An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger- An event is triggered for each sql statement executed.
PL/SQL Trigger Execution Hierarchy

The following hierarchy is followed when a trigger is fired.
1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row. This events will
alternates between BEFORE and AFTER row level triggers.
4) Finally the AFTER statement level trigger fires.

41
2. Trigger
o Creating Trigger
o Trigger is fired when Insert command is executed
Cursors
Oracle creates a memory area, known as context area, for processing an SQL
statement, which contains all information needed for processing the statement, for
example, number of rows processed etc.
A cursor is a pointer to this context area. PL/SQL controls the context area through a
cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of
rows the cursor holds is referred to as the active set.

42
You can name a cursor so that it could be referred to in a program to fetch and process
the rows returned by the SQL statement, one at a time. There are two types of cursors:
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is
executed, when there is no explicit cursor for the statement. Programmers cannot
control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor
is associated with this statement.
Attribute Description
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected

%FOUND one or more rows or a SELECT INTO statement returned one or more
rows. Otherwise, it returns FALSE.
The logical opposite of %FOUND. It returns TRUE if an INSERT,

%NOTFOUND UPDATE, or DELETE statement affected no rows, or a SELECT INTO
statement returned no rows. Otherwise, it returns FALSE.
Always returns FALSE for implicit cursors, because Oracle closes the
%ISOPEN
SQL cursor automatically after executing its associated SQL statement.
Returns the number of rows affected by an INSERT, UPDATE, or

%ROWCOUNT
DELETE statement, or returned by a SELECT INTO statement.
Explicit Cursors
Explicit cursors are programmer defined cursors for gaining more control over the
context area. An explicit cursor should be defined in the declaration section of the
PL/SQL Block. It is created on a SELECT Statement which returns more than one row.
Syntax
Working with an explicit cursor involves four steps:
o Declaring the cursor for initializing in the memory

o Opening the cursor for allocating memory
o Fetching the cursor for retrieving data
o Closing the cursor to release allocated memory

43
Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example:
CURSOR c_customers IS SELECT id, name, address FROM customers;
Opening the Cursor

Opening the cursor allocates memory for the cursor, and makes it ready for fetching the rows returned by
the SQL statement into it. For example :
OPEN c_customers;
Fetching the Cursor

Fetching the cursor involves accessing one row at a time. For example:
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor

Closing the cursor means releasing the allocated memory. For example, we will close above opened
cursor as follows:
CLOSE c_customers;
EXAMPLES
CURSOR

Dbms Lab Manual

Uploaded by

Copyright:

Available Formats

Dbms Lab Manual

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Dbms Lab Manual

Uploaded by

Copyright:

Available Formats

1

2 Data Types, Creating Tables, Retrieval of Rows

3 Working with Null Values, matching a Pattern,

4 Set Operators, Nested Queries, Joins, Sequences

5 Views, Indexes, Database Security and Privileges:

6 PL/SQL Architecture, Assignments and Expressions,

7 Stored Procedures and Exception

8 Triggers and Cursor Management in PL/SQL.

COMPUTER SCIENCE ENGG. DEPT.

AIM:- Introduction to SQL and installation of SQL Server / Oracle

● Relational Database Management System

● An important feature of relational systems is that a single database can be

● The Oracle Database (commonly referred to as Oracle RDBMS or simply as

● Oracle Application Express

COMPUTER SCIENCE ENGG. DEPT.

● It allows a fast development cycle to be achieved to create web based

⮚ Oracle – opening an existing/new database

COMPUTER SCIENCE ENGG. DEPT.

● Basic SQL Commands

DDL – Data Definition Language

DML - Data Manipulation Language

COMPUTER SCIENCE ENGG. DEPT.

Ans: SQL Database Data Types

Numeric Data Type

COMPUTER SCIENCE ENGG. DEPT.

● After using alter command

COMPUTER SCIENCE ENGG. DEPT.

COMPUTER SCIENCE ENGG. DEPT.

TRUNCATE TABLE table_name;

Implementation of DML Statements

1. The INSERT Statement

COMPUTER SCIENCE ENGG. DEPT.

AIM: Working with Null Values, Matching a Pattern from a Table,

Ans: SQL NULL

Syntax of NULL Value

1001 Henry 54 Male New York 100000

1002 Tina 36 Female Moscow 80000

COMPUTER SCIENCE ENGG. DEPT.

1003 John 24 Male 40000

1004 Mile 31 Male London 70000

1005 Tara 26 Female 50000

1006 Sohpie 29 Female London 60000

Note: "Location" column in the "Employee" table above is optional. Therefore, if

SELECT * FROM Employee

1003 John 24 Male 40000

1005 Tara 26 Female 50000

Employee ID Employee Name Age Gender Location Salary

1001 Henry 54 Male New York 100000

1002 Tina 36 Female Moscow 80000

1003 John 24 Male London 40000

1006 Sophie 29 Female London 60000

COMPUTER SCIENCE ENGG. DEPT.

SQL LIKE Operator

first_name last_name age

------------- ------------- ------

first_name last_name subject

COMPUTER SCIENCE ENGG. DEPT.

------------- ------------- ----------

Anajali Bhagwat Maths