DATABASE MANAGEMENT SYSTEMS LAB

LAB MANUAL
[Link]. – II Semester (R23)

FACULTY INCHARGE HEAD OF THE DEPARTMENT

 Database Management Systems Lab

Objectives:
· To teach the student database design and query and PL/SQL.
System/Software Requirements:
· Intel based desktop PC
· Mysql /Oracle latest version Recommended
PROGRAMS LIST:

1) Creation, altering and droping 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, NOTEXISTS, UNION,
INTERSET, Constraints.
Example:- Select the roll number and name of the student who secured fourth rank in the class.

3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and

MIN), GROUP BY, HAVING and 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)Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT in PL/SQL
block.

6) Develop a program that includes the features NESTED IF, CASE and CASE expression. The
program can be extended using the NULLIF and COALESCE functions.

7) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops using ERROR
Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE- APPLICATION ERROR.

8) Programs development using creation of procedures, passing parameters IN and OUT of

PROCEDURES.
9) Program development using 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

TEXT BOOKS :

1) ORACLE PL/SQL by example. Benjamin Rosenzweig, Elena Silvestrova, Pearson Education 3rd Edition
2) ORACLE DATA BASE LOG PL/SQL Programming SCOTT URMAN, Tata Mc-Graw Hill.
3) SQL & PL/SQL for Oracle 10g, Black Book, Dr.P.S. Deshpande.
4) Data Base Management System, Oracle SQL and PL/SQL, Pranab kumar Das Gupta, P Radha Krishna
1) Creation, altering and droping of tables and inserting rows into a table (use constraints
while creating tables) examples using SELECT command.

SQL> create table dept( deptno number(2) primary key,

2 dname varchar2(10) not null,
3 loc varchar2(8));

Table created.
SQL> desc dept
Name Null? Type
----------------------------------------- --------
DEPTNO NOT NULL NUMBER(2)
DNAME NOT NULL VARCHAR2(10)
LOC VARCHAR2(8)

SQL> insert into dept values(10,'accounting','newyork');

1 row created.
SQL> insert into dept values(20,'research','dallas');
1 row created.
SQL> insert into dept values(13,'sales','chicago');
1 row created.
SQL> insert into dept values(40,'operations','goston');
1 row created.

SQL> select *from dept;

DEPTNO DNAME LOC
---------- ---------- --------
10 accounting newyork
20 research dallas
13 sales chicago
40 operations goston

SQL> create table salgrade(grade number(5),losal number(6),hisal

number(6));
Table created.

SQL> desc salgrade

Name Null? Type
----------------------------------------- --------
GRADE NUMBER(5)
LOSAL NUMBER(6)
HISAL NUMBER(6)

SQL> insert into salgrade values(1,700,1200);

1 row created.
SQL> insert into salgrade values(2,1201,1400);
1 row created.
SQL> insert into salgrade values(3,1401,2000);
1 row created.
SQL> insert into salgrade values(4,2001,3000);
1 row created.
SQL> insert into salgrade values(5,3001,9999);
1 row created.
SQL> select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

SQL> create table emp (empno number(5) primary key,ename varchar(10) not
null,job varchar(10),mgr number(4),hiredate date,sal number(7,2)
check(sal>=500 and sal<=10000),comm number(7,2) default 1,deptno
number(2), foreign key(deptno) references dept);

Table created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7369,'smith','clerk',79
02,'17-dec-80',800,20);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7499,'allen','sale
sman',7698,'20-feb-81',1600,300,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7521,'ward','sales
man',7698,'22-feb-81',1250,500,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7566,'jones','manager',
7839,'2-apr-81',2975,20);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7654,'martin','sal
esman',7698,'28-sep-81',1250,1400,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7698,'blake','manager',
7839,'1-may-81',2850,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7782,'clark','manager',
7839,'9-jun-81',2450,10);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7788,'scott','analyst',
7566,'19-apr-87',3000,20);
1 row created.

SQL> insert into

emp(empno,ename,job,hiredate,sal,deptno)values(7839,'king','president','17
-nov-81',5000,10);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7844,'turner','sal
esman',7698,'8-sep-81',10000,0,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7876,'adems','clerk',77
88,'23-may-87',1100,20);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7900,'james','clerk',76
98,'3-dec-81',950,30);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7902,'ford','analyst',7
566,'3-dec-81',3000,20);
1 row created.

SQL> insert into

emp(empno,ename,job,mgr,hiredate,sal,deptno)values(7934,'miller','clerk',7
782,'23-jan-82',1300,10);
1 row created.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ----------
7369 smith clerk 7902 17-DEC-80 800 1 20
7499 allen salesman 7698 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 1 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 1 30
7782 clark manager 7839 09-JUN-81 2450 1 10
7788 scott analyst 7566 19-APR-87 3000 1 20
7839 king president 17-NOV-81 5000 1 10
7844 turner salesman 7698 08-SEP-81 10000 0 30
7876 adems clerk 7788 23-MAY-87 1100 1 20
7900 james clerk 7698 03-DEC-81 950 1 30
7902 ford analyst 7566 03-DEC-81 3000 1 20
7934 miller clerk 7782 23-JAN-82 1300 1 10

14 rows selected.

SQL> alter table emp add phno number(10);

Table altered.
SQL> desc emp;
Name Null? Type
------------------------------ -------- -----------------
EMPNO NOT NULL NUMBER(5)
ENAME NOT NULL VARCHAR2(10)
JOB NOT NULL VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
PHNO NUMBER(10)

SQL> alter table salgrade add constraint c1 unique(grade);

Table altered.

SQL> desc salgrade;

Name Null? Type
---------------------------- -------- ---------------------
GRADE NUMBER(1)
LOSAL NUMBER(6)
HISAL NUMBER(7)

SQL> alter table dept modify dname varchar2(20);

Table altered.

SQL> desc dept;

Name Null? Type
----------------------------- -------- ---------------------
DEPTNO NOT NULL NUMBER(10)
DNAME NOT NULL VARCHAR2(20)
LOC VARCHAR2(10)

SQL> alter table salgrade drop constraint c1;

Table altered.

SQL> desc salgrade;

Name Null? Type
------------------------------ -------- ----------------------------
GRADE NUMBER(1)
LOSAL NUMBER(6)
HISAL NUMBER(7)
SQL> select empno,ename,[Link],dname
2 from emp,dept
3 where [Link]=[Link];

EMPNO ENAME DEPTNO DNAME

---------- --------- ---------- ----------
7499 allen 30 sales
7521 ward 30 sales
7566 jones 20 research
7654 martin 30 sales
7698 blake 30 sales
 7782 clark 10 accounting
7788 scott 20 research
7839 king 10 accounting
7844 turner 30 sales
7876 adems 20 research
7902 ford 20 research
7934 miller 10 accounting
7369 smith 20 research
7900 james 30 sales

14 rows selected.

SQL> select empno,ename,[Link],dname from emp e,dept d where

[Link]=[Link];
EMPNO ENAME DEPTNO DNAME
---------- --------- ---------- ----------
7499 allen 30 sales
7521 ward 30 sales
7566 jones 20 research
7654 martin 30 sales
7698 blake 30 sales
7782 clark 10 accounting
7788 scott 20 research
7839 king 10 accounting
7844 turner 30 sales
7876 adems 20 research
7902 ford 20 research
7934 miller 10 accounting
7369 smith 20 research
7900 james 30 sales

14 rows selected.

SQL> select empno,ename,mgr,[Link],dname,loc from emp e,dept d where

[Link](+)=[Link];
EMPNO ENAME MGR DEPTNO DNAME LOC
---------- --------- ---------- ---------- ---------- --------
7499 allen 7698 30 sales chicago
7521 ward 7698 30 sales chicago
7566 jones 7839 20 research dallar
7654 martin 7698 30 sales chicago
7698 blake 7839 30 sales chicago
7782 clark 7839 10 accounting newyork
7788 scott 7566 20 research dallar
7839 king 10 accounting newyork
7844 turner 7698 30 sales chicago
7876 adems 7788 20 research dallar
7902 ford 7566 20 research dallar
7934 miller 7782 10 accounting newyork
7369 smith 7902 20 research dallar
7900 james 7698 30 sales chicago
operations bostan

15 rows selected.
SQL> select empno eno,ename worker from emp e,dept d where
[Link](+)=[Link];
ENO WORKER
---------- ---------
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7788 scott
7839 king
7844 turner
7876 adems
7902 ford
ENO WORKER
---------- ---------
7934 miller
7369 smith
7900 james
15 rows selected.

SQL> select [Link],[Link] from emp e,emp m where [Link]=[Link];

ENAME MGR
--------- ----------
allen 7698
ward 7698
jones 7839
martin 7698
blake 7839
clark 7839
scott 7566
king
turner 7698
adems 7788
ford 7566
ENAME MGR
--------- ----------
miller 7782
smith 7902
james 7698
14 rows selected.

