Database Tutorial

Course Tutorial Site

Site Admin

Instructions

without comments

Lab #5: SQL Joins

Learn how to write SELECT statements that join two tables and three tables with inner and outer joins.

Lab Description

[50 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You begin the lab by checking the files in the /home/student/Data/cit225/oracle/lib directory:

  • cleanup_oracle.sql
  • create_oracle_store2.sql
  • preseed_oracle_store.sql
  • seeding.sql

You should write the solution inside the apply_oracle_lab5.sql script file in the /home/student/Data/cit225/lab5 directory, as shown:

-- Run the prior lab script.
@/home/student/Data/cit225/oracle/lib1/utility/cleanup_oracle.sql
@/home/student/Data/cit225/oracle/lib1/create/create_oracle_store2.sql
@/home/student/Data/cit225/oracle/lib1/preseed/preseed_oracle_store.sql
@/home/student/Data/cit225/oracle/lib1/seed/seeding.sql
 
SPOOL apply_oracle_lab5.txt
 
... insert code here ...
 
SPOOL OFF

  1. [16 points] Write INNER JOIN queries that use the USING subclause and return the following results:

Instruction Details →

There are several queries to write in this lab. They’re organized by the type of join and syntax pattern.

  1. Display the MEMBER_ID and CONTACT_ID in the SELECT clause from a join of the MEMBER and CONTACT tables. You should make the join with the USING subclause based on the MEMBER_ID column, which is the primary and foreign key of the respective tables.
 Member Contact
   ID #    ID #
------- -------
   1001    1001
   1001    1002
   1002    1003
   1002    1004
   1003    1005
   1003    1006
   1003    1007
   1004    1008
   1005    1009
   1006    1010
   1007    1011
   1008    1012
  1. Display the MEMBER_ID and CONTACT_ID in the SELECT clause from a join of the MEMBER and CONTACT tables. You should put the join in the WHERE clause and base the join on the MEMBER_ID column, which is the primary and foreign key of the respective tables.
 Member Contact
   ID #    ID #
------- -------
   1001    1001
   1001    1002
   1002    1003
   1002    1004
   1003    1005
   1003    1006
   1003    1007
   1004    1008
   1005    1009
   1006    1010
   1007    1011
   1008    1012
  1. Display the CONTACT_ID and ADDRESS_ID in the SELECT clause from a join of the CONTACT and ADDRESS tables. You should make the join between the tables with the USING subclause based on the CONTACT_ID column, which is the primary and foreign key of the respective tables.
Contact Address
   ID #    ID #
------- -------
   1001    1001
   1002    1002
   1003    1003
   1004    1004
   1005    1005
   1006    1006
   1007    1007
   1008    1008
   1009    1009
   1010    1010
   1011    1011
   1012    1012
  1. Display the CONTACT_ID and ADDRESS_ID in the SELECT clause from a join of the CONTACT and ADDRESS tables. You should put the join in the WHERE clause and base the join on the CONTACT_ID column, which is the primary and foreign key of the respective tables.
Contact Address
   ID #    ID #
------- -------
   1001    1001
   1002    1002
   1003    1003
   1004    1004
   1005    1005
   1006    1006
   1007    1007
   1008    1008
   1009    1009
   1010    1010
   1011    1011
   1012    1012
  1. Display the ADDRESS_ID and STREET_ADDRESS_ID in the SELECT clause from a join of the ADDRESS and STREET_ADDRESS tables. You should make the join between the tables with the USING subclause based on the ADDRESS_ID column, which is the primary and foreign key of the respective tables.
	 Street
Address Address
   ID #    ID #
------- -------
   1001    1001
   1002    1002
   1003    1003
   1004    1004
   1005    1005
   1006    1006
   1007    1007
   1008    1008
   1009    1009
   1010    1010
   1011    1011
   1012    1012
  1. Display the ADDRESS_ID and STREET_ADDRESS_ID in the SELECT clause from a join of the ADDRESS and STREET_ADDRESS tables. You should put the join in the WHERE clause and base the join on the ADDRESS_ID column, which is the primary and foreign key of the respective tables.
	 Street
Address Address
   ID #    ID #
------- -------
   1001    1001
   1002    1002
   1003    1003
   1004    1004
   1005    1005
   1006    1006
   1007    1007
   1008    1008
   1009    1009
   1010    1010
   1011    1011
   1012    1012
  1. Display the ADDRESS_ID and TELEPHONE_ID in the SELECT clause from a join of the ADDRESS and TELEPHONE tables. You should make the join between the tables with the USING subclause based on the ADDRESS_ID column, which is the primary and foreign key of the respect tables.
Address Telephone
   ID #      ID #
------- ---------
   1001      1001
   1002      1002
   1003      1003
   1004      1004
   1005      1005
   1006      1006
   1007      1007
   1008      1008
   1009      1009
   1010      1010
   1011      1011
   1012      1012
  1. Display the ADDRESS_ID and TELEPHONE_ID in the SELECT clause from a join of the ADDRESS and TELEPHONE tables. You should put the join in the WHERE clause and base the join on the ADDRESS_ID column, which is the primary and foreign key of the respective tables.
Address Telephone
   ID #      ID #
------- ---------
   1001      1001
   1002      1002
   1003      1003
   1004      1004
   1005      1005
   1006      1006
   1007      1007
   1008      1008
   1009      1009
   1010      1010
   1011      1011
   1012      1012

  1. [8 points] Write INNER JOIN queries that use the ON subclause and return the following results:

Instruction Details →

  1. Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.
	 System
Contact    User
   ID #    ID #
------- -------
   1001       1
   1002       1
   1003       1
   1004       1
   1005       1
   1006       1
   1007       1
   1008    1001
   1009    1001
   1010    1001
   1011    1001
   1012    1001
  1. Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should put the join in the WHERE clause and base the join on the CREATED_BY column in the CONTACT table and SYSTEM_USER_ID column in the SYSTEM_USER table, which is the primary and foreign key of the respective tables.
	 System
Contact    User
   ID #    ID #
------- -------
   1001       1
   1002       1
   1003       1
   1004       1
   1005       1
   1006       1
   1007       1
   1008    1001
   1009    1001
   1010    1001
   1011    1001
   1012    1001
  1. Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.
	 System
Contact    User
   ID #    ID #
------- -------
   1001       1
   1002       1
   1003       1
   1004       1
   1005       1
   1006       1
   1007       1
   1008    1001
   1009    1001
   1010    1001
   1011    1001
   1012    1001
  1. Display the CONTACT_ID and SYSTEM_USER_ID columns in the SELECT clause from a join of the CONTACT and SYSTEM_USER tables. You should put the join in the WHERE clause and base the join on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively.
	 System
Contact    User
   ID #    ID #
------- -------
   1001       1
   1002       1
   1003       1
   1004       1
   1005       1
   1006       1
   1007       1
   1008    1001
   1009    1001
   1010    1001
   1011    1001
   1012    1001
  1. [8 points] Write INNER JOIN queries that use the ON subclause to perform a self-join on the SYSTEM_USER table. The solution requires that you create three copies of the SYSTEM_USER table by using aliases like su1, su2, and su3. Please note that joining two tables is like matching two sets, while joining three table is like joining two tables into a temporary result set (or pseudo table) and then joining the third table to the temporary result set as if it were a table. It should return the following results:

Instruction Details →

  1. [2 points] Display the SYSTEM_USER_ID and CREATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the CREATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

You should use the following column formatting for this command. Please note that column aliases replace column names when two columns share the same name. The formatting commands use the column aliases assigned in the SELECT clause of the query.

COL system_user_id  FORMAT 999999  HEADING "System|User|ID #|--------|Table #1"
COL created_by      FORMAT 999999  HEADING "Created|By|ID #|--------|Table #1"
COL system_user_pk  FORMAT 999999  HEADING "System|User|ID #|--------|Table #2"

The formatting will guarantee headers like the following:

  System  Created   System
    User       By     User
    ID #     ID #     ID #
-------- -------- --------
Table #1 Table #1 Table #2
-------- -------- --------
       1	1	 1
    1001	1	 1
    1002	1	 1
  1. [2 points] Display the SYSTEM_USER_ID and LAST_UPDATED_BY columns from one row, and the SYSTEM_USER_ID column from a row where it is also the primary key. You should make the join with the ON subclause based on the LAST_UPDATED_BY and SYSTEM_USER_ID columns, which are the foreign and primary key respectively. In a self-join, these columns may be in the same or different rows in the table.

You should use the following column formatting for this command. Please note that column aliases replace column names when two columns share the same name. The formatting commands use the column aliases assigned in the SELECT clause of the query.

COL system_user_id   FORMAT 999999  HEADING "System|User|ID #|--------|Table #1"
COL last_updated_by  FORMAT 999999  HEADING "Last|Updated|By|ID #|--------|Table #1"
COL system_user_pk   FORMAT 999999  HEADING "System|User|ID #|--------|Table #2"

The formatting will guarantee headers like the following:

	     Last
  System  Updated   System
    User       By     User
    ID #     ID #     ID #
-------- -------- --------
Table #1 Table #1 Table #2
-------- -------- --------
       1	1	 1
    1001	1	 1
    1002	1	 1
  1. [4 points] Display the SYSTEM_USER_ID and SYSTEM_USER_NAME columns from the first copy of the SYSTEM_USER table, then the following from the second copy of the SYSTEM_USER table:

    • The SYSTEM_USER_ID as the “Created By” column.
    • The SYSTEM_USER_NAME as the “Created By User Name” column.

    and, the following from the third copy of the SYSTEM_USER table:

    • The SYSTEM_USER_ID as the “Last Updated By” column.
    • The SYSTEM_USER_NAME as the “Last Updated User Name” column.

    You solve this by performing a three table join. A join between three tables requires you to join two tables before you join the result of the join between the two tables to the third table. You write the join between the table inside the FROM clause.

    You should follow these steps:

    1. You join the first copy of the SYSTEM_USER table to the second copy of the SYSTEM_USER table by joining the following two columns:

      1. The CREATED_BY column in the first copy of the SYSTEM_USER table.
      2. The SYSTEM_USER_ID column in the second copy of the SYSTEM_USER table.
    2. You join the first copy of the SYSTEM_USER table to the third copy of the SYSTEM_USER table by joining the following two columns to the result set of the prior join:
      1. The LAST_UPDATED_BY column in the first copy of the SYSTEM_USER table.
      2. The SYSTEM_USER_ID column in the third copy of the SYSTEM_USER table.

You should use the following column formatting for this command. Please note that column aliases replace column names when two columns share the same name. The formatting commands use the column aliases assigned in the SELECT clause of the query.

COL user_id        FORMAT 999999  HEADING "System|User|ID #|--------|Table #1"
COL user_name      FORMAT A8      HEADING "System|User|Name|--------|Table #1"
COL cby_user_id    FORMAT 999999  HEADING "System|User|ID #|--------|Table #2"
COL cby_user_name  FORMAT A8      HEADING "System|User|Name|--------|Table #2"
COL lby_user_id    FORMAT 999999  HEADING "System|User|ID #|--------|Table #3"
COL lby_user_name  FORMAT A8      HEADING "System|User|Name|--------|Table #3"

The formatting will guarantee headers like the following:

  System System     System System     System System
    User User	      User User 	User User
    ID # Name	      ID # Name 	ID # Name
-------- -------- -------- -------- -------- --------
Table #1 Table #1 Table #2 Table #2 Table #3 Table #3
-------- -------- -------- -------- -------- --------
       1 SYSADMIN	 1 SYSADMIN	   1 SYSADMIN
    1001 DBA1		 1 SYSADMIN	   1 SYSADMIN
    1002 DBA2		 1 SYSADMIN	   1 SYSADMIN

  1. [8 points] Display the RENTAL_ID column from the RENTAL table, the RENTAL_ID and ITEM_ID from the RENTAL_ITEM table, and ITEM_ID column from the ITEM table. You should make a join from the RENTAL table to the RENTAL_ITEM table, and then the ITEM table. Join the tables based on their respective primary and foreign key values.

Instruction Details →

  Rental Rental Item Rental Item     Item
   Table       Table	   Table    Table
-------- ----------- ----------- --------
  Rental      Rental	    Item     Item
    ID #	ID #	    ID #     ID #
-------- ----------- ----------- --------
    1001	1001	    1002     1002
    1001	1001	    1004     1004
    1001	1001	    1005     1005
    1002	1002	    1016     1016
    1002	1002	    1021     1021
    1003	1003	    1019     1019
    1004	1004	    1014     1014
    1005	1005	    1007     1007
    1005	1005	    1001     1001

After creating the foregoing result set, alter the RENTAL table by dropping the NN_RENTAL_3 constraint.

  1. [10 points] Display the DEPARTMENT_NAME from the DEPARTMENT table and the average in whole dollars of the SALARY column from the SALARY table grouped by and ordered by the DEPARTMENT_NAME column for the last two months. (HINT: The lookup values and range values with a BETWEEN operator must be not null values, and when the ending date is null you should use an NVL(some_date, SYSDATE + 1) or tomorrow to get the operator to return a value other than a null value.)

Instruction Details →

You need to copy the following code blocks into your lab before you can write and run the desired query. That’s because one script creates the DEPARTMENT, EMPLOYEE, and SALARY tables and inserts values into those tables, and the other creates the CALENDAR table that lets you determine the starting and ending date of months.

The firsts block creates the DEPARTMENT, EMPLOYEE and SALARY tables and inserts values into those tables. It follows below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176

/* Conditionally drop non-equijoin sample tables. */
BEGIN
  FOR i IN (SELECT   object_name
            ,        object_type
            FROM     user_objects
            WHERE    object_name IN ('DEPARTMENT','DEPARTMENT_S'
                                  ,'EMPLOYEE','EMPLOYEE_S'
                                  ,'SALARY','SALARY_S')
            ORDER BY object_type) LOOP
    IF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    ELSIF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
    END IF;
  END LOOP;
END;
/
 
/* Create department table. */
CREATE TABLE department
( department_id    NUMBER  CONSTRAINT department_pk PRIMARY KEY
, department_name  VARCHAR2(20));
 
/* Create a department_s sequence. */
CREATE SEQUENCE department_s;
 
/* Create a salary table. */
CREATE TABLE salary
( salary_id             NUMBER  CONSTRAINT salary_pk   PRIMARY KEY
, effective_start_date  DATE    CONSTRAINT salary_nn1  NOT NULL
, effective_end_date    DATE
, salary                NUMBER  CONSTRAINT salary_nn2  NOT NULL);
 
/* Create a salary_s sequence. */
CREATE SEQUENCE salary_s;
 
/* Create an employee table. */
CREATE TABLE employee
( employee_id    NUMBER        CONSTRAINT employee_pk  PRIMARY KEY
, department_id  NUMBER        CONSTRAINT employee_nn1 NOT NULL
, salary_id      NUMBER        CONSTRAINT employee_nn2 NOT NULL
, first_name     VARCHAR2(20)  CONSTRAINT employee_nn3 NOT NULL
, last_name      VARCHAR2(20)  CONSTRAINT employee_nn4 NOT NULL
, CONSTRAINT employee_fk FOREIGN KEY(employee_id) REFERENCES employee(employee_id));
 
/* Create an employee_s sequence. */
CREATE SEQUENCE employee_s;
 
 
/* Create an anonymous program to insert data. */
SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  /* Declare a collection of strings. */
  TYPE xname IS TABLE OF VARCHAR2(20);
 
  /* Declare a collection of numbers. */
  TYPE xsalary IS TABLE OF NUMBER;
 
  /* Local variable generated by a random foreign key. */
  lv_department_id  NUMBER;
  lv_salary_id      NUMBER;
 
  /* A collection of first names. */
  lv_first XNAME := xname('Ann','Abbey','Amanda','Archie','Antonio','Arnold'
                         ,'Barbara','Basil','Bernie','Beth','Brian','Bryce'
                         ,'Carl','Carrie','Charlie','Christine','Corneilus','Crystal'
                         ,'Dana','Darlene','Darren','Dave','Davi','Deidre'
                         ,'Eamonn','Eberhard','Ecaterina','Ebony','Elana','Eric'
                         ,'Fabian','Faith','Fernando','Farris','Fiana','Francesca'
                         ,'Gabe','Gayle','Geoffrey','Gertrude','Grayson','Guy'
                         ,'Harry','Harriet','Henry','Henrica','Herman','Hesper'
                         ,'Ian','Ida','Iggy','Iliana','Imogene','Issac'
                         ,'Jan','Jack','Jennifer','Jerry','Julian','June'
                         ,'Kacey','Karen','Kaitlyn','Keith','Kevin','Kyle'
                         ,'Laney','Lawrence','Leanne','Liam','Lois','Lynne'
                         ,'Marcel','Marcia','Mark','Meagan','Mina','Michael'
                         ,'Nancy','Naomi','Narcissa','Nasim','Nathaniel','Neal'
                         ,'Obadiah','Odelia','Ohanna','Olaf','Olive','Oscar'
                         ,'Paige','Palmer','Paris','Pascal','Patricia','Peter'
                         ,'Qadir','Qasim','Quaid','Quant','Quince','Quinn'
                         ,'Rachelle','Rafael','Raj','Randy','Ramona','Raven'
                         ,'Savina','Sadie','Sally','Samuel','Saul','Santino'
                         ,'Tabitha','Tami','Tanner','Thomas','Timothy','Tina'
                         ,'Ugo','Ululani','Umberto','Una','Urbi','Ursula'
                         ,'Val','Valerie','Valiant','Vanessa','Vaughn','Verna'
                         ,'Wade','Wagner','Walden','Wanda','Wendy','Wilhelmina'
                         ,'Xander','Xavier','Xena','Xerxes','Xia','Xylon'
                         ,'Yana','Yancy','Yasmina','Yasmine','Yepa','Yeva'
                         ,'Zacarias','Zach','Zahara','Zander','Zane');
 
  /* A collection of last names. */
  lv_last  XNAME := xname('Abernathy','Anderson','Baker','Barney'
                         ,'Christensen','Cafferty','Davis','Donaldson'
                         ,'Eckhart','Eidelman','Fern','Finkel','Frank','Frankel','Fromm'
                         ,'Garfield','Geary','Harvey','Hamilton','Harwood'
                         ,'Ibarguen','Imbezi','Lindblom','Lynstrom'
                         ,'Martel','McKay','McLellen','Nagata','Noonan','Nunes'
                         ,'O''Brien','Oakey','Patterson','Petersen','Pratel','Preston'
                         ,'Qian','Queen','Ricafort','Richards','Roberts','Robertson'
                         ,'Sampson','Simon','Tabacchi','Travis','Trevor','Tower'
                         ,'Ubel','Urie','Vassen','Vanderbosch'
                         ,'Wacha','Walcott','West','Worley','Xian','Xiang'
                         ,'Yackley','Yaguchi','Zarbarsky','Zambelli');
 
  /* A collection of department names. */
  lv_dept  XNAME := xname('Accounting','Operations','Sales','Factory','Manufacturing');
 
  /* A colleciton of possible salaries. */
  lv_salary  XSALARY := xsalary( 36000, 42000, 48000, 52000, 64000 );
 
  /* Define a local function. */
  FUNCTION random_foreign_key RETURN INTEGER IS
    /* Declare a return variable. */
    lv_return_value  NUMBER;
  BEGIN
    /* Select a random number between 1 and 5 and assign it to a local variable. */
    SELECT CASE
             WHEN num = 0 THEN 5 ELSE num
           END AS random_key
    INTO   lv_return_value
    FROM   (SELECT ROUND(dbms_random.VALUE(1,1000)/100/2,0) num FROM dual) il;
 
    /* Return the random number. */
    RETURN lv_return_value;
  END random_foreign_key;
 
BEGIN
  /* Insert departments. */
  FOR i IN 1..lv_dept.LAST LOOP
    INSERT INTO department
    ( department_id
    , department_name )
    VALUES
    ( department_s.NEXTVAL
    , lv_dept(i));
  END LOOP;
 
  /* Insert salary. */
  FOR i IN 1..lv_salary.LAST LOOP
    INSERT INTO salary
    ( salary_id
    , effective_start_date
    , salary )
    VALUES
    ( salary_s.NEXTVAL
    , TRUNC(SYSDATE) - 30
    , lv_salary(i));
  END LOOP;
 
  /* Insert random employees. */
  FOR i IN 1..lv_first.LAST LOOP
    FOR j IN 1..lv_last.LAST LOOP
      /* Assign a random values to a local variable. */
      lv_department_id := random_foreign_key;
      lv_salary_id := random_foreign_key;
 
      /* Insert values into the employee table. */
      INSERT INTO employee
      ( employee_id
      , department_id
      , salary_id
      , first_name
      , last_name )
      VALUES
      ( employee_s.NEXTVAL
      , lv_department_id
      , lv_salary_id
      , lv_first(i)
      , lv_last(j));   
    END LOOP;
  END LOOP;
 
  /* Commit the writes. */
  COMMIT;  
END;
/

After you run the anonymous block program, you can confirm it success with the following query that joins three tables together. A table join between three or more tables produces a result set similar to the result set form a two table join.

The join between two tables matches one row in a table to one or more rows in another table. The result set of a join returns the all the columns from both tables in set of matched rows. The SELECT clause chooses only those columns that you want to display.

The join between a join of two tables and a third table works like the join between two tables because conceptually it treats the result set of the first two tables as temporary table. The third table is then joined to the temporary table and creates a new temporary table with all the columns from the three tables. The SELECT clause lets you choose which columns to display from the three tables.

The occurrence of any aggregation column in the SELECT-list, or comma delimited set of column values, means you must add a GROUP BY clause to the query. The GROUP BY clause identifies non-aggregating columns that would otherwise repeat in the result set as duplicates and it returns one unique copy with the result of the aggregation function.

SELECT   d.department_name
,        ROUND(AVG(s.salary),0) AS salary
FROM     employee e INNER JOIN department d
ON       e.department_id = d.department_id INNER JOIN salary s
ON       e.salary_id = s.salary_id
GROUP BY d.department_name
ORDER BY d.department_name;

The query should use return the average salary in whole dollars:

DDEPARTMENT_NAME 	 SALARY
-------------------- ----------
Accounting		  48337
Factory 		  48138
Manufacturing		  48526
Operations		  48124
Sales			  48721

After you run the foregoing anonymous block program and verify the results, you should copy the following script into you script file:

/* Conditionally drop the table. */
BEGIN
  FOR i IN (SELECT table_name
            FROM   user_tables
            WHERE  table_name = 'MOCK_CALENDAR') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS';
  END LOOP;
END;
/
 
/* Create a mock_calendar table. */
CREATE TABLE mock_calendar
( short_month  VARCHAR2(3)
, long_month   VARCHAR2(9)
, start_date   DATE
, end_date     DATE );
 
/* Seed the table with 10 years of data. */
DECLARE
  /* Create local collection data types. */
  TYPE smonth IS TABLE OF VARCHAR2(3);
  TYPE lmonth IS TABLE OF VARCHAR2(9);
 
  /* Declare month arrays. */
  short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN'
                              ,'JUL','AUG','SEP','OCT','NOV','DEC');
  long_month  LMONTH := lmonth('January','February','March','April','May','June'
                              ,'July','August','September','October','November','December');
 
  /* Declare base dates. */
  start_date DATE := '01-JAN-15';
  end_date   DATE := '31-JAN-15';
 
  /* Declare years. */
  years      NUMBER := 4;
 
