Employee and Project Table have Many To Many Relationship. Consider the Following Entities and Their Relationship. Employee(eno,ename,city,deptname) Project(pno,pname,status) Relationship Between Employee and Project is Many to Many with descriptive attribute no_of_days employee worked on that project. Constraints: Primary Key Status to be C-Complete, P-Progressive, I-Incomplete

SQL> create table employee

  2  (eno number primary key,

  3  ename varchar(20),

  4  city varchar(20),

  5  deptname varchar(25));


Table created.


SQL> create table project

  2  (pno number primary key,

  3  pname varchar(20),

  4  status varchar(2) check(status in('C','P','I')));


Table created.


SQL> create table employee_project

  2  (eno number references employee,

  3  pno number references project,

  4  no_of_days number);


Table created.



SQL> select * from employee;


       ENO ENAME                CITY                 DEPTNAME

---------- -------------------- -------------------- -------------------------

         1 Raj                  Pune                 Computer

         2 Ramesh               Mumbai               Computer

         3 Ganesh               Delhi                HR

         4 John                 Chennai              Production

         5 Gagan                Mumbai               Sales

         6 Hitesh               Delhi                Sales


6 rows selected.


SQL> select * from project;


       PNO PNAME                ST

---------- -------------------- --

       101 Disaster Management  P

       102 Corruption           I

       103 Railway              P

       104 Library Management   C

       105 Price Control        I


SQL> select * from employee_project;


       ENO        PNO NO_OF_DAYS

---------- ---------- ----------

         1        101         26

         1        102         10

         2        102         30

         3        103         20

         4        104         38

         4        105         20

         5        105         19

         6        102         18


8 rows selected.


a)Create or Replace a PL/SQL Function to Return Total Number of Employees Working on any Project For More Than 25 Days.


SQL> set serveroutput on

SQL> create or replace function f6

  2  return number

  3  as

  4  v_count number;

  5  begin

  6  select count(ename) into v_count from employee,employee_project

  7  where employee.eno=employee_project.eno

  8  and no_of_days>25;

  9  if SQL%Found then

 10  return v_count;

 11  else

 12  return 0;

 13  end if;

 14  end f6;

 15  /


Function created.


**CALL PROGRAM**

 SQL> declare

  2  r number;

  3  begin

  4  r:=f6;

  5  if r=0 then

  6  dbms_output.put_line('Record Does Not Exist');

  7  else

  8  dbms_output.put_line('The Number Of Employees Working More Than 25 Days are:' || r);

  9  end if;

 10  end;

 11  /

The Number Of Employees Working More Than 25 Days are:3


PL/SQL procedure successfully completed.


b) Write a Cursor To Display All Employees Names Working on a Project Whose Status is Incomplete.


SQL> declare

  2  cursor c6 is

  3  select ename from employee,project,employee_project

  4  where employee.eno=employee_project.eno

  5  and project.pno=employee_project.pno

  6  and status='I';

  7  y c6%rowtype;

  8  begin

  9  dbms_output.put_line('EMPLOYEE NAMES');

 10  dbms_output.put_line('--------------');

 11  for y in c6

 12  loop

 13  dbms_output.put_line(y.ename);

 14  end loop;

 15  exception

 16  when NO_DATA_FOUND then

 17  dbms_output.put_line('NO EMPLOYEE NAMES FOUND.');

 18  end;

 19  /

EMPLOYEE NAMES

--------------

Raj

Ramesh

John

Gagan

Hitesh


PL/SQL procedure successfully completed.