Page 607
At the end of most of the chapters are exercises for you
to practice. This appendix contains the answers to
those exercises.n
Page 608
TRANSFORMER DATA FILE
TEST TEST TEST TEST TRF# DATE MFG MODEL STATUS WEIGHT LOCATION DATE1 RESULT1DATE2RESULT2 A100 09-JUL-85 W GO-5 AHISTORY 1000 9311Monroe 08-JUL-85 70 B670 10-SEP-86 GE W-97 CURRENT 2000 1719 Taylor A101 12-SEP-91 W GO-5 CURRENT 1000 9311Monroe 10-SEP-91 72 A100 12-SEP-91 W GO-5 CURRENT 100 STORES 12-SEP-91 73 B979 12-SEP-91 GE W-97 CURRENT 2000 8742 Pine 11-SEP-91 94 11-OCT-9176
After analyzing the database, you might have noticed a variety of problems. The most glaring is the need to create a new record for every transformer move. Not only is this time consuming, but also the clerk has made a mistake transfering the data to the new record. This is evident when looking at the weight attribute for transformer A100. The current weight is 100 and the previous weight was 1000. The clerk forgot to enter the last digit on the weight value when it was entered. Another mistake the clerk made was placing a letter A at the end of the model number in the first record. This mistake was corrected when the current record was created. However, during the time the A100 transformer was at the location, it had the wrong model number on the record. The model number is important information and the database does not have a mechanism to validate it.
Another problem with the database is the tests results. Each record has space for two test results. This causes several problems. When no tests have occurred, the database reserves space for the values. If you perform three or more tests on transformer you do not have any place to record the results unless you create a new record for the transformer. It is very difficult to compare the results of the tests because they are in different records in different fields.
The final problem was the manufacturer attribute. The database contains codes for the various values. This was done so the clerk would not have as much data entry. But users not familiar with the values may have a hard time determining which manufacturers the values represent.
For instance, does the W manufacturer value refer to Wagner or Westinghouse.
The next step is to identify the fields not dependent on the keys. Three fields meet this criteria: mfg, model, and status. You need to create three related tables. One will contain the mfg field and the manufacturer's full name. Use the model table as a validation table. Finally, a transformer history table will be created to hold historic transformer records. The final model is shown in Figure E.1.
Page 609
FIG. E.1
The Transformer history Table.
1. --a_03_01.sql-- describe department desc employee 2. --a_03_02.sql-- select * from department; 3. --a_03_03.sql-- c\department\employee l 4. --a_03_04.sql-- select last_name, first_name from employee order by last_name desc, first_name; 5. --a_03_05.sql-- select decode(gender, `M', `MALE', `F', `FEMALE', `UNKNOWN'), last_name, first_name from employee; 6. --a_03_06.sql-- >select last_name, first_name, gender from employee where gender = `M';
Page 610
7. --a_03_07.sql-- select last_name, gender, birth_date from employee where substr(birth_date, 4,3) = `JUL' and gender = `M'; 8. --a_03_08.sql-- select avg(nvl(wages, 0)) from employee; 9. --a_03_09.sql-- select fk_department, avg(nvl(wages,0)) "AVG WAGES" from employee group by fk_department; 10. --a_03_10.sql-- select last_name, first_name, wages, (((wages*.9)-150)/52) weekly from employee where fk_department ='INT'; 11. --a_03_11.sql-- select last_name, first_name, round(months_between(sysdate, employment_date)/12, 1) years from employee where fk_department = `POL' order by 3 desc; 12. --a_03_12.sql-- select fk_department, last_name, first_name, sysdate, employment_date, round(months_between(sysdate, employment_date)/12, 1) seniority from employee where fk_department = `POL' and sysdate-employment_date = (select max(sysdate-employment_date) from employee where fk_department = `POL'); 13. --a_03_13.sql-- select fk_department, last_name, first_name from employee where wages > (select wages from employee where last_name = `TRUMAN') and fk_department in (`INT', `WEL'); 14. --a_03_14.sql-- select first_name, last_name from employee where first_name like `_W%'; 15. --a_03_15.sql-- select last_name, first_name from employee where last_name like `%V%T%';
Page 611
16. --a_03_16.sql-- select fk_department, sum(nvl(wages, 0)) from employee where fk_department != `WEL' group by fk_department; 17. --a_03_17.sql-- select initcap(`mr. `| | rtrim(first_name)| |' `| |last_name) "NAME" from employee; 18. --a_03_18.sql-- desc tab select tname from tab;
1. --a_04_01.sql-- ttitle left tday center `EMPLOYEES BY BIRTHDATE' btitle newline left `PAGE: ` sql.pno column b heading `BIRTH DATE' format a20 column a new_val tday noprint select sysdate a, last_name, first_name, to_char(birth_date, `dd-MON-YYYY') b from employee; ttitle off btitle off 2. --a_04_02.sql-- ttitle left tday center `EMPLOYEE WAGES' right `PAGE: ` sql.pno skip 1 - center `BY' skip 1 center `GENDER, WAGES' column gender format a6 justify center column a new_val tday noprint column last_name heading `LAST|NAME' column wages center format `$99,999.99' select sysdate a, gender, last_name, wages from employee; ttitle off clear columns 3. --a_04_03.sql-- ttitle left tday center `EMPLOYEE WAGES' right `PAGE: ` sql.pno skip 1 - center `BY' skip 1 center `DEPT, GENDER, WAGES' break on fk_department skip 2 column a new_val tday noprint column gender format a6 column last_name heading `LAST|NAME' column wages center format `$99,999.99' column fk_department heading `DEPARTMENT' format a10 select sysdate a, fk_department, gender, last_name, wages from employee order by 1, 4 desc; ttitle off clear columns clear breaks
Page 612
4. --a_04_04.sql-- ttitle left tday center `EMPLOYEE WAGES' ` PAGE: ` sql.pno skip 1 - center `BY' skip 1 center `GENDER, WAGES FOR DEPARTMENT ` dpt break on fk_department page column gender format a6 column a new_val tday noprint column last_name heading `LAST|NAME' column wages center format `$99,999.99' column fk_department new_val dpt noprint select sysdate a, fk_department, gender, last_name, wages from employee order by 2, 5 desc; ttitle off clear columns clear breaks 5. --a_04_05.sql-- ttitle left tday center `EMPLOYEE WAGES' ` PAGE: ` sql.pno skip 1 - center `BY' skip 1 center `GENDER, WAGES FOR DEPARTMENT ` dpt break on fk_department page compute avg sum of wages on fk_department column gender format a6 column a new_val tday noprint column last_name heading `LAST|NAME' column wages center format `$99,999.99' column fk_department new_val dpt noprint column a new_val tday noprint select sysdate a, fk_department, gender, last_name, wages from employee order by 2, 5 desc; ttitle off clear columns clear breaks 6. --a_04_06.sql-- set termout off ttitle left tday center `EMPLOYEE WAGES' ` PAGE: ` sql.pno skip 1 - center `BY' skip 1 center `GENDER, WAGES FOR DEPARTMENT ` dpt break on fk_department page compute avg sum of wages on fk_department column gender format a6 column a new_val tday noprint column last_name heading `LAST|NAME' column wages center format `$99,999.99' column a new_val tday noprint column fk_department new_val dpt noprint spool a:a_04_06.txt select sysdate a, fk_department, gender, last_name, wages from employee order by 2, 5 desc; spool off ttitle off clear columns clear breaks set termout on
Page 613
1. --a_05_01.sql-- select last_name, first_name, purchase_date, tool_name, tool_cost from employee, tools where payroll_number = fk_payroll_number; 2. --a_05_02.sql-- break on department_name on last_name on first_name page column last_name noprint new_val ln column first_name noprint new_val fn column department noprint new_val dpt column tool_name heading "TOOL NAME" column purchase_date heading "PURCHASE|DATE" center format a8 column tool_cost heading "TOOL|COST" format $999.99 ttitle left sysdate center "TOOL PURCHASES FOR " ln', `fn skip 1 - center "DEPARTMENT " dpt select department_name, last_name, first_name, purchase_date, tool_name, tool_cost, department from employee, department, tools where department = fk_department and payroll_number = fk_payroll_number(+) order by 1,2, 3 desc; ttitle off clear break clear columns 3. --a_05_03.sql-- select department_name, sum(cost) from department, employee, glasses where department = fk_department(+) and payroll_number = fk_payroll_number(+) group by department_name order by 2 desc; 4. --a_05_04.sql-- select last_name, first_name, max(tools.purchase_date), max(glasses.purchase_date) from employee, glasses, tools where payroll_number = tools.fk_payroll_number(+) and payroll_number = glasses.fk_payroll_number(+) group by last_name, first_name; 5. --a_05_05.sql-- break on department_name on last_name on first_name page column last_name noprint new_val ln column first_name noprint new_val fn column department noprint new_val dpt column tool_name heading "TOOL/GLASSES NAME" column purchase_date heading "PURCHASE|DATE" center format a8 column tool_cost heading "COST" format $999.99 ttitle left sysdate center "TOOL AND GLASSES PURCHASES FOR " ln | | ` ` | | fn skip 1- center "DEPARTMENT " dpt
Page 614
select department_name, last_name, first_name, purchase_date, tool_name, tool_cost from employee, department, tools where department_number = fk_department_number and payroll_number = fk_payroll_number(+) union select department_name, last_name, first_name, purchase_name, optician, cost from employee, department, glasses where department_number = fk_department_number and payroll_number = fk_payroll_number(+) order by 1,2, 3 desc; ttitle off clear break clear columns 6. --a_05_06.sql-- select last_name, first_name from employee, tools where payroll_number = fk_payroll_number minus select last_name, first_name from employee, glasses where payroll_number = fk_payroll_number; 7. --a_05_07.sql-- break on department_name select department_name, last_name, first_name from department, employee where department = fk_department and payroll_number in (select fk_payroll_number from glasses minus select fk_payroll_number from tools) order by 1, 2; clear break 8. --a_05_08.sql-- break on department_name select department_name, last_name, first_name from department, employee where department = fk_department and payroll_number in (select fk_payroll_number from glasses intersect select fk_payroll_number from Âtools) order by 1, 2; clear break
1. --a_06_01.sql-- create table transformer (serial_number char(10) , location varchar2(30), purchase_date date, cost number(6,2), manufacturer varchar2(15));
Page 615
2. --a_06_02.sql-- alter table transformer modify (serial_number char(15)); alter table transformer add primary key (serial_number); alter table transformer add (oil number(4)); 3. --a_06_03.sql-- create index addind on transformer (location); 4. --a_06_04.sql-- create table transformer_tests (fk_serial_number char(15), test_date date, voltage number(3), amps number(3), overhauled char(1) check(overhauled in (`Y', `N')), primary key (fk_serial_number, test_date), foreign key (fk_serial_number) references transformer on delete Âcascade); 5. --a_06_05.sql-- create public synonym trf for transformer; or create synonym trf for transformer; Note: The "scott" user account on Personnel Oracle7 does not have the privileges to create public synonyms. You can use the system account to perform this or create a synonym used on the "scott" user account only. 6. --a_06_06.sql-- create view glassescost as select payroll_number, last_name, first_name, fk_department, sum(cost) cost from employee, glasses where payroll_number = fk_payroll_number(+) group by payroll_number, last_name, first_name, fk_department; create view deptcost1 as select department, sum(cost) deptcost from department, employee, glasses where payroll_number = fk_payroll_number(+) and department = fk_department group by department; select department, last_name, first_name, cost, deptcost, (cost/deptcost)*100 percent from glassescost, deptcost1 where department = fk_department; 7. --a_06_07.sql-- drop table transformer_tests; drop table transformer; drop index addind; drop view glassescost; drop view deptcost1; drop table t_and_d_cable_terminal_poles;
Page 616
1. --a_07_01.sql-- insert into employee (payroll_number, last_name, first_name, birth_date, wages, street, city, Âstate, employment_date, social_security_number, current_position, phone, fk_department) values (40, `GORE', `AL', '01-APR-48', 18567, `444 S. MAIN ST', `NASHVILLE', `TN', '20-JAN-92', `508-34-8912', `BILL COLLECTOR', `894-123-8765', `POL'); 2. --a_07_02.sql-- insert into employee (payroll_number, last_name, first_name, birth_date, wages, street, city, Âstate, employment_date, social_security_number, current_position, phone, fk_department) values (41, `QUAYLE', `DAN', '04-DEC-47', 20456, `1600 PENNSYLVANIA AV', `WASHINGTON', `DC', to_date('20-JAN-2000', Â`DD-MON-YYYY'), `405-39-1212', `CHIEF EXECUTIVE', `100-100-0001', Â`POL') ; 3. --a_07_03.sql-- create table temp_employee as select * from employee; delete from temp_employee; insert into temp_employee select * from employee where fk_department = `WEL'; 4. --a_07_04.sql-- update temp_employee set wages = wages * 1.15; 5. --a_07_05.sql-- update temp_employee set wages = wages * 1.01 where payroll_number not in (select fk_payroll_number from glasses); 6. --a_07_06.sql-- delete from employee where last_name = `GORE' and first_name = `AL'; select * from employee where last_name = `GORE' and first_name = `AL'; rollback; delete from employee where last_name = `GORE' and first_name = `AL'; commit; rollback; select * from employee where last_name = `GORE' and first_name = `AL'; YES 7. --a_07_07.sql-- update employee a set wages = (select wages from temp_employee where temp_employee.payroll_number = a.payroll_number) where payroll_number in (select payroll_number from temp_employee);
Page 617
8. --a_07_08.sql-- truncate table temp_employee; rollback; No 9. --a_07_09.sql-- drop table temp_employee;
1. --a_08_01.sql-- set serveroutput on; begin dbms_output.put_line (`I am a PL/SQL guru'); end; / 2. --a_08_02.sql-- set serveroutput on; declare age number; begin select avg((employment_date - birth_date)/365) into age from employee; dbms_output.put_line (age); end; / 3. --a_08_03.sql-- set serveroutput on; declare fname employee.first_name%type; lname employee.last_name%type; cursor a is select first_name, last_name from employee; begin open a; loop fetch a into fname, lname; exit when a%notfound; dbms_output.put_line (lname| |', `| |fname); end loop; end; / 4. --a_08_04.sql-- set serveroutput on; declare e_rec employee%rowtype; leap_yes_no number; cursor a is select * from employee;
Page 618
begin open a; loop fetch a into e_rec; if (a%notfound) then exit; end if; leap_yes_no := mod(to_number(to_char(e_rec.birth_date,'yy')),4); if leap_yes_no = 0 then dbms_output.put_line (e_rec.first_name| |' `| |e_rec.last_name| |'was born Âin'| | a leapyear'); end if; end loop; close a; end; / 5. --a_08_07.sql-- set serveroutput on; declare lname employee.last_name%type; begin select last_name into lname from employee where last_name = `CLINTON' and first_name = `HILLARY'; dbms_output.put_line (lname); exception when no_data_found then dbms_output.put_line (`No Records Found'); end; / 6. --a_08_06.sql-- set serveroutput on; declare No_Record_Found exception; Pragma excep_init (`No_Record_Found', -1403); lname employee.last_name%type; begin select last_name into lname from employee where last_name = `CLINTON' and first_name = `HILLARY'; exception when No_Record_Found then dbms_output.put_line (`No Records Found'); end; / 7. --a_08_07.sql-- set serveroutput on; declare emp_record employee%rowtype; franklin_excep exception; cursor a is select * from employee order by first_name; begin open a; loop fetch a into emp_record; exit when a%notfound;
Page 619
if emp_record.first_name = `FRANKLIN' then raise franklin_excep; end if; end loop; close a; exception when franklin_excep then dbms_output.put_line(`Encountered the Name Franklin'); end; /
1. --a_09_01.sql-- serveroutput on; declare e_rec employee%rowtype; tot_tools number; cursor emp is select payroll_number, last_name, first_name from employee; cursor tool is select count(*) from tools where fk_payroll_number = e_rec.payroll_number; begin open emp; fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; while (emp%found) loop open tool; fetch tool into tot_tools; close tool; dbms_output.put_line(e_rec.first_name| |' `| |e_rec.last_name| |' `| | Â'purchased `| |tot_tools| |' tools'); fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; end loop; close emp; end; / 2. --a_09_02.sql-- serveroutput on; declare e_rec employee%rowtype; tot_tools number; cursor emp is select payroll_number, last_name, first_name from employee; cursor tool is select count(*) from tools where fk_payroll_number = e_rec.payroll_number; begin open emp; fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; while not emp%notfound loop open tool; fetch tool into tot_tools; close tool; dbms_output.put_line(e_rec.first_name| |' `| |e_rec.last_name| |' `| | irc;'purchased `| |tot_tools||' tools'); fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; end loop; close emp; end; /
Page 620
3. --_09_03a.sql-- serveroutput on; declare e_rec employee%rowtype; tot_tools number; cursor emp is select payroll_number, last_name, first_name from employee; cursor tool is select count(*) from tools where fk_payroll_number = e_rec.payroll_number; begin open emp; fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; while not emp%notfound loop open tool; fetch tool into tot_tools; close tool; open emp; dbms_output.put_line(e_rec.first_name| |' `| |e_rec.last_name| |' `| |'purchased `| |tot_tools| |' tools'); fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; end loop; close emp; end; / --a_09_03b.sql-- serveroutput on; declare e_rec employee%rowtype; tot_tools number; cursor emp is select payroll_number, last_name, first_name from employee; cursor tool is select count(*) from tools where fk_payroll_number = e_rec.payroll_number; begin open emp; fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; while not emp%notfound loop open tool; fetch tool into tot_tools; close tool; if not emp%isopen then open emp; end if; dbms_output.put_line(e_rec.first_name| |' `| |e_rec.last_name| |' `| |'purchased `| |tot_tools| |' tools'); fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; end loop; close emp; end; / 4. --a_09_04.sql-- serveroutput on; declare e_rec employee%rowtype; tot_tools number; cursor emp is select payroll_number, last_name, first_name from employee; cursor tool is select count(*) from tools where fk_payroll_number = e_rec.payroll_number; begin open emp; fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name;
Page 621
while emp%rowcount < 6 loop open tool; fetch tool into tot_tools; close tool; if not emp%isopen then open emp; end if; dbms_output.put_line(e_rec.first_name||' `||e_rec.last_name||' `||'purchased `||tot_tools||' tools'); fetch emp into e_rec.payroll_number, e_rec.last_name, e_rec.first_name; end loop; close emp; end; / 5. --a_09_05.sql-- set serveroutput on; declare e_rec employee%rowtype; age number(4,0); cursor emp is select last_name, first_name, birth_date from employee order by birth_date; begin open emp; for emp_num in 1..3 loop fetch emp into e_rec.last_name, e_rec.first_name, e_rec.birth_date; age := (sysdate - e_rec.birth_date)/365; dbms_output.put_line (e_rec.first_name| |' `| |e_rec.last_name| |' is `| |age); end loop; close emp; end; / 6. -a_09_06.sql-- set serveroutput on; declare ret_date date; begin for retire in (select last_name, first_name, birth_date from employee where fk_department = `INT') loop ret_date := retire.birth_date + (65 * 365); dbms_output.put_line (retire.first_name| |' `| |retire.last_name| |' retires on `| |ret_date); end loop; end; / 7. -a_09_07.sql-- set serveroutput on; declare ret_date date; t_rec tools%rowtype; cursor tool is select tool_name from tools where fk_payroll_number = t_rec.fk_payroll_number; begin
Page 622
for retire in (select payroll_number, last_name, first_name, birth_date from Âemployee where fk_department = `INT') loop ret_date := retire.birth_date + (65 * 365); dbms_output.put_line (retire.first_name| |' `| |retire.last_name| |' retires on `| |ret_date); t_rec.fk_payroll_number := retire.payroll_number; open tool; fetch tool into t_rec.tool_name; while tool%found loop dbms_output.put_line (` Purchased a `| |t_rec.tool_name); fetch tool into t_rec.tool_name; end loop; close tool; end loop; end; / 8. _a_09_08.sql-- set serveroutput on; create function tot_dept_wages (dept in char) return number is tot_wages number; begin select sum(wages) into tot_wages from employee where fk_department = dept; return tot_wages; end; / 9. _a_09_09.sql-- set serveroutput on; create procedure total_dept_wages (dept in char) is e_rec employee%rowtype; perc number(3,2); cursor wage is select first_name, last_name, wages from employee where fk_department = dept; begin open wage; fetch wage into e_rec.first_name, e_rec.last_name, e_rec.wages; while wage%found loop perc := e_rec.wages/tot_dept_wages(dept); dbms_output.put_line (e_rec.first_name| |' `| |e_rec.last_name| |' wages are `| |perc| |' of the total'); fetch wage into e_rec.first_name, e_rec.last_name, e_rec.wages; end loop; close wage; end; /
Page 623
10. _a_09_10.sql-- set serveroutput on; begin total_dept_wages (`INT'); end; / 11. --a_09_11a.sql-- set serveroutput on; create package ans11 is cursor emp return employee%rowtype; function tot_dept_wages (dept in char) return number; procedure total_dept_wages (dept in char); end ans11; / --a_09_11b.sql-- set serveroutput on; create package body ans11 is cursor emp return employee%rowtype is select * from employee order by birth_date; procedure total_dept_wages (dept in char) is perc number(3,2); e_rec employee%rowtype; begin open ans11.emp; fetch ans11.emp into e_rec; while ans11.emp%found loop perc := e_rec.wages/tot_dept_wages(dept); dbms_output.put_line (e_rec.first_name| |' `| |e_rec.last_name| |' wages are `| |perc| |' of the total'); fetch ans11.emp into e_rec; end loop; close ans11.emp; end; function tot_dept_wages (dept in char) return number is tot_wages number; begin select sum(wages) into tot_wages from employee where fk_department = dept; return tot_wages; end; end ans11; / --a_09_11c.sql set serveroutput on;
Page 624
begin for employees in ans11.emp loop dbms_output.put_line (employees.last_name); end loop; end; / --a_09_11d.sql begin ans11.total_dept_wages (`POL'); end; /
1. _a_10_01.sql-- delete from glasses; delete from tools; delete from employee; delete from department; commit; 2. _a_10_02.ctl-- load data infile `d:\dept.dat' into table department ( department position(01:04) char(4), department_name position(06:20) char(15) ) 3. _a_10_03.ctl-- load data infile * into table employee fields enclosed by `"` (payroll_number, last_name, first_name, absences, wages, street, city, state, phone, social_security_number, employment_date date Â"dd-mon-yyyy", birth_date date "dd-mon-yyyy", current_position, fk_department) begindata "25" "COOLIDGE" "CALVIN" "0" "9500" "12 MAPLE ROAD" "PLYMOUTH" "VT" "435-897-3546" "100-02-0500" "07-AUG-1921" "01-JUL-1972" "JANITOR" "INT" "31" "JOHNSON" "LYNDON" "3" "12000" "RR #1" "STONEWALL" "TX" "560-456-9876" "456-91-2345" "23-NOV-1963" "27-AUG-1908" "TREASURER CLERK" "POL" "35" "REAGAN" "RONALD" "5" "13500" "10 RODEO LANE" "TAMPICO" "IL" "721-898-0987" "101-11-9832" "03-MAR-1980" "01-OCT-1924" "PRESIDENT" "WEL" "36" "BUSH" "GEORGE" "0" "14000" "1456 PLEASANT" "FALL HARBOR" "ME" "409-339-9087" "459-98-3456" "05-JAN-1988" "06-FEB-1911" "CLERK 2" "INT" "21" "JOHNSON" "ANDREW" "2" "7500" "1233
Page 625
TABACCO RD" "RALEIGH" "NC" "640-789-3450" "267-88-9876" "13-APR-1965" "29-DEC-1908" "SALESPERSON 1" "POL" "37" "CLINTON" "WILLIAM" "2" "15000" "1234 OAK DALE" "HOPE" "AR" "402-731-2489" "456-98-9987" "01-JAN-1992" "03-APR-1940" "CLERK 1" "POL" "34" "CARTER" "JIMMIE" "1" "13000" "RR #3" "PLAINS" "GE" "432-987-0987" "563-99-7765" "10-JUL-1976" "14-JUL-1913" "LABORER 3" "WEL" "33" "FORD" "GERALD" "0" "13000" "3301 CENTER ST" "OMAHA" "NE" "408-765-3487" "456-33-9801" "20-MAY-1973" "09-JAN-1913" "LABORER 2" "INT" "32" "NIXON" "RICHARD" "6" "12500" "12 PASADENA AVE" "YORBA LINDA" "CA" "402-636-3171" "555-45-2345" "15-DEC-1968" "27-AUG-1908" "TREASURER" "POL" "30" "KENNEDY" "JOHN" "2" "11500" "1230 N OCEAN" "BROOKLINE" "MA" "345-908-8765" "234-66-2356" "01-JAN-1961" "29-MAY-1917" "PROGRAMMER 1" "POL" "29" "EISENHOWER" "DWIGHT" "1" "0" "12 SOLDIER WAY" "DENISON" "TX" "367-098-0002" "876-99-1201" "20-MAR-1953" "14-OCT-1890" "GUARD 4" "INT" "28" "TRUMAN" "HAROLD" "0" "11000" "RRT #7" "LAMAR" "MO" "546-987-6512" "345-09-0191" "15-APR-1945" "08-MAY-1884" "COUNSELER 2" "INT" "27" "ROOSEVELT" "FRANKLIN" "3" "10400" "12 CHERRY LANE" "HYDE PARK" "NY" "983-097-8734" "001-01-0001" "26-MAR-1933" "30-JAN-1882" "CLERK 1" "POL" "26" "HOOVER" "HERBERT" "2" "10000" "1234 MAIN ST" "WEST BRANCH" "IA" "213-467-0932" "100-02-0004" "06-APR-1928" "10-AUG-1874" "MAINT. MAN 2" "WEL" "24" "WILSON" "WOODROW" "1" "9000" "123 SMOKEY ROAD" "STAUTON" "VA" "567-123-9867" "200-05-9879" "05-SEP-1912" "28-DEC-1856" "MAINT. MAN 3" "POL" "23" "TAFT" "WILLIAM" "2" "8500" "1234 RIVERFRONT RD" "CINCINNATI" "OH" "234-632-7806" "340-90-9856" "01-JUN-1908" "15-SEP-1857" "VICE PRESIDENT" "WEL" "22" "ROOSEVELT" "THEODORE" "0" "8000" "12 BROADWAY" "NEW YORK" "NY" "389-329-0418" "378-66-9854" "20-NOV-1902" "27-OCT-1858" "CONTROLLER" "INT" "20" "ANTHONY" "SUSAN" "1" "7000" "123 DOLLAR ROAD" "ADAMS" "MA" "458-094-0987" "503-89-7898" "30-MAR-1840" "15-FEB-1820" "SALESPERSON 2" "WEL" "19" "ROOSEVELT" "ELEANOR" "0" "0" "123 W 57 TH" "NEW YORK" "NY" "120-234-9876" "509-66-8999" "20-MAR-1932" "11-OCT-1884" "SYSTEM ANALYST" "WEL" 4. _a_10_04.ctl-- load data infile * into table glasses fields terminated by "," (fk_payroll_number, purchase_date date "dd-mon-yy", optician, cost, check_number) begindata 34,12-AUG-79,Greenberg Optical,175,N8754 25,15-NOV-23,Greenberg Optical,175,A12356 31,31-JAN-64,Peralman Optical,170,B9054
Page 626
35,23-OCT-83,Greenberg Optical,165,X6789 21,31-MAY-67,Greenberg Optical,165,B7865 34,08-SEP-77,Pearlman Optical,164,B9087 33,01-FEB-74,Downtown Optical,145,B9876 32,23-JUN-70,Downtown Optical,123,B897 19,01-JUL-33,TROIA OPTICAL,134,BV12798 19,20-JUL-35,TROIA OPTICAL,143,BN9876 20,12-AUG-40,PEARLMAN OPTICAL,120,X9820 22,12-MAR-03,GREENBERG OPTICAL,123,X9812 22,06-MAY-04,WYOMING OPTICAL,145,Y1245 29,31-MAR-53,GREENBERG OPTICAL,15,V8762 28,12-OCT-47,LAMAR OPTICAL,110,Z9876 27,03-JUN-33,HYDE PARK OPTICAL,129,C9876 24,01-JAN-17,STERLING OPTICAL,123,C8734 23,08-NOV-10,WAL-MART,145,N8762 4. _a_10_05.ctl-- load data infile * into table tools fields terminated by "," optionally enclosed by `"` (fk_payroll_number, purchase_date date "dd-mon- yy", payroll_deduct, tool_name, tool_cost, payment_amount, last_payment_amount, first_payment_date date "dd-mon-yy", last_payment_date date "dd-mon-yy") begindata 25,01-OCT-22,Y,Pliers,"25",10,"5",01-SEP-22,"01-OCT- 22" 25,01-FEB-23,N,Vice Grips,"10",10,,, 35,04-JUN-80,Y,3/4" Wrench,"4",2,"2",01-JUL-80,"15- JUL-80" 35,06-NOV-82,Y,Tool Chest,"16.75",6,"4.75",01-DEC- 80,"01-JAN-81" 35,24-APR-81,N,Knife,"7.95",7.95,,, 36,23-SEP-88,,Drill Bit,"2.75", 2.75,,, 36,10-NOV-88,Y,Drill,"35.95",10,"5.95",01-DEC-88,"15- JAN-89" 36,23-FEB-89,Y,Hack Saw,"7.5",4,"3.5",01-MAR-89,"15-MAR- 89" 21,01-FEB-66,Y,Fountain Pen,"5.95",3,"2.95",01-MAR- 66,"15-MAR-66" 21,10-MAY-67,Y,Shovel,"10.75",5.75,"5",01-JUN-67,"15- JUN-67" 33,01-JAN-74,Y,Golf Balls,"12",5,"2",01-FEB-74,"15-MAR- 74" 33,10-AUG-74,N,1st Aid Kit,"0",,,,, 33,23-MAR-77,N,1st Aid Kit,"0",,,,, 32,14-FEB-69,Y,Hack Saw,"12.75",6,"6.75",01-MAR-68,"15- MAR-69" 32,21-OCT-69,Y,Pliers,"5.75",5.75,"5.75",01-NOV-69,"01- NOV-69" 19,01-MAY-33,Y,CALCULATOR,"55",10,"5",15-MAY-33,"15-JUL- 33" 19,06-SEP-34,N,FOUNTAIN PEN,"1.95",1.95,"1.95",, 19,30-NOV-34,Y,PLIERS,"5",5,"5",01-DEC-34,"01-DEC- 34" 20,31-MAY-40,Y,BRIEF CASE,"43.95",10,"13.95",15-JUN- 40,"01-AUG-40" 20,12-DEC-41,Y,CALCULATOR,"34.95",15,"4.95",15-DEC- 41,"15-JAN-42" 20,03-MAY-43,N,STAPLER,"9.95",9.95,"9.95",, 22,01-FEB-03,N,BIG STICK,"34",34,,, 22,10-MAR-05,Y,RIFLE,"290",70,"10",15-MAR-05,"15-MAY- 05" 29,31-MAR-53,Y,UNIFORM,"200",50,"50",15-APR-53,"15-JUN- 53" 29,31-MAR-53,Y,RIFLE,"150",50,"50",01-JUN-53,"01-JUL- 53" 29,01-MAR-53,N,BOOTS,"25",25,"25",, 27,01-MAY-33,N,CIGARETTE HOLDE,"12",12,"12",, 27,10-SEP-34,N,STAPLER,"8",8,"8",, 26,12-MAR-29,Y,BROOM,"8",4,"4",15-MAR-29,"01-APR- 29" 26,31-MAY-29,Y,TIN SNIPS,"16",8,"8",15-JUN-29,"01-JUL- 29" 24,04-NOV-13,N,DUST PAN,"4.95",4.95,"4.95",,
Page 627
24,04-NOV-13,Y,VACUUM,"100",25,"24",15-NOV-13,"15-JAN- 14" 24,10-SEP-15,Y,VISE GRIPS,"12",6,"6",15-SEP-15,"01-SEP- 15" 23,01-AUG-09,N,FOUNTAIN PEN,"23",23,"23",,
Page 628
On the Items Tab sheet, click the Select Columns button. Click the Layout Tab.
On the Layout Tab sheet, select "Form" on the Style picklist, "Vertical" on the Orientation picklist, and "1" in the Records item. Press OK to create the block.
Save, generate, and run the form.
Page 629
Open the Object Navigator. Select and expand the Windows object. Double-click the button next to the window object. Set the following properties:
X position | 100 | |
Y position | 30 | |
Width | 320 | |
Height | 304 | |
Title | Department Update |
Save, generate, and run the form.
You may use the a_13_01.fmb file to practice these steps.
Page 630
Go_item (`one.fk_department'); List_values;
Page 631
Open the Object Navigator. Drag the Photo button item to the Button Palette block. Expand the Photo item. Select the triggers child object. Click the create icon. Select When-button-pressed from the dialog. Enter the following in the PL/SQL editor for the trigger:declare filename varchar2(25); Begin Filename := `d:\'| |to_char(:one.payroll_number)| |'emp.tif'; Read_file_image (filename, `TIF', `one.imageitem'); End;
If (:two.last_payment_date < sysdate) then Message (`You Must Enter A Future Date'); Raise form_trigger_failure; End if;
Go_block (`one'); Enter_query;
Go_block (`one');
Begin Select department_name into :one.department_name from Âdepartment
Page 632
Where uppert(rtrim(department,' `)) = upper(rtrim(:one.fk_department,' `)); exception when others then :one.department_name := `UNKNOWN'; end;
Re-sequence the items on block one by opening the object navigator and dragging the objects.
Declare Alert_value number; Begin If (:two.last_payment_date < sysdate) then Alert_value := show_alert(`bad_date'); Raise form_trigger_failure; End if; End;
Canvas-view type | stacked |
Width | 592 |
Bevel | none |
Background | cyan |
Stacked View Width | 592 |
Stacked View Height | 210 |
Display Y Position | 90 |
Open the Layout Editor. Select each of the items on block three. Open the multi-item property sheet. Change the canvas property to canvas2. Select the image item. Double-click the item to open the property sheet. Change the canvas property to canvas3. Select all of the boilerplate for block2. Select Edit, Cut. Open the Object Navigator. Double-click canvas2. Position the cursor on the layout. Select Edit, Paste. Format the items on the form. Open the canvas3 layout editor. Position the image item.
Open the Object Navigator. Open the block-two property sheet. Change the records displayed property to 10. Open the block-three property sheet. Change the records displayed property to 10. Open the Layout Editor. Select the Employee Photo button. Select Edit, Copy menu option. Select Edit, Paste. Drag and position the new button.
Page 633
Double-click the new button item. Change the name property of the item to glasses. Change the label property of the item to GLASSES. Select Edit, Paste. Drag and position the new button. Double-click the new button item. Change the name property of the item to tools. Change the label property of the item to TOOLS.
Open the Object Navigator and locate the When-button-pressed trigger for the glasses button. Open the trigger's PL/SQL editor. Change the code block to go_block (``three''). Locate the When-button-press trigger for the tools button. Open the trigger's PL/SQL editor. Change the code block to go_block (``two''). Open the When-button-pressed PL/SQL editor for the Photo button. Place the following two statements on the first line of the executable code:
Go_block (`one'); Go_item (`image37');
Page 634