SQL> select [Link],[Link],[Link] manager,[Link] from emp e,emp m

where [Link]=[Link] and [Link]<[Link];
ENAME HIREDATE MANAGER HIREDATE
--------- ------------------ --------- ------------------
ward 22-FEB-81 blake 01-MAY-81
allen 20-FEB-81 blake 01-MAY-81
clark 09-JUN-81 king 17-NOV-81
blake 01-MAY-81 king 17-NOV-81
jones 02-APR-81 king 17-NOV-81
smith 17-DEC-80 ford 03-DEC-81
6 rows selected.
SQL> select job from emp where deptno=20 union select job from emp where
deptno=30;
JOB
---------
analyst
clerk
manager
salesman

SQL> select empno,ename from emp where deptno=10 union select empno,ename
from emp where deptno=30 order by 1;
EMPNO ENAME
---------- ---------
7499 allen
7521 ward
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7934 miller

9 rows selected.
 2) Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS,
UNION, INTERSET, Constraints.
Example:- Select the roll number and name of the student who secured fourth rank in the
class.

SQL> select job from emp where deptno=20 intersect select job from emp
where deptno=30;
JOB
---------
clerk
manager

SQL> select job from emp where deptno=20 minus select job from emp where
deptno=10 minus select job from emp where deptno=30;
JOB
---------
analyst

SQL> select ename from emp where deptno=(select deptno from emp where
ename='miller');
ENAME
---------
clark
king
miller

SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
---------
turner

SQL> select * from dept where deptno=(select distinct deptno from emp
where mgr=7698);
DEPTNO DNAME LOC
---------- ---------- --------
30 sales chicago

SQL> create table incr

2 (empno number(4),
3 amt number(7,2),
4 idate date,foreign key(empno) references emp);
Table created.

SQL> insert into incr values(7698,9000,'1-may-82');

1 row created.
SQL> insert into incr values(7934,7000,'23-jan-83');
1 row created.
SQL> insert into incr values(7844,10000,'17-nov-82');
1 row created.
SQL> insert into incr values(7654,9000,'28-sep-82');
1 row created.
SQL> select * from incr;
 EMPNO AMT IDATE
---------- ---------- ------------------
7698 9000 01-MAY-82
7934 7000 23-JAN-83
7844 10000 17-NOV-82
7654 9000 28-SEP-82

SQL> select ename from emp where empno in(select empno from incr);
ENAME
---------
blake
miller
turner
martin

SQL> select empno,ename,sal,deptno from emp e where sal>(select avg(sal)

from emp where deptno=[Link]);
EMPNO ENAME SAL DEPTNO
---------- --------- ---------- ----------
7566 jones 2975 20
7788 scott 3000 20
7839 king 5000 10
7844 turner 10000 30
7902 ford 3000 20

SQL> select empno,ename,job,deptno from emp e where exists(select empno

from emp where [Link]=[Link]);
EMPNO ENAME JOB DEPTNO
---------- --------- --------- ----------
7698 blake manager 30
7839 king president 10
7566 jones manager 20
7788 scott analyst 20
7782 clark manager 10
7902 ford analyst 20
6 rows selected.

SQL> select * from emp where deptno=10 and exists(select count(*) from emp
where deptno=10 group by deptno having count(*)>10);
no rows selected

SQL> select ename,job from emp e where not exists(select mgr from emp
where mgr=[Link]);
ENAME JOB
--------- ---------
turner salesman
ward salesman
martin salesman
allen salesman
miller clerk
smith clerk
adems clerk
james clerk
8 rows selected.
SQL> select ename from emp where sal>any(select sal from emp where
deptno=20);
ENAME
---------
turner
king
scott
ford
jones
blake
clark
allen
miller
martin
ward
ENAME
---------
adems
james

13 rows selected.

SQL> select empno,ename,sal from emp where sal>any(select sal from emp
where job='manager');
EMPNO ENAME SAL
---------- --------- ----------
7844 turner 10000
7839 king 5000
7788 scott 3000
7902 ford 3000
7566 jones 2975
7698 blake 2850

6 rows selected.

SQL> select ename from emp where sal>all(select sal from emp where
deptno=20);
ENAME
---------
king
turner

SQL> select empno,ename,sal from emp where sal>all(select sal from emp
where job='manager');
EMPNO ENAME SAL
---------- --------- ----------
7902 ford 3000
7788 scott 3000
7839 king 5000
7844 turner 10000

SQL> select ename,sal from emp where sal>all(select distinct sal from emp
where deptno=30);
no rows selected
SQL> select [Link] from emp e where [Link] in(select [Link] from dept
d where [Link]='chicago');
ENAME
---------
allen
ward
martin
blake
turner
james
6 rows selected

SQL> select [Link] from emp e where [Link] not in(select [Link] from
dept d where [Link]='chicago');
ENAME
---------
miller
king
clark
smith
ford
adems
scott
jones

8 rows selected.
SQL> select [Link] from emp e,dept d
where [Link]=[Link] and [Link]='dallas'
union
select [Link] from emp e,dept d
where [Link]=[Link] and [Link]='newyork';
ENAME
---------
clark
king
miller

SQL> select [Link] from emp e INTERSECT select [Link] from dept d;
DEPTNO
----------
10
20
30
SQL> select deptno from dept minus select deptno from emp;

DEPTNO

SQL> select ename,sal from emp;

ENAME SAL
--------- ----------
allen 1600
ward 1250
jones 2975
martin 1250
blake 2850
clark 2450
scott 3000
king 5000
turner 10000
adems 1100
ford 3000

ENAME SAL
--------- ----------
miller 1300
smith 800
james 950
14 rows selected.

SQL> select [Link] from emp e where [Link] in(select [Link] from dept
d where [Link]='chicago');
ENAME
---------
allen
ward
martin
blake
turner
james
6 rows selected.

SQL> select [Link] from emp e where [Link] not in(select [Link] from
dept d where [Link]='chicago');
ENAME
---------
miller
king
clark
smith
ford
adems
scott
jones
8 rows selected.

SQL> select [Link] from emp e INTERSECT select [Link] from dept d;
DEPTNO
----------
10
20
30
SQL> select deptno from dept minus select deptno from emp;

DEPTNO
----------
40
 3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and
MIN), GROUP BY, HAVING and Creation and dropping of Views.

SQL> select count(*) from emp;

COUNT(*)
----------
14
SQL> select count(distinct job) from emp;
COUNT(DISTINCTJOB)
------------------
5
SQL> select sum(sal) from emp;
SUM(SAL)
----------
37525
SQL> select max(sal) from emp where job='salesman';
MAX(SAL)
----------
10000
SQL> select min(sal) from emp;
MIN(SAL)
----------
800
SQL> select avg(sal),count(*) from emp where deptno=20;
AVG(SAL) COUNT(*)
---------- ----------
2175 5
SQL> select deptno,count(*) from emp group by deptno;
DEPTNO COUNT(*)
---------- ----------
30 6
20 5
10 3

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

DEPTNO SUM(SAL)
---------- ----------
30 17900
20 10875
10 8750

SQL> select job,count(*) from emp group by job order by count(*) desc;
JOB COUNT(*)
--------- ----------
salesman 4
clerk 4
manager 3
analyst 2
president 1

SQL> select job,sum(sal),avg(sal),max(sal),min(sal) from emp group by job;

JOB SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)

--------- ---------- ---------- ---------- ----------
salesman 14100 3525 10000 1250
president 5000 5000 5000 5000
clerk 4150 1037.5 1300 800
manager 8275 2758.33333 2975 2450
analyst 6000 3000 3000 3000
SQL> select job,avg(sal) from emp where job!='manager' group by job;
JOB AVG(SAL)
--------- ----------
salesman 3525
president 5000
clerk 1037.5
analyst 3000

SQL> select job,sum(sal),avg(sal),max(sal),min(sal) from emp where

deptno=20 group by job;
JOB SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)
--------- ---------- ---------- ---------- ----------
clerk 1900 950 1100 800
manager 2975 2975 2975 2975
analyst 6000 3000 3000 3000

SQL> select deptno,avg(sal) from emp group by deptno having count(*)>5;

DEPTNO AVG(SAL)
---------- ----------
30 2983.33333

SQL> select job,max(sal) from emp group by job having max(sal)>=500;

JOB MAX(SAL)
--------- ----------
salesman 10000
president 5000
clerk 1300
manager 2975
analyst 3000

SQL> select job,sum(sal),avg(sal),max(sal),min(sal) from emp where

deptno=20 group by job having avg(sal)>1000;
JOB SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)
--------- ---------- ---------- ---------- ----------
manager 2975 2975 2975 2975
analyst 6000 3000 3000 3000

SQL> select job,sum(sal),avg(sal),max(sal),min(sal) from emp where

