CMS COLLEGE OF SCIENCE & COMMERCE

(AUTONOMOUS)
Chinnavedampatti-Coimbatore-641 049.
Affiliated to Bharathiar University
Reaccredited by NAAC at 'A' level with a cGPA of 3.53 out of 4
College with Potential for Excellence

Department of Information Technology

PRACTICAL RECORD
RELATIONAL DATABASE MANAGEMENT SYSTEM
OCTOBER 2018

II B.Sc. Computer Technology

2017- 2020 Batch
CMS COLLEGE OF SCIENCE & COMMERCE
(AUTONOMOUS)
Chinnavedampatti-Coimbatore-641 049.
Affiliated to Bharathiar University
Reaccredited by NAAC at 'A' level with a CGPA of 3.53 out of 4
College with Potential for Excellence

Register No: _____________________

Certified that the Bonafide record of work done by

Mr./Ms.__________________________

of____________________ during the year 2018-2019.

............................... ................................
Staff In-Charge Head of the Department

Submitted for the Practical Examination held on_______________ at CMS

College of Science & Commerce, Coimbatore-641 049.

......................... .........................
Internal Examiner External Examiner
 INDEX

S.NO DATE TITLE PAGENO SIGNATURE

1 COMPARISON OPERATOR

2 SET OPERATOR

3 LOGICAL OPERATOR

4 SORTING QUERIES

5 GROUP BY, SUBQURIES

6 BUILT IN OPERATIONS

7 FIBONACCI SERIES PROGRAM

8 FACTORIAL PROGRAM

9 TRIGGER PROGRAM

10 ELECTRICITY BILL PROGRAM

11 SPLITTING OF TABLE

12 JOINING OF TABLES
Ex.No: COMPARISON OPERATOR
Date:

AIM:

ALGORITHM:
CODING:
SQL> create table employ(emp_no number(4),emp_name varchar2(10),DOJ
varchar2(20),designation varchar2(10),dept_name varchar2(10),salary numeric(7));
Table created.
SQL> desc employ;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMP_NO NUMBER(4)
EMP_NAME VARCHAR2(10)
DOJ VARCHAR2(20)
DESIGNATION VARCHAR2(10)
DEPT_ NAME VARCHAR2(10)
SALARY NUMBER(7)

SQL> insert into employ

values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary);
Enter value for emp_no: 111
Enter value for emp_name: alex
Enter value for doj: 19-jan-2000
Enter value for designation: manager
Enter value for dept_name: it
Enter value for salary: 6000
old 1: insert into employe
values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary)
new 1: insert into employ values(111,'anu','19-jan-2000','manager','it',6000)

1 row created.

SQL> /
Enter value for emp_no: 222
Enter value for emp_name: aravind
Enter value for doj: 16-jul-2000
Enter value for designation: deen
Enter value for dept_name: it
Enter value for salary: 2000
old 1: insert into employ
values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary)
new 1: insert into employ values(222,'abi','16-jul-2000','deen','it',2000)

1 row created.

SQL> /
Enter value for emp_no: 333
Enter value for emp_name: Dinesh
Enter value for doj: 17-mar-2000
Enter value for designation: staff
Enter value for dept_name: it
Enter value for salary: 7000
old 1: insert into employ
values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary)
new 1: insert into employ values(333,'guna','17-mar-2000','staff','it',7000)

1 row created.

SQL> /
Enter value for emp_no: 444
Enter value for emp_name: Vijay
Enter value for doj: 17-jan-2000
Enter value for designation: deen
Enter value for dept_name: it
Enter value for salary: 9000
old 1: insert into employe
values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary)
new 1: insert into employ values(444,'nave','17-jan-2000','deen','bcom',9000)

1 row created.

SQL> /
Enter value for emp_no: 555
Enter value for emp_name: Mani
Enter value for doj: 15-jul-2000
Enter value for designation: clreak
Enter value for dept_name: it
Enter value for salary: 7000
old 1: insert into employe
values(&emp_no,'&emp_name','&doj','&designation','&dept_name',&salary)
new 1: insert into employe values(555,'valar','15-jul-2000','accountar','bcom', 7000)

1 row created

SQL>select * from employ;

EMP_NO EMP_NAME DOJ DESIGNATION DEPT_NAME SALARY

--------- ---------- -------------------- ---------- ---------- --------- ----------- ---------------------
111 alex 19-jan-2000 manager it 6000
222 aravind 16-jul-2000 deen it 2000
333 dinesh 17-mar-2000 staff it 7000
444 vijay 17-jan-2000 deen it 9000
555 mani 15-jul-2000 clreak it 7000

SQL> select dept_name,sum(salary) from employ group by dept_name;

DEPT_NAME SUM(SALARY)
---------- -----------
it 16000
it 15000

