Page 607
APPENDIX E
Answers to Practice
Problems
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
Chapter 2
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.
)
Chapter 3
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;
Chapter 4
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
Chapter 5
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
Chapter 6
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
Chapter 7
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;
Chapter 8
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;
/
Chapter 9
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;
/
Chapter 10
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",,
Chapter 11
- Double-click the Run Forms icon in the Developer 2000 icon grouping. The
database should also be entered.
- Click the query button. Enter WEL in the department field. Click the query
button again. Click the down keyboard arrow several times. Click the up keyboard
arrow several times.
- Click the F7 function key. Click the quit button. Enter a record into the form. Click
the F10 function key.
- Click the down keyboard arrow button. Click the Ctrl+F1 buttons. Identify the
duplicate record button. Close the Function key dialog box. Click the duplicate record
button. Click the Save button. An error should occur. Select Help, Display error from the
menu. The error will appear. Close the error dialog box. Change the value in the
Payroll number field. Click the save button.
- Press the F7 function key. Enter #is null or wages > 12000 into the Wages field. Click the F8 function key.
Chapter 12
- _A_12_01.fmb-
Open the Object Navigator. Select the Forms object. Click the Create tool. A new form file will be created. Select the blocks object. Click the Create tool. The New Block Options dialog box will appear.
On the General Tab sheet enter Department in the Base Table item. Enter one in the Block Name item. Select the Items tab.
On the Items Tab sheet, click the Select Columns button. This will display the available columns from the Department table. Click the Layout button.
On the Layout Tab sheet, enter Tabular in the Style pick list, Vertical in the Orientation picklist, and 12 in the Records item. Check the Button Palette and Scrollbar checkboxes. Press the OK button. The form has now been created. Save, generate, and run the form.
- _A_12_02.fmb-
Open the Object Navigator. Select the Forms object. Click the Create button.
Select the Blocks object. Click the Create button. The New Block Options dialog box will appear. The General Tab sheet will appear.
Enter employee in the Base Table item and one in the Block Name item. Click the Items tab.
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.
Chapter 13
- -A_13_01-
Open the Object Navigator. Click the File Open button. Open the Employee form created in the exercises in Chapter 12 or form A_12_01.fmb on the CD.
Select the Tools, Layout Editor menu option. Select each part of the block frame one. Delete each of the selected frame items.
Select the Payroll Number item. Hold the shift key and select the Payroll Number item boilerplate. Both items should now be selected. Drag the items to the proper form location.
Repeat this procedure for each of the items on the form.
Use the Arrange|Align Object menu option to align the various items.
Select all of the text items. Set the fill color to white using the Fill color tool.
Open the Object Navigator. Select the canvas object. Select the Tools, Properties menu option. Set the Width to "592," the Height to "378," and the background color to "cyan." Close the canvas property sheet.
Select and expand the Windows object. Double click the button to the left of the window object. This will open the item's property sheet. Change the Width property to "592," the Height to "378," and the Title to "Employee Update."
Save, generate, and run the form.
- -A_13_02.fmb-
Open the Object Navigator. Click the File Open button. Open the Employee form created in the exercises in Chapter 12 or form A_12_02.fmb on the CD.
Select the Tools|Layout Editor menu option. Select each part of the block frame one. Delete each of the selected frame items.
Select the Department_name item. Hold the shift key down and select the items boilerplate. Drag the two selected items to the right. Repeat this procedure for the Department item and the scroll bar. Arrange the items.
Select the each text item and set the fill color to white by using the Fill tool.
Click the Text tool. Select the Department boilerplate. Enter the missing boilerplate characters.
Open the Object Navigator. Select the canvas object. Select the Tools|Properties menu option. Set the Width property to "320," the Height to "304," and Background to "r50g75b50." Close the property sheet.
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.
Chapter 14
You may use the a_13_01.fmb file to practice these steps.
- Open the Object Navigator. Select the blocks object. Click the create icon on the tool
bar. The New Block Options dialog box opens.
- The settings for the General Tab are: Base Table--tools, Block Name--two,
Canvas--use default, and Sequence id--2.
- Click the Items tab. Click the Select Columns button to populate the list box. Change
the Label settings to suit. Include all items.
- Click the Layout tab. Change the Records setting to 4. Check the Scrollbar check box.
- Click the Master/Detail tab. Enter
one in the Master Block setting. Enter one.payroll_number =
two.fk_payroll_number in the join condition. Click the
OK button to create the block.
- Format the block in the middle of the screen.
- Open the Object Navigator. Select the block object. Click the create icon on the tool
bar. The New Block Option dialog box opens.
- The settings for the General Tab are: Base Table--glasses, Block Name--three,
Canvas--default, and Sequence id--3.
- Click the Items tab. Click the Select Columns button to populate the list box. Change
the Label settings to suit. Include all items.
- Click the Layout tab. Change the Records setting to 4. Check the Scrollbar and
Button Palette check boxes.
- Click the Master/Detail tab. Enter
one in the Master Block setting. Enter one.payroll_number =
three.fk_payroll_number in the join condition. Click the
OK button to create the block.
- Format the block.
- Open the Object Navigator. Select the Visual Attributes object. Click the create icon. Double-click the icon next to the newly created visual attribute to open its property sheet. Change the Foreground property to red.
Page 630
- Open the property sheets for block two and three. Place the name of the visual
attribute in the Current Record Attribute properties.
- Open the Object Navigator. Select the LOV object. Click the create button to open
the New LOV dialog box. Enter the following query:
select department, department_name from department order by
department. Click the OK button. Change the name of the LOV and record group to DEPT.
- Open the Dept LOV's property sheet. Change the title property to Valid
Departments. Change the Background color to r100h100b50. Change the X Position property to
160 and the Y Position property to 100. Select the Column mapping property and press
the More button. Enter one.fk_department' into the department column Return
Item property. Click OK.
- Open the property sheet for the fk_department item on block one. Enter
DEPT in the LOV property. Enter true in the LOV for Validation property.
- Open the Layout Editor. Select the push button tool. Place a default button on the
canvas. Resize the button and place it adjacent to the department item. Double-click the button
to open its property sheet. Change the Name of the item to Deptlov and the Label to a
pipe symbol (|). Open the Object Navigator. Locate and expand the Deptlov button
item. Select the Trigger child object and click the create button. Select the
When-Button-Pressed trigger from the dialog box. Enter the following in the PL/SQL editor:
Go_item (`one.fk_department'); List_values;
- Open the Layout Editor. Double-click the payroll_deduct item to open its property
sheet. Change the Item Type to Check Box. Change the Default Value to Y. Change the
Label to (Check if Yes). Change the Background Color to Cyan. Resize the item on the
Layout Editor. Change the Check property to Y and the Unchecked property to M.
- Open the Object Navigator. Select the gender item. Open its property sheet. Change
the Item Type to radio group. Change the default value to M. Change the Background
to Cyan. Navigate to the Object Navigator. Expand the gender item. Select the
Radio Buttons object. Click the create icon twice. Open the first radio button's property
sheet. Change the Label to Male and the Value to M. Open the second radio button's
property sheet. Change the Label to Female and the Value to F. Open the Layout Editor. The
radio buttons will be located in the top left-hand corner. Select and drag them to the
proper location.
- Open the Layout Editor. Double-click the state item. Change the Item Type to List
Item. Select the List Elements property. Click the More button. Enter the State values
and their descriptions. Create a blank description with a null value. This will be the
default. Resize the list on the layout editor.
- Open the Layout Editor. Select the image tool. Place an image on the canvas. Be sure it is a block-one item. You may use the Object Navigator to drag the item there. Open the image item's property sheet. Change the Base Table Item to false and the Navigable property to false. Change the Sizing Style to adjust. Open the Layout Editor. Select the push button tool. Place the button on the canvas. Double-click the button to open the button's property sheet. Change the Name to photo and the Label to Employee Photo.
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;
Chapter 15
- -a_15_01.fmb-
Open the Object Navigator. Select and expand block two. Select and expand the items object. Select and expand the last_payment_date item. Create a trigger by clicking the Create icon. Select the When-validate-item trigger from the dialog box. Enter the following PL/SQL code block into the PL/SQL editor:If (:two.last_payment_date < sysdate) then Message (`You Must Enter A Future Date'); Raise form_trigger_failure; End if;
- -a_15_01.fmb-
Open the Object Navigator. Select and expand the form level triggers object. Click the create tool. Select the key-entqry trigger. Enter the following into the PL/SQL editor:Go_block (`one'); Enter_query;
Select and expand the button_palette block. Select and expand the items object. Select and expand the Query button object. Expand the triggers object. Open the When-button-pressed trigger object. Enter the following in the first section of the if statement before the statement "do_key (`enter_query');":Go_block (`one');
- -a_15_01.fmb-
Open the Layout Editor. Move the social_security_number text item and boilerplate below the state text item. Select the street text item. Select Edit, Copy, select Edit, Paste. Drag the new item to the second row below the department text item. Double-click the new text item to open the property sheet. Change the name property to department_name, the navigable property to false, maximum_length property to 30, query_length to 30, and the base table property to false.
Open the Object Navigator. Select the triggers object under block one. Click the create tool. Select the post-query trigger. Enter the following in the PL/SQL editor: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.
- -a_15_01.fmb-
Open the Object Navigator. Select the alerts object. Click the Create tool. Change the name of the alert to "bad_date." Open the alerts' property sheet. Set the Button2 property to Null. Set the Message property to You May Only Enter Future Dates Into the Last Payment Date field. Select the When-validate-item trigger under the last_payment_date item. Double-click the item to open the PL/SQL editor. Change the trigger script to: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;
- -a_15_02.fmb-
Open the Object navigator. Select the canvas-view object. Click the Create item twice. Change the names of the new canvases to canvas2 and canvas3. Change the properties on canvas2 and canvas3 to the following:
| 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');
Chapter 17
- -a_17_01.rdf-
Open the Object Navigator. Create a new report module. Open the data model editor by double-clicking the data model object. Select the query tool. Place a query box on the model layout area. Double-click the query box. Enter the following query into the box: Select fk_department, last_name, first_name, wages from employee order by fk_department. Click the Apply button. Press the Close button. Click the default layout icon at the top of the data model editor. Select the tabular style and modify the column labels. Click OK when done. Save the file. Execute the file by double-clicking the run icon.
- -a_17_02.rdf-
Open the file created in exercise one in Reports Designer. Open the data model editor by double-clicking the data model icon or selecting Tools, Data Model Editor menu option. Select the last_name column. Create a new group by dragging the item beneath the existing group box. Select the first_name column. Drag it into the new group box. Select the wages column. Drag it into the new group box. Create a new default layout by double-clicking the default layout icon. Click OK when done. Save the file. Execute the file by double-clicking the run icon.
- -a_17_03.rdf-
Open the file create in exercise two in Reports Designer. Open the data model editor by double-clicking the data model icon or selecting Tools, Data Model Editor menu option. Select the query tool. Create a new query box on the model layout area. Double-click the new query box. Enter the following in the Select statement window: Select department, department_name from department. Click the Apply button to validate the statement. Click the Close button when done. Activate the data link tool by clicking the data link icon. Select the fk_department column. Hold down the mouse button. Drag the data link line to the department column. Click the default layout icon. Open the Data/Selection tab of the dialog box. Deselect the department column. Click OK. Save the file. Execute the file by double-clicking the run icon.
Page 634