Some help: Notice: before working on these problems, you need to execute the com
ID: 3686886 • Letter: S
Question
Some help:
Notice: before working on these problems, you need to
execute the command
set serveroutput on;
re-run the script files companyDB.sql to create the COMPANY database.
run the following command to make the COMPANY database permanently stored in database commit; purse recyclebin; // remove all tables whose names start with BIN$
before you start working on each problem, run the command “savepoint pt1;”, after you finish testing the solution to the problem, run the command “rollback to savepoint pt1;”. By doing so, you will use the same database (state) when working on each problem.
Use cursor to solve the following questions although some may be solved without it. Since you are required to use cursor for the following questions, you need to write an anonymous block that contains a cursor. Be sure to save all your codes in a file.
1. For each employee who is working on more than 1 project, print his/her name and project names.
2. Use cursor for update to increase the salaries by 10% for the employees who work for department 5
3. Use parameterized cursor to list, for a given project number, the names of employees working on it. Then test the parameterized cursor for the project with pnumber=10;
----------------------------------------
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');
Explanation / Answer
1)
2)
SET SERVEROUTPUT ON
DECLARE
FirstName employee.fname%type;
MiddleInit employee.minit%type;
LastName employee.lname%type;
EmpSalary employee.salary%type;
SocialSec employee.ssn%type := '888665555';
DepName Department.dname%type;
DepNum Department.dnumber%type;
NewSalary employee.salary%type;
BEGIN
SELECT fname, minit, lname, dname, Dnumber, Salary
INTO FirstName, MiddleInit, LastName, DepName, DepNum, EmpSalary
FROM Employee, department
WHERE SSN = SocialSec
and dno = dnumber;
DBMS_OUTPUT.PUT_LINE(FirstName||' '|| MiddleInit ||' '||
LastName ||' ' || DepName || ' ' || DepNum || ' ' || EmpSalary);
IF DepNum = 1 then
NewSalary := EmpSalary * 1.05; -- increase salary by 5%;
DBMS_OUTPUT.PUT_LINE('New Salary = ' || NewSalary);
ELSIF DepNum = 4 then
NewSalary := EmpSalary * 1.1; -- increase salary by 10%;
DBMS_OUTPUT.PUT_LINE('New Salary = ' || NewSalary);
ELSIF DepNum = 5 then
NewSalary := EmpSalary * 1.15; -- increase salary by 15%;
DBMS_OUTPUT.PUT_LINE('New Salary = ' || NewSalary);
END IF;
END;
========================
James E Borg Headquarters 1 55000
New Salary = 57750
===================
Try change SSN to ‘123456789’ or ‘333445555’ etc.ja
3)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.