SQL>select dept_name,sum(salary) from employ group by dept_name;

DEPT_NAME SUM(SALARY)
---------- -----------
it 16000
it 15000

SQL>select emp_no,emp_name,dept_name from employ where salary>=5000;

EMP_NO EMP_NAME DEPT_NAME
--------- ---------- ----------
111 alex it
333 aravind it
444 vijay it
555 mani it

SQL> select * from employ where salary between 5000 and 10000;
EMP_NO EMP_NAME DOJ DESIGNATION DEPT_NAME SALARY
--------- ---------- -------------------- ---------- ---------- ---------
111 alex 19-jan-2000 manager it 6000
333 aravind 17-mar-2000 staff it 7000
444 vijay 17-jan-2000 deen it 9000
555 mani 15-jul-2000 clreak it 7000

SQL> select * from employ where emp_name like'm%';

EMP_NO EMP_NAME DOJ DESIGNATION DEPT_NAME SALARY
--------- ---------- -------------------- ---------- ---------- --------
555 mani 15-jul-2000 clreak it 7000
RESULT:

Ex.No: SET OPERATOR

Date:

AIM:

ALGORITHM:
CODING:
SQL> create table student (stu_regno numeric(6),sname varchar2(20),m1 number(3),m2
number(3),m3 number(3),maverage numeric(5,2));
Table created.
SQL> insert into student values(&regno,'&sname',&m1,&m2,&m3,&maverage);
Enter value for regno: 1111
Enter value for sname: abirami
Enter value for m1: 90
Enter value for m2: 90
Enter value for m3: 90
Enter value for maverage: 90
old 1: insert into student values(&regno,'&sname',&m1,&m2,&m3,&maverage)
new 1: insert into student values(1111,'abirami',90,90,90,90)

1 row created.

SQL> /
Enter value for regno: 2222
Enter value for sname: vani
Enter value for m1: 89
Enter value for m2: 89
Enter value for m3: 89
Enter value for maverage: 89
old 1: insert into student values(&regno,'&sname',&m1,&m2,&m3,&maverage)
new 1: insert into student values(2222,'vani',89,89,89,89)

1 row created.

SQL> /
Enter value for regno: 3333
Enter value for sname: anu
Enter value for m1: 45
Enter value for m2: 45
Enter value for m3: 45
Enter value for maverage: 45
old 1: insert into student values(&regno,'&sname',&m1,&m2,&m3,&maverage)
new 1: insert into student values(3333,'anu',45,45,45,45)

1 row created.

SQL> select * from student where m1>=40 and m2>=40 and m3>=40;

STU_REGNO SNAME M1 M2 M3 MAVERAGE

--------- -------------------- --------- --------- --------- ---------
1111 abirami 90 90 90 90
2222 vani 89 89 89 89
3333 anu 45 45 45 45
SQL> select * from student where maverage=80 or maverage=90;

STU_REGNO SNAME M1 M2 M3 MAVERAGE

--------- -------------------- --------- --------- --------- ---------
1111 abirami 90 90 90 90

SQL> select * from student where maverage in(89);

STU_REGNO SNAME M1 M2 M3 MAVERAGE

--------- -------------------- --------- --------- --------- ---------
2222 vani 89 89 89 89
SQL> select * from student where mvaerage not null;
no rows selected.

RESULT:
Ex.No: LOGICAL OPERTOR
Date:

AIM:

ALGORITHM:
CODING:
SQL> create table account (custno varchar2(4),custname varchar2(15),accno
varchar2(4),balamount number(10));
Table created.

SQL>desc account;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
CUSTNO VARCHAR2(4)
CUSTNAME VARCHAR2(15)
ACCNO VARCHAR2(4)
BALAMOUNT NUMBER(10)

SQL> insert into account values('&custno','&custname','&accno',&balamount);

Enter value for custno: 1
Enter value for custname: tamil
Enter value for accno: m001
Enter value for balamount: 6000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('1','tamil','m001',6000)

1 row created.

SQL> /
Enter value for custno: 2
Enter value for custname: velu
Enter value for accno: m002
Enter value for balamount: 8000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('2','velu','m002',8000)

1 row created.

SQL> /
Enter value for custno: 3
Enter value for custname: valar
Enter value for accno: m003
Enter value for balamount: 7000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('3','valar','m003',7000)

1 row created.

SQL> /
Enter value for custno: 4
Enter value for custname: tamil
Enter value for accno: m004
Enter value for balamount: 5000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('4','tamil','m004',5000)
1 row created.

SQL> /
Enter value for custno: 5
Enter value for custname: valar
Enter value for accno: m005
Enter value for balamount: 7000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('5','valar','m005',7000)

1 row created.

SQL> /
Enter value for custno: 6
Enter value for custname: harini
Enter value for accno: m006
Enter value for balamount: 4000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('6','harini','m006',4000)

