SRI VENKATESWARA COLLEGE OF ENGINEERING AND

TECHNOLOGY(AUTONOMOUS)

Recognized by AICTE, NBA, NAAC and

Govt. of A.P. Affiliated by J.N.T.U.A., ANANTAPUR

R.V.S. Nagar, Tirupati Road, CHITTOOR- 517127

Department of CSE

RELATIONAL DATABASE MANAGEMENT SYSTEMS LAB

(20ACS10)

Name: _____________________________ Roll No: _______________

Class: _____________________________ Branch: _______________

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 1

 SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY.
(AUTONOMOUS)
II [Link] I Semester CSE (DS)

Code: 20ACS10 RELATIONAL DATABASE MANAGEMENT SYSTEMS LAB

- -
Objectives:

At the end of the course the student will be able to:

1. Design and implement a database schema for given problem.
2. Implement SQL queries using query language tools.
3. Apply the normalization techniques for development of application software to realistic problems.
4. Formulate queries using SQL tools for DML/DDL/DCL commands
LIST OF EXPERMENTS
(Minimum Ten Experiments to be conducted)

LIST OF EXPERIMENTS
1. Creation, altering and dropping of tables and inserting rows into a table (use constraints while creating
tables) examples using SELECT command.
2. Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, INTERSECT,
EXCEPT operators.. Example:, Select the roll number and name of the student who secured fourth rank in
the class.
3. Using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING, Creation
and dropping of Views.
4. Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation,
lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day,
add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date)
5. i) Creation of simple PL/SQL program which includes declaration section, executable section and
exception –handling section (Ex. Student marks can be selected from the table and printed for those who
secured first class and an exception can be raised if no records were found) ii) Implement COMMIT,
ROLLBACK and SAVEPOINT in PL/SQL block.
6. Develop a program that includes the features NESTED IF, CASE and CASE expression.
7. Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR
Handling, BUILT –IN Exceptions, USE defined Exceptions.

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 2

8. Program development using a creation of procedures, passing parameters IN and OUT of
PROCEDURES.
9. Program development using the creation of stored functions, invoke functions in SQL Statements and
write complex functions.
10. Program development using creation of package specification, package bodies, private objects, package
variables and cursors and calling stored packages.
11. Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR, WHERE
CURRENT of clause and CURSOR variables.
12. Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and INSTEAD
OF Triggers.

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 3

Name: _____________________________ Roll No: _______________

INDEX

[Link] DATE EXPERIMENT NAME SIGNATURE

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 4

EXP NO: DATE:
CREATION, ALTERING AND DROPPING OF TABLES AND INSERTING ROWS INTO A TABLE
(USE CONSTRAINTS WHILE CREATING TABLES) EXAMPLES USING SELECT COMMAND.

AIM: Queries for Creation, altering and dropping of tables and inserting rows into a table (use constraints
while creating tables) examples using SELECT command.

Procedure:

Step-1(Create student Table)

CREATE TABLE Students
(
Std_Id varchar(10) primary key,
Std_Name varchar(50),
Std_Age number,
Std_Address varchar(100),
Std_City varchar(50),
Std_Grade number
);
Step-2 Alter table by dropping column:
Alter table students drop column Std_Grade;
Table Altered.
Step-3 View the table structure:
Desc students;
Step-4 (insert data into table)
INSERT INTO Students VALUES (‘21781A0501’, ‘KARTHIK’, 19, ‘svcet’, ‘CHITTOOR’);
INSERT INTO Students VALUES (‘21781A0502’, ‘VISWA’,20, ‘RVS NAGAR’, ‘CHITTOOR’);
INSERT INTO StudentsVALUES (‘21781A0503’,‘GANESH’,21, ‘GANDHINAGAR’, ‘THIRUPATI’);
INSERT INTO Students VALUES (‘21781A0504’, ‘HARISH’, 20, ‘psr’, ‘BANGALORE’);
INSERT INTO Students VALUES (‘21781A0505’, ‘HEMA’, 19, ‘svcet’, ‘CHITTOOR’);
Step-5 ( perform the following Queries)
SELECT * FROM Students;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 5

