[PL/SQL: stored function] Write a script that performs the steps listed below.

i) Creates a function deptavgsal which takes a department number and returns the average salary of all employees in the department. If the input department number is missing, the function should return -1, and if invalid, return -2.

ii) Calls the function as a stand-alone (i.e., without a base table) function, once with no department number, three times with an invalid number (specifically, 2, 3, and 6), three times with a valid number (specifically, 1, 4, and 5). If a function is to be called stand- alone, then the system dummy table DUAL may be used instead. (Run select * from DUAL; to see whats in the table DUAL.

iii) Calls the function inside a query statement to retrieve the name and the deptavgsal of all departments of which the deptavgsal is greater than 32000 and displays the result in the increasing order of department number.

iv) Drops the function.

CREATE TABLE EMPLOYEE Figure 6.1 SOL CREATE TABLE data definition statements for defining the COMPANY schema from Fiqure 5.7 (Fname VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, VARCHAR(30), CHAR, DECIMAL (10,2) CHAR(9), INT NOT NULL, Minit Lname Ssn Bdate Address NOT NULL, NOT NULL, ex Salary Super_ssn Dno NOT NULL PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT VARCHAR(15) INT CHAR(9) DATE, NOT NULL NOT NULL, NOT NULL name Dnumber Mgr ssn Mgr start_date PRIMARY KEY (Dnumber), UNIQUE (Dname) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE DEPT LOCATIONS INT VARCHAR(15) NOT NULL NOT NULL (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber)) CREATE TABLE PROJECT Pname NOT NULL NOT NULL Pnumber Plocation Dnum VARCHAR(15 INT VARCHAR(15), INT NOT NULL PRIMARY KEY (Pnumber) UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)); CREATE TABLE WORKS ON CHAR(9) INT DECIMAL(3,1) NOT NULL NOT NULL, NOT NULL ssn no Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber)) CREATE TABLE DEPENDENT CHAR(9) VARCHAR(15 CHAR, DATE VARCHAR(8), NOT NULL NOT NULL ssn Dependent_name Sex Bdate Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE EMPLOYEE Figure 6.1 SOL CREATE TABLE data definition statements for defining the COMPANY schema from Fiqure 5.7 (Fname VARCHAR(15) CHAR, VARCHAR(15) CHAR(9) DATE, VARCHAR(30), CHAR, DECIMAL (10,2) CHAR(9), INT NOT NULL, Minit Lname Ssn Bdate Address NOT NULL, NOT NULL, ex Salary Super_ssn Dno NOT NULL PRIMARY KEY (Ssn), CREATE TABLE DEPARTMENT VARCHAR(15) INT CHAR(9) DATE, NOT NULL NOT NULL, NOT NULL name Dnumber Mgr ssn Mgr start_date PRIMARY KEY (Dnumber), UNIQUE (Dname) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)) CREATE TABLE DEPT LOCATIONS INT VARCHAR(15) NOT NULL NOT NULL (Dnumber Dlocation PRIMARY KEY (Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT (Dnumber)) CREATE TABLE PROJECT Pname NOT NULL NOT NULL Pnumber Plocation Dnum VARCHAR(15 INT VARCHAR(15), INT NOT NULL PRIMARY KEY (Pnumber) UNIQUE (Pname), FOREIGN KEY (Dnum) REFERENCES DEPARTMENT (Dnumber)); CREATE TABLE WORKS ON CHAR(9) INT DECIMAL(3,1) NOT NULL NOT NULL, NOT NULL ssn no Hours PRIMARY KEY (Essn, Pno), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn) FOREIGN KEY (Pno) REFERENCES PROJECT (Pnumber)) CREATE TABLE DEPENDENT CHAR(9) VARCHAR(15 CHAR, DATE VARCHAR(8), NOT NULL NOT NULL ssn Dependent_name Sex Bdate Relationship PRIMARY KEY (Essn, Dependent_name), FOREIGN KEY (Essn) REFERENCES EMPLOYEE(Ssn))