deptno=20 group by job having avg(sal)>1000 order by sum(sal);
JOB SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)
--------- ---------- ---------- ---------- ----------
manager 2975 2975 2975 2975
analyst 6000 3000 3000 3000

SQL> select ename from emp where sal=(select max(sal) from emp);
ENAME
---------
turner
SQL> select job,avg(sal) from emp group by job having avg(sal)=(select
max(avg(sal)) from emp group by job);
JOB AVG(SAL)
--------- ----------
president 5000

SQL> select * from emp where deptno=10 and exists(select count(*) from emp
where deptno=10 group by deptno having count(*) >10);

no rows selected

SQL>
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
SQL> select ABS(-65) from dual;
ABS(-65)
----------
65
SQL> select CEIL(SAL) "CEIL(88.9) from emp
where sal BETWEEN 3000 AND 5000;

CEIL(88.9)
----------
3000
5000
3000
SQL> select FLOOR(SAL),CEIL(88.9) from emp
where sal BETWEEN 3000 AND 5000;

FLOOR(SAL) CEIL(88.9)
---------- ----------
3000 89
5000 89
3000 89

SQL> select MOD(200,300) from dual;

MOD(200,300)
------------
200

SQL> select sal,POWER(sal,2) from emp where deptno=10;

SAL POWER(SAL,2)
---------- ------------
2450 6002500
5000 25000000
1300 1690000

SQL> select comm-sal,SIGN(comm-sal) from emp where deptno=30;

COMM-SAL SIGN(COMM-SAL)
---------- --------------
-1300 -1
-750 -1
150 1

-1500 -1
SQL> select sal,SQRT(sal) from emp where deptno=10;
SAL SQRT(SAL)
---------- ----------
2450 49.4974747
5000 70.7106781
1300 36.0555128
SQL> select TRUNC(90.723,1),TRUNC(90.723,-1),TRUNC(90.723) from dual;

TRUNC(90.723,1) TRUNC(90.723,-1) TRUNC(90.723)

--------------- ---------------- -------------
90.7 90 90

SQL> select sal,TRUNC(SQRT(sal),2) from emp where deptno=10;

SAL TRUNC(SQRT(SAL),2)
---------- ------------------
2450 49.49
5000 70.71
1300 36.05

SQL> select ROUND(90.723,1),ROUND(90.723,-1),ROUND(90.723) from dual;

ROUND(90.723,1) ROUND(90.723,-1) ROUND(90.723)

--------------- ---------------- -------------
90.7 90 91

SQL> select sal,ROUND(SQRT(sal),2) from emp where deptno=10;

SAL ROUND(SQRT(SAL),2)
---------- ------------------
2450 49.5
5000 70.71
1300 36.06

SQL> select EXP(4) from dual;

EXP(4)
----------
54.59815

SQL> select CHR(37) a,CHR(100) b,CHR(101) c from dual;

A B C
- - -
% d e

SQL> select CONCAT('Alphabet','soup') "Dinner" from dual;

Dinner
------------
Alphabetsoup

SQL> select INITCAP(dname) from dept;

INITCAP(DNAME)
--------------------
Accounting
Sales
Research
Operations
SQL> select LOWER(dname),LOWER('XYZ') from dept;
LOWER(DNAME) LOW
-------------------- ---
accounting xyz
sales xyz
research xyz
operations xyz

SQL> select UPPER(dname),UPPER('abc') from dept;

UPPER(DNAME) UPP
-------------------- ---
ACCOUNTING ABC
SALES ABC
RESEARCH ABC
OPERATIONS ABC

SQL> select LPAD(dname,15,'$'),LPAD(dname,15,'') from dept;

LPAD(DNAME,15,'$')
------------------------------------------------------------
LPAD(DNAME,15,'')
------------------------------------------------------------
$$$$$accounting
$$$$$$$$$$sales
$$$$$$$research

LPAD(DNAME,15,'$')
------------------------------------------------------------
LPAD(DNAME,15,'')
------------------------------------------------------------
$$$$$operations

SQL> select RPAD(dname,15,'$'),RPAD(dname,15,'') from dept;

RPAD(DNAME,15,'$')
------------------------------------------------------------
RPAD(DNAME,15,'')
------------------------------------------------------------
accounting$$$$$
sales$$$$$$$$$$
research$$$$$$$
RPAD(DNAME,15,'$')
------------------------------------------------------------
RPAD(DNAME,15,'')
------------------------------------------------------------
operations$$$$$

SQL> select dname,LTRIM(dname),LTRIM(dname,'R') from dept;

DNAME LTRIM(DNAME) LTRIM(DNAME,'R')
-------------------- -------------------- --------------------
accounting accounting accounting
sales sales sales
research research research
operations operations operations
SQL> select dname,RTRIM(dname),RTRIM(dname,'s') from dept;

DNAME RTRIM(DNAME) RTRIM(DNAME,'S')

-------------------- -------------------- --------------------
accounting accounting accounting
sales sales sale
research research research
operations operations operation

SQL> select REPLACE('This and That','Th','B')"First" from dual;

First
-----------
Bis and Bat

SQL> select REPLACE('This and That','Th')"Second" from dual;

Second
---------
is and at

SQL> select dname,SUBSTR(dname,2,4),SUBSTR(dname,4) from dept;

