Ex.

NO
DATE Experiment Name PAGE NO SIGNATURE

1 EXECUTE SINGLE LINE QUERY

AND GROUP FUNCTION

2
EXECUTE DDL COMMANDS

3
EXECUTE DML COMMANDS

4 EXECUTE DCL AND TCL COMMANDS

5 IMPLEMENT NESTED QUERIES IN SQL

IMPLEMENT JOIN OPERATIONS IN

6
SQL

CREATE VIEWS FOR A PARTICULAR

7
TABLE

8 LOCKS FOR A PARTICULAR TABLE

PL/SQL PROCEDURE FOR AN

9 APPLICATION USING EXEPTION
HANDLING

PL/SQL PROCEDURE FOR AN

10
APPLICATION USING CURSOR

PL/SQL PROCEDURE FOR AN

11
APPLICATION USING FUNCTIONS

PL/SQL PROCEDURE FOR AN

12
APPLICATION USING PACKAGE
Ex. no: 01
EXECUTE SINGLE LINE QUERY AND GROUP FUNCTIONS

SQL> create table employee (empno number(15),name varchar(20),dept varchar(20));

Table created.

SQL> desc employee;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(15)
NAME VARCHAR2(20)
DEPT VARCHAR2(20)

SQL> insert into employee values(&empno,'&name','&dept');

Enter value for empno: 101
Enter value for name: Ragul Enter value for dept: B.Com.,(CA)
old 1: insert into employee values(&empno,'&name','&dept')
new 1: insert into employee values(101,'Ragul','B.Com.,(CA)')

1 row created.

SQL> /
Enter value for empno: 102
Enter value for name: Rithu Enter value for dept: BCA old 1:
insert into employee values(&empno,'&name','&dept') new
1: insert into employee values(102,'Rithu','BCA')

1 row created.

SQL> /
Enter value for empno: 103
Enter value for name: Shivanya Enter value for dept: BBA old
1: insert into employee values(&empno,'&name','&dept') new
1: insert into employee values(103,'Shivanya','BBA')

1 row created.

SQL> /
Enter value for empno: 104
Enter value for name: Sivangi Enter value for dept: B.Com old
1: insert into employee values(&empno,'&name','&dept') new
1: insert into employee values(104,'Sivangi','B.Com')

1 row created.

SQL> /
Enter value for empno: 105
Enter value for name: Prithika Enter value for dept: MCA old
1: insert into employee values(&empno,'&name','&dept') new
1: insert into employee values(105,'Prithika','MCA')

1 row created.
SQL> select * from employee;

EMPNO NAME DEPT

---------- -------------------- --------------------
101 Ragul B.Com.,(CA)
102 Rithu BCA
103 Shivanya BBA
104 Sivangi B.Com
105 Prithika MCA
SQL> select upper(name) from employee;

UPPER(NAME)
--------------------
RAGUL
RITHU
SHIVANYA
SIVANGI
PRITHIKA

SQL> select lower(name) from employee;

LOWER(NAME)
--------------------
ragul rithu
shivanya
sivangi
prithika

SQL> select concat(name,dept) from employee;

CONCAT(NAME,DEPT)
----------------------------------------
RagulB.Com.,(CA)
RithuBCA
ShivanyaBBA
SivangiB.Com
PrithikaMCA
SQL> select length(dept) from employee;

LENGTH(DEPT)
------------
11
3
3
5
3

SQL> select round(3.456) from dual;

ROUND(3.456)
------------
3

SQL> select sqrt(144) from dual;

SQRT(144)
----------
12

SQL> select sysdate from dual;

SYSDATE
---------
16-MAR-23

SQL> drop table employee;

Table dropped.
SQL> create table employee(empno number(10),name varchar(20),salary number(10));

Table created.

SQL> desc employee;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(10)
NAME VARCHAR2(20)
SALARY NUMBER(10)

SQL> insert into employee values(&empno,'&name',&salary);

Enter value for empno: 201
Enter value for name: Ragul Enter value for salary: 50000 old
1: insert into employee values(&empno,'&name',&salary) new
1: insert into employee values(201,'Ragul',50000)

1 row created.

SQL> /
Enter value for empno: 202 Enter
value for name: Rithu
Enter value for salary: 60000
old 1: insert into employee values(&empno,'&name',&salary) new
1: insert into employee values(202,'Rithu',60000)

1 row created.

SQL> /
Enter value for empno: 203
Enter value for name: Shivanya Enter value for salary: 40000
old 1: insert into employee values(&empno,'&name',&salary)
new 1: insert into employee values(203,'Shivanya',40000)

1 row created.

SQL> /
Enter value for empno: 204
Enter value for name: Shivangi Enter value for salary: 80000
old 1: insert into employee values(&empno,'&name',&salary)
new 1: insert into employee values(204,'Shivangi',80000)

1 row created.

SQL> /
Enter value for empno: 205
Enter value for name: Prithika Enter value for salary: 90000
old 1: insert into employee values(&empno,'&name',&salary)
new 1: insert into employee values(205,'Prithika',90000)

1 row created.

SQL> select * from employee;

EMPNO NAME SALARY

---------- -------------------- ----------
201 Ragul 50000
202 Rithu 60000
203 Shivanya 40000
204 Shivangi 80000
205 Prithika 90000
SQL> select max(salary) from employee;

MAX(SALARY)
-----------
90000

SQL> select min(salary) from employee;

MIN(SALARY)
-----------
40000

SQL> select avg(salary) from employee;

AVG(SALARY)
-----------
64000

SQL> select sum(salary) from employee; SUM(SALARY)

