_________________________

companyDB.sql

drop table department cascade constraints; create table department ( Dname varchar2(15) not null, Dnumber int not null, Mgr_ssn char(9) not null, mgr_start_date Date, primary key (Dnumber), Unique (Dname));

insert into DEPARTMENT values ('Research', '5', '333445555', '22-May-1988'); insert into DEPARTMENT values ('Administration', '4', '987654321', '01-Jan-1995'); insert into DEPARTMENT values ('Headquarters', '1', '888665555', '19-Jun-1981');

drop table employee cascade constraints; create table employee ( Fname varchar2(15) not null, Minit char(1), Lname varchar2(15) not null, Ssn char(9), Bdate date, Address varchar2(30), Sex char(1), Salary decimal(10,2), super_ssn char(9), dno int, primary key (Ssn), foreign key (dno) references department(Dnumber));

insert into EMPLOYEE values ( 'John', 'B', 'Smith', '123456789', '09-Jan-1965', '731 Fondren, Houston, TX', 'M', '30000', '333445555', '5' ); insert into EMPLOYEE values ( 'Franklin', 'T', 'Wong', '333445555', '08-Dec-1955', '638 Voss, Houston, TX', 'M', '40000', '888665555', '5' ); insert into EMPLOYEE values ('Alicia', 'J', 'Zelaya', '999887777', '19-Jan-1968', '3321 Castle, Spring, TX', 'F', '25000', '987654321', '4' ); insert into EMPLOYEE values ('Jennifer', 'S', 'Wallace', '987654321', '20-Jun-1941', '291 Berry, Bellaire, TX', 'F', '43000', '888665555', '4'); insert into EMPLOYEE values ('Ramesh', 'K', 'Narayan', '666884444', '15-Sep-1962', '975 Fire Oak, Humble, TX', 'M', '38000', '333445555', '5'); insert into EMPLOYEE values ('Joyce', 'A', 'English', '453453453', '31-Jul-1972', '5631 Rice, Houston, TX', 'F', '25000', '333445555', '5'); insert into EMPLOYEE values ('Ahmad', 'V', 'Jabbar', '987987987', '29-Mar-1969', '980 Dallas, Houston, TX', 'M', '25000', '987654321', '4'); insert into EMPLOYEE values ('James', 'E', 'Borg', '888665555', '10-Nov-1937', '450 Stone, Houston, TX', 'M', '55000', NULL, '1');

alter table employee add (foreign key(super_ssn) references employee(ssn)); alter table department add (foreign key(mgr_ssn) references employee(ssn));

drop table DEPT_LOCATIONS cascade constraints; create table DEPT_LOCATIONS ( Dnumber int not null, Dlocation varchar2(15) not null, primary key (Dnumber, Dlocation), foreign key(Dnumber) references department(dnumber));

insert into DEPT_LOCATIONS values ('1', 'Houston'); insert into DEPT_LOCATIONS values ('4', 'Stafford'); insert into DEPT_LOCATIONS values ('5', 'Bellaire'); insert into DEPT_LOCATIONS values ('5', 'Sugarland'); insert into DEPT_LOCATIONS values ('5', 'Houston');

drop table project cascade constraints; create table project ( Pname varchar2(15) not null, Pnumber int not null, Plocation varchar2(15), Dnum int not null, primary key (Pnumber), unique (Pname), foreign key(Dnum) references department(Dnumber)); insert into PROJECT values ('ProjectX', '1', 'Bellaire', '5'); insert into PROJECT values ('ProjectY', '2', 'Sugarland', '5'); insert into PROJECT values ('ProjectZ', '3', 'Houston', '5'); insert into PROJECT values ('Computerization', '10', 'Stafford', '4'); insert into PROJECT values ('Reorganization', '20', 'Houston', '1'); insert into PROJECT values ('Newbenefits', '30', 'Stafford', '4');

drop table works_on cascade constraints; create table works_on ( Essn char(9) not null, Pno int not null, hours decimal(3,1), primary key(essn, pno), foreign key(Essn) references employee, foreign key(pno) references project); insert into WORKS_ON values ('123456789', '1', '32.5'); insert into WORKS_ON values ('123456789', '2', '7.5'); insert into WORKS_ON values ('666884444', '3', '40.0'); insert into WORKS_ON values ('453453453', '1', '20.0'); insert into WORKS_ON values ('453453453', '2', '20.0'); insert into WORKS_ON values ('333445555', '2', '10.0'); insert into WORKS_ON values ('333445555', '3', '10.0'); insert into WORKS_ON values ('333445555', '10', '10.0'); insert into WORKS_ON values ('333445555', '20', '10.0'); insert into WORKS_ON values ('999887777', '30', '30.0'); insert into WORKS_ON values ('999887777', '10', '10.0'); insert into WORKS_ON values ('987987987', '10', '35.0'); insert into WORKS_ON values ('987987987', '30', '5.0'); insert into WORKS_ON values ('987654321', '30', '20.0'); insert into WORKS_ON values ('987654321', '20', '15.0'); insert into WORKS_ON values ('888665555', '20', NULL);

drop table dependent cascade constraints; create table dependent ( essn char(9) not null, dependent_name varchar2(15) not null, Sex char(1), Bdate date, relationship varchar2(8), primary key(essn, dependent_name), foreign key(essn) references employee); insert into DEPENDENT values ('333445555', 'Alice', 'F', '05-Apr-1986', 'Daughter'); insert into DEPENDENT values ('333445555', 'Theodore', 'M', '25-Oct-1983', 'Son'); insert into DEPENDENT values ('333445555', 'Joy', 'F', '03-May-1958', 'Spouse'); insert into DEPENDENT values ('987654321', 'Abner', 'M', '28-Feb-1942', 'Spouse'); insert into DEPENDENT values ('123456789', 'Michael', 'M', '04-Jan-1988', 'Son'); insert into DEPENDENT values ('123456789', 'Alice', 'F', '30-Dec-1988', 'Daughter'); insert into DEPENDENT values ('123456789', 'Elizabeth', 'F', '05-May-1967', 'Spouse');

Assignment 6 PL/SQL Assignment 3 You need to create COMPANY database first in order to complete this assignment. Be sure to download companyDB.sql and execute it before you start. Write a stored procedure named as update_superSSN. The procedure takes two input parameters: empSSN (as an employee's SSN) and new_superSSN (as a new supervisor's SSN). The procedure will update the supervisor's SSN to new superSSN for the given empSSN. The template for defining such a procedure is given as below create procedure update_superSSN (empSSN in employee. SSN%type, new-superSSN in employee. supe r s sn%type) as //procedure code Write a stored function named as NumOfEmployee_ByDept. The function has one input parameter deptname (i.e., department name) and returns the number of employees working in that department. Once the stored function is compiled without any errors, you can run the following SQL query Select dname, NumfEmployee_byDept (dname) Erom department