DNAME SUBSTR(DNAME,2,4
-------------------- ----------------
SUBSTR(DNAME,4)
--------------------------------------------------------------------
accounting ccouounting
sales aleses
research eseaearch

DNAME SUBSTR(DNAME,2,4
-------------------- ----------------
SUBSTR(DNAME,4)
--------------------------------------------------------------------
operations perarations

SQL> select TRANSLATE('abcdefghij','abcdef','123456') from dual;

TRANSLATE(
----------
123456ghij

SQL> select dname,LTRIM(dname),LTRIM(dname,'s') from dept;

DNAME LTRIM(DNAME) LTRIM(DNAME,'S')

-------------------- -------------------- --------------------
accounting accounting accounting
sales sales ales
research research research
operations operations operations
SQL> select dname,LTRIM(dname),LTRIM(dname,'r') from dept;

DNAME LTRIM(DNAME) LTRIM(DNAME,'R')

-------------------- -------------------- --------------------
accounting accounting accounting
sales sales sales
research research esearch
operations operations operations

SQL> select TRANSLATE('abcdefghij','abcdefghij','123456') from dual;

TRANSL
------
123456

SQL> select ASCII('') from dual;

ASCII('')
----------

SQL> select ASCII(' ') from dual;

ASCII('')
----------
32

SQL> select ASCII('a') from dual;

ASCII('A')
----------
97

SQL> select dname,INSTR(dname,'e') from dept;

DNAME INSTR(DNAME,'E')
-------------------- ----------------
accounting 0
sales 4
research 2
operations 3

SQL> select dname,LENGTH(dname) from dept;

DNAME LENGTH(DNAME)
-------------------- -------------
accounting 10
sales 5
research 8
operations 10

SQL> select UPPER(ename),LOWER(ename),INITCAP(ename),LENGTH(ename)

from emp;

UPPER(ENAM LOWER(ENAM INITCAP(EN LENGTH(ENAME)

---------- ---------- ---------- -------------
SMITH smith Smith 5
ALLEN allen Allen 5
WARD ward Ward 4
JONES jones Jones 5
MARTIN martin Martin 6
BLAKE blake Blake 5
CLARK clark Clark 5
SCOTT scott Scott 5
KING king King 4
TURNER turner Turner 6
ADAMS adams Adams 5

UPPER(ENAM LOWER(ENAM INITCAP(EN LENGTH(ENAME)

---------- ---------- ---------- -------------
JAMES james James 5
FORD ford Ford 4
MILLER miller Miller 6

14 rows selected.

SQL> select
ename,INSTR(ename,'a'),SUBSTR(job,1,3),LPAD(ename,10,'.'),RPAD(ename,10,'.
')
2 from emp;

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
smith 0 cle
.....smith
smith.....

allen 1 sal
.....allen
allen.....

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------

ward 2 sal
......ward
ward......

jones 0 man
.....jones

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
jones.....

martin 2 sal
....martin
martin....

blake 3 man

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
.....blake
blake.....

clark 3 man
.....clark
clark.....

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
scott 0 ana
.....scott
scott.....

king 0 pre
......king
king......

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------

turner 0 sal
....turner
turner....

adams 1 cle
.....adams

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
adams.....

james 2 cle
.....james
james.....

ford 0 ana

ENAME INSTR(ENAME,'A') SUBSTR(JOB,1

---------- ---------------- ------------
LPAD(ENAME,10,'.')
----------------------------------------
RPAD(ENAME,10,'.')
----------------------------------------
......ford
ford......

miller 0 cle
....miller
miller....

14 rows selected.

SQL> select SYSDATE from dual;

SYSDATE
------------------
13-AUG-15

SQL> select hireddate,ADD_MONTHS(hireddate,4),ADD_MONTHS(hireddate,-4)

2 from emp where deptno=10;

HIREDDATE ADD_MONTHS(HIREDDA ADD_MONTHS(HIREDDA

------------------ ------------------ ------------------
09-JUN-81 09-OCT-81 09-FEB-81
17-NOV-81 17-MAR-82 17-JUL-81
23-JAN-82 23-MAY-82 23-SEP-81

SQL> select ROUND(TO_DATE('12-apr-71'),'MM') "Nearest month"

from dual;
Nearest month
------------------
01-APR-71

SQL> select MONTHS_BETWEEN('05-jan-98','05-jan-98'),MONTHS_BETWEEN('05-

mar-98','05-jan-98')
2 from dual;
MONTHS_BETWEEN('05-JAN-98','05-JAN-98') MONTHS_BETWEEN('05-MAR-98','05-
JAN-98')
--------------------------------------- ----------------------------------
-----
0 2

SQL> select SYSDATE,LAST_DAY(SYSDATE)

2 from dual;
SYSDATE LAST_DAY(SYSDATE)
------------------ ------------------
13-AUG-15 31-AUG-15

SQL> select MONTHS_BETWEEN('05-jan-98','05-mar-98'),MONTHS_BETWEEN('05-

mar-98','05-jan-98'from dual;

MONTHS_BETWEEN('05-JAN-98','05-MAR-98') MONTHS_BETWEEN('05-MAR-98','05-
JAN-98')
--------------------------------------- ----------------------------------
-2 2

SQL> select SYSDATE,NEXT_DAY(SYSDATE,'WEDNESDAY') from dual;

SYSDATE NEXT_DAY(SYSDATE,'
------------------ ------------------
13-AUG-15 19-AUG-15

SQL> select SYSDATE,TO_CHAR(SYSDATE,'DAY') from dual;

SYSDATE TO_CHAR(SYSDATE,'DAY')
------------------ ------------------------------------
13-AUG-15 THURSDAY

SQL> select TO_CHAR(TO_DATE('20-mar-98'),'RM') from dual;

TO_C
----
III

SQL> select GREATEST(10,'7',-1) from dual;

GREATEST(10,'7',-1)
-------------------
10

SQL> select LEAST('abcd','ABCD','a','XYZ') "Least" from dual;

Leas
----
ABCD

SQL> select ename,sal,comm,sal+comm,gross,sal+NULL(comm,0) "New Gross"

SQL> select dname,TRANSLATE(dname,'e','1') from dept;

DNAME
--------------------
TRANSLATE(DNAME,'E','1')
--------------------------------------------------------------------------
accounting
accounting
sales
sal1s
research
r1s1arch
DNAME
--------------------
TRANSLATE(DNAME,'E','1')
--------------------------------------------------------------------------
operations
op1rations

SQL> select ename,TO_CHAR(hireddate,'DD/MM/YY') as hireddate

2 from emp
3 where deptno=20;

ENAME HIREDDAT
---------- --------
smith 17/12/80
jones 02/04/81
scott 19/04/87
adams 23/05/87
ford 03/12/81

SQL> select eno,ename,job,TO_CHAR(sal,'$9.999') as sal

2 from emp;

ENO ENAME JOB SAL

---------- ---------- ---------- -------
7369 smith clerk #######
7499 allen salesman #######
7521 ward salesman #######
7566 jones manager #######
7654 martin salesman #######
7698 blake manager #######
7782 clark manager #######
7788 scott analyst #######
7839 king president #######
7844 turner salesman #######
7876 adams clerk #######

ENO ENAME JOB SAL

---------- ---------- ---------- -------
7900 james clerk #######
7902 ford analyst #######
7984 miller clerk #######
14 rows selected.

SQL> select eno,ename,job,TO_CHAR(sal,'$9999') as salary from emp;

ENO ENAME JOB SALARY

---------- ---------- ---------- ------
7369 smith clerk $800
7499 allen salesman $1600
7521 ward salesman $1250
7566 jones manager $2975
7654 martin salesman $1250
7698 blake manager $2850
7782 clark manager $2450
7788 scott analyst $3000
7839 king president $5000
7844 turner salesman $1500
7876 adams clerk $1100
ENO ENAME JOB SALARY
---------- ---------- ---------- ------
7900 james clerk $950
7902 ford analyst $3000
7984 miller clerk $1300

14 rows selected.

SQL> select TO_CHAR(hireddate,'YY') as YY,count(*)

2 from emp
3 group by TO_CHAR(hireddate,'YY');

YY COUNT(*)
-- ----------
87 2
81 10
82 1
80 1

SQL> select ename,TO_CHAR(hireddate,'MON YY') as hireddate

2 from emp
3 where TO_CHAR(hireddate) BETWEEN TO_DATE('01-apr-81')
4 and TO_DATE('30-apr-82');

ENAME HIREDDATE
---------- ---------------
jones APR 81
martin SEP 81
blake MAY 81
clark JUN 81
king NOV 81
turner SEP 81
james DEC 81
ford DEC 81
miller JAN 82

9 rows selected.
SQL> select ename,sal+TO_NUMBER('100.00') as salary
2 from emp;

ENAME SALARY
---------- ----------
smith 900
allen 1700
ward 1350
jones 3075
martin 1350
blake 2950
clark 2550
scott 3100
king 5100
turner 1600
adams 1200

ENAME SALARY
---------- ----------
james 1050
ford 3100
miller 1400

14 rows selected.

SQL> select 'Department number'||

2 deptno||
3 'with name'||
4 INITCAP(dname)||
5 'is situated in'||
6 INITCAP(loc)
7 as concatenatedstring
8 from dept;

CONCATENATEDSTRING
--------------------------------------------------------------------------
------
Department number10with nameAccountingis situated inNewyork
Department number20with nameSalesis situated inChicago
Department number30with nameResearchis situated inDallas
Department number40with nameOperationsis situated inBoston

SQL> select LPAD(ename,9,'*')

2 from emp;

LPAD(ENAME,9,'*')
------------------------------------
****smith
****allen
*****ward
****jones
***martin
****blake
****clark
****scott
*****king
***turner
****adams

LPAD(ENAME,9,'*')
------------------------------------
****james
*****ford
***miller
14 rows selected.

SQL> select RPAD(ename,9,'*')

2 from emp;

RPAD(ENAME,9,'*')
------------------------------------
smith****
allen****
ward*****
jones****
martin***
blake****
clark****
scott****
king*****
turner***
adams****

RPAD(ENAME,9,'*')
------------------------------------
james****
ford*****
miller***
14 rows selected.

SQL> select LTRIM(ename,'s') from emp where deptno=20;

LTRIM(ENAM
----------
mith
jones
cott
adams
ford

SQL> select RTRIM(ename,'s') from emp where deptno=20;

RTRIM(ENAM
----------
smith
jone
scott
adam
ford

SQL> select ename,UPPER(ename),LOWER(ename),INITCAP(ename)from emp

2 where deptno=10;

ENAME UPPER(ENAM LOWER(ENAM INITCAP(EN

---------- ---------- ---------- ----------
clark CLARK clark Clark
king KING king King
miller MILLER miller Miller

SQL> select ename,LENGTH(ename)

2 from emp
3 where deptno=30
4 order by LENGTH(ename);

ENAME LENGTH(ENAME)
---------- -------------
ward 4
allen 5
james 5
blake 5
turner 6
martin 6

6 rows selected.

SQL> select DISTINCT(SUBSTR(job,1,4)) as job

2 from emp;

JOB
----------------
anal
cler
pres
sale
mana

SQL> select ename,INSTR(ename,'s')

2 from emp
3 where deptno=20;

ENAME INSTR(ENAME,'S')
---------- ----------------
smith 1
jones 5
scott 1
adams 5
ford 0

SQL> select NEXT_DAY(hireddate,'SUN') as holiday

2 from emp;

HOLIDAY
------------------
21-DEC-80
22-FEB-81
01-MAR-81
05-APR-81
04-OCT-81
03-MAY-81
14-JUN-81
26-APR-87
22-NOV-81
13-SEP-81
24-MAY-87

HOLIDAY
------------------
06-DEC-81
06-DEC-81
24-JAN-82

14 rows selected.

SQL> select hireddate,ADD_MONTHS(hireddate,12) as REVIEWDATE

2 from emp
3 where deptno=20;

HIREDDATE REVIEWDATE
------------------ ------------------
17-DEC-80 17-DEC-81
02-APR-81 02-APR-82
19-APR-87 19-APR-88
23-MAY-87 23-MAY-88
03-DEC-81 03-DEC-82

SQL> select hireddate,LAST_DAY(hireddate) as LASTDAY

2 from emp
3 where deptno=10;

HIREDDATE LASTDAY
------------------ ------------------
09-JUN-81 30-JUN-81
17-NOV-81 30-NOV-81
23-JAN-82 31-JAN-82

SQL> select LEAST(9,3,56,89,23,1,0,-2,12,34,7,22) as LOWEST

2 from dual;

LOWEST
----------
-2

SQL> select GREATEST(9,3,56,89,23,1,0,-2,12,34,7,22) as HIGHEST from dual;

HIGHEST
----------
 89

SQL> select TRUNC(567,23165613)

2 from dual;

TRUNC(567,23165613)
-------------------
567

SQL> select TRUNC(567.231651,3)

2 from dual;

TRUNC(567.231651,3)
-------------------
567.231

SQL>
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)Insert data into student table and use COMMIT, ROLLBACK and SAVEPOINT
in PL/SQL block

SQL> create table student

2 (rno number(5),
3 name varchar2(10),
4 s1 number(3),
5 s2 number(3),
6 s3 number(3),
7 s4 number(3),
8 total number(3),
9 avg number(7,2),
10 res varchar2(10));

SQL> desc student;

Name Null? Type
----------------------------------------- -------- ----------------------
------
RNO NUMBER(5)
NAME VARCHAR2(10)
S1 NUMBER(3)
S2 NUMBER(3)
S3 NUMBER(3)
S4 NUMBER(3)
TOTAL NUMBER(3)
AVG NUMBER(7,2)
RES VARCHAR2(10)
SQL> select * from student;
No rows selected
declare
x student%rowtype;
begin
[Link]:=&rno;
[Link]:='&name';
x.s1:=&s1;
x.s2:=&s2;
x.s3:=&s3;
x.s4:=&s4;
[Link]:=x.s1+x.s2+x.s3+x.s4;
[Link]:=[Link]/4;
if(([Link]<=100) and ([Link]>=70)) then
[Link]:='Distination';
dbms_output.put_line([Link]);
elsif(([Link]<=69) and ([Link]>=60)) then
[Link]:='First Class';
dbms_output.put_line([Link]);
elsif(([Link]<=59) and ([Link]>=50)) then
[Link]:='Second class';
dbms_output.put_line([Link]);
 elsif(([Link]<=49) and ([Link]>=35)) then
[Link]:='Third Class';
dbms_output.put_line([Link]);
else
[Link]:='Fail';
dbms_output.put_line([Link]);
end if;
if([Link]>100) then
raise_application_error(-200,'Not Valid Data');
end if;
insert into student
values([Link],[Link],x.s1,x.s2,x.s3,x.s4,[Link],[Link],[Link]);
commit;
end;

/
Enter value for rno: 41
old 4: [Link]:=&rno;
new 4: [Link]:=41;
Enter value for name:kba
old 5: [Link]:='&name';
new 5: [Link]:='kba';
Enter value for s1:67
old 6: x.s1:=&s1;
new 6: x.s1:=67;
Enter value for s2:89
old 7: x.s2:=&s2;
new 7: x.s2:=89;
Enter value for s3:56
old 8: x.s3:=&s3;
new 8: x.s3:=56;
Enter value for s4:90
old 9: x.s4:=&s4;
new 9: x.s4:=90;

PL/SQL procedure successfully completed.

SQL> /
Enter value for rno:56
old 4: [Link]:=&rno;
new 4: [Link]:=56;
Enter value for name:knsl
old 5: [Link]:='&name';
new 5: [Link]:='knsl';
Enter value for s1:89
old 6: x.s1:=&s1;
new 6: x.s1:=89;
Enter value for s2:90
old 7: x.s2:=&s2;
new 7: x.s2:=90;
Enter value for s3:78
old 8: x.s3:=&s3;
new 8: x.s3:=78;
Enter value for s4:88
old 9: x.s4:=&s4;
new 9: x.s4:=88;

PL/SQL procedure successfully completed.

SQL> /
Enter value for rno:36
old 4: [Link]:=&rno;
new 4: [Link]:=36;
Enter value for name:ks
old 5: [Link]:='&name';
new 5: [Link]:='ks';
Enter value for s1:65
old 6: x.s1:=&s1;
new 6: x.s1:=65;
Enter value for s2:77
old 7: x.s2:=&s2;
new 7: x.s2:=77;
Enter value for s3:82
old 8: x.s3:=&s3;
new 8: x.s3:=82;
Enter value for s4:92
old 9: x.s4:=&s4;
new 9: x.s4:=92;

PL/SQL procedure successfully completed.

SQL>/

Enter value for rno:33

old 4: [Link]:=&rno;
new 4: [Link]:=33;
Enter value for name:kur
old 5: [Link]:='&name';
new 5: [Link]:='kur';
Enter value for s1:69
old 6: x.s1:=&s1;
new 6: x.s1:=69;
Enter value for s2:55
old 7: x.s2:=&s2;
new 7: x.s2:=55;
Enter value for s3:98
old 8: x.s3:=&s3;
new 8: x.s3:=98;
Enter value for s4:76
old 9: x.s4:=&s4;
new 9: x.s4:=76;
PL/SQL procedure successfully completed.

SQL> select * from student;

RNO NAME S1 S2 S3 S4 TOTAL AVG RES

---------- ---------- ------ ------- ---------- ----- ------ --------
41 kba 67 89 56 90 302 75.5 Distination
56 knsl 89 90 78 88 345 86.2 Distination
36 ks 65 77 82 92 316 79 Distination
33 kur 69 55 98 76 298 74.5 Distination

6) Develop a program that includes the features NESTED IF, CASE and
CASE expression. The program can be extended using the NULLIF and
COALESCE functions.

SQL> create table e1

2 as
3 select * from emp;

Table created.

SQL> declare
2 x [Link]%type;
3 y [Link]%type;
4 bonus number(7,2);
5 begin
6 select sal,eno into x,y from e1
7 where eno=&eno;
8 if x between 500 and 1000 then
9 bonus:=500;
10 elsif x between 1001 and 2000 then
11 bonus:=1000;
12 elsif x between 2001 and 3000 then
13 bonus:=2000;
14 else
15 bonus:=3000;
16 end if;
17 update e1 set sal=sal+bonus
18 where eno=y;
19 end;
20 /
Enter value for eno: 7369
old 7: where eno=&eno;
new 7: where eno=7369;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7499
old 7: where eno=&eno;
new 7: where eno=7499;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7521
old 7: where eno=&eno;
new 7: where eno=7521;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7566
old 7: where eno=&eno;
new 7: where eno=7566;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7654
old 7: where eno=&eno;
new 7: where eno=7654;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7698
old 7: where eno=&eno;
new 7: where eno=7698;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7782
old 7: where eno=&eno;
new 7: where eno=7782;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7839
old 7: where eno=&eno;
new 7: where eno=7839;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7844
old 7: where eno=&eno;
new 7: where eno=7844;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7876
old 7: where eno=&eno;
new 7: where eno=7876;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7900
old 7: where eno=&eno;
new 7: where eno=7900;
PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7902
old 7: where eno=&eno;
new 7: where eno=7902;

PL/SQL procedure successfully completed.

SQL> /
Enter value for eno: 7934
old 7: where eno=&eno;
new 7: where eno=7934;

PL/SQL procedure successfully completed.

SQL> select * from e1;

ENO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- ----------
7369 smith clerk 7902 17-DEC-80 2300 20
7499 allen salesmen 7698 20-FEB-81 2600 300 30
7521 ward salesmen 7698 22-FEB-81 2250 500 30
7566 jones manager 7839 02-APR-81 4975 20
7654 martin salesme 7698 28-SEP-81 2250 1400 30
7698 blake manager 7839 01-MAY-81 4850 30
7782 clark manager 7839 09-JUN-81 4450 10
7788 scott analyist 7566 19-APR-87 3000 20
7839 king president 17-NOV-81 8000 10
7844 jurner salesmen 7698 08-SEP-81 2500 30
7876 adoms clerk 7788 23-MAY-87 2100 20
7900 james clerk 7698 03-DEC-81 1450 30
7902 ford analyist 7566 03-DEC-81 5000 20
7934 miller clerk 7782 23-JAN-82 2300 10

6b.
SQL> declare
2 x [Link]%type;
3 begin
4 select Deptno into x from dept
5 where Deptno=&Deptno;
6 case x
7 when 10 then
8 dbms_output.put_line('Accounting');
9 when 20 then
10 dbms_output.put_line('Research');
11 when 30 then
12 dbms_output.put_line('Sales');
13 when 40 then
14 dbms_output.put_line('Operations');
15 else
16 dbms_output.put_line('Unknown Dept');
17 end case;
18 end;
 19 /
Enter value for deptno: 10
old 5: where Deptno=&Deptno;
new 5: where Deptno=10;
Accounting

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 20
old 5: where Deptno=&Deptno;
new 5: where Deptno=20;
Research

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 30
old 5: where Deptno=&Deptno;
new 5: where Deptno=30;
Sales

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 40
old 5: where Deptno=&Deptno;
new 5: where Deptno=40;
Operations

PL/SQL procedure successfully completed.

SQL> /
Enter value for deptno: 60
old 5: where Deptno=&Deptno;
new 5: where Deptno=60;
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

6c.
SQL> declare
2 n number:=&n;
3 r number;
4 begin
5 r:=nullif(mod(n,2),0);
6 dbms_output.put_line('Remainder is'||r);
7 end;
8 /
SQL> set serveroutput on
SQL> /
Enter value for n: 6
old 2: n number:=&n;
new 2: n number:=6;
Remainder is
PL/SQL procedure successfully completed.

SQL> /
Enter value for n: 5
old 2: n number:=&n;
new 2: n number:=5;
Remainder is1

6d.
SQL> declare
2 a number:=&a;
3 b number:=&b;
4 c number:=&c;
5 begin
6 case
7 when ((a>b) and (a>c)) then
8 dbms_output.put_line('a is Biggest'||a);
9 when ((b>a) and (b>c)) then
10 dbms_output.put_line('b is Biggest'||b);
11 else
12 dbms_output.put_line('c is Biggest'||c);
13 end case;
14 end;

SQL> set serveroutput on

SQL> /
Enter value for a: 6
old 2: a number:=&a;
new 2: a number:=6;
Enter value for b: 3
old 3: b number:=&b;
new 3: b number:=3;
Enter value for c: 9
old 4: c number:=&c;
new 4: c number:=9;
c is Biggest9

PL/SQL procedure successfully completed.

SQL> /
Enter value for a: 98
old 2: a number:=&a;
new 2: a number:=98;
Enter value for b: 32
old 3: b number:=&b;
new 3: b number:=32;
Enter value for c: 76
old 4: c number:=&c;
new 4: c number:=76;
a is Biggest98
PL/SQL procedure successfully completed.

SQL> /
Enter value for a: 5676
old 2: a number:=&a;
new 2: a number:=5676;
Enter value for b: 34657
old 3: b number:=&b;
new 3: b number:=34657;
Enter value for c: 2346
old 4: c number:=&c;
new 4: c number:=2346;
b is Biggest34657

6e.
SQL> create table e2
2 (eid number(5),
3 ename varchar2(10),
4 sal number(7,2),
5 dname varchar2(10),
6 comm number(7,2));

Table created.

SQL> desc e2;

Name Null? Type
----------------------------------------- -------- ----------------------
EID NUMBER(5)
ENAME VARCHAR2(10)
SAL NUMBER(7,2)
DNAME VARCHAR2(10)
COMM NUMBER(7,2)

SQL> insert into e2 values(&1,'&2',&3,'&4',&5);

Enter value for 1: 101
Enter value for 2: A
Enter value for 3: 10000
Enter value for 4: Sales
Enter value for 5: 10
old 1: insert into e2 values(&1,'&2',&3,'&4',&5)
new 1: insert into e2 values(101,'A',10000,'Sales',10)
1 row created.

SQL> /
Enter value for 1: 102
Enter value for 2: B
Enter value for 3: 2000
Enter value for 4: IT
Enter value for 5: 20
old 1: insert into e2 values(&1,'&2',&3,'&4',&5)
new 1: insert into e2 values(102,'B',2000,'IT',20)
1 row created.
SQL> /
Enter value for 1: 103
Enter value for 2: C
Enter value for 3: 28000
Enter value for 4: IT
Enter value for 5: 20
old 1: insert into e2 values(&1,'&2',&3,'&4',&5)
new 1: insert into e2 values(103,'C',28000,'IT',20)
1 row created.

SQL> /
Enter value for 1: 104
Enter value for 2: D
Enter value for 3: null
Enter value for 4: Sales
Enter value for 5: null
old 1: insert into e2 values(&1,'&2',&3,'&4',&5)
new 1: insert into e2 values(104,'D',null,'Sales',null)
1 row created.

SQL> /
Enter value for 1: 105
Enter value for 2: E
Enter value for 3: null
Enter value for 4: Sales
Enter value for 5: 10
old 1: insert into e2 values(&1,'&2',&3,'&4',&5)
new 1: insert into e2 values(105,'E',null,'Sales',10)
1 row created.

SQL> select * from e2;

EID ENAME SAL DNAME COMM

---------- ---------- ---------- ---------- ----------
101 A 10000 Sales 10
102 B 2000 IT 20
103 C 28000 IT 20
104 D Sales
105 E Sales 10

SQL> select COALESCE(comm,sal,eid) from e2;

COALESCE(COMM,SAL,EID)
----------------------
10
20
20
104
10
7) Program development using WHILE LOOPS, numeric FOR LOOPS, nested loops
using ERROR Handling, BUILT –IN Exceptions, USE defined Exceptions, RAISE-
APPLICATION ERROR.

7a. using for

SQL> declare
2 i number;
3 k number;
4 n number:=&n;
5 begin
6 for i in 1..10
7 loop
8 k:=n*i;
9 dbms_output.put_line(n||'*'||i||'='||k);
10 end loop;
11 end;
12 /
Enter value for n: 6
old 4: n number:=&n;
new 4: n number:=6;
6*1=6
6*2=12
6*3=18
6*4=24
6*5=30
6*6=36
6*7=42
6*8=48
6*9=54
6*10=60

PL/SQL procedure successfully completed.

b. system defined exception

SQL> declare
2 x [Link]%type;
3 begin
4 select job into x from e1 where job='&job';
5 dbms_output.put_line(x||'Job type appeared only once');
6 commit;
7 exception
8 when too_many_rows then
9 dbms_output.put_line(x||'Job type Found more than Once');
10 commit;
11 when no_data_found then
12 dbms_output.put_line(x||'Job type not Found');
13 end;
14 /

Enter value for job: salesmen

old 4: select job into x from e1 where job='&job';
new 4: select job into x from e1 where job='salesmen';
salesmenJob type Found more than Once
PL/SQL procedure successfully completed.

SQL> /
Enter value for job: president
old 4: select job into x from e1 where job='&job';
new 4: select job into x from e1 where job='president';
presidentJob type appeared only once

PL/SQL procedure successfully completed.

SQL> /
Enter value for job: student
old 4: select job into x from e1 where job='&job';
new 4: select job into x from e1 where job='student';
Job type not Found

PL/SQL procedure successfully completed.

c. using while
SQL> declare
2 n number(5);
3 r number(5);
4 s number(5):=0;
5 n1 number(5);
6 begin
7 n:=&n;
8 n1:=n;
9 while(n>0)
10 loop
11 r:=mod(n,10);
12 s:=s+r;
13 n:=trunc(n/10);
14 end loop;
15 dbms_output.put_line('sum of digits is'||s);
16 end;
17 /
Enter value for n: 123
old 7: n:=&n;
new 7: n:=123;
sum of digits is6
PL/SQL procedure successfully completed.

d. nested for
SQL> declare
2 n number:=&n;
3 j number;
4 i number;
5 k number;
6 begin
7 for i in 1..n
8 loop
9 for j in 1..6
10 loop
11 k:=i*j;
12 dbms_output.put_line(i || '*' || j || '=' || k);
13 dbms_output.new_line;
14 end loop;
15 dbms_output.new_line;
16 dbms_output.put_line('........');
17 end loop;
18 end;
19 /

Enter value for n: 3

old 2: n number:=&n;
new 2: n number:=3;
1*1=1
1*2=2
1*3=3
1*4=4
1*5=5
1*6=6
........
2*1=2
2*2=4
2*3=6
2*4=8
2*5=10
2*6=12
........
3*1=3
3*2=6
3*3=9
3*4=12
3*5=15
3*6=18
........

PL/SQL procedure successfully completed.

e. user defined exception

SQL> declare
 2 e exception;
3 n number:=&n;
4 f number:=1;
5 i number;
6 begin
7 if n<0 then
8 raise e;
9 end if;
10 for i in 1..n
11 loop
12 f:=f*i;
13 end loop;
14 dbms_output.put_line('factorial of n is'||f);
15 exception
16 when e then
17 dbms_output.put_line('factorial for negative numbers is an error');
18 end;
19 /
Enter value for n: 5
old 3: n number:=&n;
new 3: n number:=5;
factorial of n is120
PL/SQL procedure successfully completed.
SQL> /
Enter value for n: -2
old 3: n number:=&n;
new 3: n number:=-2;
factorial for negative numbers is an error
PL/SQL procedure successfully completed.
 8) Programs development using creation of procedures, passing parameters
IN and OUT of PROCEDURES

a. procedure to compute factorial of a number

SQL> create or replace procedure factorial(n number) IS
2 f number:=1;
3 i number;
4 e exception;
5 begin
6 if n<0 then
7 raise e;
8 end if;
9 for i in 1..n
10 loop
11 f:=f*i;
12 end loop;
13 dbms_output.put_line('factorial of'||n||'is'||f);
14 exception
15 when e then
16 dbms_output.put_line('factorial for negative numbers donot exist');
17 end factorial;
18 /

Procedure created.

SQL> /

Procedure created.

SQL> set serveroutput on;

SQL> execute factorial(4);
factorial of4is24

PL/SQL procedure successfully completed.

SQL> execute factorial(-9);
Factorial for Negative Number donot exists

PL/SQL procedure successfully completed.

SQL> execute factorial(-6);
Factorial for Negative Number donot exists

PL/SQL procedure successfully completed.

b. procedure to increment emp sal

SQL> create or replace procedure a(eid number) IS
2 incr [Link]%type;
3 net [Link]%type;
4 veno [Link]%type;
5 vsal [Link]%type;
6 vcomm [Link]%type;
7 begin
8 select eno,sal,NVL(comm,0) into veno,vsal,vcomm from e1
9 where eno=eid;
10 net:=vsal+vcomm;
11 if vsal<=3000 then
12 incr:=0.20*net;
13 elsif vsal>=3000 and vsal<=6000 then
14 incr:=0.40*net;
15 else
16 incr:=0.40*net;
17 end if;
18 update e1 set sal=sal+incr where eno=eid;
19 exception
20 when no_data_found then
21 dbms_output.put_line('emp doesnot exist');
22 end a;
23 /

Procedure created.
SQL> select eno,sal from e1 where eno=7369;
ENO SAL
---------- ----------
7369 960

SQL> execute a(7369);

PL/SQL procedure successfully completed.

SQL> select eno,sal from e1 where eno=7369;

ENO SAL
---------- ----------
7369 1152

.
SQL> select eno,sal from emp where eno=7934;
ENO SAL
---------- ----------
7934 1300
SQL> select eno,sal from emp where eno=7521;
ENO SAL
---------- ----------
7521 1250

SQL> execute a(7521);

PL/SQL procedure successfully completed.

SQL> execute a(7934);

PL/SQL procedure successfully completed.
SQL> execute a(5414);
emp not exists

PL/SQL procedure successfully completed.

SQL>
[Link] development using creation of stored functions, invoke functions in SQL
Statements and write complex functions.

9a.
1 create or replace function fact(n number,f in out number) return
number IS
2 i number;
3 begin
4 if n<0 then
5 return -1;
6 end if;
7 for i in 1..n
8 loop
9 f:=f*i;
10 end loop;
11 return f;
12* end fact;
SQL> /

Function created.

1 declare
2 n number:=&n;
3 f number:=1;
4 k number;
5 begin
6 k:=fact(n,f);
7 if k<0 then
8 dbms_output.put_line('factorial for negative numbers donot exist');
9 else
10 dbms_output.put_line('factorial is'||k);
11 end if;
12* end;
13 /
Enter value for n: 4
old 2: n number:=&n;
new 2: n number:=4;
Factorial is24
PL/SQL procedure successfully completed.

SQL> /
Enter value for n: 6
old 2: n number:=&n;
new 2: n number:=6;
Factorial is720
PL/SQL procedure successfully completed.

SQL> /
Enter value for n: -6
old 2: n number:=&n;
new 2: n number:=-6;
Factorial for Negative Numbers not exist
PL/SQL procedure successfully completed.

9b.
1 create or replace function a1(eid number) return number IS
2 incr [Link]%type;
3 net [Link]%type;
4 veno [Link]%type;
5 vsal [Link]%type;
6 vcomm [Link]%type;
7 begin
8 select eno,sal,NVL(comm,0) into veno,vsal,vcomm from e1
9 where eno=eid;
10 net:=vsal+vcomm;
11 if vsal<=3000 then
12 incr:=0.20*net;
13 elsif vsal>=3000 and vsal<=6000 then
14 incr:=0.30*net;
15 else
16 incr:=0.40*net;
17 end if;
18 return incr;
19* end a1;
20 /

Function created.

SQL> ed;
Wrote file [Link]

1 declare
2 eid number:=&eid;
3 k [Link]%type;
4 begin
5 k:=a1(eid);
6 update e1 set sal=sal+k where eno=eid;
 7* end;
8 /
Enter value for eid: 7369
old 2: eid number:=&eid;
new 2: eid number:=7369;

PL/SQL procedure successfully completed.

SQL> select eno,sal from e1 where eno=7369;

ENO SAL
---------- ----------
7369 1990.66

10) Program development using creation of package specification, package bodies,

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

10 a.
1 create or replace package p1 is
2 procedure p_empdept1(dn NUMBER);
3* end p1;
SQL> /

Package created.

SQL> ed;
Wrote file [Link]

1 create or replace package body p1 is

2 procedure p_empdept1(dn in number) is dummydept number;
3 begin
4 select distinct dn into dummydept from emp where deptno=dn;
5 dbms_output.put_line('dept number present');
6 exception
7 when no_data_found then
8 dbms_output.put_line('dept number not present');
9 end;
10* end p1;
SQL> /

Package body created.

SQL> execute p1.p_empdept1(20);

dept number present

PL/SQL procedure successfully completed.

SQL> execute p1.p_empdept1(50);
dept number not present

PL/SQL procedure successfully completed.

10 b.
SQL> create or replace package p2 is procedure fact3(n number);
2 end p2;
3 /

Package created.

SQL> create or replace package body p2 is procedure fact3(n in number) is

2 f number:=1;
3 i number;
4 e exception;
5 begin
6 if n<0 then raise e;
7 end if;
8 for i in 1..n
9 loop
10 f:=f*i;
11 end loop;
12 dbms_output.put_line('factorial of'||n||'is'||f);
13 exception
14 when e then
15 dbms_output.put_line('not possible');
16 end fact3;
17 end p2;
18 /

Package body created.

SQL> set serveroutput on;

SQL> execute p2.fact3(4);
factorial of4is24
PL/SQL procedure successfully completed.

SQL> execute p2.fact3(-4);

not possible
PL/SQL procedure successfully completed.

SQL> execute p2.b(6);

factorial 6is720
PL/SQL procedure successfully completed.

SQL> execute p2.b(-6);

Not possible
PL/SQL procedure successfully completed.

SQL> execute p2.b(7);

factorial 7is5040
PL/SQL procedure successfully completed.
SQL> execute p2.b(-7);
Not possible
PL/SQL procedure successfully completed.

10 c.
create or replace package p3 as
procedure adde(id [Link]%type,name [Link]%type,job [Link]%type,mgr
[Link]%type,hireddate [Link]%type,comm [Link]%type,sal
[Link]%type,deptno [Link]%type,phno [Link]%type);
procedure demp(id [Link]%type);
procedure uemp(id [Link]%type);
end p3;
SQL> /
Package created.

SQL> ed;
Wrote file [Link]

1 create or replace package body p3 is

2 procedure adde(id [Link]%type,name [Link]%type,job [Link]%type,mgr
[Link]%type,hireddate [Link]%type,comm [Link]%type,sal
[Link]%type,deptno [Link]%type,phno [Link]%type) is
3 begin
4 insert into e1
values(id,name,job,mgr,hireddate,comm,sal,deptno,phno);
5 end adde;
6 procedure demp(id [Link]%type) is
7 begin
8 delete from e1 where eno=id;
9 end demp;
10 procedure uemp(id [Link]%type) is
11 begin
12 update e1 set sal=sal+3000 where eno=id;
13 end uemp;
14* end p3;
15 /

Package body created.

SQL> execute [Link](7369);

PL/SQL procedure successfully completed.

SQL> select eno,sal from e1 where eno=7369;

ENO SAL
---------- ----------
7369 4990.66
SQL> select * from e1 where eno=123;

no rows selected
SQL> execute [Link](123,'jone','dba',456,'12-sep-89',345,5000,20,9988);

PL/SQL procedure successfully completed.

SQL> select * from e1 where eno=123;

ENO ENAME JOB MGR HIREDDATE COMM

---------- ---------- ---------- ---------- ------------------ ----------
SAL DEPTNO PHNO
---------- ---------- ----------
123 jone dba 456 12-SEP-89 345
5000 20 9988

SQL> execute [Link](123);

PL/SQL procedure successfully completed.

SQL> select * from e1 where eno=123;

no rows selected

SQL>

11. Develop programs using features parameters in a CURSOR, FOR UPDATE

CURSOR, WHERE CURRENT of clause and CURSOR variables.

a.
SQL> declare
2 veno [Link]%type;
3 vename [Link]%type;
4 vsal [Link]%type;
5 vDeptno [Link]%type;
6 cursor c1 is select eno,ename,sal,Deptno from e1
7 where sal+NVL(comm,0)>1500;
8 begin
9 open c1;
10 loop
11 fetch c1 into veno,vename,vsal,vDeptno;
12 if c1%found then
13 dbms_output.put_line(veno||' '||vename||' '||vsal||' '||vDeptno);
14 else
15 exit;
16 end if;
17 end loop;
18 close c1;
19 end;
20 /
7499 allen 1600 30
7521 ward 2524 30
7566 jones 3570 20
7654 martin 1250 30
7698 blake 2850 30
7782 clark 2450 10
7788 scott 3000 20
7839 king 5000 10
7902 ford 3000 20
7934 miller 2246.4 10

PL/SQL procedure successfully completed.

11b.
SQL> declare
2 salary [Link]%type;
3 s number:=0;
4 cursor c1 is select sal from e1;
5 begin
6 open c1;
7 loop
8 fetch c1 into salary;
9 s:=s+salary;
10 exit when c1%notfound;
11 update emp set sal=sal+sal*10/100
12 where Deptno=20 and Deptno=40;
13 end loop;
14 dbms_output.put_line('Total is: ');
15 dbms_output.put_line(to_char(s));
16 dbms_output.put_line('Rows Fetched'||c1%rowcount);
17 close c1;
18 end;
19 /
Total is:
34276.8
Rows Fetched14

PL/SQL procedure successfully completed.

11 c.
SQL> declare
2 i number;
3 cursor c2 is
4 select * from e1
5 where Deptno=20
6 for update of sal;
7 begin
8 for i in c2
9 loop
10 update e1 set sal=10000
11 where current of c2;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.

SQL> select * from emp

2 where Deptno=20;

ENO ENAME JOB MGR HIREDATE SAL COMM DEPTNO PHNO

7369 smith clerk 7902 17-DEC-80 800 20
7566 jones manager 7839 02-APR-81 2975 20
7788 scott analyist 7566 19-APR-87 3000 20
7876 adoms clerk 7788 23-MAY-87 1100 20
7902 ford analyist 7566 03-DEC-81 3000 20

SQL> select * from e1

2 where Deptno=20;

ENO ENAME JOB MGR HIREDATE SAL

---------- ---------- ---------- ---------- ------------------ ----------
COMM DEPTNO PHNO
---------- ---------- ----------
7369 smith clerk 7902 17-DEC-80 10000
20
7566 jones manager 7839 02-APR-81 10000
20
7788 scott analyist 7566 19-APR-87 10000
20
7876 adoms clerk 7788 23-MAY-87 10000
20
7902 ford analyist 7566 03-DEC-81 10000
20

12) Develop Programs using BEFORE and AFTER Triggers, Row and Statement
Triggers and INSTEAD OF Triggers

12a.
SQL> create table client_master
(clientno number(10),
bal_due number(10,2),
address varchar2(10),
6 city varchar2(10));

Table created.

SQL> insert into client_master values(&1,'&2',&3,'&4','&5');

Enter value for 1: 1
Enter value for 2: abc
Enter value for 3: 300
Enter value for 4: sacet
Enter value for 5: vetapalem
old 1: insert into client_master values(&1,'&2',&3,'&4','&5')
new 1: insert into client_master values(1,'abc',300,'sacet','vetapalem')

1 row created.

SQL> /
Enter value for 1: 2
Enter value for 2: xyz
Enter value for 3: 500
Enter value for 4: saec
Enter value for 5: chirala
old 1: insert into client_master values(&1,'&2',&3,'&4','&5')
new 1: insert into client_master values(2,'xyz',500,'saec','chirala')

1 row created.

SQL> /
Enter value for 1: 3
Enter value for 2: pqr
Enter value for 3: 700
Enter value for 4: sacet
Enter value for 5: vetapalem
old 1: insert into client_master values(&1,'&2',&3,'&4','&5')
new 1: insert into client_master values(3,'pqr',700,'sacet','vetapalem')

1 row created.

SQL> select * from client_master;

CLIENTNO NAME BAL_DUE ADDRESS CITY

---------- ---------- ---------- ---------- ----------
1 abc 300 sacet vetapalem
2 xyz 500 saec chirala
3 pqr 700 sacet vetapalem

SQL> create table audit_client

2 (clientno number,
3 name varchar2(10),
4 bal_due number(10,2),
5 operation varchar2(10),
6 userid varchar2(10),
7 odate date);

Table created.

12 b.
SQL> create or replace trigger audit_trail before update on client_master
2 for each row
3 declare
4 oper varchar2(10);
5 clientno client_master.clientno%type;
6 name client_master.name%type;
7 bal_due client_master.bal_due%type;
8 begin
9 if updating then
10 oper:='update';
11 end if;
12 if deleting then
13 oper:='delete';
14 end if;
15 clientno:=:[Link];
16 name:=:[Link];
17 bal_due:=:old.bal_due;
18 insert into audit_client
values(clientno,name,bal_due,oper,user,sysdate);
19 end;
20 /

Trigger created.

SQL> select * from client_master;

CLIENTNO NAME BAL_DUE ADDRESS CITY

---------- ---------- ---------- ---------- ----------
1 abc 300 sacet vetapalem
2 xyz 500 saec chirala
3 pqr 700 sacet vetapalem

SQL> update client_master set bal_due=bal_due+100

2 where clientno=2;

1 row updated.

SQL> select * from audit_client;

CLIENTNO NAME BAL_DUE OPERATION USERID ODATE

---------- ---------- ---------- ---------- ---------- ------------------
2 xyz 500 update CSE541 08-OCT-15

12 c.
SQL> create or replace trigger audit_trail after delete on client_master
2 for each row
 3 declare
4 oper varchar2(10);
5 clientno client_master.clientno%type;
6 name client_master.name%type;
7 bal_due client_master.bal_due%type;
8 begin
9 if updating then
10 oper:='update';
11 end if;
12 if deleting then
13 oper:='delete';
14 end if;
15 clientno:=:[Link];
16 name:=:[Link];
17 bal_due:=:old.bal_due;
18 insert into audit_client
values(clientno,name,bal_due,oper,user,sysdate);
19 end;
20 /

Trigger created.
SQL> delete from client_master where clientno=3;
1 row deleted.
SQL> select * from audit_client;
CLIENTNO NAME BAL_DUE OPERATION USERID ODATE
---------- ---------- ---------- ---------- ---------- ------------------
2 xyz 500 update CSE541 08-OCT-15
3 pqr 700 delete CSE541 08-OCT-15

SQL>

13. Write a Java program that connects to a database using JDBC

import [Link].*;
class ConnectToOraDB {
public static void main(String args[])throws Exception{
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","sco
tt","tiger");
[Link]("..........Connected to Oracle
Database......"+[Link]());
}
}
Output:

E:\acedamic\f\j2ee\jdbc>javac [Link]

E:\acedamic\f\j2ee\jdbc>java ConnectToOraDB
..........Connected to Oracle Database......class
[Link]
Ction

14. Write a Java program to connect to a database using JDBC and insert
values into it

Java program to create table in oracle DB:

import [Link].*;
class CreateTable {
public static void main(String args[])throws Exception{
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","sco
tt","tiger");
[Link]("..........Connected to Oracle
Database...........");
Statement stmt=[Link]();
String sql="create table std(sid number(4),sname
varchar2(10),marks number(3))";
[Link](sql);
}
}
Output:

E:\acedamic\f\j2ee\jdbc>javac [Link]

E:\acedamic\f\j2ee\jdbc>java CreateTable
..........Connected to Oracle Database...........

E:\acedamic\f\j2ee\jdbc>
Output:

SQL> desc std;

Name Null? Type
----------------------------------------- -------- ----------------------
-----

SID NUMBER(4)
SNAME VARCHAR2(10)
MARKS NUMBER(3)

SQL>

Java program to insert rows into a table:

import [Link].*;
class InsertUsingStatement {
public static void main(String args[])throws Exception{
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","sco
tt","tiger");
[Link]("..........Connected to Oracle
Database...........");
 Statement stmt=[Link]();
String sql1="insert into std values(1001,'sone',60)";
String sql2="insert into std values(1002,'stwo',70)";
String sql3="insert into std values(1003,'sthr',80)";
[Link](sql1);
[Link](sql2);
[Link](sql3);
}
}
Output:
E:\acedamic\f\j2ee\jdbc>java InsertUsingStatement
..........Connected to Oracle Database...........

SQL> select *from std;

SID SNAME MARKS

---------- ---------- ----------
1001 sone 60
1002 stwo 70
1003 sthr 80

SQL>

[Link] a Java program to connect to a database using JDBC and delete

values from it

//deleting selected rows

import [Link].*;
class DeleteUsingPreparedStatement {
public static void main(String args[])throws Exception{
[Link]("[Link]");
Connection
con=[Link]("jdbc:oracle:thin:@localhost:1521:xe","sco
tt","tiger");
[Link]("..........Connected to Oracle
Database...........");
String sql="delete from student where sid=?";
PreparedStatement pstmt=[Link](sql);
[Link](1,1004);
[Link]();
[Link](1,1005);
[Link]();

}
}