1 row created.

SQL> /
Enter value for custno: 7
Enter value for custname: priya
Enter value for accno: m007
Enter value for balamount: 2000
old 1: insert into account values('&custno','&custname','&accno',&balamount)
new 1: insert into account values('7','priya','m007',2000)

1 row created.

SQL> select * from account;

CUSTNO CUSTNAME ACCNO BALAMOUNT

---- --------------- ---- ---------
1 tamil m001 6000
2 velu m002 8000
3 valar m003 7000
4 tamil m004 5000
5 valar m005 7000
6 harini m006 4000
7 priya m007 2000

7 rows selected.

SQL> create table loan (custno varchar2(4),custname varchar2(15),loan varchar2(4),loanamount

number
(6));

Table created.
SQL> insert into loan values('&custno','&custname','&loan',&loanamount);
Enter value for custno: 01
Enter value for custname: tamil
Enter value for loan: m001
Enter value for loanamount: 8000
old 1: insert into loan values('&custno','&custname','&loan',&loanamount)
new 1: insert into loan values('01','tamil','m001',8000)

1 row created.

SQL> /
Enter value for custno: 02
Enter value for custname: jaya
Enter value for loan: m002
Enter value for loanamount: 6000
old 1: insert into loan values('&custno','&custname','&loan',&loanamount)
new 1: insert into loan values('02','jaya','m002',6000)

1 row created.

SQL> /
Enter value for custno: 03
Enter value for custname: tamil
Enter value for loan: m003
Enter value for loanamount: 6000
old 1: insert into loan values('&custno','&custname','&loan',&loanamount)
new 1: insert into loan values('03','tamil','m003',6000)

1 row created.

SQL> /
Enter value for custno: 04
Enter value for custname: valar
Enter value for loan: m004
Enter value for loanamount: 7000
old 1: insert into loan values('&custno','&custname','&loan',&loanamount)
new 1: insert into loan values('04','valar','m004',7000)

1 row created.

SQL> /
Enter value for custno: 05
Enter value for custname: raja
Enter value for loan: m005
Enter value for loanamount: 4000
old 1: insert into loan values('&custno','&custname','&loan',&loanamount)
new 1: insert into loan values('05','raja','m005',4000)

1 row created.

SQL> select * from loan;

CUSTNO CUSTNAME LOAN LOANAMOUNT
---- --------------- ---- ----------
01 tamil m001 8000
02 jaya m002 6000
03 tamil m003 6000
04 valar m004 7000
05 raja m005 4000

SQL> select custname from account

2 union
3 selectcustname from loan;

CUSTNAME
---------------
harini
jaya
priya
raja
tamil
valar
velu

7 rows selected.

SQL>select custnamefrom account

2 union all
3 select custname from loan;

CUSTNAME
---------------
tamil
velu
valar
tamil
valar
harini
priya
tamil
jaya
tamil
valar
raja

12 rows selected.

SQL> select custname from account

2 intersect
3 select custname from loan;

CUSTNAME
---------------
tamil
valar

SQL> select custname from account

2 minus
3 select custname from loan;

CUSTNAME
---------------
harini
priya

RESULT:
Ex.No: SORTING QUERIES
Date:

AIM:

ALGORITHM:
CODING:
SQL> create table branch(branchcodevarchar2(5),custnamevarchar2(15),branchname
varchar2(20),balance number(5));
Table created.
SQL> desc branch;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
BRANCHCODE VARCHAR2(5)
CUSTNAME VARCHAR2(15)
BRANCHNAME VARCHAR2(20)
BALANCE NUMBER(5)

SQL> insert into branch values('&branchcode','&custname','&branchnamre',&balance);

Enter value for branchcode: a01
Enter value for custname: malini
Enter value for branchnamre: madukkarai
Enter value for balance: 7000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a01','malini','madukkarai',7000)

1 row created.

SQL> /
Enter value for branchcode: a02
Enter value for custname: uma
Enter value for branchnamre: madhurai
Enter value for balance: 3000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a02','uma','madhurai',3000)

1 row created.

SQL> /
Enter value for branchcode: a03
Enter value for custname: ashok
Enter value for branchnamre: kuruchi
Enter value for balance: 6000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a03','ashok','kuruchi',6000)

1 row created.

SQL> /
Enter value for branchcode: a04
Enter value for custname: roops
Enter value for branchnamre: hariyana
Enter value for balance: 4000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a04','roops','hariyana',4000)

1 row created.

SQL> /
Enter value for branchcode: a05
Enter value for custname: anandhu
Enter value for branchnamre: ukkadam
Enter value for balance: 9000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a05','anandhu','ukkadam',9000)

1 row created.