SELECT Std_Id, Std_Name FROM Students;
SELECT distinct Std_Id, Age FROM Courses;
Step-6 Drop table:
Drop table Students;
Table dropped
Step-7 Check for table deletion using select:
Select * from Students;
Output: ERROR in line 1:
Table or view does not exist

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 6

EXP NO: DATE:

QUERIES (ALONG WITH SUB QUERIES) USING ANY, ALL, IN, EXISTS, NOT EXISTS, UNION,
INTERSECT, EXCEPT OPERATORS.. EXAMPLE:, SELECT THE ROLL NUMBER AND NAME OF
THE STUDENT WHO SECURED FOURTH RANK IN THE CLASS.

AIM:
Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, INTERSECT,
EXCEPT operators.. Example:, Select the roll number and name of the student who secured fourth rank in
the class.

Procedure:
Step-1(Create student Table)
CREATE TABLE Students
(
Std_Id varchar(10),
Std_Name varchar(50),
Std_Age number,
Std_Address varchar(100),
Std_City varchar(50)
);
Step-2 (insert data into table)
INSERT INTO Students VALUES (‘21781A0501’, ‘KARTHIK’, 19, ‘svcet’, ‘CHITTOOR’);
INSERT INTO Students VALUES (‘21781A0502’, ‘VISWA’,20, ‘RVS NAGAR’, ‘CHITTOOR’);
INSERT INTO StudentsVALUES (‘21781A0503’,‘GANESH’,21, ‘GANDHINAGAR’,
‘THIRUPATI’);
INSERT INTO Students VALUES (‘21781A0504’, ‘HARISH’, 20, ‘psr’, ‘BANGALORE’);
INSERT INTO Students VALUES (‘21781A0505’, ‘HEMA’, 19, ‘svcet’, ‘CHITTOOR’);
Step-3 (Create another table Courses )
CREATE TABLE Courses
(
Course_Id varchar(5),
Course_Name varchar(20),

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 7

Std_Id varchar(10),
Marks number
);
Step-4(Insert data for Courses registered Max of 3 courses for 5 students)
INSERT INTO Courses VALUES (‘CSE01’,‘CandDS’, ‘21781A0501’,65);
INSERT INTO Courses VALUES (‘CSE02’,‘Python’, ‘21781A0501’,75);
INSERT INTO Courses VALUES (‘CSE03’,‘OS’, ‘21781A0501’,85);

INSERT INTO Courses VALUES (‘CSE01’,‘CandDS’, ‘21781A0502’,75);

INSERT INTO Courses VALUES (‘CSE02’,‘Python’, ‘21781A0502’,85);
INSERT INTO Courses VALUES (‘CSE03’,‘OS’, ‘21781A0502’,67);

INSERT INTO Courses VALUES (‘CSE01’,‘CandDS’, ‘21781A0503’,54);

INSERT INTO Courses VALUES (‘CSE02’,‘Python’, ‘21781A0503’,64);
INSERT INTO Courses VALUES (‘CSE03’,‘OS’, ‘21781A0503’,77);

INSERT INTO Courses VALUES (‘CSE01’,‘CandDS’, ‘21781A0504’,74);

INSERT INTO Courses VALUES (‘CSE03’,‘OS’, ‘21781A0504’,61);

INSERT INTO Courses VALUES (‘CSE01’,‘CandDS’, ‘21781A0505’,74);

INSERT INTO Courses VALUES (‘CSE02’,‘Python’, ‘21781A0505’,64);
INSERT INTO Courses VALUES (‘CSE03’,‘OS’, ‘21781A0505’,87);

Step-5 ( perform the following Querries)

SELECT * FROM Students;
SELECT * FROM Courses;

SELECT Std_Id, Std_Name FROM Students;

SELECT distinct Std_Id, Course_Name FROM Courses;
SELECT Std_Id FROM Courses;

SELECT Std_Id, Std_Name FROM Students

WHERE Std_Id = ANY(SELECT Std_Id FROM Courses WHERE Marks > 65);

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 8

SELECT Std_Id, Std_Name FROM Students
WHERE Std_Id = ALL(SELECT Std_Id FROM Courses WHERE Marks > 65);

