Employee (eno, ename, city, deptname)
Project (pno, pname, status)
******************************************************************************
SQL>
create table employee52(eno number(5) primary key,ename varchar2(10),
city varchar2(10),dept_name
varchar2(10));
Table
created.
SQL> desc employee52;
SQL>
insert into employee52 values(1,’yogesh’,’pune’,’mech’);
1
row created.
SQL>
insert into employee52 values(2,'samir','satara','civil');
1
row created.
SQL>
insert into employee52 values(3,'ganesh','phaltan','comp');
1
row created.
SQL>
insert into employee52 values(4,'suraj','mumbai','it');
1
row created.
SQL>
insert into employee52 values(5,'raj','indapur','bca');
1
row created.
SQL> select * from employee52;
SQL>
create table project52(pno number(5) primary key,pname varchar2(10),
status varchar2(10) check(status
in('c','p','i')));
Table
created.
SQL> desc project52;
SQL>
insert into project52 values(101,'engg','p');
1
row created.
SQL>
insert into project52 values(102,'swmaking','i');
1
row created.
SQL>
insert into project52 values(103,'polution','c');
1
row created.
SQL>
insert into project52 values(104,'game','i');
1
row created.
SQL>
insert into project52 values(105,'software','p');
1
row created.
SQL> select * from project52;
SQL>
create table emp_pro52(eno number(5) references employee52,pno number(5)
references project52,no_of_days number(5));
Table
created.
SQL> desc emp_pro52;
SQL> insert into emp_pro52 values(1,102,5);
1
row created.
SQL> insert into emp_pro52 values(1,105,3);
1
row created.
SQL> insert into emp_pro52 values(2,101,2);
1
row created.
SQL> insert into emp_pro52 values(1,104,1);
1
row created.
SQL> insert into emp_pro52 values(3,105,4);
1
row created.
SQL> insert into emp_pro52 values(4,102,5);
1
row created.
SQL> insert into emp_pro52 values(5,104,7);
1
row created.
SQL> insert into emp_pro52 values(2,102,6);
1
row created.
SQL> select * from emp_pro52;
1)create or replace pl/sql function
to return total no of incomplete project of given employee.
SQL>
create or replace function f9
2
(v_ename in varchar2)
3
return number
4 is
5
v_count number;
6 begin
7
select count(project52.pno) into v_count from
employee52,project52,emp_pro52
8
where employee52.eno=emp_pro52.eno and project52.pno=emp_pro52.pno and
9
status='i' and employee52.ename=v_ename;
10
return v_count;
11 end
f9;
12 /
Function
created.
SQL>
declare
2
eename varchar2(10);
3 c
number;
4 begin
5
eename:='&eename';
6
c:=f9(eename);
7
dbms_output.put_line('No of incomplite project-'||c);
8 end;
9 /
***Output***
Enter
value for eename: yogesh
old 5: eename:='&eename';
new 5: eename:='yogesh';
No
of incomplite project-2
PL/SQL
procedure successfully completed.
2) create or replace pl/sql
function to return total number of employees working on any project for more
than 25 days
SQL> create or replace function f10
2
return number
3 is
4
v_count number;
5 begin
6
select count(employee52.eno) into v_count from employee52,project52, emp_pro52
7
where employee52.eno=emp_pro52.eno and project52.pno=emp_pro52.pno and
8
no_of_days>25;
9
return v_count;
10 end
f10;
11 /
Function
created.
SQL>
declare
2 c
number;
3 begin
4
c:=f10();
5
dbms_output.put_line('Total number of employee-'||c);
6 end;
7 /
***OUTPUT***
Total
number of employee-3
PL/SQL
procedure successfully completed.