SQL> /
Enter value for branchcode: a06
Enter value for custname: deniyal
Enter value for branchnamre: ganapathy
Enter value for balance: 3000
old 1: insert into branch values('&branchcode','&custname','&branchnamre',&balance)
new 1: insert into branch values('a06','deniyal','ganapathy',3000)

1 row created.

SQL> select * from branch;

BRANCH CUSTNAME BRANCHNAME BALANCE

----- --------------- -------------------- ---------
a01 malini madukkarai 7000
a02 uma madhurai 3000
a03 ashok Kuruchi 6000
a04 roops hariyana 4000
a05 anandhu ukkadam 9000
a06 deniyal ganapathy 3000

6 rows selected.

SQL> select * from branch order by custname;

BRANCH CUSTNAME BRANCHNAME BALANCE

----- --------------- -------------------- ---------
a05 anandhu ukkadam 9000
a03 ashok kuruchi 6000
a06 deniyal ganapathy 3000
a01 malini madukkarai 7000
a04 roops hariyana 4000
a02 uma madhurai 3000

6 rows selected.

SQL> select * from branch order by branchname, custname;

BRANCH CUSTNAME BRANCHNAME BALANCE
----- --------------- -------------------- ---------
a06 deniyal ganapathy 3000
a04 roops hariyana 4000
a03 ashok kuruchi 6000
a02 uma madhurai 3000
a01 malini madukkarai 7000
a05 anandhu ukkadam 9000

6 rows selected.

SQL> select * from branch order by balance desc;

BRANCH CUSTNAME BRANCHNAME BALANCE

----- --------------- -------------------- ---------
a05 anandhu ukkadam 9000
a01 malini madukkarai 7000
a03 ashok kuruchi 6000
a04 roops hariyana 4000
a02 uma madhurai 3000
a06 deniyal ganapathy 3000

6 rows selected;

SQL> select * from branch order by balance;

BRANCH CUSTNAME BRANCHNAME BALANCE

----- --------------- -------------------- ---------
a02 uma madhurai 3000
a06 deniyal ganapathy 3000
a04 roops hariyana 4000
a03 ashok kuruchi 6000
a01 malini madukkarai 7000
a05 anandhu ukkadam 9000

6 rows selected.

SQL> select * from branch where branchname='ukkadam' order by custname;

BRANCH CUSTNAME BRANCHNAME BALANCE

----- --------------- -------------------- ---------
a05 anandhu ukkadam 9000

SQL> selectcustname,balance from branch where branchname='kuruchi' order by balance desc;

CUSTNAME BALANCE
--------------- ---------
ashok 6000

SQL>select custname,balance from branch where branchname='kuruchi' order by balance;

CUSTNAME BALANCE
--------------- ---------
ashok 6000

RESULT:
Ex.No: GROUP BY, SUBQUERIES
Date:

AIM:

ALGORITHM:
SQL> create table emplye (empid varchar(5),emp_name varchar(9),manager_id varchar(6),doj
date,salary
number(9),dep_id varchar(6));

Table created

SQL> select *from emply;

EMPI EMP_NAME DESIGATI MANAGER_ DOJ SALARY DEP_ID

---- -------- -------- -------- --------- --------- ------
1001 alex manager a001 15-JAN-95 25000 d001
1002 aravind manager a002 25-FEB-95 23000 d002
1003 vinoth owner a003 01-APR-95 26000 d003
1004 mani manager a004 30-MAR-95 24500 d004
1005 vijay manager a005 16-JUN-95 22500 doo5

SQL> select count(*),avg(salary),dep_id,desigation from emply group by dep_id,desigation;

COUNT(*) AVG(SALARY) DEP_ID DESIGATI

--------- ----------- ------ --------
1 25000 d001 manager
1 23000 d002 manager
1 26000 d003 owner
1 24500 d004 manager
1 22500 doo5 manager

SQL> select dep_id,count(*) from emply group by dep_id having count(*)<4;

DEP_ID COUNT(*)
------ ---------
d001 1
d002 1
d003 1
d004 1
doo5 1

SQL> select dep_id,desigation,count(*) from emply group by dep_id,desigation;

DEP_ID DESIGATI COUNT(*)

------ -------- ---------
d001 manager 1
d002 manager 1
d003 owner 1
d004 manager 1
doo5 manager 1

SQL> select empid,emp_name,desigation, doj, months_between(sysdate,doj)"EXPERIENCE"

from emply where

2 manager_id in (select manager_id from emply where desigation = 'manager');

EMPI EMP_NAME DESIGATI DOJ EXPERIENCE
---- -------- -------- --------- ----------
1001 alex manager 15-JAN-95 284.65966
1002 aravind manager 25-FEB-95 283.33708
1004 mani manager 30-MAR-95 282.17579
1005 vijay manager 16-JUN-95 279.62741

RESULT:
Ex.No: BUILT IN OPERATIONS
Date:

AIM:

ALGORITHM:
SQL> create table emp2z(empnovarchar(5),empnamevarchar(10),dojdate,deptvarchar(10),salary
number
(10,2));

Table created.

SQL>desc emp2z;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO VARCHAR2(5)
EMPNAME VARCHAR2(10)
DOJ DATE
DEPT VARCHAR2(10)
SALARY NUMBER(10,2)

SQL> insert into emp2z values('001','john','12-jan-2000','hr',10000.00);

1 row created

SQL> insert into emp2z values('002','tamil','13-jan-2000','management',20000.00);

1 row created.

SQL> insert into emp2z values('003','anandhu','14-jan-2000','it',30000.00);

1 row created.

SQL> insert into emp2z values('004','roops','15-jan-2000','bca',40000.00);

1 row created.

SQL> insert into emp2z values('005','sajee','16-jan-2000','b.com',50000.00);

1 row created.

SQL> select * from emp2z;

EMPNO EMPNAME DOJ DEPT SALARY

----- ---------- --------- ---------- ---------
001 john 12-JAN-00 hr 10000
002 tamil 13-JAN-00 management 20000
003 anandhu 14-JAN-00 it 30000
004 roops 15-JAN-00 bca 40000
005 sajee 16-JAN-00 b.com 50000

SQL> select upper(empname) "uppercase" from emp2z;

uppercase
----------
JOHN
TAMIL
ANANDHU
ROOPS
SAJEE

SQL> select lower (empname) from emp2z;

