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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONDECLARE
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 ONCREATE 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 ONCREATE 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 MYTRIG4BEFORE 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_empAS
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_dateBEFORE 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;