SELECT Std_Id, Std_Name FROM Students

WHERE Std_Id IN(SELECT DISTINCT Std_Id FROM Courses WHERE Marks > 65);

SELECT Std_Id, Std_Name FROM Students

WHERE EXISTS(SELECT DISTINCT Std_Id FROM Courses WHERE Marks > 65);

SELECT Std_Id FROM Students

UNION
SELECT Std_Id FROM Courses;

SELECT Std_Id FROM Students

UNION ALL
SELECT Std_Id FROM Courses;

SELECT Std_Id FROM Students

INTERSECT
SELECT Std_Id FROM Courses;

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 9

EXP NO: DATE:

Using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY,
HAVING, Creation and dropping of Views.

Aim: Using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING,
Creation and dropping of Views.

Procedure:
Step-1 (Create product table)
create table product(pno int, pname varchar(30), price float, quantity int);
Step-2 (Insert data into product table)
insert into product values(1, ‘Dairy milk’,60,2);
insert into product values(2, ‘Good day’,25,4);
insert into product values(3, ‘Boost’, 10,6);
insert into product values(4, ‘magg’,5,10);
insert into product values(5, ‘book’,20,20);
select * from product;
Step-3 (Execute the aggregate functions)
Count function
select count(price) from product;
select count(quantity) from product;
Sum function
select sum(price) from product;
select sum(quantity) from product;
Avg function
select avg(price) from product;
select avg(quantity) from product;
Max function
select max(price) from product;
select max(quantity) from product;
Min function
select min(price) from product;
select min(quantity) from product;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 10

Step-4 (Create an employee table)
Create table employee(e_id int,e_name varchar(20),dept varchar(10),e_sal float);
Step-5 (Insert data into employee table)
insert into employee values(1, ‘Raghav’, ‘CSE’,60000);
insert into employee values(2, ‘Mohan’, ‘CSE’,70000);
insert into employee values(3, ‘Abhai’, ‘EEE’,50000);
insert into employee values(4, ‘Nagraju’, ‘ECE’,65000);
Step-6 (Execute the group by, having clauses)
Select * from employee;
select dept, sum(e_sal) from employee group by dept;
select dept, sum(e_sal) from employee group by dept having sum(e_sal)>80000;
Step-7 (Creating views)
create table students(sid int,sname varchar(10));
create table marks(sid int,sub1 int,sub2 int,sub3 int);
Step-7 A (Insert data into students, marks tables using insert command)
insert into students values(101, ‘Kartheek’);
insert into students values(102, ‘Vijay’);
insert into students values(103, ‘Bhargav’);
insert into marks values(101, 50,60,70);
insert into marks values(102, 55,65,75);
insert into marks values(103, 75,70,80);
create view stu as
select [Link],[Link],marks.sub1,marks.sub2,marks.sub3
From students, marks where [Link]=[Link];
Select * from stu;
RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 11

EXP NO: DATE:

Queries using Conversion functions (to_char, to_number and to_date), string functions
(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date
functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round,
to_char, to_date)