LOWER(EMPN
----------
john
tamil
anandhu
roops
sajee

SQL> select lpad (empname,15,'*') from emp2z;

LPAD(EMPNAME,15
---------------
***********john
**********tamil
********anandhu
**********roops
**********sajee
SQL> select concat (empno,empname) from emp2z;

CONCAT(EMPNO,EM
---------------
001john
002tamil
003anandhu
004roops
005sajee

SQL> select rpad (empname,16,'*') from emp2z;

RPAD(EMPNAME,16,
----------------
john************
tamil***********
anandhu*********
roops***********
sajee***********

SQL> select empname,length(empname) from emp2z;

EMPNAME LENGTH(EMPNAME)
---------- ---------------
john 4
tamil 5
anandhu 7
roops 5
sajee 5

SQL> select substr (empname,3,2) from emp2z;

SU
--
hn
mi
an
op
je

SQL> select months_between(doj,sysdate) from emp2z;

MONTHS_BETWEEN(DOJ,SYSDATE)
---------------------------
120.35166
120.38391
120.41617
120.44843
120.48069

SQL> select add_months(sysdate,4) from dual;

ADD_MONTH
---------
01-MAY-90

SQL> select empname,doj,add_months(doj,3) from emp2z where empno=2;

EMPNAME DOJ ADD_MONTH

---------- --------- ---------
tamil 13-JAN-00 13-APR-00
SQL> select sqrt(empno) from emp2z;

SQRT(EMPNO)
-----------
1
1.4142136
1.7320508
2
2.236068

SQL> select round (salary) from emp2z;

ROUND(SALARY)
-------------
10000
20000
30000
40000
 50000

SQL>select trunc (salary,1) from emp2z;

TRUNC(SALARY,1)
---------------
10000
20000
30000
40000
50000

SQL> select ceil (salary) from emp2z;

CEIL(SALARY)
------------
10000
20000
30000
40000
50000

SQL> select mod (salary,2) from emp2z;

MOD(SALARY,2)
-------------
0
0
0
0
0

SQL> select exp(empno) from emp2z;

EXP(EMPNO)
----------
2.7182818
7.3890561
20.085537
54.59815
148.41316

SQL> select power (empno,3) from emp2z;

POWER(EMPNO,3)
--------------
1
8
27
 64
125

RESULT:
Ex.No: FIBONACCI SERIES PROGRAM
Date:

AIM:

ALGORITHM:
SQL> declare
2 a number;
3 b number;
4 c number;
5 n number;
6 i number;
7 begin
8 n:=&number;
9 a:=0;
10 b:=1;
11 dbms_output.put_line(a);
12 dbms_output.put_line(b);
13 i:=2;
14 loop
15 c:=a+b;
16 a:=b;
17 b:=c;
18 dbms_output.put_line(c);
19 i:=i+1;
20 exit when i=n;
21 end loop;
22 end;
23 /
Enter value for number: 5
old 8: n:=&number;
new 8: n:=5;

PL/SQL procedure successfully completed.

SQL> set serveroutput on;

SQL> /
Enter value for number: 5
old 8: n:=&number;
new 8: n:=5;
0
1
1
2
3

PL/SQL procedure successfully completed.

RESULT:
Ex.No: FACTORIAL PROGRAM
Date:

AIM:

ALGORITHM:
SQL> declare
2 a number;
3 b number;
4 c number;
5 n number;
6 i number;
7 begin
8 n:=&number;
9 a:=0;
10 b:=1;
11 dbms_output.put_line(a);
12 dbms_output.put_line(b);
13 i:=2;
14 loop
15 c:=a+b;
16 a:=b;
17 b:=c;
18 dbms_output.put_line(c);
19 i:=i+1;
20 exit when i=n;
21 end loop;
22 end;
23 /
Enter value for number: 5
old 8: n:=&number;
new 8: n:=5;
SQL> /
Enter value for number: 5
old 8: n:=&number;
new 8: n:=5;
0
1
1
2
3

PL/SQL procedure successfully completed.

RESULT:
Ex.No: TRIGGER PROGRAM
Date:

AIM:

ALGORITHM:
SQL> create table bcom(reg_no varchar2(10),name varchar2(15),mark1 number(3),mark2
number(3),mark3 number(3));
Table created.

SQL> create or replace trigger block1z

2 before insert on bcom
3 for each row
4 begin
5 if :new.mark1>100 or :new.mark2>100 or :new.mark3>100
6 or :new.mark1<0 or :new.mark2<0 or :new.mark3<0 then
7 raise_application_error(-20002,'invalid marks');
8 end if;
9 end;
10 /

Trigger created.

SQL> insert into bcomvalues('&reg_no','&name',&mark1,&mark2,&mark3);

Enter value for reg_no: 17bbit010
Enter value for name: tamil
Enter value for mark1: 100
Enter value for mark2: 90
Enter value for mark3: 78
old 1: insert into bcom values('&reg_no','&name',&mark1,&mark2,&mark3)
new 1: insert into bcom values('17bbit010','tamil',100,90,78)

1 row created.

SQL> /
Enter value for reg_no: 17bbit210
Enter value for name: anandhi
Enter value for mark1: 90
Enter value for mark2: 102
Enter value for mark3: 103
old 1: insert into bcom values('&reg_no','&name',&mark1,&mark2,&mark3)
new 1: insert into bcom values('17bbit210','anandhi',90,102,103)
insert into bcom values('17bbit210','anandhi',90,102,103)
*
ERROR at line 1:
ORA-20002: invalid marks
ORA-06512: at "BSCIT210.BLOCK1Z", line 4
ORA-04088: error during execution of trigger 'BSCIT210.BLOCK1Z'

SQL> /
Enter value for reg_no: 17bbit21
Enter value for name: roops
Enter value for mark1: 90
Enter value for mark2: 80
Enter value for mark3: 90
old 1: insert into bcom values('&reg_no','&name',&mark1,&mark2,&mark3)
new 1: insert into bcom values('17bbit21','roops',90,80,90)

1 row created.

RESULT:

Ex.No: ELECTRICITY BILL PROGRAM

Date:

AIM:

ALGORITHM:
 declare
cursor eb is select *from ebill;
e number;
begin
for e in eb
loop
if(e.units>500)then
e.amount:=((e.units-500)*3)+600+100+50;
elsif(e.units>200)then
e.amount:=((e.units-200)*2)+100+50;
elsif(e.units>100)then
e.amount:=((e.units-100)*1)+50;
else
e.amount:=e.units*0.5;
end if;
update ebill set amount=e.amount where custno=e.custno;
end loop;
end;

SQL> create table e_bill1(custno varchar2(4),custname varchar2(15),amoun

));

Table created.

SQL> insert into e_bill1 values('&custno','&custname',&amount,&units);

Enter value for custno: 01
Enter value for custname: tamil
Enter value for amount: 0
Enter value for units: 200
old 1: insert into e_bill1 values('&custno','&custname',&amount,&units
new 1: insert into e_bill1 values('01','tamil',0,200)

1 row created.

SQL> /
Enter value for custno: 02
Enter value for custname: anandhi
Enter value for amount: 0
Enter value for units: 100
old 1: insert into e_bill1 values('&custno','&custname',&amount,&units
new 1: insert into e_bill1 values('02','anandhi',0,100)

1 row created.

SQL> /
Enter value for custno: 03
Enter value for custname: athul
Enter value for amount: 0
Enter value for units: 400
old 1: insert into e_bill1 values('&custno','&custname',&amount,&units
new 1: insert into e_bill1 values('03','athul',0,400)

1 row created.

SQL> /
Enter value for custno: 03
Enter value for custname: uma
Enter value for amount: 0
Enter value for units: 500
old 1: insert into e_bill1 values('&custno','&custname',&amount,&units
new 1: insert into e_bill1 values('03','uma',0,500)

1 row created.
SQL> select * from e_bill1;

CUST CUSTNAME AMOUNT UNITS

---- --------------- --------- --------- ----------- --------
01 tamil 0 200
02 anandhi 0 100
03 athul 0 400
03 uma 0 500

declare
cursor eb is select *from ebill;
e number;
begin
for e in eb
loop
if(e.units>500)then
e.amount:=((e.units-500)*3)+600+100+50;
elsif(e.units>200)then
e.amount:=((e.units-200)*2)+100+50;
elsif(e.units>100)then
e.amount:=((e.units-100)*1)+50;
else
e.amount:=e.units*0.5;
end if;
update ebill set amount=e.amount where custno=e.custno;
end loop;
end;

SQL> select * from e_bill1;

CUST CUSTNAME AMOUNT UNITS
---- --------------- --------- ---------
01 tamil 200
02 anandhi 100
03 athul 400
04 uma 500
RESULT:
Ex.No: SPLITTING OF TABLE
Date:

AIM:

ALGORITHM:
CODEING:

SQL> create table student(regno varchar2(8),name varchar2(15),result varchar2(4));

Table created.

SQL> insert into student values('&regno','&name','&result');

Enter value for regno: 17bct01
Enter value for name: tamil
Enter value for result: pass
old 1: insert into student values('&regno','&name','&result')
new 1: insert into student values('17bbit01','tamil','pass')

1 row created.

SQL> /
Enter value for regno: 17bct02
Enter value for name: velu
Enter value for result: fail
old 1: insert into student values('&regno','&name','&result')
new 1: insert into student values('17bbit02','velu','fail')

1 row created.

SQL> /
Enter value for regno: 17bctt03
Enter value for name: valar
Enter value for result: pass
old 1: insert into student values('&regno','&name','&result')
new 1: insert into student values('17bbit03','valar','pass')

1 row created.

SQL> /
Enter value for regno: 17bct04
Enter value for name: ammu
Enter value for result: pass
old 1: insert into student values('&regno','&name','&result')
new 1: insert into student values('17bbit04','ammu','pass')
1 row created.

SQL> select * from student;

REGNO NAME RESU

-------- --------------- ----
17bct01 tamil pass
17bct02 velu fail
17bct03 valar pass
17bct04 ammu pass

SQL> create table pass(regno varchar2(8),name varchar2(15),resukt varchar2(4));

Table created.
SQL> insert into pass values('&regno','&name','&result');
Enter value for regno: 17bct11
Enter value for name: guna
Enter value for result: fail
old 1: insert into pass values('&regno','&name','&result')
new 1: insert into pass values('17bbit11','guna','fail')

1 row created.

SQL> /
Enter value for regno: 17bct12
Enter value for name: kamal
Enter value for result: pass
old 1: insert into pass values('&regno','&name','&result')
new 1: insert into pass values('17bbit12','kamal','pass')

1 row created.

SQL> /
Enter value for regno: 17bct13
Enter value for name: roops
Enter value for result: pass
old 1: insert into pass values('&regno','&name','&result')
new 1: insert into pass values('17bbit13','roops','pass')

1 row created.

SQL> /
Enter value for regno: 17bct14
Enter value for name: sajee
Enter value for result: pass
old 1: insert into pass values('&regno','&name','&result')
new 1: insert into pass values('17bbit14','sajee','pass')

1 row created.

SQL> select * from pass;

REGNO NAME RESU

-------- --------------- ----
17bct11 guna fail
17bct12 kamal pass
17bct13 roops pass
17bct14 sajee pass

SQL> create table fail(regno varchar2(8),name varchar2(15),result varchar2(4));

Table created.

SQL> insert into fail values('*regno','&name','&result');

Enter value for name:
Enter value for result:
old 1: insert into fail values('*regno','&name','&result')
new 1: insert into fail values('*regno','','')

1 row created.

SQL> insert into fail2w values('&regno','&name','&result');

Enter value for regno: 17bbit11
Enter value for name: tamil
Enter value for result: pass
old 1: insert into fail2w values('&regno','&name','&result')
new 1: insert into fail2w values('17bbit11','tamil','pass')

1 row created.

SQL> /
Enter value for regno: 17bbit13
Enter value for name: harini
Enter value for result: pass
old 1: insert into fail2w values('&regno','&name','&result')
new 1: insert into fail2w values('17bbit13','harini','pass')

1 row created.

SQL> /
Enter value for regno: 17bbit14
Enter value for name: priya
Enter value for result: pass
old 1: insert into fail2w values('&regno','&name','&result')
new 1: insert into fail2w values('17bbit14','priya','pass')

1 row created.

SQL> /
Enter value for regno: 17bbit15
Enter value for name: sajee
Enter value for result: fail
old 1: insert into fail2w values('&regno','&name','&result')
new 1: insert into fail2w values('17bbit15','sajee','fail')

1 row created.

SQL> select * from fail2w;

REGNO NAME RESU

-------- --------------- ----
*regno
17bbit11 tamil pass
17bbit13 harini pass
17bbit14 priya pass
17bbit15 sajee fail
SQL> declare
2 cursor stud is select * from student;
3 s number;
4 begin
5 for s in stud
6 loop
7 ifs.result='pass'then
8 insert into pass values(s.regno,s.name,s.result);
9 else
10 insert into fail values(s.regno,s.name,s.result);
11 end if;
12 end loop;
13 end;
14 /
Procedure successfully completed.
RESULT:
Ex.No: JOINING OF TABLES
Date:

AIM:

ALGORITHM:
CODEING:

SQL> create table first4z(regno varchar2(8),name varchar2(15),percentage number(5,2));

Table created.

SQL> insert into first4z values('&regno','&name','&percentage');

Enter value for regno: 17bct01
Enter value for name: tamil
Enter value for percentage: 89
old 1: insert into first4z values('&regno','&name','&percentage')
new 1: insert into first4z values('17bct01','tamil','89')

1 row created.

SQL> /
Enter value for regno: 17bct02
Enter value for name: harini
Enter value for percentage: 90
old 1: insert into first4z values('&regno','&name','&percentage')
new 1: insert into first4z values('17bct02','harini','90')

1 row created.

SQL> /
Enter value for regno: 17bct03
Enter value for name: priya
Enter value for percentage: 70
old 1: insert into first4z values('&regno','&name','&percentage')
new 1: insert into first4z values('17bct03','priya','70')

1 row created.

SQL> /
Enter value for regno: 17bct04
Enter value for name: sajeena
Enter value for percentage: 60
old 1: insert into first4z values('&regno','&name','&percentage')
new 1: insert into first4z values('17bct04','sajeena','60')

1 row created.

SQL> /
Enter value for regno: 17bct05
Enter value for name: roops
Enter value for percentage: 40
old 1: insert into first4z values('&regno','&name','&percentage')
new 1: insert into first4z values('17bct05','roops','40')

1 row created.

SQL> select * from first4z;

REGNO NAME PERCENTAGE
-------- --------------- ---------- ---------------
17bct01 tamil 89
17bct02 harini 90
17bct03 priya 70
17bct04 sajeena 90
17bct05 roops 90

SQL> create table second4z(regno varchar2(8),address varchar2(35));

Table created.

SQL> insert into second4z values('&regno','&address');

Enter value for regno: 17bct01
Enter value for address: madukkarai
old 1: insert into second4z values('&regno','&address')
new 1: insert into second4z values('17bct01','madukkarai')

1 row created.

SQL> /
Enter value for regno: 17bct02
Enter value for address: kuruchi
old 1: insert into second4z values('&regno','&address')
new 1: insert into second4z values('17bct02','kuruchi')

1 row created.

SQL> /
Enter value for regno: 17bct03
Enter value for address: hosur
old 1: insert into second4z values('&regno','&address')
new 1: insert into second4z values('17bct03','hosur')

1 row created.

SQL> /
Enter value for regno: 17bct04
Enter value for address: sundharapuram
old 1: insert into second4z values('&regno','&address')
new 1: insert into second4z values('17bct04','sundharapuram')

1 row created.

SQL> /
Enter value for regno: 17bbit05
Enter value for address: krishnakiri
old 1: insert into second4z values('&regno','&address')
new 1: insert into second4z values('17bct05','krishnakiri')

1 row created.

SQL> select * from second4z;

REGNO ADDRESS
-------- -----------------------------------
17bct01 madukkarai
17bct02 kuruchi
17bct03 hosur
17bct04 sundharapuram
17bct05 krishnakiri

SQL> create table final4z(regno varchar2(8),name varchar2(15),percentage number(5,2),add

2(35));

Table created.

declare
cursor cur_sd1 is select f.regno,f.name,f.percentage,s.address from first4z f,
second4z s where f.regno=s.regno;
stud_regno first4z.regno%type;
stud_name first4z.name%type;
percentage first4z.percentage%type;
address second4z.address%type;
nodata exception;
begin
open cur_sd1;
loop
fetch cur_sd1 into stud_regno,stud_name,percentage,address;
if cur_sd1%notfound then
raisenodata;
else
insert into final4z values(stud_regno,stud_name,percentage,address);
end if;
end loop;
exception
whennodata then
if cur_sd1% rowcount=0 then
dbms_output.put_line('there is no more record');
else
dbms_output.put_line('program executes');
end if;
close cur_sd1;
end;
/

pl/sql procedure successfully completed.

SQL> select * from final;

regno name address percentage

-------------------------------------------------------------
17bct01 tamil madukkarai 89
17bct02 harini kuruchi 90
17bct03 sajee ukkadam 90
17bct04 roops gandhipuram 90
17bct05 priya hosur 90

RESULT: