A) create three normalized tables

B) write a series of select statements against them

desired output:

1) a list of all secretaries working in the main office

2) a list of all Oracle consultants working in the Natick office who are residents of Norton, MA, and have birthdays in September

3) an alphabetical list of all employees working in Accounting who have been with the company for at least one year together with their employee numbers

4) a list of all part-time groundskeepers earning less than $12/hr together with their phone numbers

5) an alphabetical list of all employees together with their Social Security numbers and the Federal income and Social Security taxes they paid last year

6) an alphabetical list of all employees, their employee numbers, jobs, and total compensation received last year, broken down and subtotaled by location and department, and then grandtotaled

commands:

1) sqlplus esherman/password

2) create database good_job;

3) create tablespace sample datafile 'exercise #1';

[in the following statements, V2=VARCHAR2, NN=NOT NULL, N=NUMBER]

4) create table EMP (Ssn V2(11) NN PRIMARY KEY, Name V2(40) NN, Dept V2(25), Loc V2(25), Job V2(25), Hired DATE, Birth DATE) tablespace sample;

5) create table EMPADD (Empno N(4) NN, Name V2(40) NN, Street V2(50), City V2(50), State V2(2), Zip V2(10), Phone V2(20), PRIMARY KEY (Empno, Name)) tablespace sample;

6) create table COMP (Empno N(4) NN PRIMARY KEY, Name V2(40) NN, Wage N(6,2) NN CHECK (Wage>5.15), Hours N(6,2), Pay N(6,2), Ftax N(6,2) Sstax N(6,2), Date DATE) tablespace sample;

7) create index EMP_Ssn on EMP(Name, Hired, Birth) tablespace sample;

8) create index EMPADD_Name_Empno on EMPADD (City, Phone) tablespace sample;

9) create index COMP_Empno on COMP(Name, Wage) tablespace sample;

10) autocommit on

11) insert EMP values ('123-45-6789', 'Doe, John A.', 'Grounds', 'Narragansett', 'Head', '07-JUL-96', '25-DEC-56');

12) ......

13) insert EMPADD values (0394, 'Doe, John A.', '25 Smith Street', 'North Kingstown', 'RI', '02852-2022', '401-885-1212');

14) ......

15) insert into COMP values (0294, 'Doe, John A.', 11.75, 40.00, 470.00, 78.24, 29.14, '19-APR-97');

16) ......

17) column Ssn heading "Soc Sec No'

18) column Name format a25

19) column Dept format a6

20) column Loc format a6

21) column Job format a10

22) column Street format a20

23) column City format a15

24) set pagesize 65

25) set newpage 5

26) break on Loc skip 1 on Dept skip 1 on report skip 1 nodup

27) compute sum of Pay on Loc Dept report

28) spool exercise.one

29) select Name from EMP
         where Loc='Main' and Job='Sec'
         order by Hired;
      /

30) select EMP.Name from EMP, EMPADD
         where EMP.Name=COMP.Name, Loc='Natick', Job='Oraconsult',
         Birth Like '%S%', City='Norton', and State='MA';
      /

31) select EMP.Name, Empno from EMP, EMPADD
         where EMP.Name=COMP.Name, Dept='Acct' and Hired<'25-APR-96'
         order by EMP.Name;
      /

32) select EMP.Name, Phone from EMP, EMPADD, COMP
         where EMP.Name=EMPADD.Name=COMP.Name,
         Dept='Ground', Hours<40, and Wage<12.00;
      /

33) select EMP.Name, Ssn, SUM(Ftax) Fedtax, SUM(Sstax) Soctax
         from EMP, COMP
         where EMP.Name=COMP.Name and Date Like '%96'
         order by EMP.Name;
      /

34) select Loc, Dept, EMP.Name, Empno, Job, SUM(Pay) as 96_Comp
         from EMP, COMP
         where EMP.Name=COMP.Name and Date Like '%96'
         group by Loc, Dept
         order by Loc, Dept, EMP.Name;
      /

35) spool off

36) exit