spool outfile
set pagesize 66
set linesize 80
set echo on
 
/*  ora_ex1 Nannette Thacker    */

/* Simple Oracle program to create tables, fill in values, run SQL, and drop tables */

/*      2/19/97 nkt
*       CREATE TABLE for:
*       Employee TABLE
*               PRIMARY KEY = "Employee Number"
*               Relations to:
*                  "Employee Job" TABLE with concatenated PRIMARY KEY
of:
*                  "employee number" and "Job Number"
*                  Relations to:
*                    JOB TABLE with PRIMARY KEY of "Job Number"
*/
 
CREATE TABLE Employee
(
      "Employee Number"     number(4),
      Name                  varchar2(25),
      "Street address"      varchar2(60),
      city                  varchar2(25),
      state                 varchar2(2),
      zip                   varchar2(10),
      "start date"          date,
      CONSTRAINT pk_employee PRIMARY KEY ("Employee Number")
                 using index TABLESPACE USERS
)
STORAGE (INITIAL 1K
         NEXT 1K
         PCTINCREASE 0)
TABLESPACE USERS     ;
 
 
/*
* CREATE TABLE for:
*       JOB TABLE
*               PRIMARY KEY = "Job Number"
*               Relations to:
*                  Employee and "Employee Job" tables
*/
 
 
CREATE TABLE JOB
(
      "Job Number"          number(4),
      Description         varchar2(60),
      CONSTRAINT pk_JOB PRIMARY KEY ("Job Number")
                 using index TABLESPACE USERS
)
STORAGE (INITIAL 1K
         NEXT 1K
         PCTINCREASE 0)
TABLESPACE USERS     ;   
 
/*      2/19/97 nkt
*       CREATE TABLE for:
*       "Employee Job" TABLE
*               PRIMARY KEY = "employee number" "Job Number"
concatenated
*               Relations to:
*                  Employee and JOB tables
*/
 
CREATE TABLE "Employee Job"
(
      "employee number"     number(4),
      "Job Number"          number(4),
      "Skill level"         number(2),
      CONSTRAINT pk_employee_job
               PRIMARY KEY ("employee number","Job Number")
                 using index TABLESPACE USERS,
      CONSTRAINT fk_employee_job
                 FOREIGN KEY ("employee number") 
                 REFERENCES
                  Employee("Employee Number"),
      CONSTRAINT fk_employee_job2
                 FOREIGN KEY ("Job Number")
                 REFERENCES JOB("Job Number")
)
STORAGE (INITIAL 1K
         NEXT 1K
         PCTINCREASE 0)
TABLESPACE USERS     ;
 
 
 

