Lab #5 : Oracle SQL Joins
Lab #5: Oracle Assignment
Objectives
The lab is designed to teach you how join tables with equijoins and non-equijoin queries. Specifically, you should learn the following:
- Write equijoins between tables with ANSI 1989 and ANSI 1992 syntax.
- Write equijoins between two copies of the same table with ANSI 1989 and ANSI 1992 syntax.
- Write non-equijoin queries against the result of cross joins with ANSI 1989 and ANSI 1992 syntax.
- Write non-equijoin queries against the result of cross joins with ANSI 1989 and ANSI 1992 syntax.
Business Scenario
Relational databases store information in tables by subject. Most business problems involve information from more than one table because they look at complex data sets. A customer’s name, address, and telephone number is a natural set of complex information because it involves more than one subject. For example, the customer name would be one subject, the customer’s address another subject, and the customer’s telephone number a third subject.
You group these relational subjects together by using queries or SELECT
statements that join information from multiple tables into a single result set. By definition result sets are always denormalized. Sometimes you use queries to join the information together before creating a JSON (JavaScript Object Notation) structure, which you may read into a web page.
Overview
The lab is designed to teach you how to write queries. Some queries join data based on the equality of values between rows, and others simply filter the product of cross joins.
Equijoins
Equijoins tie rows of tables together by matching primary and foreign key values. That means that equijoins work by checking the equality of two values. Equijoins only work when the database design supports:
- A primary key column or set of columns that hold a list of unique values.
- A foreign key column or set of columns that hold a copy of the single column or a set of columns that acts as another row’s primary key. That primary key can exist in another table or in the same table, and the latter case occurs with a recursive relationship.
Non-equijoins
Non-equijoins work by using a cross join. A cross join matches one row in one table with every row in another table, then it repeats the process by matching the next row in the first table with every row in the same table until it runs out of rows in the first table.
The non-equijoin then filters the rows returned from the cross join. Non-equijoins typically work with range comparison operation that:
- Check whether one value is greater than or less than another value.
- Check whether one value is between two other values.
Filtering inequality statements also work inside inner joins. All non-equijoin filters are inside the WHERE clause of a query. This lab will use only a non-equijoin inside a three table inner join statement.
Help Section
This lab relies on what you learned in the first lesson where we covered the idea of queries or SELECT statements. If you need to review this material, please use these links:
The following is an article that qualifies SQL join concepts, logic, implementation, and inheritance tree:
The following web pages explain how to write cross, inner, natural, left, right, and full joins with ANSI SQL-89 and SQL-92 syntax. They explain the concept of set theory, rules surrounding joins, and syntax examples.
You may also need to review the idea of non-equijoins, which were introduced in the WHERE
Clause page. Here is a summary page on non-equijoin:
The lab has five parts.
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/lib/cleanup_oracle.sql @/home/student/Data/cit225/oracle/lib2/create/create_oracle_store2.sql @/home/student/Data/cit225/oracle/lib2/preseed/preseed_oracle_store.sql @/home/student/Data/cit225/oracle/lib2/seed/seeding.sql SPOOL apply_oracle_lab5.txt ... insert code here ... SPOOL OFF |
- [16 points] Write
INNER JOIN
queries that use theUSING
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.
- Display the
MEMBER_ID
andCONTACT_ID
in theSELECT
clause from a join of theMEMBER
andCONTACT
tables. You should make the join with theUSING
subclause based on theMEMBER_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 |
- Display the
MEMBER_ID
andCONTACT_ID
in theSELECT
clause from a join of theMEMBER
andCONTACT
tables. You should put the join in theWHERE
clause and base the join on theMEMBER_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 |
- Display the
CONTACT_ID
andADDRESS_ID
in theSELECT
clause from a join of theCONTACT
andADDRESS
tables. You should make the join between the tables with theUSING
subclause based on theCONTACT_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 |
- Display the
CONTACT_ID
andADDRESS_ID
in theSELECT
clause from a join of theCONTACT
andADDRESS
tables. You should put the join in theWHERE
clause and base the join on theCONTACT_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 |
- Display the
ADDRESS_ID
andSTREET_ADDRESS_ID
in theSELECT
clause from a join of theADDRESS
andSTREET_ADDRESS
tables. You should make the join between the tables with theUSING
subclause based on theADDRESS_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 |
- Display the
ADDRESS_ID
andSTREET_ADDRESS_ID
in theSELECT
clause from a join of theADDRESS
andSTREET_ADDRESS
tables. You should put the join in theWHERE
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 |
- Display the
CONTACT_ID
andTELEPHONE_ID
in theSELECT
clause from a join of theCONTACT
andTELEPHONE
tables. You should make the join between the tables with theUSING
subclause based on theCONTACT_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 |
- Display the
CONTACT_ID
andTELEPHONE_ID
in theSELECT
clause from a join of theCONTACT
andTELEPHONE
tables. You should put the join in theWHERE
clause and base the join on theCONTACT_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 |
- [8 points] Write
INNER JOIN
queries that use theON
subclause and return the following results:
Instruction Details →
- Display the
CONTACT_ID
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should make the join with theON
subclause based on theCREATED_BY
andSYSTEM_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 |
- Display the
CONTACT_ID
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should put the join in theWHERE
clause and base the join on theCREATED_BY
column, 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 |
- Display the
CONTACT_ID
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should make the join with theON
subclause based on theLAST_UPDATED_BY
andSYSTEM_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 |
- Display the
CONTACT_ID
andSYSTEM_USER_ID
columns in theSELECT
clause from a join of theCONTACT
andSYSTEM_USER
tables. You should put the join in theWHERE
clause and base the join on theLAST_UPDATED_BY
andSYSTEM_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 |
- [8 points] Write
INNER JOIN
queries that use theON
subclause to perform a self-join on theSYSTEM_USER
table. The solution requires that you create three copies of the SYSTEM_USER table by using aliases likesu1
,su2
, andsu3
. 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 →
- [2 points]Display the
SYSTEM_USER_ID
andCREATED_BY
columns from one row, and theSYSTEM_USER_ID
column from a row where it is also the primary key. You should make the join with theON
subclause based on theCREATED_BY
andSYSTEM_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 |
- [2 points]Display the
SYSTEM_USER_ID
andLAST_UPDATED_BY
columns from one row, and theSYSTEM_USER_ID
column from a row where it is also the primary key. You should make the join with theON
subclause based on theLAST_UPDATED_BY
andSYSTEM_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 |
- [4 points] Display the
SYSTEM_USER_ID
andSYSTEM_USER_NAME
columns from the first copy of theSYSTEM_USER
table, then the following from the second copy of theSYSTEM_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:
- You join the first copy of the
SYSTEM_USER
table to the second copy of theSYSTEM_USER
table by joining the following two columns:- The
CREATED_BY
column in the first copy of theSYSTEM_USER
table. - The
SYSTEM_USER_ID
column in the second copy of theSYSTEM_USER
table.
- The
- You join the second copy of the
SYSTEM_USER
table to the third copy of theSYSTEM_USER
table by joining the following two columns to the result set of the prior join:- The
LAST_UPDATED_BY
column in the first copy of theSYSTEM_USER
table. - The
SYSTEM_USER_ID
column in the third copy of theSYSTEM_USER
table.
- The
- The
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 |
- [8 points] Display the
RENTAL_ID
column from theRENTAL
table, theRENTAL_ID
andITEM_ID
from theRENTAL_ITEM
table, andITEM_ID
column from theITEM
table. You should make a join from theRENTAL
table to theRENTAL_ITEM
table, and then theITEM
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 and drop the NN_RENTAL_3
constraint.
- [10 points] Display the
DEPARTMENT_NAME
from theDEPARTMENT
table and the average in whole dollars of theSALARY
column from theSALARY
table grouped by and ordered by theDEPARTMENT_NAME
column for the last two months. (HINT: The lookup values and range values with aBETWEEN
operator must be not null values, and when the ending date is null you should use anNVL(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 atTRUNC(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.