Aim: Queries using Conversion functions (to_char, to_number and to_date), string functions
(Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions
(Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round)

Procedure:
Conversion functions
1.to_char
create table db(dob date);
select to_char(dob,'month dd,year’);

2.to_number
SQL> select to_number(234.87) from dual;

3.to_date
SQL> select to_date(‘jan-21-1998’, ‘month dd,yy’) from dual;

String functions
[Link]
SQL> select lower(‘RAM’) from dual;
[Link]
SQL> select upper(‘ram’) from dual;
[Link]
SQL> select concat(‘hello’, ‘sai’) from dual;
[Link]
SQL> select initcap(‘sai’) from dual;
[Link]
SQL> select length(‘sai’) from dual;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 12

[Link]
SQL> select instr(‘ruhanika’, ‘a’) from dual;
[Link]
SQL> select substr(‘ruhanika’,5) from dual;
[Link]
SQL> select lpad(‘ruhanika’, 10,’****’) from dual;
[Link]
SQL select rpad(‘ruhanika’,10,’****’) from dual:
[Link]
SQL select trim(‘ ruhanika’) from dual;
[Link]
SQL> select rtrim(‘ ruhanika ’) from dual;

Date functions
[Link]
SQL> select sysdate from dual;
2.last_day
SQL> select last_day(sysdate) from dual;
3.next_day
SQL> select next_day(‘20-nov-2015’,’friday’) from dual;
4.add_months
SQL> select add_months(sysdate,2) from dual;
5.months_between
SQL> select months_between(‘20-Jan-2022’, ‘20-May-2022’) from dual;
[Link]
SQL> select least(10,11,12) from dual;
SQL >select least(‘s’, ‘f’, ‘a’) from dual;
[Link]
SQL> select greatest(10,11,12) from dual;
SQL> select greatest(‘s’, ‘f’, ‘a’) from dual;
[Link]
SQL> select round(21.088) from dual;
SQL> select trim(21.088) from dual;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 13

 RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 14

EXP NO: DATE:
i) Creation of simple PL/SQL program which includes declaration section,
executable section and exception –handling section (Ex. Student marks
can be selected from the table and printed for those who secured first class
and an exception can be raised if no records were found)
ii) Implement COMMIT, ROLLBACK and SAVEPOINT in PL/SQL block.

Aim: i) Creation of simple PL/SQL program which includes declaration section, executable section and
exception –handling section (Ex. Student marks can be selected from the table and printed for those who
secured first class and an exception can be raised if no records were found) ii) Implement COMMIT,
ROLLBACK and SAVEPOINT in PL/SQL block.

Procedure:
Step-1: Create table Student
create table student (sid int, sname varchar(20), sclass varchar(10));

Step-2: Insert data into student table

insert into student values(1, ‘abhi’, ‘first’);
insert into student values(2, ‘sai’, ‘first’);
insert into student values(3, ‘krish’, ‘first’);

Step-3:write program which include declaration section, executable section and exception –handling
section
Program:
declare
stu_id number;
stu_name varchar(20);
cursor stu_cur is
select sid, sname
from student
where sclass=’first’;
Begin
Open stu_cur;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 15

Loop
fetch stu_cur into stu_id, stu_name;
exit when stu_cur%notfound;
dbms_output.put_line(‘student_id: ‘ || stu_id || ‘student_name:’ || stu_name);
end loop; close stu_cur;
end;
/

Output:
student id: 1student_name:abhi
student id: 2student_name:sai
student id: 5student_name:krish
PL/SQL procedure successfully completed

Prg-5 ii) Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT In
PL/SQL block.
Step-4 : Create table ‘stu’ and insert data into ‘stu’ table
create table stu(name varchar(10), branch varchar(10));
insert into stu values(‘sai’, ‘it’);
Savepoint h;
savepoint created

Step-5 Write program block

SQL> set serveroutput on;
SQL> begin
savepoint g;
insert into stu values(‘ruhi’, ‘cse’);
exception
when dup_val_on_index then
rollback to g;
commit;
end;
/

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 16

SQL>Rollback to h;
Rollback completed

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 17

EXP NO: DATE:
Develop a program that includes the features NESTED IF, CASE and CASE
expression.
Aim: Develop a program that includes the features NESTED IF, CASE and CASE expressions.
Procedure:
Syntax of Nested if
if (condition1) then
-- Executes when condition1 is true
if (condition2) then
-- Executes when condition2 is true
end if;
end if;

Program:
declare
name char(20):=’Sai’;
age number:= 20;
nationality char(10) :=’Indian’;

begin
if (age > 18) then
if (nationality = ’Indian’) then
dbms_output.put_line(name || ‘you are eligible to vote’ );
end if;
end if;
end;
/
Program:
declare
num1 number:= 10;
num2 number:= 20;
num3 number:= 20;

begin
if (num1 > num2) then
if num1 > num3 then

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 18

 dbms_output.put_line(num1 || ‘ is largest’ );
else
dbms_output.put_line(num3 || ‘ is largest’ );
end if ;
else
if num2 > num3 then
dbms_output.put_line(num2 || ‘ is largest’ );
else
dbms_output.put_line(num3 || ‘ is largest’ );
end if;
end;
/
Output:
PL/SQL procedure successfully completed

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 19

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 20