BEGIN
 
  /* Loop through years and months. */
  FOR i IN 1..years LOOP
    FOR j IN 1..short_month.COUNT LOOP
      INSERT INTO mock_calendar VALUES
      ( short_month(j)
      , long_month(j)
      , ADD_MONTHS(start_date,(j-1)+(12*(i-1)))
      , ADD_MONTHS(end_date,(j-1)+(12*(i-1))));
    END LOOP;
  END LOOP;
 
  /* Commit the records. */
  COMMIT;
END;
/

After you run the foregoing anonymous block program and verify the results, you should copy the following script into you script file:

/* Set output parameters. */
SET PAGESIZE 16
 
/* Format column output. */
COL short_month FORMAT A5 HEADING "Short|Month"
COL long_month  FORMAT A9 HEADING "Long|Month"
COL start_date  FORMAT A9 HEADING "Start|Date"
COL end_date    FORMAT A9 HEADING "End|Date" 
 
/* Query the results from the table. */
SELECT * FROM mock_calendar;

The query should use return four years of calendar data, like:

Short Long	Start	  End
Month Month	Date	  Date
----- --------- --------- ---------
JAN   January	01-JAN-17 31-JAN-17
FEB   February	01-FEB-17 28-FEB-17
MAR   March	01-MAR-17 31-MAR-17
APR   April	01-APR-17 30-APR-17
MAY   May	01-MAY-17 31-MAY-17
JUN   June	01-JUN-17 30-JUN-17
JUL   July	01-JUL-17 31-JUL-17
AUG   August	01-AUG-17 31-AUG-17
SEP   September 01-SEP-17 30-SEP-17
OCT   October	01-OCT-17 31-OCT-17
NOV   November	01-NOV-17 30-NOV-17
DEC   December	01-DEC-17 31-DEC-17
 
Short Long	Start	  End
Month Month	Date	  Date
----- --------- --------- ---------
JAN   January	01-JAN-18 31-JAN-18
FEB   February	01-FEB-18 28-FEB-18
MAR   March	01-MAR-18 31-MAR-18
APR   April	01-APR-18 30-APR-18
MAY   May	01-MAY-18 31-MAY-18
JUN   June	01-JUN-18 30-JUN-18
JUL   July	01-JUL-18 31-JUL-18
AUG   August	01-AUG-18 31-AUG-18
SEP   September 01-SEP-18 30-SEP-18
OCT   October	01-OCT-18 31-OCT-18
NOV   November	01-NOV-18 30-NOV-18
DEC   December	01-DEC-18 31-DEC-18
 
Short Long	Start	  End
Month Month	Date	  Date
----- --------- --------- ---------
JAN   January	01-JAN-19 31-JAN-19
FEB   February	01-FEB-19 28-FEB-19
MAR   March	01-MAR-19 31-MAR-19
APR   April	01-APR-19 30-APR-19
MAY   May	01-MAY-19 31-MAY-19
JUN   June	01-JUN-19 30-JUN-19
JUL   July	01-JUL-19 31-JUL-19
AUG   August	01-AUG-19 31-AUG-19
SEP   September 01-SEP-19 30-SEP-19
OCT   October	01-OCT-19 31-OCT-19
NOV   November	01-NOV-19 30-NOV-19
DEC   December	01-DEC-19 31-DEC-19
 
