Question

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');

ASSIyen b 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. 1. 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 emp SSN. The template for defining such a procedure is given as below create procedure update super SSN(empSSN in employee. SSN type, new_super SSN in employee. superssn&type) as ... //procedure code 2. Write a stored function named as Numof Employee_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 From dname, Numof Employee_byDept (dname) department;

Answer 1

This demonstration is using Oracle Live SQL.

1.Stored Procedure :

create procedure update_superSSN(empSSN in employee.SSN%type,
new_superSSN in employee.super_ssn%type)
as
begin
update employee set super_ssn=new_superSSN where ssn=empSSN;
end;

Executing stored procedure :

exec update_superSSN('123456789','333445555');

Employee table :

FNAME MINIT LNAME SSN BDATE ADDRESS SEX SALARY SUPER_SSN DNO John Smith 12345678989-JAN-65 731 Fondren, Houston, TXM 30000 3334455555 Franklin T Wong 333445555 88-DEC-55 638 Voss, Houston, TX M 48000 8886655555 9876543214 Alicia J Zelaya 25000 Jennifer S Wallace 43000 Ramesh к Narayan 999887777 987654321 666884444 453453453 987987987 19-JAN-68 20-JUN-41 15-SEP-62 31-JUL-72 29-MAR-69 3321 Castle, Spring, TX 291 Berry, Bellaire, TX 975 Fire Oak, Humble, Tx 5631 Rice, Houston, TX 980 Dallas, Houston, TX F F M F M 38000 888665555 4 333445555 5 3334455555 987654321 4 Joyce A English 25000 Ahmad V Jabbar 25000 Borg 888665555 18-NOV-37 450 Stone, Houston, TX M 55800 James Download csv ose colocad

*******************************

2.Function :

create function NumOfEmployee_ByDept(deptname varchar2)
return number
is
numEmp number(2):=0;
begin
select count(ssn) into numEmp from employee where dno=(select dnumber from department where
dname=deptname);
return numEmp;
end;

Calling function :

select dname,NumOfEmployee_ByDept(dname) from department;

Screen in Oracle Live SQL :