Assignment 01 : Table creation with constraints:
1. TABLE NAME: DEPT
NAME TYPE CONSTRAINTS
DEPTNO NUMBER(2) Primary key
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
create table dept
deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13)
);
Table Created
--> insert into dept values(&deptno,'&dname','&loc');
Enter value for deptno :10
Enter value for dname :accounting
Enter value for deptno :newyork
Records:
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESERCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2. TABLE NAME: EMP
NAME TYPE CONSTRAINTS
EMPNO NUMBER(4) Primary key
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) Foreign key
externaldept (deptno)
create table emp
(
empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) references dept1(deptno)
);
Table Created
--> insert into emp values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);
Enter value for empno :7369
Enter value for ename :smith
Enter value for job :clerk
Enter value for mgr :7902
Enter value for hiredate :17-dec-80
Enter value for sal :800
Enter valuefor comm :
Enter value for deptno :20
Records:
EMPNO ENAME JOB MG HIREDAT SAL COMM DEPTN
R E O
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALEESMA 7698 20-FEB-81 1600 300 30
N
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTI PRESIDENT 7566 28-SEP-81 1250 1400 30
N
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLERK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNE SALESMAN 7698 08-SEP-81 1500 0 30
R
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
3.Create table CUSTOMER_MST
Column name Data type Constraints
Cust_no Varchar2(6) Cust_no like AND , VVN or BAK
Cust_name Varchar2(15)
Address Varchar2(20)
city Varchar2(20) Primary key (cust_no)
create table customer_mst
cust_no varchar2(6) check( cust_no like 'ANA%' or cust_no like 'VVN%' or cust_no like
'BAK%'),
cust_name varchar2(15) not null,
addressvarchar2(20),
city varchar2(10) check(city in('Anand','V V Nagar','Bakrol')),
primary key(cust_no)
);
insert into customer_mst values('&cust_no','&cust_name','&address','&city');
Enter value for cust_no: ANA001
Enter value for cust_name: alan
Enter value for address: baroda
Enter value for city: Anand
old 1: insert into customer_mst values('&cust_no','&cust_name','&address','&city')
new 1: insert into customer_mst values('ANA001','alan','baroda','Anand')
1 row created.
Records:
Cust_no Cust_name address city
ANA001 Alan New bus stand Anand
VVN001 Smith Nana bazaar VV nagar
ANA002 Blake Town hall Anand
BAK001 Adams Vadtal road Bakrol
VVN002 Williams Mota bazaar VV nagar
ANA003 rudricks Railway station anand
4.Create table CUSTOMER_DTL
Column name Data type Constraints
Cust_no Varchar2(6) References (cust_no)
Bill_no Varchar2(5)
Prev_read Number(5)
Curr_read Number(5)
Unit_price Number(5,2) Check (unit_price >0)
Due_date date
create table customer_dtl
cust_no varchar2(6) references customer_mst(cust_no),
bill_non varchar2(3),
prev_read number(5),
curr_read number(5),
unit_price number(5,2) check(unit_price>0),
due_date date
);
update customer_dtl set unit_price=5.50;
insert into customer_dtl
values('&cust_no','&bill_non',&prev_read,&curr_read,&unit_price,'&due_date');
Enter value for cust_no: ANA001
Enter value for bill_mon: nov
Enter value for prev_read: 1234
Enter value for curr_read: 2545
Enter value for unit_price: 5.50
old 1: insert into customer_dtl
values('&cust_no','&bill_mon','&prev_read','&curr_read','&unit_pr
new 1: insert into customer_dtl values('ANA001','nov','1234','2545','5.50')
1 row created.
Records:
Cust_no Bill_no Prev_read Curr_read Unit_price Due_date
ANA001 NOV 1234 2545 5.50 26-DEC-05
VVN001 DEC 256 365 5.50 20-JAN-06
ANA002 NOV 245 578 5.50 26-DEC-05
BAK001 DEC 45 145 5.50 20-JAN-06
VVN002 DEC 245 545 5.50 20-JAN-06
ANA003 NOV 2255 2365 5.50 26-DEC-05
Assignment 02 : PL/SQL Block:
1. Write a PL/SQL block to print “HELLO MESSAGE”.
declare
begin
dbms_output.put_line('hello');
end;
2. Write a PL/SQL block that will take two integer numbers. Print addition, subtraction,
multiplication, division of the inputted numbers.
declare
a number(10);
b number(10);
ans number(10);
begin
a:=&number;
b:=&number;
ans:=a+b;
dbms_output.put_line('addition is ' || ans);
ans:=a-b;
dbms_output.put_line('substraction is ' || ans);
ans:=a*b;
dbms_output.put_line('multiplication is ' || ans);
ans:=a/b;
dbms_output.put_line('division is ' || ans);
end;
3. Write a PL/SQL block to take an input in the form of number and check whether the number is
EVEN or ODD.
declare
x number(4);
begin
x:=&x;
if mod(x,2)=0 then
dbms_output.put_line(to_char(x)||' is even');
end if;
if mod(x,2)=1 then
dbms_output.put_line(to_char(x)||' is odd');
end if;
end;
4. Write a PL/SQL block to take an input in the form of number and check whether the number is
positive, negative or zero.
declare
x number(4);
begin
X:=&X;
if x>0 then
dbms_output.put_line(to_char(x)||' is positive');
end if;
if x<0 then
dbms_output.put_line(to_char(x)||' is nagetive');
end if;
if x=0 then
dbms_output.put_line(to_char(x)||' is zero');
end if;
end;
5. Display the values value from 1 to 10 using LOOP..END LOOP, WHILE..LOOP AND FOR
LOOP.
# LOOP..END LOOP
declare
i number(3);
n number(3);
begin
i:=1;
n:=&n;
loop
dbms_output.put_line(i);
i:=i+1;
exit when i>n;
end loop;
end;
# WHILE..LOOP
declare
i number(3);
n number(3);
begin
i:=&i;
n:=&n;
while i<=n
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
/
# FOR LOOP
declare
begin
for i in 1..100
loop
if mod(i,2)=0 then
dbms_output.put_line(i);
end if;
end loop;
end;
Assignment 03 : PL/SQL Block – Implicit cursor (EMP and DEPT table) (Use
of COMMIT, ROLLBACK and SAVEPOINT)
1. Write a PL/SQL block that will take an input in the form of department number.
Increase the salaries of the employees working in the department by 5.5%. If the increment
updates the total salary of that department by 255 take back the increment else give the
increment permanently.
declare
c_dno number(2);
tot_sal number(8);
ett_sal number(8);
begin
c_dno:=&deptno;
select sum(sal) into tot_sal from emp where deptno=c_dno;
ett_sal:=tot_sal * .055;
if ett_sal > 255 then
ROLLBACK;
dbms_output.put_line('1st ROLLBACK');
else
update emp set sal=sal + (sal * .055)
where deptno =c_dno;
dbms_output.put_line('Its update and going for commite');
commite;
end if;
end;
2. Write a PL/SQL block that will update the salaries of the employees working with
SCOTT. If more than FOUR employees salaries are affected by the increment take back
the increment, else give the increment permanently.
declare
c_dno number(2);
total_rows number(3);
begin
select dept no into c_dno from emp where ename='scott';
dbms_output.put_line('DEPT NO IS:' || c_dno);
update emp sal sal=sal where deptno=c_dno total_row:=SQL % row count;
dbms_output.put_line(total_rows ||' customer selected');
If total_rows > 4 then
ROLLBACK;
dbms_output.put_line('Salary will be ROLLBACK');
else
commite;
dbms_output.put_line('Salary will be commited');
end if;
end;
3. Write a PL/SQL block that will take an input in the form of department number.
Decrease the salaries of the employees working in that department by 0.75%. If at least a
record is updated keep the change permanent else print appropriate message and
terminate the block.
declare
c_dno number(2);
tot_sal number(8);
ett_sal number(8);
begin
c_dno:=&deptno;
update emp set sal = sal - (sal*.075) where deptno =c_dno;
if
SQL %ROWCOUNT >=1 then
commit;
dbms_output.put_line ('salary will be commited');
else
ROLLBACK;
dbms_output.put_line ('salary will be Rollback');
end if;
end;
/
Assignment 04 : CURSOR
1. Display empno, name, salary, job and deptno of employees using cursor & %rowtype.
declare
c_empno emp.empno % type;
c_ename emp.ename % type;
c_sal emp.sal % type ;
c_job emp.job % type;
c_deptno emp.deptno % type;
cursor emprec is select emp no,ename,sal,job,deptno from emp;
Begin
dbms_output.put_line( 'employee no' || 'Employee name' || ''|| 'job'|| 'salary');
open emp_rec;
loop
fetch emp_rec into c.empnp, c_ename, c_job, c_deptno;
exit when emp_rec % notfound;
dbms_output.put_line(''||c_empno ||''||c_name ||''||c_job ||''|| c_sal);
` dbms_output.put_line('');
end loop;
close emp_rec;
end;
2. Write a cursor to display Department wise Employee information in following format.
Department wise Employee Information
------------------------------------------------------
Department No. : XX Department Name: XXXX
----------------------------------------------------------------------------------------------
Employee Employee Job Salary Commission Hiredate
Number Name
.. .. .. .. .. ..
---------------------------------------------------------------------------------------------- ----------------------
declare
c_empno emp.empno % type;
c_ename emp.ename % type;
c_sal emp.sal % type ;
c_job emp.job % type;
c_deptno emp.deptno % type;
c_dname dept.dname%type;
cursor dept_rec is select deptno,dname from dept;
cursor emp_rec is select empno,ename,sal,job,deptno from emp where deptno =
c_deptno;
Begin
open dept_rec;
loop
fetch dept_rec into c_deptno, c_dname;
exit when dept_rec % notfound;
dbms_output.put_line('deptno'||c_deptholl ||'deptname'||c_dname);
dbms_output.put_line('Employee No'||'Employee name'||'Commision Hire Date');
open emp_rec;
loop
fetch emp_rec into c_empno,c.ename,c.sal,c_job,c.dept no;
exit when emp_rec%notfound;
dbms_output.put_line(''||c_empno||''||c_ename||''||c_job||''||c_sal);
dbms_output.put_line('');
end loop;
close emp_rec;
end loop;
close dept_rec;
end;
3. Write a cursor to display the Electricity consumption report in following format.
Electricity consumption
------------------------------------------------------------------------------------------------------------
Customer No Name Consumption City
... .. .. ..
------------------------------------------------------------------------------------------------------------
Total Consumption: -----------
declare
cust_no ele_cust.cust_no%type;
cust_name ele_cust.cust_name%type;
cust_consum ele_cust.cust_consum%type;
cust_city ele_cust.cust_city%type;
lot_consum number(8):= 0;
cursor ele_rec is select cust_no,cust_name,cust_consum, cust_city,from ele_cust;
Begin
dbms_output.put_line( 'Electricity consumption');
dbms_output.put_line( '--------------------------------');
dbms_output.put_line( 'Consumerno name consumption city');
open ele_rec;
loop
fetch ele_rec cust_no, cust_name, cust_consum, cust_city;
dbms_output.put_line(cust_no||''||cust_name||''||cust_consum||''||cust_city);
` dbms_output.put_line('');
tot_consum;= tot_consum + cust_consum;
dbms_output.put_line('');
dbms_output.put_line('------------------------------');
dbms_output.put_line('total Consumption : || total_consum');
end loop;
close ele_rec;
end;
4. Write a cursor that will take input as a city and month. And display the inputted month
wise report for particular city in the following format:
Electricity Bill
Current Date:_______
Month: __________
--------------------------------------------------------------------------------------------------------------------
Readings
Customer No. Name Previous Current Consumption Unit Price Total Bill
--------------------------------------------------------------------------------------------------------------------
* ** ** ** ** ** **
--------------------------------------------------------------------------------------------------------------------
declare
c_cust_bill ele_cust.cust_bill%type;
c_prev ele_cust.prev_%type;
c_curr ele_cust.curr_%type;
c_cust_month ele_cust.month%type;
cust_no ele_cust.cust_no%type;
cust_name ele_cust.cust_name%type;
cust_city ele_cust.cust_city%type;
cust_consum ele_cust.cust_consum%type;
tot_val number(8):= 0;
cursor ele_rec is select
cust_month,cust_bill,cust_name,cust_consum,unit_price,unit_price*cust_consum from ele_cus.
where cust_city = '& cust_city' and cust_month =& month;
Begin
dbms_output.put_line( 'Electricity Bill');
dbms_output.put_line( 'Current date:'|| sysdate);
dbms_output.put_line( 'month:'|| &&month);
dbms_output.put_line( '--------------------------------');
dbms_output.put_line( 'Consumerno name consumption city');
open ele_rec;
loop
fetch dept_rec into
c.cust_month,c.cust_bill,c.cust_name,c_prev,c_curr,c_cust_consum,c_unit_price,tot_val;
exit when ele_rec%notfound;
dbms_output.put_line(c_cust_bill ||''||c.cust_name||''||
c_prev||''||c_curr||''||c_cust_consum||''||c_unit_price||''||tot_val);
dbms_output.put_line(cust_no||''||cust_name||''||cust_consum||''||cust_city);
` dbms_output.put_line('');
dbms_output.put_line('------------------------------');
dbms_output.put_line('total Consumption : || total_consum');
end loop;
close ele_rec;
end;
Assignment 05 : PL/SQL Block with exception handling. (EMP and DEPT
table)
1.Write a PL/SQL block to take an input in the form of department number. Print
department information if the department number is present in the DEPT table else print
appropriate message and terminate the block.
declare
no number(2);
name varchar2(15);
location varchar2(13);
begin
no:=&no;
select deptno, dname, loc into no, name, location from dept where deptno=no;
dbms_output.put_line('no' || 'name' || 'location');
dbms_output.put_line(no||' ' ||name||' ' ||location);
exception
when no_data_found then dbms_out.put_line ('This record is not available in table');
end;
2. Write a PL/SQL block to take an input in the form of job. Print employee information if
the job is with one employee in the EMP table, if the job is with more than one employee
print appropriate message and terminate the block, and if the job is not present print
appropriate message and terminate the block.
declare
emp_no number(4);
ename varchar2(10);
job1 varchar2(25);
mgr1 varchar2(4);
hiredate date;
sal number(7,2);
comm number(7,2);
dept_no number(2);
begin
job1:='&job1';
select emp_no,ename,job,mgr,hiredate, sal, comm, dept_no into emp_no, ename, job1,
mgr1, hiredate, sal, comm, dept_no from emp where job=job1;
dbms_output.put_line('emp_no'||'ename'||'job'||'mgr'||'hiredate'||'salary'||;'commission'||'dept
_no');
dbms_output.put_line(emp_no||' '||ename||' '||job||' '||mgr||' '||hiredate||' '||sal||' '||comm||'
'||dept_no);
exception
when too_many_rows then dbms_output.put_line('This'||job1 ||'contained more than one
record');
when NO_DATA_FOUND then dbms_output.put_line('This' ||job1||'has no record');
end;
3. Write a PL/SQL block to take an input in the form of department number. If the total
salary paid to that department is greater than 1900 then raise the user defined exception,
print appropriate message and terminate the block. If the total salary is less than or equal
to 1900 update the salaries of the employees working in the department by 2.5%.
declare
total_sal number(10):=0;
deptno emp.dept_no%type;
ename emp.ename%type;
new_sal emp.sal%type;
my solution exception;
cursor N is select deptno, ename, sal from emp where deptno = &deptno;
begin
savepoint s;
open N;
loop
select deptno,ename,sal into deptno, name, new_sal from emp where
deptno=deptno;
fetch N into deptno, name, new_sal;
exit when N% not found;
total_sal:=total_sal+new_sal;
dbms_output.put_line('total salary' || total_sal);
end loop;
if total_sal > 1900 then
raise my solution;
else
update emp set sal=sal*2.5/100;
where deptno=deptno;
dbms_output.put_line('salary || sdsd;'||new_sal||name);
end if;
close N
exception
when my solution then
dbms_output,put_line ('salary is greater than 1900');
rollback to S;
end;
Assignment 06 : FUNCTION
1.Create a function to accept deptno and return dname.
create or replace function f1(dno in number)
return varchar2
is
name varchar2(14);
begin
select dname into name from dept where deptno=dno;
return name;
End;
select f1(10) from dual;
2.Create a function to check salaries of two employees, and return the max out of two.
create or replace function f2 (eno1 in number,eno2 in number)
return number
is
s1 number(7,2);
s2 number(7,2);
begin
select sal into s1 from emp1 where empno=eno1;
select sal into s2 from emp1 where empno=eno2;
if s1>s2 then
return s1;
else if s1<s2 then
return s2;
else
dbms_output.put_line('Both salaries are equal');
end if;
end if;
end;
select f2(7844,7902) from dual;
3. Create a function that will take employee number, check comm for particular empno
and return his sal, if comm > 450 else return 0 and display message accordingly.
create or replace function f3 (eno in number)
return number
is
s number(7,2);
com number(7,2);
begin
select sal,comm into s,com from emp where empno=eno;
if com>450 then
dbms_output.put_line('Salary of employee is:'||s);
else
dbms_output.put_line('Salary is lessthan 450');
return 0;
end if;
end;
Assignment 07 : PROCEDURE
1. Write a procedure to display the employee name if the salary > 3500 --for a particular
empno. (supply an empno & return employee name)
Create or replace procedure proc1(eno in number)
is
s number(7,2);
name varchar2(15);
begin
select ename,sal in to name,s from emp where empno=eno;
if s>3500 then
dbms_output.put_line('salary is greater then 3500');
dbms_output.put_line('ename='||name);
else
dbms_output.put_line('salary is less then 3500');
end if;
end;
2.Write a procedure that will take department number, if that department is having more
than four employees print that department information from the procedure, else print
appropriate message from the BLOCK which calls the procedure.
Create or replace procedure proc2(dno in number)
is
cn number(4);
dnm varchar2(13);
l varchar2(14);
begin
select count(8) into cn from emp where deptno=dno;
if cn>4 then
select dname, loc into dnm, l from dept where deptno=dno;
dbms_output.put_line('Department Number:'||dno);
dbms_output.put_line('Department Name:'||dnm);
dbms_output.put_line('Department Location:'||l);
else
dbms_output.put_line('Department less then 4..:');
end if;
end;
Assignment 08 : TRIGGERS
Create a table with same structure as EMP named EMP1
1.Insert a record in EMP1 table as and when user inserts a record in EMP table.
create table emp1
empno number(4) primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) references dept1(deptno)
);
insert into emp1 values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);
create table emp1 as select * from emp;
create or replace trigger t1
after insert
on emp
for each row
declare
begin
insert into emp values(&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);
end;
2.Insert record(s) in EMP1 table as and when user updates record(s) of EMP table.
create or replace trigger t2
after update
on emp
for each row
declare
begin
update emp1set
empno=:new.empno,ename=:new.ename,job=:new.job,mgr=:new.mgr,hiredate=:new.hiredate,sa
l=:new.sal,comm=:new.comm,deptno=:new.deptno;
dbms_output.put_line('record update sucessfully');
end;
3.Insert record(s) in EMP1 table when user deletes record(s) from EMP table.
create or replace trigger t3
after delete
on emp
for each row
declare
begin
delete from emp1 where empno=:old.empno;
dbms_output.put_line('record deleted from emp1');
end;