-----------
320000

SQL> select count(salary) from employee;

COUNT(SALARY)
-------------
5
SQL> commit;
Commit complete.
Ex. No: 02

EXECUTE DDL COMMANDS

SQL> create table empdetails (empno number(15),name varchar(20),dept varchar(20));

Table created.

SQL> desc empdetails;

Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(15)
NAME VARCHAR2(20)
DEPT VARCHAR2(20)

SQL> insert into empdetails values(&empno,'&name','&dept');

Enter value for empno: 101
Enter value for name: Ragul Enter value for dept: B.Com.,(CA)
old 1: insert into empdetails values(&empno,'&name','&dept')
new 1: insert into empdetails values(101,'Ragul','B.Com.,(CA)')

1 row created.

SQL> /
Enter value for empno: 102
Enter value for name: Rithu Enter value for dept: BBA old 1:
insert into empdetails values(&empno,'&name','&dept') new
1: insert into empdetails values(102,'Rithu','BBA')

1 row created.

SQL> /
Enter value for empno: 103
Enter value for name: Shivanya Enter value for dept: BCA old
1: insert into empdetails values(&empno,'&name','&dept') new
1: insert into empdetails values(103,'Shivanya','BCA')

1 row created.

SQL> /
Enter value for empno: 104
Enter value for name: Shivangi Enter value for dept: MCA old
1: insert into empdetails values(&empno,'&name','&dept') new
1: insert into empdetails values(104,'Shivangi','MCA')

1 row created.

SQL> /
Enter value for empno: 105
Enter value for name: Prithika Enter value for dept: MBA old
1: insert into empdetails values(&empno,'&name','&dept') new
1: insert into empdetails values(105,'Prithika','MBA')

1 row created.

SQL> alter table empdetails add salary number(20);

Table altered.

SQL> alter table empdetails modify salary number(25);

Table altered.
SQL> select * from empdetails;

EMPNO NAME DEPT SALARY

---------- -------------------- -------------------- ----------
101 Ragul B.Com.,(CA)
102 Rithu BBA
103 Shivanya BCA
104 Shivangi MCA
105 Prithika MBA

SQL> truncate table empdetails;

Table truncated.

SQL> select * from empdetails;

no rows selected

SQL> drop table empdetails;

Table dropped.

SQL> select * from empdetails; select

* from empdetails
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> commit;

Commit complete.
Ex. No: 03
EXECUTE DML COMMANDS

SQL>create table productdetails(productid number(20),productname

varchar(20),productprice number(20),product_quantity number(20));

Table created.
SQL> desc productdetails;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCTID NUMBER(20)
PRODUCTNAME VARCHAR2(20)
PRODUCTPRICE NUMBER(20)
PRODUCT_QUANTITY NUMBER(20)

SQL> insert into productdetails

values(&productid,'&productname',&productprice,&product_quantity);
Enter value for productid: 1101
Enter value for productname: Laptop
Enter value for productprice: 150000 Enter
value for product_quantity: 20
old 1: insert into productdetails
values(&productid,'&productname',&productprice,&product_quantity) new
1: insert into productdetails values(1101,'Laptop',150000,20)

1 row created.
SQL> /
Enter value for productid: 1102
Enter value for productname: Smartphone
Enter value for productprice: 50000
Enter value for product_quantity: 50
old 1: insert into productdetails
values(&productid,'&productname',&productprice,&product_quantity) new
1: insert into productdetails values(1102,'Smartphone',50000,50)

1 row created.
SQL> /
Enter value for productid: 1103
Enter value for productname: Pendrive
Enter value for productprice: 2800 Enter
value for product_quantity: 30
old 1: insert into productdetails
values(&productid,'&productname',&productprice,&product_quantity) new
1: insert into productdetails values(1103,'Pendrive',2800,30)

1 row created.
SQL> /
Enter value for productid: 1104
Enter value for productname: CD
Enter value for productprice: 50 Enter
value for product_quantity: 60
old 1: insert into productdetails
values(&productid,'&productname',&productprice,&product_quantity) new
1: insert into productdetails values(1104,'CD',50,60)

1 row created.
SQL> /
Enter value for productid: 1105
Enter value for productname: CPU
Enter value for productprice: 20000 Enter
value for product_quantity: 60
old 1: insert into productdetails
values(&productid,'&productname',&productprice,&product_quantity) new 1: insert
into productdetails values(1105,'CPU',20000,60)
1 row created.
SQL> update productdetails set productprice=100 where productid=1103;

1 row updated.
SQL> delete from productdetails where productname='Laptop';

1 row deleted.
SQL> select productname,productprice from productdetails;

PRODUCTNAME PRODUCTPRICE
-------------------- ------------
Smartphone 50000
Pendrive 100
CD 50
CPU 20000

SQL> select * from productdetails where productid=1103;

PRODUCTID PRODUCTNAME PRODUCTPRICE PRODUCT_QUANTITY

---------- -------------------- ------------ ----------------
1103 Pendrive 100 30

SQL> delete from productdetails where productprice>120;

2 rows deleted.
SQL> select * from productdetails;
PRODUCTID PRODUCTNAME PRODUCTPRICE PRODUCT_QUANTITY
---------------------------------------------------- ------------------------------- -------------------------------------
1103 Pendrive 100 30
1104 CD 50 60

SQL> commit;
Commit complete.
Ex. No: 04
EXECUTE DCL AND TCL COMMANDS

SQL> create table t1(sno number(10),name varchar(10),course varchar(10),amount

number(10));

Table created.

