CREATE TABLE DEPARTMENT( DEPTNO INT PRIMARY KEY, DEPTNAME VARCHAR(30) ); INSERT INTO DEPARTMENT VALUES(1,'COMPUTER SCIENCE'); CREATE TABLE PROJECT( PROGNO INT, PNAME VARCHAR(30), DEPTNO INT, FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ); INSERT INTO PROJECT VALUES(12,'CLOUD LEARNING',1); CREATE TABLE EMPLOYEE( EMPNO INT PRIMARY KEY , ENAME VARCHAR(30), ADDRESS VARCHAR(30), SALARY BIGINT, DOB DATE, DEPTNO INT, FOREIGN KEY(DEPTNO) REFERENCES DEPARTMENT(DEPTNO) ); INSERT INTO EMPLOYEE VALUES(1111,'MRUDUL','123STREET',10000,'1992-12-05',1); CREATE TABLE WORKS_ON( EMPNO INT, PROJECTNO INT, NOOFHOURS INT, FOREIGN KEY(EMPNO) REFERENCES EMPLOYEE(EMPNO), FOREIGN KEY(PROJECTNO) REFERENCES PROJECT(PROJECTNO) ); INSERT INTO WORKS_ON VALUES(1111,12,3);

Depend on the database which you created in assignment 1 write subprograms as following:

a. Procedure that will accept project number then display list the employee name and work hours for all employees who work in the project.

b. Function that will accept the department number then return the number of employee in the department.

PL/SQL