Short Long	Start	  End
Month Month	Date	  Date
----- --------- --------- ---------
JAN   January	01-JAN-20 31-JAN-20
FEB   February	01-FEB-20 29-FEB-20
MAR   March	01-MAR-20 31-MAR-20
APR   April	01-APR-20 30-APR-20
MAY   May	01-MAY-20 31-MAY-20
JUN   June	01-JUN-20 30-JUN-20
JUL   July	01-JUL-20 31-JUL-20
AUG   August	01-AUG-20 31-AUG-20
SEP   September 01-SEP-20 30-SEP-20
OCT   October	01-OCT-20 31-OCT-20
NOV   November	01-NOV-20 30-NOV-20
DEC   December	01-DEC-20 31-DEC-20

Now you can add a WHERE clause to the following query. The WHERE clause should check whether the current effective salary start date is within minus sixty days of today and today.

When a null value may exist for any effective_start_date column value, you should wrap that column value in an NVL function call. You should use a value outside of the planned range when a null value is found in the effective_start_date column, like the day after tomorrow. You can represent common dates for this type of problem as follows:

  • A day 30-days ago would be TRUNC(SYSDATE) - 30.
  • Yesterday would be TRUNC(SYSDATE) - 1.
  • Today would be TRUNC(SYSDATE) when measuring a date between today and some arbitrary date.
  • Today would be TRUNC(SYSDATE) + 1 when measuring a date between some arbitrary date in the past and today because today runs until the start of tomorrow.
  • Tomorrow start at TRUNC(SYSDATE) + 1 and ends at TRUNC(SYSDATE) + 2; which means you use the former to end today and the latter for tomorrow.

The query should return aggregated results for all those rows where the effective_start_date is within the range of today minus 60-days and tomorrow morning.

SELECT   d.department_name
,        ROUND(AVG(s.salary),0) AS salary
FROM     employee e INNER JOIN department d
ON       e.department_id = d.department_id INNER JOIN salary s
ON       e.salary_id = s.salary_id
WHERE    ...
GROUP BY d.department_name
ORDER BY d.department_name;

The query should return the same average salaries in whole dollars as when it was run without a WHERE clause.

DEPARTMENT_NAME 	 SALARY
-------------------- ----------
Accounting		  48209
Factory 		  48414
Manufacturing		  48768
Operations		  48598
Sales			  48308

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

After you write the apply_oracle_lab5.sql script, you should navigate to the /home/student/Data/cit225/oracle/lab5 directory. You call the sqlplus utility from the same directory and then you run the apply_oracle_lab5.sql script with the following syntax:

@apply_oracle_lab5.sql

You should submit your apply_oracle_lab5.sql script file and apply_oracle_lab5.txt log file for a grade.

Written by michaelmclaughlin

August 13th, 2018 at 11:20 am

Posted in