/* insert values into the Employee table */
INSERT INTO Employee VALUES
        (1,'Nannette Thacker','4560 S.
Plantation','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (2,'Sylvester Stallone','5360 N. Grove','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (3,'Sean Connery','724 E. Central','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (4,'Emelio Estevez','724 S. Vine','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (5,'Homer Simpson','852 Main St.','Springfield','KS','67005',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (6,'Duke Nukem','128 Palmer Circle','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (7,'Kevin Sorbo','1517 Osage Park','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (8,'Drew Carey','21 Main Vanilla Drive','Chicago
City','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (9,'Lucy Lawless','1428 Main Odyssey
Road','Wichita','KS','67216',
        '23-OCT-95');
INSERT INTO Employee VALUES
        (10,'Darkwing Duck','1515 Main Street','Wichita','KS','67216',
        '23-OCT-95');
 
 
/* insert values into the JOB table */ 
INSERT INTO JOB VALUES (1,'Office Letch');
INSERT INTO JOB VALUES (2,'Water Cooler Bum');
INSERT INTO JOB VALUES (3,'Coffee Hound');
INSERT INTO JOB VALUES (4,'Magazine Reader');
INSERT INTO JOB VALUES (5,'Internet Time Waster');
INSERT INTO JOB VALUES (6,'Programmer');
INSERT INTO JOB VALUES (7,'Office Hunk');
INSERT INTO JOB VALUES (8,'Court Jester');
INSERT INTO JOB VALUES (9,'Stamp Lickers');
INSERT INTO JOB VALUES (10,'Adventurer');
 
/* insert values into the "Employee Job" table */ 
INSERT INTO "Employee Job" VALUES (1,6,10);
INSERT INTO "Employee Job"("employee number","Job Number") VALUES (2,7);
INSERT INTO "Employee Job" VALUES (3,7,10);
INSERT INTO "Employee Job" VALUES (4,7,8);
INSERT INTO "Employee Job" VALUES (5,2,10);
INSERT INTO "Employee Job" VALUES (6,10,9);
INSERT INTO "Employee Job" VALUES (7,7,9);
INSERT INTO "Employee Job" VALUES (8,8,10);
INSERT INTO "Employee Job" VALUES (9,10,9);
INSERT INTO "Employee Job" VALUES (10,10,9);
 
/* these are secondary jobs held by various employees */
INSERT INTO "Employee Job" VALUES (1,9,9);
INSERT INTO "Employee Job" VALUES (2,9,9);
INSERT INTO "Employee Job" VALUES (3,9,9);
INSERT INTO "Employee Job" VALUES (4,9,9);
INSERT INTO "Employee Job" VALUES (5,9,9);
INSERT INTO "Employee Job" VALUES (6,9,9);
INSERT INTO "Employee Job" VALUES (7,9,9);
INSERT INTO "Employee Job" VALUES (8,9,9);
INSERT INTO "Employee Job" VALUES (9,9,9);
INSERT INTO "Employee Job" VALUES (10,9,9);
 
 
 
/*
*       2/19/97 nkt
*       WITHOUT using a subquery:
*       list all employee's names and start dates
*       that have a job description of 'Office Hunk'
*       and a skill level above 7.
*/
 
SELECT
        Employee.Name, Employee."start date"
        FROM Employee, JOB, "Employee Job"
        WHERE Employee."Employee Number"
        = "Employee Job"."employee number"
        AND "Employee Job"."Job Number" = JOB."Job Number"
        AND JOB.Description = 'Office Hunk'
        AND "Employee Job"."Skill level" > 7 ;
 
/*
*       2/19/97 nkt
*       USING a subquery:
*       list all employee's names and start dates
*       that have a job description of 'Office Hunk'
*       and a skill level above 7.
*/
 
SELECT
        Employee.Name, Employee."start date"
        FROM Employee
        WHERE Employee."Employee Number"
        IN
                /*
                find employee numbers for those who have
                a skill level > 7 and are office hunks
                */
        (SELECT
                "Employee Job"."employee number"
                FROM "Employee Job", JOB
                WHERE "Employee Job"."Skill level" > 7
                AND "Employee Job"."Job Number" = JOB."Job Number"
                AND JOB.Description = 'Office Hunk'
        ) ;
 
 
/*
*   2/19/97 nkt
*   QUERY to show all employees names who live on main street.
*/
 
     SELECT
        Employee.Name
        FROM Employee
                /*
                convert the street address field value stored to UPPER
                and return those fields that contain MAIN in the
                street address. Will return 'main street' 'main st.'
                'Main St.' 'Main Street', main without street
                included, and any combination thereof.
                */
        WHERE UPPER(Employee."Street address")
                LIKE '%MAIN%' ;
 
/*
*       2/19/97 nkt
*       QUERY to show all employees names who have a
*       job with no skill level assigned.
*/
            SELECT
                Employee.Name
                FROM Employee
                WHERE Employee."Employee Number"
                IN
                /*
                find employee numbers for those who have
                a skill level that has not been assigned a value
                */
                (SELECT
                                "Employee Job"."employee number"
                                FROM "Employee Job"
                                WHERE "Employee Job"."Skill level"
                                        IS NULL
                ) ;
 
 
/* drop the tables with cascade constraints as we don't want to retain
any */ 
DROP TABLE JOB  CASCADE CONSTRAINTS ;
DROP TABLE "Employee Job"   CASCADE CONSTRAINTS ;
DROP TABLE Employee   CASCADE CONSTRAINTS ;
 
 
spool off