CONTENTS
PAGE
S.NO DATE PROGRAM NAME SIGNATURE
NO
Execute a Single line Query and
1
Group Functions
2 Execute DDL Commands
Execute DML Commands
3
Execute DCL and TCL
4 Commands
5 Implement the Nested Queries
Implement Join Operations in
6 SQL
Create views for a particular table
7
Implement Locks for a Particular
8
table
Write PL/SQL procedure for an
9 application using Exception
handling
Write PL/SQL procedure for an
10
application using Cursors
Write PL/SQL procedure for an
11
application using Functions
Write PL/SQL procedure for an
12
application using Package
EXECUTE GROUP FUNCTION
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table product(product_id number(3),p_name varchar2(20),quantity
number(3),rate number(3));
Table created.
SQL> desc product;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT_ID NUMBER(3)
P_NAME VARCHAR2(20)
QUANTITY NUMBER(3)
RATE NUMBER(3)
SQL> insert into product values(01,'soap',10,100);
1 row created.
SQL> insert into product values(02,'shampoo',7,75);
1 row created.
SQL> insert into product values(03,'oil',5,50);
1 row created.
SQL> select*from product;
PRODUCT_ID P_NAME QUANTITY RATE
____________ ________ __________ ________
1 soap 10 100
2 shampoo 7 75
3 oil 5 50
SQL> select count (p_name)from product;
COUNT(P_NAME)
-------------
3
SQL> select avg (rate)from product;
AVG(RATE)
----------
75
SQL> select min (rate) as smallestprice from product;
SMALLESTPRICE
-------------
50
SQL> select max (rate) as largestprice from product;
LARGESTPRICE
------------
100
SQL> select sum(quantity)from product;
SUM(QUANTITY)
-------------
22
EXECUTE DDL COMMANDS
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table student( id number(6),reg_no number(10),name
varchar2(20),dept
varchar2(10));
Table created.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(6)
REG_NO NUMBER(10)
NAME VARCHAR2(20)
DEPT VARCHAR2(10)
SQL> alter table student add(age number(3));
Table altered.
SQL> alter table student modify(dept varchar2(20));
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- --- ----------------------------
ID NUMBER(6)
REG_NO NUMBER(10)
NAME VARCHAR2(20)
DEPT VARCHAR2(20)
AGE NUMBER(3)
SQL> rename student to student_details;
Table renamed.
SQL> truncate table student_details;
Table truncated.
SQL> select*from student_details;
no rows selected
SQL> drop table student_details;
Table dropped.
EXECUTE DML COMMANDS
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table employee3(e_id number(8),e_name varchar2(20),salary
varchar2(11),dept varchar2(18),address varchar2(19));
Table created.
SQL> desc employee3;
Name Null? Type
----------------------------------------- -------- ----------------------------
E_ID NUMBER(8)
E_NAME VARCHAR2(20)
SALARY VARCHAR2(11)
DEPT VARCHAR2(18)
ADDRESS VARCHAR2(19)
SQL> insert into employee3 values(101,'santhiya',400000,'manager','cdm');
1 row created.
SQL> insert into employee3 values(102,'shri',600000,'security','bhuvanagiri');
1 row created.
SQL> insert into employee3 values(103,'ashok',700000,'engineer','mutlur');
1 row created.
SQL> insert into employee3 values(104,'jasan',800000,'clerk','cuddalore');
1 row created.
SQL> select*from employee3;
E_ID E_NAME SALARY DEPT ADDRESS
---------- -------------------- ----------- ------------------ ------------------
101 santhiya 400000 manager cdm
102 shri 600000 security bhuvanagiri
103 ashok 700000 engineer mutlur
104 jasan 800000 clerk cuddalore
SQL> update employee3 set salary=25000 where dept='manager';
1 row updated.
SQL> select*from employee3;
E_ID E_NAME SALARY DEPT ADDRESS
---------- --------------- ---------------- ------------------ -------------------
101 santhiya 25000 manager cdm
102 shri 60000 security bhuvanagiri
103 ashok 70000 engineer mutlur
104 jasan 80000 clerk cuddalore
SQL> update employee3 set e_id=105,salary=52000 where e_name='jasan';
1 row updated.
SQL> select*from employee3;
E_ID E_NAME SALARY DEPT ADDRESS
---------- --------------- ---------------- ------------------ -------------------
101 santhiya 25000 manager cdm
102 shri 60000 security bhuvanagiri
103 ashok 70000 engineer mutlur
105 jasan 52000 clerk cuddalore
SQL> delete employee3 where e_id=102;
1 row deleted.
SQL> select*from employee3;
E_ID E_NAME SALARY DEPT ADDRESS
---------- --------------- ---------------- ------------------ -------------------
101 santhiya 25000 manager cdm
103 ashok 70000 engineer mutlur
105 jasan 52000 clerk cuddalore
TCL – TRANSACTION CONTROL SYSTEM
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> create table emp(eno number(5),name varchar2(20),salary number(10));
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(5)
NAME VARCHAR2(20)
SALARY NUMBER(10)
SQL> insert into emp values(100,'Akil',5000);
1 row created.
SQL> insert into emp values(101,'Saran',6000);
1 row created.
SQL> insert into emp values(103,'Sunil',5000);
1 row created.
SQL> select * from emp;
ENO NAME SALARY
---------- -------------------- ----------
100 Akil 5000
101 Saran 6000
103 Sunil 5000
SQL> update emp set salary=5000 where eno=101;
1 row updated.
SQL> commit;
Commit complete.
SQL> rollback;
Rollback complete.
SQL> select * from emp;
ENO NAME SALARY
---------- -------------------- ----------
100 Akil 5000
101 Saran 5000
103 Sunil 5000
SQL> savepoint a;
Savepoint created.
SQL> insert into emp values(104,'Deva',5000);
1 row created.
SQL> savepoint b;
Savepoint created.
SQL> insert into emp values(105,'Ezhil',5000);
1 row created.
SQL> select * from emp;
ENO NAME SALARY
---------- -------------------- ----------
100 Akil 5000
101 Saran 5000
103 Sunil 5000
104 Deva 5000
105 Ezhil 5000
SQL> rollback to b;
Rollback complete.
SQL> select * from emp;
ENO NAME SALARY
---------- -------------------- ----------
100 Akil 5000
101 Saran 5000
103 Sunil 5000
104 Deva 5000
SQL> commit;
Commit complete.
SQL> select * from emp;
ENO NAME SALARY
---------- -------------------- ----------
100 Akil 5000
101 Saran 5000
103 Sunil 5000
104 Deva 5000
DCL – DATA CONTROL LANGUAGE
SQL> create user admin identified by bca;
User created.
SQL> grant create session to admin;
Grant succeeded.
SQL> grant create table to admin;
Grant succeeded.
SQL> connect
Enter user-name: admin
Enter password:
Connected.
SQL> connect
Enter user-name: system
Enter password:
Connected.
SQL> revoke create session from admin;
Revoke succeeded.
SQL> connect admin
Enter password:
ERROR:
ORA-01045: user ADMIN lacks CREATE SESSION privilege; logon denied
NESTED QUERIES
SQL> connect system
Enter password:
Connected.
SQL> create table depositor21(custname varchar2(15),custid number(4),balance
number(6),accno number(6));
Table created.
SQL> desc depositor21;
Name Null? Type
-------------------------------- -------- ----------------------------
CUSTNAME VARCHAR2(15)
CUSTID NUMBER(4)
BALANCE NUMBER(6)
ACCNO NUMBER(6)
SQL> insert into depositor21 values('agas',1,10000,22);
1 row created.
SQL> insert into depositor21 values('nivi',2,20000,33);
1 row created.
SQL> insert into depositor21 values('kabi',7,30000,44);
1 row created.
SQL> select * from depositor21;
CUSTNAME CUSTID BALANCE ACCNO
--------------- ---------- ---------- ----------
agas 1 10000 22
nivi 2 20000 33
kabi 7 30000 44
SQL> create table loan27(loanid number(5),custname varchar2(15), balance
number(15));
Table created.
SQL> desc loan27;
Name Null? Type
--------------------------------- -------- ----------------------------
LOANID NUMBER(5)
CUSTNAME VARCHAR2(15)
BALANCE NUMBER(15)
SQL> insert into loan27 values(1,'agas',30000);
1 row created.
SQL> insert into loan27 values(2,'jeni',400);
1 row created.
SQL> insert into loan27 values(8,'abi',500);
1 row created.
SQL> select*from loan27;
LOANID CUSTNAME BALANCE
---------- --------------- ----------
1 agas 30000
2 jeni 400
8 abi 500
SQL> select custname from loan27 where custname in(select custname from
depositor21);
CUSTNAME
---------------
agas
SQL> select custname from loan27 where custname not in(select custname from
depositor21);
CUSTNAME
---------------
jeni
abi
SQL> select custname from depositor21 where exists(select*from loan27 where
loan27. custname=depositor21.custname);
CUSTNAME
---------------
agas
SQL> select custname from depositor21 where not exists(select*from loan27 where
loan27.custname=depositor21.custname);
CUSTNAME
---------------
nivi
kabi
SQL> select*from depositor21 where custname in(select custname from loan27
where
balance>=8000);
CUSTNAME CUSTID BALANCE ACCNO
--------------- ---------- ---------- ----------
agas 1 10000 22
SQL JOIN OPERATION
SQL> connect system
Enter password:
Connected.
SQL> create table loan22(loanno number(15) primary key,branchname
varchar2(15), amount number(15));
Table created.
SQL> desc loan22;
Name Null? Type
---------------------------- -------- ----------------------------
LOANNO NOT NULL NUMBER(15)
BRANCHNAME VARCHAR2(15)
AMOUNT NUMBER(15)
SQL> create table borrower22(custname varchar2(15),loanno number(15));
Table created.
SQL> desc borrower22;
Name Null? Type
-------------------------------- -------- ----------------------------
CUSTNAME VARCHAR2(15)
LOANNO NUMBER(15)
SQL> insert into loan22 values(1,'cdm',10000);
1 row created.
SQL> insert into loan22 values(2,'chennai',20000);
1 row created.
SQL> insert into loan22 values(3,'pondy',70000);
1 row created.
SQL> select*from loan22;
LOANNO BRANCHNAME AMOUNT
---------- --------------- ----------
1 cdm 10000
2 chennai 20000
3 pondy 70000
SQL> insert into borrower22 values('agas',1);
1 row created.
SQL> insert into borrower22 values('thanu',2);
1 row created.
SQL> insert into borrower22 values('nisha',4);
1 row created.
SQL> insert into borrower22 values('jeni',5);
1 row created.
SQL> select * from borrower22;
CUSTNAME LOANNO
--------------- ----------
agas 1
thanu 2
nisha 4
jeni 5
SQL> select loan22.loanno,branchname,amount,custname,borrower22.loanno from
loan22, borrower22 where loan22.loanno=borrower22.loanno;
LOANNO BRANCHNAME AMOUNT CUSTNAME LOANNO
---------- --------------- ---------- --------------- ----------
1 cdm 10000 agas 1
2 chennai 20000 thanu 2
SQL> select loan22.loanno,branchname,amount,custname,borrower22.loanno from
loan22 left join borrower22 on loan22.loanno=borrower22.loanno;
LOANNO BRANCHNAME AMOUNT CUSTNAME LOANNO
---------- --------------- ---------- --------------- ----------
1 cdm 10000 agas 1
2 chennai 20000 thanu 2
3 pondy 70000
SQL> select loan22.loanno,branchname,amount,custname,borrower22.loanno from
loan22 right join borrower22 on loan22.loanno=borrower22.loanno;
LOANNO BRANCHNAME AMOUNT CUSTNAME LOANNO
---------- --------------- ---------- --------------- ----------
1 cdm 10000 agas 1
2 chennai 20000 thanu 2
jeni 5
nisha 4
VIEW CREATION AND MANIPULATION
SQL> connect system
Enter password:
Connected.
SQL> create table studtable21(sid number(3) primary key,studname varchar2(10),
address varchar2(15));
Table created.
SQL> create table studmark21(sid number(3),marks number(3),age number(3));
Table created.
SQL> create view viewtable21 as select studname,address from studtable where
sid<4;
View created.
SQL> insert into studtable21 values(1,'agas','cdm');
1 row created.
SQL> insert into studtable21 values(2,'kabi','sirkazhi');
1 row created.
SQL> insert into studtable21 values(3,'nivi','chennai');
1 row created.
SQL> insert into studtable21 values(4,'jenii','pomdy');
1 row created.
SQL> insert into studmark21 values(1,50,19);
1 row created.
SQL> insert into studmark21 values(2,80,18);
1 row created.
SQL> insert into studmark21 values(3,70,17);
1 row created.
SQL> insert into studmark21 values(4,75,16);
1 row created.
SQL> select * from viewtable;
STUDNAME ADDRESS
---------- ---------------
kabi sirkazhi
kabi chennai
jeni pondy
SQL> create view viewtable5 as select sid studname from studtable21 order by
studname;
View created.
SQL> select*from viewstudname;
SID STUDNAME
---------- ----------
5 agal
4 jeni
2 kabi
3 kabi
SQL> create view viewmarks5 as select *from studmark21;
View created.
SQL> select*from viewmarks5;
SID MARKS AGE
---------- ---------- ----------
1 50 19
2 80 18
3 70 17
4 75 16
SQL> drop view viewmarks5;
View dropped.
SQL> delete from viewtable where studname='jeni';
1 row deleted.
SQL> select*from viewtable;
STUDNAME ADDRESS
---------- ---------------
kabi sirkazhi
kabi chennai
EXCEPTION HANDLING
SQL> connect system
Enter password:
Connected.
SQL> create table cutomers (Id number(5), Name varchar2), Age number(3),
Address varchar2(20), Amount number(5));
Table created
SQL> insert into customers values(121, ‘Shri’, 28,’Cdm’,5000);
1 row created
SQL> insert into customers values(122, ‘Riyaz’, 30,’Cdm’,8000);
1 row created
SQL> insert into customers values(123, ‘Shiv’, 27,’Vpm’,10000);
1 row created
SQL> insert into customers values(124, ‘Saran’, 29,’Bvg’,15000);
1 row created
SQL> Select * from customers;
ID NAME AGE ADDRESS AMOUNT
121 Shri 28 Cdm 5000
122 Riyaz 30 Cdm 8000
123 Shiv 27 Vpm 10000
124 Saran 29 Bvg 15000
SQL>Set serveroutput on;
declare
c_id customers.Id%type:=125;
c_name customers.Name%type;
c_addr customers.Address%type;
begin
select name, address into c_name,c_addr from customers where Id=c_id;
dbms_output.put_line(‘Name:’||c_name);
dbms_output.put_line(‘Address:’||c_addr);
exception
when no_data_found then
dbms_output.put_line(‘No such customers!’);
when others then
dbms_output.put_line(‘Error’);
end;
/
PL/SQL procedure successfully completed.
No such customers !
PL/SQL USING CURSORS
SQL> connect system
Enter password:
Connected.
SQL> create table salarydetails(empid number(5) primary key,empname
varchar2(15)
,basicsalary number(8),grade char(1),netsalary number(8));
Table created.
SQL> desc salarydetails;
Name Null? Type
----------------------------------- -------- ----------------------------
EMPID NOT NULL NUMBER(5)
EMPNAME VARCHAR2(15)
BASICSALARY NUMBER(8)
GRADE CHAR(1)
NETSALARY NUMBER(8)
SQL> insert into salarydetails values(01,'agas',10000,'a',null);
1 row created.
SQL> insert into salarydetails values(02,'jeni',20000,'b',null);
1 row created.
SQL> insert into salarydetails values(03,'abii',70000,'c',null);
1 row created.
SQL> insert into salarydetails values(04,'nivi',40000,'a',null);
1 row created.
SQL> insert into salarydetails values(05,'agal',45000,'b',null);
1 row created.
SQL> select*from salarydetails;
EMPID EMPNAME BASICSALARY G NETSALARY
---------- --------------- ----------- - ----------
1 agas 10000 a
2 jeni 20000 b
3 abii 70000 c
4 nivi 40000 a
5 agal 45000 b
SQL> edit program 4.sql;
declare
cursor c6 is select grade from salary_details1 for update of net_salary;
cgrade salary_details1.grade%type;
bonus number;
begin
open c6;
loop
fetch c6 into cgrade;
exit when c6%notfound;
if cgrade='a' then
bonus:=1000;
elsif cgrade='b' then
bonus:=500;
else
bonus:=250;
end if;
update salary_details1 set net_salary=basic_salary+bonus where current of
c6;
end loop;
close c6;
end;
SQL> @program6.sql;
PL/SQL procedure successfully completed.
SQL> select*from salarydetails;
EMPID EMPNAME BASICSALARY G NETSALARY
---------- --------------- ----------- - ----------
1 agas 10000 a 11000
2 jeni 20000 b 20500
3 abii 70000 c 70250
4 nivi 40000 a 41000
5 agal 45000 b 45500
PL/SQL PROGRAM FOR FUNCTIONS
SQL> connect system
Enter password:
Connected.
SQL> create table contact(custname varchar2(15),address varchar2(15),city
varchar2(20), phno number(12));
Table created.
SQL> desc contact;
Name Null? Type
------------------------- -------- ----------------------------
CUSTNAME VARCHAR2(15)
ADDRESS VARCHAR2(15)
CITY VARCHAR2(20)
PHNO NUMBER(12)
SQL> insert into contact values('agasthiya','puthur','sirkali',9382615739);
1 row created.
SQL> insert into contact values('jeni','n-nagar','cdm',9123456789);
1 row created.
SQL> insert into contact values('nivi','s-street','nagai',9234567891);
1 row created.
SQL> select*from contact;
CUSTNAME ADDRESS CITY PHNO
--------------- --------------- -------------------- ----------
agasthiya puthur sirkali 9382615739
jeni n-nagar cdm 9123456789
nivi s-street nagai 9234567891
SQL> edit agas.sql;
create or replace function find_address(no contact.phno%type) return
varchar2
is
addr contact.address%type;
begin
select address into addr from contact where phno=no;
return addr;
end;
Function created.
SQL> set serveroutput on
SQL> edit function.sql
declare
num contact.phno%type:=#
con_addr contact.address%type;
begin
con_addr:=find_address(num);
dbms_output.put_line('address is'||con_addr);
exception
when no_data_found then
dbms_output.put_line('no record found');
end;
Enter value for num: 1234567890
old 2: num contact.phno%type:=#
new 2: num contact.phno%type:=1234567890;
no record found
PL/SQL procedure successfully completed.
Enter value for num: 9123456789
old 2: num contact.phno%type:=#
new 2: num contact.phno%type:=9123456789;
address is n-nagar
PL/SQL procedure successfully completed.
PL/SQL PROGRAM FOR PACKAGES
SQL> connect system
Enter password:
Connected.
SQL> create table customers(id number(3) primary key, name varchar2(20),salary
number(5));
Table created.
SQL> desc customers;
Name Null? Type
------------------------ -------- ----------------------------
ID NOT NULL NUMBER(3)
NAME VARCHAR2(20)
SALARY NUMBER(5)
SQL> insert into customers values(01,'agas',10000);
1 row created.
SQL> insert into customers values(02,'nivi',20000);
1 row created.
SQL> insert into customers values(03,'jeni',30000);
1 row created.
SQL> create package cust1_salary as
2 procedure find_sal(c_id customers.id%type);
3 end cust1_salary;
4 /
Package created.
SQL> set serveroutput on;
SQL> edit program7.sql;
create or replace package body cust1_salary as
procedure find_sal(c_id customers.id%type)
is c_sal customers.salary%type;
begin
select salary into c_sal from customers where id=c_id;
dbms_output.put_line('salary:'||c_sal);
end find_sal;
end cust1_salary;
Package body created.
SQL> execute cust1_salary.find_sal(01);
salary:10000
PL/SQL procedure successfully completed.
SQL> select*from customers;
ID NAME SALARY
---------- -------------------- ----------
1 agas 10000
2 nivi 20000
3 jeni 30000
