Previous | Table of Contents |

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

  1. Double-click the Run Forms icon in the Developer 2000 icon grouping. The database should also be entered.

  2. 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.

  3. Click the F7 function key. Click the quit button. Enter a record into the form. Click the F10 function key.

  4. 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.

  5. Press the F7 function key. Enter #is null or wages > 12000 into the Wages field. Click the F8 function key.

Chapter 12

  1. _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.

  2. _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

  1. -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.

  2. -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.

  1. Open the Object Navigator. Select the blocks object. Click the create icon on the tool bar. The New Block Options dialog box opens.

  2. The settings for the General Tab are: Base Table--tools, Block Name--two, Canvas--use default, and Sequence id--2.

  3. Click the Items tab. Click the Select Columns button to populate the list box. Change the Label settings to suit. Include all items.

  4. Click the Layout tab. Change the Records setting to 4. Check the Scrollbar check box.

  5. 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.

  6. Format the block in the middle of the screen.

  7. Open the Object Navigator. Select the block object. Click the create icon on the tool bar. The New Block Option dialog box opens.

  8. The settings for the General Tab are: Base Table--glasses, Block Name--three, Canvas--default, and Sequence id--3.

  9. Click the Items tab. Click the Select Columns button to populate the list box. Change the Label settings to suit. Include all items.

  10. Click the Layout tab. Change the Records setting to 4. Check the Scrollbar and Button Palette check boxes.

  11. 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.

  12. Format the block.

  13. 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

  1. Open the property sheets for block two and three. Place the name of the visual attribute in the Current Record Attribute properties.

  2. 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.

  3. 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.

  4. 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.

  5. 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;
    


  6. 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.

  7. 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.

  8. 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.

  9. 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

  1. -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;
    


  2. -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');
    


  3. -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.
  1. -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;
    


  2. -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

  1. -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.

  2. -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.

  3. -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

Previous | Table of Contents |