SQL> desc t1;

Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(10)
NAME VARCHAR2(10)
COURSE VARCHAR2(10)
AMOUNT NUMBER(10)

SQL> insert into t1 values(&sno,'&name','&course',&amount);

Enter value for sno: 101
Enter value for name: Ragul
Enter value for course: BBA Enter value for amount: 100000
old 1: insert into t1 values(&sno,'&name','&course',&amount)
new 1: insert into t1 values(101,'Ragul','BBA',100000)

1 row created.

SQL> /
Enter value for sno: 102
Enter value for name: Rithu
Enter value for course: BCA
Enter value for amount: 150000
old 1: insert into t1 values(&sno,'&name','&course',&amount) new
1: insert into t1 values(102,'Rithu','BCA',150000)
1 row created.

SQL> /
Enter value for sno: 103
Enter value for name: Shivanya
Enter value for course: MCA Enter value for amount: 200000
old 1: insert into t1 values(&sno,'&name','&course',&amount)
new 1: insert into t1 values(103,'Shivanya','MCA',200000)

1 row created.

SQL> select * from t1;

SNO NAME COURSE AMOUNT

---------- ---------- ---------- ----------
101 Ragul BBA 100000
102 Rithu BCA 150000
103 Shivanya MCA 200000

SQL> commit;

Commit complete.
SQL> update t1 set amount=10000 where sno=102;

1 row updated.

SQL> select * from t1;

 SNO NAME COURSE AMOUNT
---------- ---------- ---------- ----------
101 Ragul BBA 100000
102 Rithu BCA 10000
103 Shivanya MCA 200000

SQL> rollback;

Rollback complete.

SQL> select * from t1;

SNO NAME COURSE AMOUNT

---------- ---------- -------------------- ------------------
101 Ragul BBA 100000
102 Rithu BCA 150000
103 Shivanya MCA 200000

SQL> savepoint s1;

Savepoint created.

SQL> update t1 set name='Ram' where name='Rithu';

1 row updated.

SQL> select * from t1;

SNO NAME COURSE AMOUNT
-------- ------------- ------------------- ---------------------
101 Ragul BBA 100000
102 Ram BCA 150000
103 Shivanya MCA 200000

SQL> savepoint s2;

Savepoint created.

SQL> rollback to savepoint s1;

Rollback complete.

SQL> select * from t1;

SNO NAME COURSE AMOUNT

---------- ---------- --------------- ------------------
101 Ragul BBA 100000
102 Rithu BCA 150000
103 Shivanya MCA 200000

SQL> commit;
Commit complete.
Enter user-name: connect/ sys as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ -------------------------- ---------
SCOTT 84 02-APR-10
OWBSYS_AUDIT 83 02-APR-10
OWBSYS 79 02-APR-10
APEX_030200 78 02-APR-10
APEX_PUBLIC_USER 76 02-APR-10
FLOWS_FILES 75 02-APR-10
MGMT_VIEW 74 02-APR-10
SYSMAN 72 02-APR-10
SPATIAL_CSW_ADMIN_USR 70 02-APR-10
SPATIAL_WFS_ADMIN_USR 67 02-APR-10
MDDATA 65 02-APR-10

USERNAME USER_ID CREATED

------------------------------ ------------------------- ---------
MDSYS 57 02-APR-10
SI_INFORMTN_SCHEMA 56 02-APR-10
ORDPLUGINS 55 02-APR-10
ORDDATA 54 02-APR-10
ORDSYS 53 02-APR-10
OLAPSYS 61 02-APR-10
ANONYMOUS 46 02-APR-10
XDB 45 02-APR-10
CTXSYS 43 02-APR-10
EXFSYS 42 02-APR-10
XS$NULL 2147483638 02-APR-10
USERNAME USER_ID CREATED
------------------------------ ------------------------- ---------
WMSYS 32 02-APR-10
APPQOSSYS 31 02-APR-10
DBSNMP 30 02-APR-10
ORACLE_OCM 21 02-APR-10
DIP 14 02-APR-10
OUTLN 9 02-APR-10
SYSTEM 5 02-APR-10
SYS 0 02-APR-10

30 rows selected.

SQL> create user lion identified by tiger;

User created.

SQL> grant create session to lion;

Grant succeeded.

SQL> create table b1(sno number(10),name varchar(10));

Table created.

SQL> insert into b1 values(&sno,'&name');

Enter value for sno: 101 Enter value for
name: Raj old 1: insert into b1
values(&sno,'&name') new 1: insert into
b1 values(101,'Raj')
1 row created.

SQL> /
Enter value for sno: 102
Enter value for name: Ram
old 1: insert into b1 values(&sno,'&name') new
1: insert into b1 values(102,'Ram')

1 row created.

SQL> /
Enter value for sno: 103 Enter value for
name: Reema old 1: insert into b1
values(&sno,'&name') new 1: insert into
b1 values(103,'Reema')

1 row created.

SQL> grant all on b1 to lion;

Grant succeeded.

SQL> revoke delete on b1 from lion;

Revoke succeeded.
SQL> commit;
Commit complete.
Ex. No: 05
IMPLEMENT NESTED QUERIES IN SQL

SQL> create table book(isbn number(20),title varchar(20),pyear

number(20),authorname varchar(20),pubname varchar(20));

Table created.

SQL> desc book;

Name Null? Type
----------------------------------------- -------- ----------------------------
ISBN NUMBER(20)
TITLE VARCHAR2(20)
PYEAR NUMBER(20)
AUTHORNAME VARCHAR2(20)
PUBNAME VARCHAR2(20)

