SOLUTION MANUAL(Odd#)
CHAPTER 1
CODING EXERCISES
1.
|
SET SERVEROUTPUT ON
-- 6/3/2003
BEGIN
DBMS_OUTPUT.PUT_LINE('Name:John Smith');
DBMS_OUTPUT.PUT_LINE('Class:CMID563');
DBMS_OUTPUT.PUT_LINE('Semester:Summer 2003');
DBMS_OUTPUT.PUT_LINE('Session:1A2');
END;
/
|
3.
|
SET SERVEROUTPUT ON
DECLARE
var_dpt_name department.dpt_name%TYPE;
var_dpt_mgrssn department.dpt_mgrssn%TYPE;
var_dpt_mgr_start_date department.dpt_mgr_start_date%TYPE;
var_col VARCHAR2(20);
BEGIN
SELECT dpt_name, dpt_mgrssn, dpt_mgr_start_date
INTO var_dpt_name, var_dpt_mgrssn,
var_dpt_mgr_start_date
FROM department
WHERE dpt_no = &dept_number;
DBMS_OUTPUT.PUT_LINE('Department Name:' ||
var_dpt_name);
DBMS_OUTPUT.PUT_LINE('SSN of Department''s
Manager:' || var_dpt_mgrssn);
DBMS_OUTPUT.PUT_LINE('Manager''s start date:' ||
var_dpt_mgr_start_date);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data
found!');
END;
/
|
5.
|
SET SERVEROUTPUT ON
DECLARE
a NUMBER(10,2);
-- set the precision to 10 with 2 decimal
BEGIN
a := &b + &c * &d**2 - &e / 4;
DBMS_OUTPUT.PUT_LINE('a:' || a);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error!');
END;
/
|
CHAPTER 2
CODING EXERCISES
1.
|
SET
SERVEROUTPUT ON
DECLARE
v_date DATE :=
TO_DATE('&sv_user_date', 'DD-MM-YYYY');
v_day VARCHAR2(15);
BEGIN
v_day := RTRIM(TO_CHAR(v_date,
'DAY'));
IF v_day IN ('SATURDAY',
'SUNDAY') THEN
DBMS_OUTPUT.PUT_LINE(v_date||'
falls on weekend');
END IF;
- control resumes here
DBMS_OUTPUT.PUT_LINE('Done…');
END;
/
|
3.
|
SET
SERVEROUTPUT ON
DECLARE
v_final_grade NUMBER :=
&sv_num;
v_letter_grade CHAR(1);
BEGIN
IF v_final_grade BETWEEN 90
AND 100 THEN
v_letter_grade
:= 'A';
ELSIF v_final_grade BETWEEN 80
AND 89 THEN
v_letter_grade
:= 'B';
ELSIF v_final_grade BETWEEN 70
AND 79 THEN
v_letter_grade
:= 'C';
ELSIF v_final_grade BETWEEN 60
AND 69 THEN
v_letter_grade
:= 'D';
ELSE
v_letter_grade
:= 'F';
END IF;
-- control resumes here
DBMS_OUTPUT.PUT_LINE('Letter
grade is: '||v_letter_grade);
END;
/
|
5.
|
SET
SERVEROUTPUT ON
DECLARE
v_year number(4) :=
&sv_year;
BEGIN
IF MOD(v_year,100) = 0 THEN
IF
MOD(v_year,400) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_year
||' is a Century and leap year');
ELSE
DBMS_OUTPUT.PUT_LINE(v_year
||' is a Century and not a leap year');
END IF;
ELSIF MOD(v_year,4) = 0 THEN
DBMS_OUTPUT.PUT_LINE(v_year
||' is not a century but is a leap year');
ELSE
DBMS_OUTPUT.PUT_LINE(v_year
||' is not a century and not a leap year');
END IF;
DBMS_OUTPUT.PUT_LINE('Done…');
END;
/
|
7.
|
SET
SERVEROUTPUT ON
DECLARE
CURSOR mycur IS
SELECT * FROM
shipment;
total_value NUMBER(8,2) := 0;
myrow shipment%ROWTYPE;
BEGIN
OPEN mycur;
LOOP
FETCH mycur
INTO myrow;
EXIT WHEN
mycur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Description:
'|| myrow.description);
DBMS_OUTPUT.PUT_LINE('Price:
'|| myrow.itemrate);
DBMS_OUTPUT.PUT_LINE('Quantity
on hand: '|| myrow.qty_hand);
DBMS_OUTPUT.PUT_LINE('value:
'|| myrow.qty_hand* myrow.itemrate);
total_value :=
total_value + myrow.qty_hand * myrow.itemrate;
END LOOP;
CLOSE mycur;
DBMS_OUTPUT.PUT_LINE('Total
Value: '|| total_value);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There
are no items in this table');
END;
/
|
9.
|
SET
SERVEROUTPUT ON
DECLARE
CURSOR cur_emp is
SELECT
emp_last_name, emp_middle_name, emp_first_name, emp_salary
FROM
employee, department
WHERE
emp_dpt_number = dpt_no
AND
dpt_name = 'Production'
ORDER
BY emp_first_name;
rec_emp cur_emp%rowtype;
BEGIN
DBMS_OUTPUT.PUT_LINE('Department:
Production');
OPEN cur_emp;
LOOP
FETCH cur_emp
INTO rec_emp;
EXIT WHEN
cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Full
Name:' || rec_emp.emp_first_name || ' '
||
rec_emp.emp_middle_name || ' '
||
rec_emp.emp_last_name);
DBMS_OUTPUT.PUT('Salary:'
|| rec_emp.emp_salary);
IF rec_emp.emp_salary <= 30000 THEN
DBMS_OUTPUT.PUT_LINE('
(Class A)');
ELSIF
rec_emp.emp_salary BETWEEN 30000 AND 60000 THEN
DBMS_OUTPUT.PUT_LINE('
(Class B)');
ELSE
DBMS_OUTPUT.PUT_LINE('
(Class C)');
END IF;
END LOOP;
CLOSE cur_emp;
END;
/
|
11.
|
SET
SERVEROUTPUT ON
DECLARE
exp_not_found EXCEPTION;
v_day VARCHAR2(10);
var_counter INTEGER := 0;
CURSOR cur_emp is
SELECT
emp_last_name, emp_first_name, emp_middle_name, emp_date_of_birth
FROM
employee;
rec_emp cur_emp%rowtype;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp
INTO rec_emp;
EXIT WHEN
cur_emp%NOTFOUND;
v_day :=
RTRIM(TO_CHAR(rec_emp.emp_date_of_birth, 'DAY'));
IF v_day =
'TUESDAY' THEN
DBMS_OUTPUT.PUT_LINE('Full
Name:' || rec_emp.emp_first_name || ' '
||
rec_emp.emp_middle_name || ' '
||
rec_emp.emp_last_name);
var_counter
:= var_counter + 1;
END IF;
IF var_counter
= 2 THEN
EXIT;
END IF;
END LOOP;
CLOSE cur_emp;
IF var_counter <> 2 THEN
RAISE
exp_not_found;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Could
not find 2 employees born on Tuesday!');
WHEN exp_not_found THEN
DBMS_OUTPUT.PUT_LINE('Could
not find 2 employees born on Tuesday!');
END;
/
|
13.
|
SET
SERVEROUTPUT ON
DECLARE
sum_work_hours_planned assignment.work_hours_planned%TYPE
:= 0.0;
sum_work_hours assignment.work_hours%TYPE
:= 0.0;
CURSOR cur_dep is
SELECT dpt_no,
dpt_name, emp_last_name, emp_first_name, emp_middle_name
FROM
department DEP, employee EMP
WHERE
DEP.dpt_mgrssn = EMP.emp_ssn
ORDER BY
emp_first_name;
rec_dep cur_dep%rowtype;
CURSOR cur_pro(par_dept_number
department.dpt_no%TYPE) is
SELECT
pro_name
FROM project
WHERE
pro_dept_number = par_dept_number;
BEGIN
OPEN cur_dep;
LOOP
FETCH cur_dep
INTO rec_dep;
EXIT WHEN
cur_dep%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department:
' || rec_dep.dpt_name);
DBMS_OUTPUT.PUT_LINE('Department
Manager:' || rec_dep.emp_first_name ||
'
' || rec_dep.emp_middle_name || ' ' || rec_dep.emp_last_name);
FOR rec_pro IN
cur_pro(rec_dep.dpt_no)
LOOP
DBMS_OUTPUT.PUT_LINE('Project
Name:' || rec_pro.pro_name);
END LOOP;
SELECT
SUM(work_hours), SUM(work_hours_planned)
INTO
sum_work_hours, sum_work_hours_planned
FROM
assignment
WHERE
work_pro_number in (
SELECT
pro_number
FROM
project
WHERE
pro_dept_number = rec_dep.dpt_no);
DBMS_OUTPUT.PUT_LINE('Total
work hours:' || sum_work_hours);
DBMS_OUTPUT.PUT_LINE('Total
work hours planned:' || sum_work_hours_planned);
DBMS_OUTPUT.PUT_LINE('**************************************');
END LOOP;
CLOSE cur_dep;
END;
/
|
15.
|
SET
SERVEROUTPUT ON
DECLARE
par_room_id bed.room_id%TYPE :=
'&room_id';
CURSOR cur_pat IS
SELECT
pat_last_name, pat_middle_name, pat_first_name, pat_id
FROM
patient PAT, bed BED
WHERE
PAT.bed_number = BED.bed_number
AND
room_id = par_room_id
ORDER
BY pat_first_name;
rec_pat cur_pat%rowtype;
CURSOR cur_staff(param1
patient.pat_id%type) is
SELECT
DISTINCT staff_first_name, staff_middle_name, staff_last_name
FROM
staff STF, treatment TRM
WHERE
STF.staff_id = TRM.staff_id
AND
pat_id = param1;
rec_staff cur_staff%rowtype;
BEGIN
DBMS_OUTPUT.PUT_LINE('Room
id:' || par_room_id);
OPEN cur_pat;
LOOP
FETCH cur_pat
INTO rec_pat;
EXIT WHEN
cur_pat%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Full
Name:' || rec_pat.pat_first_name || ' '
||
rec_pat.pat_middle_name || ' '
||
rec_pat.pat_last_name);
DBMS_OUTPUT.PUT_LINE('Treated
by:');
OPEN
cur_staff(rec_pat.pat_id);
LOOP
FETCH
cur_staff INTO rec_staff;
EXIT
WHEN cur_staff%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Staff
member''s Name:' || rec_staff.staff_first_name || ' '
||
rec_staff.staff_middle_name || ' '
||
rec_staff.staff_last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('**************************************');
CLOSE
cur_staff;
END LOOP;
CLOSE cur_pat;
END;
/
|
17.
|
SET
SERVEROUTPUT ON SIZE 10000;
DECLARE
exc_exist EXCEPTION;
var_cnt NUMBER;
var_bol_exist BOOLEAN :=
FALSE; -- indicate if the situation where a patient is treated and prescribed
--
by the same staff member exists.
CURSOR cur_pre IS
SELECT
DISTINCT PRE.pat_id, PRE.staff_id, hospital_title,
staff_first_name,
staff_middle_name, staff_last_name,
pat_first_name,
pat_middle_name, pat_last_name
FROM
prescription PRE, staff STF, patient PAT
WHERE
PRE.staff_id = STF.staff_id
AND PAT.pat_id
= PRE.pat_id;
rec_pre cur_pre%ROWTYPE;
BEGIN
OPEN cur_pre;
LOOP
FETCH cur_pre
INTO rec_pre;
EXIT WHEN
cur_pre%NOTFOUND;
SELECT
count(*) INTO var_cnt
FROM
treatment
WHERE
pat_id = rec_pre.pat_id
AND
staff_id = rec_pre.staff_id;
IF var_cnt
> 0 THEN
var_bol_exist
:= TRUE;
DBMS_OUTPUT.PUT_LINE('Staff
member''s Name:' || rec_pre.staff_first_name ||
'
' || rec_pre.staff_middle_name || ' ' || rec_pre.staff_last_name);
DBMS_OUTPUT.PUT_LINE('Patient''s
Name:' || rec_pre.pat_first_name ||
'
' || rec_pre.pat_middle_name || ' ' || rec_pre.pat_last_name);
DBMS_OUTPUT.PUT_LINE('**************************************');
END IF;
END LOOP;
CLOSE cur_pre;
IF var_bol_exist THEN
RAISE
exc_exist;
END IF;
EXCEPTION
WHEN exc_exist THEN
DBMS_OUTPUT.PUT_LINE('Patient
treated and prescribed by same staff found!');
END;
/
|
19.
|
SET
SERVEROUTPUT ON
DECLARE
var_cnt INTEGER :=
0; -- indicate the number of prescriptions for a specific patient.
CURSOR cur_pat IS
SELECT
PRE.pat_id, pat_last_name, pat_middle_name, pat_first_name
FROM
prescription PRE, patient PAT
WHERE
PRE.pat_id = PAT.pat_id
GROUP
BY PRE.pat_id, pat_last_name, pat_middle_name, pat_first_name
HAVING
count(*) > 1;
rec_pat cur_pat%ROWTYPE;
CURSOR cur_last_update(param1
prescription.pat_id%TYPE) IS
SELECT
(SYSDATE - date_last_updated) date_diff
FROM
prescription
WHERE pat_id =
param1;
rec_last_update
cur_last_update%ROWTYPE;
BEGIN
OPEN cur_pat;
LOOP
FETCH cur_pat
INTO rec_pat;
EXIT WHEN
cur_pat%NOTFOUND;
OPEN
cur_last_update(rec_pat.pat_id);
LOOP
FETCH
cur_last_update INTO rec_last_update;
EXIT
WHEN cur_last_update%NOTFOUND;
IF
rec_last_update.date_diff < 7 AND rec_last_update.date_diff > 0 THEN
var_cnt
:= var_cnt + 1;
END
IF;
IF
var_cnt = 2 THEN
DBMS_OUTPUT.PUT_LINE('Patient''s
Name:' || rec_pat.pat_first_name ||
'
' || rec_pat.pat_middle_name || ' ' || rec_pat.pat_last_name);
EXIT;
END
IF;
END LOOP;
CLOSE
cur_last_update;
var_cnt := 0;
-- reset to 0 and start checking on another pat_id.
END LOOP;
CLOSE cur_pat;
END;
/
|
CHAPTER 3
CODING EXERCISES
1.
|
SET
SERVEROUTPUT ON
CREATE
OR REPLACE FUNCTION incr_salary(
ssn varchar2,percentage
NUMBER)
RETURN
NUMBER
IS
rsal NUMBER(10,2);
BEGIN
UPDATE employee
SET emp_salary = ((emp_salary
* percentage)/100+emp_salary)
WHERE emp_ssn=ssn;
SELECT emp_salary INTO rsal
FROM employee
WHERE emp_ssn = ssn;
RETURN (rsal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee
not found ');
END
incr_salary;
|
3.
|
SET
SERVEROUTPUT ON
CREATE
OR REPLACE PROCEDURE emp_dept(
empno IN VARCHAR2,
deptname OUT VARCHAR2)
IS
BEGIN
SELECT dpt_name
INTO deptname FROM department
WHERE dpt_no =
(SELECT
emp_dpt_number FROM employee WHERE emp_ssn = empno) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee
or Dept does not exist ');
END
emp_dept;
|
5.
|
CREATE
OR REPLACE TRIGGER MYTRIG4
BEFORE
UPDATE ON EMPLOYEE FOR EACH ROW
BEGIN
IF (:NEW.EMP_SALARY <
20000) THEN
RAISE_APPLICATION_ERROR(21001,'SALARY
IS TOO LESS');
END IF;
END;
/
|
7.
|
CREATE
OR REPLACE PACKAGE pac_emp
AS
PROCEDURE
pro_find_emp(
par_emp_ssn IN employee.emp_ssn%TYPE,
par_full_name OUT VARCHAR2,
par_dept_no OUT department.dpt_no%TYPE);
FUNCTION
fun_get_salary(
par_emp_ssn employee.emp_ssn%TYPE,
par_percentage NUMBER)
RETURN NUMBER;
END
pac_emp;
/
CREATE
OR REPLACE PACKAGE BODY pac_emp
AS
PROCEDURE
pro_find_emp(
par_emp_ssn IN employee.emp_ssn%TYPE,
par_full_name OUT VARCHAR2,
par_dept_no OUT department.dpt_no%TYPE)
IS
var_emp_last_name
employee.emp_last_name%TYPE;
var_emp_middle_name
employee.emp_middle_name%TYPE;
var_emp_first_name
employee.emp_first_name%TYPE;
BEGIN
SELECT emp_last_name,
emp_middle_name, emp_first_name, emp_dpt_number
INTO
var_emp_last_name, var_emp_middle_name, var_emp_first_name, par_dept_no
FROM employee
WHERE emp_ssn
= par_emp_ssn;
par_full_name :=
var_emp_first_name || ' ' || var_emp_middle_name || ' ' || var_emp_last_name;
EXCEPTION
WHEN NO_DATA_FOUND then
par_full_name := null;
par_dept_no := -1;
END
pro_find_emp;
FUNCTION
fun_get_salary(
par_emp_ssn employee.emp_ssn%TYPE,
par_percentage NUMBER)
RETURN NUMBER
IS
ret_salary NUMBER(7,2);
BEGIN
SELECT emp_salary
INTO ret_salary
FROM employee
WHERE emp_ssn = par_emp_ssn;
ret_salary := ret_salary * (1
+ par_percentage / 100);
return (ret_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
return (-1);
END
fun_get_salary;
END
pac_emp;
/
SET
SERVEROUTPUT ON
DECLARE
var_full_name employee.emp_first_name%type;
var_dpt_no department.dpt_no%type;
BEGIN
pac_emp.pro_find_emp('999666666',var_full_name,
var_dpt_no);
DBMS_OUTPUT.PUT_LINE(var_full_name || '
works in department ' || var_dpt_no);
DBMS_OUTPUT.PUT_LINE('His adjusted
salary is ' || fun_get_salary('999666666', 10));
END;
/
|
9.
|
CREATE
OR REPLACE FUNCTION fun_class(
par_ssn CHAR)
RETURN
VARCHAR
IS
ret_class VARCHAR2(10);
var_salary NUMBER(7,2);
BEGIN
SELECT emp_salary
INTO var_salary
FROM employee
WHERE emp_ssn = par_ssn;
IF var_salary <= 30000 THEN
ret_class
:='Class A';
ELSIF var_salary BETWEEN 30000
AND 60000 THEN
ret_class
:='Class B';
ELSE
ret_class
:='Class C';
END IF;
RETURN (ret_class);
EXCEPTION
WHEN NO_DATA_FOUND then
RETURN ('No Class');
END
fun_class;
/
|
11.
|
CREATE
OR REPLACE TRIGGER tri_start_date
BEFORE
INSERT OR UPDATE ON department FOR EACH ROW
BEGIN
IF
(RTRIM(TO_CHAR(:NEW.dpt_mgr_start_date, 'DAY')) IN ('SATURDAY', 'SUNDAY'))
THEN
RAISE_APPLICATION_ERROR(-20000,
'The start date cannot be on a Saturday or Sunday');
END IF;
END;
/
|
13.
|
DROP
TABLE lt_asgn_history;
DROP
TABLE asgn_history;
CREATE
TABLE lt_asgn_history (
work_emp_ssn CHAR(9),
work_pro_number NUMBER(2),
work_hours_completed NUMBER(5,1),
date_last_updated
DATE,
CONSTRAINT
pk_lt_asgn_history
PRIMARY KEY ( work_emp_ssn,
work_pro_number, date_last_updated),
CONSTRAINT
fk_lt_asgn_history
FOREIGN KEY (work_emp_ssn,
work_pro_number) REFERENCES assignment
)
;
CREATE
TABLE asgn_history (
work_emp_ssn CHAR(9),
work_pro_number NUMBER(2),
work_hours_completed NUMBER(5,1),
date_last_updated
DATE,
CONSTRAINT
pk_asgn_history
PRIMARY KEY ( work_emp_ssn,
work_pro_number, date_last_updated),
CONSTRAINT
fk_asgn_history
FOREIGN KEY (work_emp_ssn,
work_pro_number) REFERENCES assignment
)
;
CREATE
OR REPLACE TRIGGER tri_assg
AFTER
INSERT OR UPDATE OF work_hours_planned ON assignment
FOR
EACH ROW
BEGIN
IF :new.work_hours_planned = 0
THEN
IF
:new.work_hours >= 100 THEN
INSERT
INTO LT_ASGN_HISTORY (
work_emp_ssn,
work_pro_number, work_hours_completed, date_last_updated)
VALUES
(:new.work_emp_ssn, :new.work_pro_number,:new.work_hours, SYSDATE);
ELSE
INSERT
INTO ASGN_HISTORY (
work_emp_ssn,
work_pro_number, work_hours_completed, date_last_updated)
VALUES
(:new.work_emp_ssn, :new.work_pro_number,:new.work_hours, SYSDATE);
END IF;
END IF;
END;
/
UPDATE
assignment
SET
work_hours = 107.1, work_hours_planned = 0
WHERE
work_emp_ssn = '999333333' AND work_pro_number = 3;
UPDATE
assignment
SET
work_hours = 34, work_hours_planned = 0
WHERE
work_emp_ssn = '999222222' AND work_pro_number = 10;
|
No comments:
Post a Comment