EXP NO: DATE:
Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions

Aim: Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR
Handling, BUILT –IN Exceptions, USER defined Exceptions.

Procedure:
Simple loop:
Program:
DECLARE
i NUMBER := 1;
BEGIN
LOOP
EXIT WHEN i>10;
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
END LOOP;
END;
/
Program:
declare
n number;
endvalue number;
sum1 number default 0;
begin
endvalue:= &endvalue ;
n:=1 ;
while(n<endvalue)
loop
sum1:=sum1+n;
n:= n+2;
end loop ;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 21

 dbms_output.put_line('sum of odd numbers between 1 and ‘ || endvalue || 'is' || sum1);
end;
/
Output:
Enter value for endvalue: 19
old 6: endvalue:=&endvalue ;
new 6: endvalue – 19;
sum of odd numbers between 1 and 19 is 81

PL/SQL procedure successfully completed.

Syntax of For loop:
FOR counter IN initial_value .. final_value LOOP
LOOP statements;
END LOOP;
Program :
DECLARE
VAR1 NUMBER;
BEGIN
VAR1:=10;
FOR VAR2 IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
END LOOP;
END;
/
OUTPUT:
10
20
30
40
50
60
70
80

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 22

 90
100
PL/SQL procedure successfully completed.
ERROR Handling, BUILT –IN Exceptions

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;

Create customer table (with Cid,Cname,Caddress attributes)

Program:
DECLARE
c_id [Link]%type := 5;
c_name [Link]%type;
c_addr [Link]%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 23

 EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
OUTPUT:
Name:
Address:
PL/SQL procedure successfully completed.

Program:
declare
name char(20):=’Sai’;
age number:= 20;
nationality char(10) :=’Indian’;
begin
if (age > 18) then
if (nationality = ’Indian’) then
dbms_output.put_line(name || ‘you are eligible to vote’ );
end if;
end if;
end;
/

Program:
declare
num1 number:= 10;
num2 number:= 20;
num3 number:= 20;
begin
if (num1 > num2) then

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 24

 if num1 > num3 then
dbms_output.put_line(num1 || ‘ is largest’ );
else
dbms_output.put_line(num3 || ‘ is largest’ );
end if ;
else
if num2 > num3 then
dbms_output.put_line(num2 || ‘ is largest’ );
else
dbms_output.put_line(num3 || ‘ is largest’ );
end if;
end;
/

Output:
PL/SQL procedure successfully completed

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 25

 EXP NO: DATE:

Program development using a creation of procedures, passing parameters IN and

OUT of PROCEDURES.
Aim: Program development using a creation of procedures, passing parameters IN and OUT of
PROCEDURES
Procedure:

Step-1: Create table enquiry

create table enquiry (enqno1 number, fname varchar2(30));
insert into enquiry values(111,'sai’);
insert into enquiry values(112, ‘sindhu');

Program:
create procedure findname(enquiryno1 IN number,fname1 OUT varchar2)
is
fname2 varchar2(30);
begin
select fname into fname2
from enquiry
where enqnol=enquiryno1;
fname1:=fname2;
exception
when no_data_found then
raise_application_error(-20100, ‘The given number is not present’);
end;
/

Calling Procedure:
declare
enqno2 number(5);
fname2 varchar2(30);
begin
enqno2:=111;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 26

 findname(enqno2, fname2);
dbms_output.put_line(fname2);
end;
/

OUTPUT: sai

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 27

 EXP NO: DATE:

Program development using the creation of stored functions, invoke functions in

SQL Statements and write complex functions.

Aim: Program development using the creation of stored functions, invoke functions in SQL Statements
and write complex functions.

Procedure:

Step-1: Create table dept

create table dept(deptno int, dname varchar(10));
insert into dept values(0505, 'Deepak');
Program:
create or replace function getname(dno number)
return varchar2 as
fname1 varchar2(30);
begin
select dname
into fname1
from dept
where deptno = dno;
return(fname1);
exception
when no_data_found then
raise_ application_error(-20100, ‘The dno is present’);
end;
/
Calling Function:
declare
fname2 varchar2(30);
deptno2 number(5);
begin
deptno:=0505;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 28

 fname2:= getname(dno);
dbms_output.put_line(fname2);
end;
/

Output: Deepak

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 29

EXP NO: DATE:

Program development using creation of package specification, package bodies,

private objects, package variables and cursors and calling stored packages.

Aim: Program development using creation of package specification, package bodies, private objects,
package variables and cursors and calling stored packages.

Procedure:

Step-1: create a table dept1

create table dept1(dname varchar2(10), deptno number);
insert into dept values('accounting',10);
insert into dept values('hr',20);
Step-2: create a table dept
create table dept(dno number, vt varchar2(10), dloc varcar2(20));
Step-3 : creating package header
create or replace package test
is
procedure savedept (dno in number, dloc in varchar);
end;
/
Step-4: creating package body
create or replace package body test
is
function getdno(dno in number)
return varchar
is
dnum varchar(20);
begin
select dname into dnum from dept
where deptno=dno;
return dnum;
end;

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 30

 procedure savedept (dno in number,dloc in varchar)
vt varchar(20)
begin
vt:=getno(dno);
insert into dept values(dno,vt,dloc);
exception
when dup_val_on_index then
raise_application_error(-2007, 'duplicate');
end;
end;
/
Step-5: Executing procedure:
exec [Link](10, ‘Chittoor’);
Step-6: Display the table
Select * from dept;

Step-1: Create table dept

create table dept(deptno int, dname varchar(10));
insert into dept values(0505, 'Deepak');
Program:
create or replace function getname(dno number)
return varchar2 as
fname1 varchar2(30);
begin
select dname
into fname1
from dept
where deptno = dno; return(fname1);
exception
when no_data_found then
raise_ application_error(-20100, ‘The dno is present’);
end;
/

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 31

Calling Function:
declare
fname2 varchar2(30);
deptno2 number(5);
begin
deptno:=0505;
fname2:= getname(dno);
dbms_output.put_line(fname2);
end;
/
Output: Deepak

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 32

EXP NO: DATE:

Develop programs using features parameters in a CURSOR, FOR UPDATE

CURSOR, WHERE CURRENT of clause and CURSOR variables.

Aim: Develop programs using features parameters in a CURSOR, FOR UPDATE CURSOR, WHERE
CURRENT of clause and CURSOR variables.

Procedure:

Step-1: create an employ table, Insert data into employee table and retrieve the
name of employ whose salary is greater than 25000 by PL/SQL
Program:
create table employ(eid int,ename varchar2(20),salary int);
insert into employ values(101, ‘Raghav’, 26000);
insert into employ values(102, ‘Bharghav’, 36000);
insert into employ values(103, ‘Rajesh’, 16000);
Step-2 Program:
declare
emp_rec varchar(30);
cursor emp_cur is
select ename
from employ where salary>25000;
begin
open emp_cur;
loop
fetch emp_cur into emp_rec;
exit when emp_rec%notfound;
dbms_output.put_line(emp_rec);

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 33

 end loop;
close emp_cur;
end;
/
Output:
Raghav
Bhargav
PL/SQL procedure successfully completed.

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 34

EXP NO: DATE:

Develop Programs using BEFORE and AFTER Triggers, Row and Statement
Triggers and INSTEAD OF Triggers.
Aim: Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and
INSTEAD OF Triggers.

Procedure:

Step-1 Create Table Dept

Create table dept1(dname varchar2(10), deptno int);
Step-2: create a trigger:
Create or replace trigger trg2
after insert or delete or update
on dept1
for each row
when ([Link]>0)
begin
dbms_output.put_line(‘trigger fired’);
end;
/
Step-3 Insert:
Insert into dept1 values(‘CSE’,30);
OUTPUT:
trigger fired
I row created
Step-4 UPDATE:
update dept1 set deptno=19 where dname=’CSE’;

OUTPUT:
trigger fired
I row updated

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 35

Step-5 : DELETE:
Delete from dept1 where deptno=30;
OUTPUT:
trigger fired
I row deleted

RESULT:

SRI VENKATESWARA COLLEGE OF ENGINEERING AND TECHNOLOGY(AUTONOMOUS),CHITTOOR Page 36