SQL> insert into book values(&isbn,'&title',&pyear,'&authorname','&pubname');

Enter value for isbn: 101
Enter value for title: Marketing
Enter value for pyear: 2020
Enter value for authorname: sultan chand Enter value for pubname: Margham
old 1: insert into book values(&isbn,'&title',&pyear,'&authorname','&pubname')
new 1: insert into book values(101,'Marketing',2020,'sultan chand','Margham')

1 row created.

SQL> /
Enter value for isbn: 102
Enter value for title: Economics
Enter value for pyear: 2021
Enter value for authorname: Dhanpat Rai Enter
value for pubname: Dhanpat Rai & co.
old 1: insert into book values(&isbn,'&title',&pyear,'&authorname','&pubname')
new 1: insert into book values(102,'Economics',2021,'Dhanpat Rai','Dhanpat Rai &
co.')

1 row created.

SQL> /
Enter value for isbn: 103
Enter value for title: Mathematics
Enter value for pyear: 2022
Enter value for authorname: R D sharma Enter value for pubname: Margham old
1: insert into book values(&isbn,'&title',&pyear,'&authorname','&pubname') new
1: insert into book values(103,'Mathematics',2022,'R D sharma','Margham')

1 row created.

SQL> select * from book;

ISBN TITLE PYEAR AUTHORNAME

---------- -------------------- ---------- --------------------
PUBNAME
--------------------
101 Marketing 2020 sultan chand
Margham

102 Economics 2021 Dhanpat Rai

Dhanpat Rai & co.

103 Mathematics 2022 R D sharma

Margham
SQL> create table author(authorname varchar(20),country varchar(20));

Table created.

SQL> desc author;

Name Null? Type
----------------------------------------- -------- ----------------------------
AUTHORNAME VARCHAR2(20)
COUNTRY VARCHAR2(20)

SQL> insert into author values('&authorname','&country');

Enter value for authorname: R D sharma Enter value for
country: India old 1: insert into author
values('&authorname','&country') new 1: insert into
author values('R D sharma','India')

1 row created.

SQL> /
Enter value for authorname: Dhanpat Rai Enter value for
country: China old 1: insert into author
values('&authorname','&country') new 1: insert into
author values('Dhanpat Rai','China')

1 row created.
SQL> /
Enter value for authorname: Sultan chand Enter value for
country: India old 1: insert into author
values('&authorname','&country') new 1: insert into
author values('Sultan chand','India')

1 row created.

SQL> select * from author;

AUTHORNAME COUNTRY
-------------------- --------------------
R D sharma India
Dhanpat Rai China
Sultan chand India

SQL> select title,authorname,pubname from book where pyear in(2020,2021);

TITLE AUTHORNAME PUBNAME

-------------------- -------------------- --------------------
Marketing sultan chand Margham
Economics Dhanpat Rai Dhanpat Rai & co.

SQL> commit;

Commit complete.
Ex. No: 06

IMPLEMENT JOIN OPERATIONS IN SQL

SQL> create table eb(eno number(20),name varchar(20),unit number(20),amt

number(10));
Table created.
SQL> desc eb;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(20)
NAME VARCHAR2(20)
UNIT NUMBER(20)
AMT NUMBER(10)
SQL> insert into eb values(&eno,'&name',&unit,&amt);
Enter value for eno: 101
Enter value for name: Ragul
Enter value for unit: 100 Enter value for amt: 2000 old
1: insert into eb values(&eno,'&name',&unit,&amt) new
1: insert into eb values(101,'Ragul',100,2000)

1 row created.
SQL> /
Enter value for eno: 102
Enter value for name: Rithu
Enter value for unit: 200 Enter value for amt: 3000 old
1: insert into eb values(&eno,'&name',&unit,&amt) new
1: insert into eb values(102,'Rithu',200,3000)

1 row created.
SQL> /
Enter value for eno: 103
Enter value for name: Shivanya
Enter value for unit: 300 Enter value for amt: 4000 old
1: insert into eb values(&eno,'&name',&unit,&amt) new
1: insert into eb values(103,'Shivanya',300,4000)

1 row created.
SQL> /
Enter value for eno: 104
Enter value for name: Shivangi
Enter value for unit: 500 Enter value for amt: 6000 old
1: insert into eb values(&eno,'&name',&unit,&amt) new
1: insert into eb values(104,'Shivangi',500,6000)

1 row created.
SQL> /
Enter value for eno: 105
Enter value for name: Prithika
Enter value for unit: 600 Enter value for amt: 8000 old
1: insert into eb values(&eno,'&name',&unit,&amt) new
1: insert into eb values(105,'Prithika',600,8000)

1 row created.
SQL> select * from eb;

ENO NAME UNIT AMT

---------- -------------------- ---------- ----------
101 Ragul 100 2000
102 Rithu 200 3000
103 Shivanya 300 4000
104 Shivangi 500 6000
105 Prithika 600 8000
SQL> create table eb1(eno number(4),pdate date);

Table created.
SQL> insert into eb1(eno,pdate)values(201,(to_date('03-04-2003','dd-mm-yy')));

1 row created.
SQL> insert into eb1(eno,pdate)values(101,(to_date('03-04-2003','dd-mm-yy')));

1 row created.
SQL> insert into eb1(eno,pdate)values(102,(to_date('03-04-2003','dd-mm-yy')));

1 row created.
SQL> insert into eb1(eno,pdate)values(103,(to_date('04-04-2003','dd-mm-yy')));

1 row created.
SQL> insert into eb1(eno,pdate)values(104,(to_date('04-05-2003','dd-mm-yy')));

1 row created.
SQL> insert into eb1(eno,pdate)values(105,(to_date('04-05-2004','dd-mm-yy')));

1 row created.
SQL> desc eb1;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(4)
PDATE DATE
SQL> select * from eb1;

ENO PDATE
---------- ---------
201 03-APR-03
101 03-APR-03
102 03-APR-03
103 04-APR-03
104 04-MAY-03
105 04-MAY-04

6 rows selected.
SQL> select * from eb inner join eb1 on eb.eno=eb1.eno;

ENO NAME UNIT AMT ENO PDATE

---------- -------------------- ---------- ---------- ---------- ---------
101 Ragul 100 2000 101 03-APR-03
102 Rithu 200 3000 102 03-APR-03
103 Shivanya 300 4000 103 04-APR-03
104 Shivangi 500 6000 104 04-MAY-03
105 Prithika 600 8000 105 04-MAY-04

SQL> select * from eb left outer join eb1 on eb.eno>eb1.eno;

 ENO NAME UNIT AMT ENO PDATE
---------- -------------------- ---------- ---------- ---------- ---------
105 Prithika 600 8000 104 04-MAY-03
105 Prithika 600 8000 103 04-APR-03
105 Prithika 600 8000 102 03-APR-03
105 Prithika 600 8000 101 03-APR-03
104 Shivangi 500 6000 103 04-APR-03
104 Shivangi 500 6000 102 03-APR-03
104 Shivangi 500 6000 101 03-APR-03
103 Shivanya 300 4000 102 03-APR-03
103 Shivanya 300 4000 101 03-APR-03
102 Rithu 200 3000 101 03-APR-03
101 Ragul 100 2000

11 rows selected.
SQL> select * from eb right outer join eb1 on eb.eno<eb1.eno;

ENO NAME UNIT AMT ENO PDATE

---------- -------------------- ---------- ---------- ---------- ---------
105 Prithika 600 8000 201 03-APR-03
104 Shivangi 500 6000 201 03-APR-03
103 Shivanya 300 4000 201 03-APR-03
102 Rithu 200 3000 201 03-APR-03
101 Ragul 100 2000 201 03-APR-03
104 Shivangi 500 6000 105 04-MAY-04
103 Shivanya 300 4000 105 04-MAY-04
102 Rithu 200 3000 105 04-MAY-04
101 Ragul 100 2000 105 04-MAY-04
103 Shivanya 300 4000 104 04-MAY-03
102 Rithu 200 3000 104 04-MAY-03
 ENO NAME UNIT AMT ENO PDATE
---------- -------------------- ---------- ---------- ---------- --------------------------------
101 Ragul 100 2000 104 04-MAY-03
102 Rithu 200 3000 103 04-APR-03
101 Ragul 100 2000 103 04-APR-03
101 Ragul 100 2000 102 03-APR-03
101 03-APR-03
16 rows selected.
SQL> select * from eb full outer join eb1 on eb.eno=eb1.eno;

ENO NAME UNIT AMT ENO PDATE

---------- -------------------- ---------- ---------- ---------- --------- ------------------------
201 03-APR-03
101 Ragul 100 2000 101 03-APR-03
102 Rithu 200 3000 102 03-APR-03
103 Shivanya 300 4000 103 04-APR-03
104 Shivangi 500 6000 104 04-MAY-03
105 Prithika 600 8000 105 04-MAY-04

6 rows selected.
SQL> select eb.name,eb1.pdate from eb natural join eb1;

NAME PDATE
-------------------- ---------
Ragul 03-APR-03
Rithu 03-APR-03
Shivanya 04-APR-03
Shivangi 04-MAY-03
Prithika 04-MAY-04
SQL> select eno,name,unit,amt from eb natural join eb1;

ENO NAME UNIT AMT

---------- -------------------- ---------- ----------
101 Ragul 100 2000
102 Rithu 200 3000
103 Shivanya 300 4000
104 Shivangi 500 6000
105 Prithika 600 8000
SQL> select eb.name,eb1.pdate from eb cross join eb1;

NAME PDATE
-------------------- ---------
Ragul 03-APR-03
Ragul 03-APR-03
Ragul 03-APR-03
Ragul 04-APR-03
Ragul 04-MAY-03
Ragul 04-MAY-04
Rithu 03-APR-03
Rithu 03-APR-03
Rithu 03-APR-03
Rithu 04-APR-03
Rithu 04-MAY-03
NAME PDATE
-------------------- ---------
Rithu 04-MAY-04
Shivanya 03-APR-03
Shivanya 03-APR-03
Shivanya 03-APR-03
Shivanya 04-APR-03
Shivanya 04-MAY-03
Shivanya 04-MAY-04
Shivangi 03-APR-03
Shivangi 03-APR-03
Shivangi 03-APR-03
Shivangi 04-APR-03

NAME PDATE
-------------------- ---------
Shivangi 04-MAY-03
Shivangi 04-MAY-04
Prithika 03-APR-03
Prithika 03-APR-03
Prithika 03-APR-03
Prithika 04-APR-03
Prithika 04-MAY-03
Prithika 04-MAY-04

30 rows selected.
SQL> commit;

Commit complete.
Ex. No: 07
CREATE VIEWS FOR A PARTICULAR TABLE

SQL> create table cust (id number(10)primary key, name varchar(20),address

varchar(20),salary number(10));

Table created.

SQL> desc cust;

Name Null? Type
-------------------------------------- ------- ----------------------------
ID NOT NULL NUMBER(10)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(20)
SALARY
NUMBER(10)
SQL> insert into cust values(&id,'&name','&address',&salary);
Enter value for id: 121
Enter value for name: ramesh
Enter value for address: hyderabad Enter value for salary:
60000 old 1: insert into cust
values(&id,'&name','&address',&salary) new 1: insert into
cust values(121,'ramesh','hyderabad',60000)

1 row created.

SQL> /
Enter value for id: 122
Enter value for name: hema
Enter value for address: delhi Enter value for salary:
40000 old 1: insert into cust
values(&id,'&name','&address',&salary) new 1: insert
into cust values(122,'hema','delhi',40000)

1 row created.

SQL> /
Enter value for id: 123
Enter value for name: yash
Enter value for address: chennai Enter value for salary:
56000 old 1: insert into cust
values(&id,'&name','&address',&salary) new 1: insert
into cust values(123,'yash','chennai',56000)

1 row created.

SQL> /
Enter value for id: 124
Enter value for name: ravi
Enter value for address: gujart Enter value for salary:
80000 old 1: insert into cust
values(&id,'&name','&address',&salary) new 1: insert
into cust values(124,'ravi','gujart',80000)

1 row created.

SQL> /
Enter value for id: 125
Enter value for name: hemanth
Enter value for address: nagpur Enter value for salary:
60000 old 1: insert into cust
values(&id,'&name','&address',&salary) new 1: insert
into cust values(125,'hemanth','nagpur',60000) 1 row
created.

SQL> select * from cust;

ID NAME ADDRESS SALARY

---------- -------------------- ------------------- ----------
121 ramesh hyderabad 60000
122 hema delhi 40000
123 yash chennai 56000
124 ravi gujarat 80000
125 hemanth nagpur 60000

SQL> create view cu_view as select name,address from cust;

View created.

SQL> select * from cust;

ID NAME ADDRESS SALARY

---------- ------------------ ------------------ ----------
121 ramesh hyderabad 60000
122 hema delhi 40000
123 yash chennai 56000
124 ravi gujarat 80000
125 hemanth nagpur 60000

SQL> create view cu_view as select name,address from cust where address is not null
with check option;
create view cu_view as select name,address from cust where address is not null with
check option
 *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> update cu_view set address='bangalore'where name='ramesh';

1 row updated.

SQL> select * from cust;

ID NAME ADDRESS SALARY

---------- -------------------- -------------------- ----------
121 ramesh bangalore 60000
122 hema delhi 40000
123 yash chennai 56000
124 ravi gujarat 80000
125 hemanth nagpur 60000

SQL> delete from cu_view where address='delhi';

1 row deleted.
SQL> select * from cust;

ID NAME ADDRESS SALARY

---------- -------------------- -------------------- ----------
121 ramesh bangalore 60000
123 yash chennai 56000
124 ravi gujarat 80000
125 hemanth nagpur 60000

SQL> commit;
Commit complete.
Ex. No: 08
LOCKS FOR PARTICULAR TABLE

create table t1(sno number(10),name varchar(20), salary number(10));

Table created.
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
SNO NUMBER(10)
NAME VARCHAR2(20)
SALARY NUMBER(10)

SQL> insert into t1 values (&sno,'&name',&salary);

Enter value for sno: 101
Enter value for name: ragul Enter value for salary:
10000 old 1: insert into t1 values
(&sno,'&name',&salary) new 1: insert into t1
values (101,'ragul',10000)
1 row created.

SQL> /
Enter value for sno: 102
Enter value for name: ram Enter value for salary:
20000 old 1: insert into t1 values
(&sno,'&name',&salary) new 1: insert into t1
values (102,'ram',20000)
1 row created.
SQL> select * from t1;

SNO NAME SALARY

---------- -------------------- ----------
101 ragul 10000
102 ram 20000
SQL> lock table t1 in SHARE MODE WAIT 500;
Table(s) Locked.
SQL> set autocommit on;
SQL> update t1 set salary=15000 where sno=101;
1 row updated.
SQL> select * from t1;

SNO NAME SALARY

---------- -------------------- ----------
101 ragul 15000
102 ram 20000
Commit complete.
SQL> lock table t1 in SHARE MODE NOWAIT;
Table(s) Locked.
SQL> select count(*) from t1;
COUNT(*)
----------
2
SQL> lock table t1 in EXCLUSIVE MODE NOWAIT;
Table(s) Locked.
Ex. No: 09

PL/SQL PROCEDURE FOR AN APPLICATION

USING EXCEPTION HANDLING

SQL> create table alumini(aname varchar2(15),address

varchar2(10),phone number(12), degree varchar2(10));

Table created.
SQL> insert into alumini values('&aname','&address','&phone','&degree','&desig');
Enter value for aname: aswini
Enter value for address: tvm
Enter value for phone: 222233
Enter value for degree: bca Enter value for desig: designer old 1: insert into
alumini values('&aname','&address','&phone','&degree','&desig') new 1: insert
into alumini values('aswini','tvm','222233','bca','designer')
1 row created.
SQL> /
Enter value for aname: balu
Enter value for address: chennai
Enter value for phone: 222277
Enter value for degree: bba Enter value for desig: manager old 1: insert into
alumini values('&aname','&address','&phone','&degree','&desig') new 1: insert
into alumini values('balu','chennai','222277','bba','manager')
1 row created.
SQL> /
Enter value for aname: chitra
Enter value for address: car str
Enter value for phone: 222999
Enter value for degree: b.com Enter value for desig: accountant old 1: insert into
alumini values('&aname','&address','&phone','&degree','&desig') new 1: insert
into alumini values('chitra','car str','222999','b.com','accountant')
1 row created.
SQL> /
Enter value for aname: devi
Enter value for address: bye pass
Enter value for phone: 222559
Enter value for degree: ba Enter value for desig: teacher old 1: insert into alumini
values('&aname','&address','&phone','&degree','&desig') new 1: insert into
alumini values('devi','bye pass','222559','ba','teacher')
1 row created.
SQL> select *from alumini;
ANAME ADDRESS PHONE DEGREE DESIG
----------- ---------- --------- ---------- ----------
aswini tvm 222233 bca designer
balu chennai 222277 bba manager
chitra car str 222999 b.com accountant
devi bye pass 222559 ba teacher
SQL> declare
2 alumini_name alumini.aname%type;
3 change_address exception;
4 name varchar2(10);
5 a varchar2(15);
6 a1 varchar2(15);
7 begin
8 name:='&name';
9 a:='&a';
10 select address into a1 from alumini where aname=name;
11 if(a1=a)then
12 dbms_output.put_line('********************');
13 dbms_output.put_line('address not changed');
14 dbms_output.put_line('*******************');
15 else
16 update alumini set address=a where aname=name;
17 raise change_address;
18 end if;
19 exception when change_address then
20 dbms_output.put_line('address changed');
21 end;
22 /
Enter value for name: aswini
old 8: name:='&name'; new
8: name:='aswini'; Enter
value for a: tvm old 9:
a:='&a'; new 9: a:='tvm';
******************
address not changed
******************
PL/SQL procedure successfully completed.
SQL> /
Enter value for name: aswini
old 8: name:='&name';
new 8: name:='aswini';
Enter value for a: us old 9:
a:='&a'; new 9: a:='us';
address changed
PL/SQL procedure successfully completed.

SQL> select *from alumini;

ANAME ADDRESS PHONE DEGREE DESIG
----------- ---------- --------- ---------- ----------
aswini us 222233 bca designer
balu chennai 222277 bba manager
chitra car str 222999 b.com accountant
devi bye pass 222559 ba teacher

SQL> commit;
Commit complete.
Ex. No: 10

PL/SQL PROCEDURE FOR AN APPLICATION

USING CURSOR

SQL> create table emp11(eno number(5),ename varchar(20),bp number(5),hra

number(5),da number(5),np number(5));
Table created.
SQL> declare
2 eno number(5);
3 ename varchar(20);
4 bp number(10);
5 hra number(5);
6 da number(5);
7 np number(5);
8 cursor c is select*from emp11;
9 begin
10 eno:=&eno;
11 ename:='&ename';
12 bp:=&bp;
13 hra:=&hra;
14 da:=&da;
15 np:=bp+hra+da;
16 insert into emp11 values(eno,ename,bp,hra,da,np);
17 for x in c
18 loop
19 if x.bp>5000 then
20 update emp11 set np=bp+hra+da where eno=x.eno;
21 else
22 update emp11 set np=bp+da where eno=x.eno;
23 end if;
24 end loop;
25 end;
26 / Enter value for eno: 13 old 10: eno:=&eno; new 10: eno:=13; Enter value for
ename: vijay old 11: ename:='&ename'; new 11: ename:='vijay'; Enter value for
bp: 10000 old 12: bp:=&bp; new 12: bp:=10000; Enter value for hra: 5000 old 13:
hra:=&hra; new 13: hra:=5000; Enter value for da: 2000 old 14: da:=&da; new 14:
da:=2000;
PL/SQL procedure successfully completed.
SQL> / Enter value for eno: 14
old 10: eno:=&eno; new 10:
eno:=14; Enter value for
ename: nandu old 11:
ename:='&ename'; new 11:
ename:='nandu'; Enter value
for bp: 11000 old 12:
bp:=&bp; new 12:
bp:=11000; Enter value for
hra: 6000 old 13: hra:=&hra;
new 13: hra:=6000;
Enter value for da: 3000
old 14: da:=&da; new
14: da:=3000;
PL/SQL procedure successfully completed.
SQL> / Enter value for eno:
15 old 10: eno:=&eno; new
10: eno:=15; Enter value for
ename: arjun old 11:
ename:='&ename'; new 11:
ename:='arjun'; Enter value
for bp: 12000 old 12:
bp:=&bp; new 12:
bp:=12000; Enter value for
hra: 7000 old 13:
hra:=&hra; new 13:
hra:=7000; Enter value for
da: 4000 old 14: da:=&da;
new 14: da:=4000;
PL/SQL procedure successfully completed.
SQL> / Enter value for eno:
16 old 10: eno:=&eno; new
10: eno:=16; Enter value for
ename: kalai old 11:
ename:='&ename'; new 11:
ename:='kalai'; Enter value
for bp: 13000 old 12:
bp:=&bp;
new 12: bp:=13000;
Enter value for hra: 8000
old 13: hra:=&hra; new
13: hra:=8000; Enter
value for da: 5000 old
14: da:=&da; new 14:
da:=5000;
PL/SQL procedure successfully completed.
SQL> /
Enter value for eno: 17 old 10:
eno:=&eno; new 10: eno:=17;
Enter value for ename: nakunul
old 11: ename:='&ename'; new
11: ename:='nakunul'; Enter
value for bp: 14000 old 12:
bp:=&bp; new 12: bp:=14000;
Enter value for hra: 9000 old
13: hra:=&hra; new 13:
hra:=9000; Enter value for da:
6000 old 14: da:=&da; new
14: da:=6000;
PL/SQL procedure successfully completed.
SQL> select*from emp11;
ENO ENAME BP HRA DA NP
------ ---------- ---- ------ ----- ------
13 vijay 10000 5000 2000 17000
14 nandu 11000 6000 3000 20000
15 arjun 12000 7000 4000 23000 16 kalai 13000
8000 5000 26000
17 nakunul 14000 9000 6000 29000

5 rows selected.
SQL> commit;
Commit complete.
Ex. No: 11

PL/SQL PROCEDURE FOR AN APPLICATION

USING FUNCTIONS

SQL> create table pho_d12(ph_no number(20)primary key,user_name

varchar2(15),door_no number(10),street varchar2(15),city varchar2(15),pincode
number(7));
Table created.
SQL> desc pho_d12;
Name Null? Type
-------- ------ ------
PH_NO NOT NULL NUMBER(20)
USER_NAME VARCHAR2(15)
DOOR_NO NUMBER(10)
STREET VARCHAR2(15)
CITY VARCHAR2(15)
PINCODE NUMBER(7)

SQL> insert into pho_d12

values(&ph_no,'&user_name',&door_no,'&street','&city',&pincode);
Enter value for ph_no: 241236
Enter value for user_name: divya
Enter value for door_no: 12
Enter value for street: byepass
Enter value for city: tvm Enter
value for pincode: 600023
old 1: insert into pho_d12
values(&ph_no,'&user_name',&door_no,'&street','&city',&pincode) new 1:
insert into pho_d12 values(241236,'divya',12,'byepass','tvm',600023)
1 row created.
SQL> /
Enter value for ph_no: 222336
Enter value for user_name: seema
Enter value for door_no: 22
Enter value for street: gandhiroad
Enter value for city: chennai Enter
value for pincode: 606604
old 1: insert into pho_d12
values(&ph_no,'&user_name',&door_no,'&street','&city',&pincode) new 1: insert into
pho_d12 values(222336,'seema',22,'gandhiroad','chennai',606604)
1 row created.
SQL> /
Enter value for ph_no: 246567
Enter value for user_name: vishal
Enter value for door_no: 44
Enter value for street: mainroad
Enter value for city: coimbatore Enter value for pincode: 636009 old 1:
insert into pho_d12 values(&ph_no,'&user_name',&door_no,'&street','
&city',&pincode) new 1: insert into pho_d12
values(246567,'vishal',44,'mainroad','coimbatore',
636009)
1 row created.
SQL> /
Enter value for ph_no: 266780
Enter value for user_name: priya
Enter value for door_no: 12
Enter value for street: carstreet
Enter value for city: salem Enter value for pincode: 636008 old 1: insert into
pho_d12 values(&ph_no,'&user_name',&door_no,'&street',
'&city',&pincode)
new 1: insert into pho_d12 values(266780,'priya',12,'carstreet','salem',636008)
1 row created.

SQL> select *from pho_d12;

PH_NO USER_NAME DOOR_NO STREET CITY PINCODE
241236 divya 12 byepass tvm 600023
222336 seema 22 gandhiroad chennai 606604
246567 vishal 44 mainroad coimbatore 636009
266780 priya 12 carstreet salem 636008
SQL> set serveroutput on;
SQL> create or replace function fun(phone in number)return varchar as address
varchar2(60);
2 begin
3 select user_name||','||door_no||','||city||','||pincode into address from pho_d12
where ph_no=phone;
4 return address;
5 exception when no_data_found then return 'Address not found';
6 end;
7 /
Function created.
1 declare
2 address varchar2(60);
3 phone number(10);
4 begin
5 phone:=&ph_no;
6 address:=fun(phone);
7 dbms_output.put_line('*************************');
8 dbms_output.put_line('phone details using functions');
9 dbms_output.put_line('*****************************');
10 dbms_output.put_line('Address:'||address);
11 end;
SQL> /

Enter value for ph_no: 222236

old 5: phone:=&ph_no; new
5: phone:=222236;
*************************
phone details using functions
**************************
Address:Address not found
PL/SQL procedure successfully completed.
SQL> /

Enter value for ph_no: 222336

old 5: phone:=&ph_no; new
5: phone:=222336;
*************************
phone details using functions
**************************
Address:seema,22,chennai,606604
PL/SQL procedure successfully completed.

SQL> commit;
Commit complete.
Ex. No: 12

PL/SQL PROCEDURE FOR AN APPLICATION

USING PACKAGE

SQL> create table emp(eid number(2),enamevarchar(8),salary number(6));

Table created.
SQL> insert into empvalues(1,'Arun',7000);
1 row created.
SQL> insert into empvalues(2,'John',8000);
1 row created.
SQL> insert into empvalues(3,'Bala',10000);
1 row created.
SQL> insert into empvalues(4,'Moorthi',6000);
1 row created.
SQL> insert into empvalues(5,'Swetha',15000);
1 row created.
SQL> select *from emp;

EID ENAME SALARY

----- ---------- --------------
1 Arun 7000
2 John 8000
3 Bala 10000
4 Moorthi 6000
5 Swetha 15000
SQL> set serveroutput on; SQL> create or replace function femp(id in number)return
number
2 is
3fsal number(10);
4totalsalary number(10);
5hra number(10);
6 pf number(10);
7 da number(10);
8 begin
9 select salary into fsal from emp where eid=id;
10hra:=fsal*0.1;
11 pf:=fsal*0.2;
12 da:=fsal*0.1;
13 totalsalary:=hra+pf+da+fsal;
14 returntotalsalary;
15 end;
16 /
Function created.
SQL> select femp(3) from dual;
FEMP(3)
------------
14000
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.