What this exercise is about
This exercise gives you the opportunity to work with table expressions and recursive SQL.
What you should be able to do
At the end of the lab, you should be able to:
• Code SQL statements using nested table expressions • Code SQL statements using common table expressions
• Code recursive SQL statements that control the depth of recursion
Introduction
See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.
Requirements
• Student handout • SQL Reference
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list
Problem 1
Prepare a report giving information about the average total earnings of all
employees hired in the same year. The report should include employees hired from the beginning of 1970 till the end of 1980. Use a nested table expression to fulfill the requirements of this lab problem.
Problem 2
Now, use a common table expression to meet the requirements of problem 1.
Problem 3
For each employee, display the employee number, the salary, and the average salary and department number of the employee’s department. The list should be sorted by department number and employee number.
Problem 4
A table named CARS contains the bill-of-materials for making a specific model of a Honda Accord. You created the CARS table when you executed the CRTAB
member at the beginning of the labs. Determine all the major parts necessary to construct the Passenger Compartment of the car. In other words, do not take the recursion to the maximum possible depth:, control the recursion so that it does not iterate more than once after the initialization.
You can determine the contents of the CARS table with the following SELECT statement.
SELECT * FROM CARS ORDER BY 1, 2, 3, 4
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 7. Table Expressions and Recursive SQL 7-3
Uempty The CARS table should contain the following data:
---+---+---+---+---+---+---+---+
Problem list with Expected Results
Problem 1
Prepare a report giving information about the average total earnings of all employees hired in the same year. The report should include employees hired from the beginning of 1970 till the end of 1980. Use a nested table expression to fulfill the requirements of this lab
problem.
Now, use a common table expression to meet the requirements of problem 1.
Result
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 7. Table Expressions and Recursive SQL 7-5
Uempty
Problem 3
For each employee, display the employee number, the salary, and the average salary and department number of the employee’s department. The list should be sorted by department number and employee number.
Result
The complete result set contains 32 rows.
Problem 4
A table named CARS contains the bill-of-materials for making a specific model of a Honda Accord. You created the CARS table when you executed the CRTAB
member at the beginning of the labs. Determine all the major parts necessary to construct the Passenger Compartment of the car. In other words, do not take the recursion to the maximum possible depth; control the recursion so that it does not iterate more than once after the initialization.
You can determine the contents of the CARS table with the following SELECT statement.
The CARS table should contain the following data:
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 7. Table Expressions and Recursive SQL 7-7
Uempty Result
LEVEL ASSEMBLY COMPONENT QUANTITY --- --- --- --- 0 PASSENGER COMPARTMENT DASHBOARD 1 0 PASSENGER COMPARTMENT SEAT GROUP 3 0 PASSENGER COMPARTMENT TRUNK 1 1 DASHBOARD CONTROL CLUSTER 1 1 DASHBOARD GAUGE CLUSTER 1 1 DASHBOARD STEREO SYSTEM 1 1 DASHBOARD VENT 4 1 SEAT GROUP FRONT SEAT ASSEMBLY 6 1 SEAT GROUP REAR SEAT ASSEMBLY 3 1 TRUNK TOOL KIT 1
End of Problem list with Expected Results
Exclusivo
formación proyecto
C.F.T.I.C.
Solutions
Problem 1
SELECT TEMP.HIREYEAR, AVG(TEMP.TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS FROM
(SELECT YEAR(HIREDATE) AS HIREYEAR,
SALARY + BONUS + COMM AS TOTAL_EARNINGS FROM EMPLOYEE
WHERE HIREDATE BETWEEN '1970-01-01' AND '1980-12-31' ) AS TEMP GROUP BY TEMP.HIREYEAR;
Problem 2
WITH TEMP AS (SELECT YEAR(HIREDATE) AS HIREYEAR, SALARY + COMM + BONUS AS TOTAL_EARNINGS FROM EMPLOYEE WHERE HIREDATE BETWEEN '1970-01-01' AND '1980-12-31') SELECT HIREYEAR, DECIMAL(AVG(TOTAL_EARNINGS),9,2) AS AVG_TOTAL_EARNINGS FROM TEMP GROUP BY HIREYEAR;
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 7. Table Expressions and Recursive SQL 7-9
Uempty
Problem 3
SELECT EMPNO, SALARY, AVG_TAB.AVG_SAL, E.WORKDEPT
FROM EMPLOYEE E INNER JOIN (SELECT WORKDEPT ,AVG(SALARY) AS AVG_SAL FROM EMPLOYEE
GROUP BY WORKDEPT) AS AVG_TAB ON E.WORKDEPT = AVG_TAB.WORKDEPT
ORDER BY E.WORKDEPT, EMPNO;
It is not necessary to restrict yourself to the new join syntax to solve this problem. The old join syntax could also be used. For example, the following work:
SELECT EMPNO, SALARY, AVG_TAB.AVG_SAL, E.WORKDEPT
FROM EMPLOYEE E , (SELECT WORKDEPT ,AVG(SALARY) AS AVG_SAL FROM EMPLOYEE
GROUP BY WORKDEPT) AS AVG_TAB WHERE E.WORKDEPT = AVG_TAB.WORKDEPT
ORDER BY E.WORKDEPT, EMPNO;
Problem 4
WITH CAR_PARTS (LEVEL, ASSEMBLY, COMPONENT, QUANTITY) AS (SELECT 0, ROOT.ASSEMBLY, ROOT.COMPONENT, ROOT.QUANTITY FROM CARS ROOT
WHERE ROOT.LEVEL = 2
AND ASSEMBLY = 'PASSENGER COMPARTMENT' UNION ALL
SELECT PARENT.LEVEL + 1, CHILD.ASSEMBLY, CHILD.COMPONENT, PARENT.QUANTITY * CHILD.QUANTITY FROM CAR_PARTS PARENT, CARS CHILD
WHERE PARENT.COMPONENT = CHILD.ASSEMBLY AND PARENT.LEVEL < 1)
SELECT LEVEL, ASSEMBLY, COMPONENT, QUANTITY FROM CAR_PARTS
ORDER BY LEVEL, ASSEMBLY, COMPONENT;
End of solutions
Exclusivo
formación proyecto
C.F.T.I.C.
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 8. UDT and UDF 8-1
Uempty
Exercise 8. UDT and UDF
What this exercise is about
This exercise will give you some practice coding the DDL to create user-defined data types (UDTs) and user-defined functions (UDFs).
What you should be able to do
At the end of the lab, you should:
• Have some experience defining UDTs and UDFs
Introduction
Refer to the SQL Reference Guide for the syntax diagram and options of the required CREATE and ALTER statements.
Requirements
• Student handout • SQL Reference
Exclusivo
formación proyecto
C.F.T.I.C.
Problem list
Problem 1
Create two User-Defined Distinct Data types, one for German marks called
GER_MARK and one for British pounds called UK_POUND. Let them both be based on DECIMAL(9,2).
Problem 2
Add two columns to the EMPLOYEE table. One called GER_SAL and the other UK_SAL. Define GER_SAL as GER_MARK and UK_SAL as UK_POUND. DB2 for z/OS users will have to use table TESTEMP instead of EMPLOYEE as EMPLOYEE is a view in DB2 for z/OS.
Problem 3
Create sourced column functions to calculate average, find minimum and maximum values for the new data types.
UPDATE the new columns in EMPLOYEE with following values:
EMPNO GER_SAL UK_SAL 000010 100000 40000 000020 80000 31000 000030 72000 29000
Use your new user-defined functions to calculate the average salary in German marks and British Pounds for these three employees.
End of Problem list
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 8. UDT and UDF 8-3
Uempty
Solutions
Problem 1
CREATE DISTINCT TYPE UK_POUND AS DECIMAL(9,2) WITH COMPARISONS;
CREATE DISTINCT TYPE GER_MARK AS DECIMAL(9,2) WITH COMPARISONS;
Problem 2
ALTER TABLE EMPLOYEE ADD GER_SAL GER_MARK;
ALTER TABLE EMPLOYEE ADD UK_SAL UK_POUND;
Problem 3
Create sourced column functions to calculate average, find minimum and maximum values for the new data types.
CREATE FUNCTION AVG(UK_POUND)
UPDATE the new columns in EMPLOYEE with following values:
EMPNO GER_SAL UK_SAL 000010 100000 40000 000020 80000 31000 000030 72000 29000
UPDATE EMPLOYEE SET GER_SAL=100000, UK_SAL=40000 WHERE EMPNO='000010';
UPDATE EMPLOYEE SET GER_SAL=80000, UK_SAL=31000 WHERE EMPNO='000020';
UPDATE EMPLOYEE SET GER_SAL=72000, UK_SAL=29000 WHERE EMPNO='000030';
Use your new user-defined functions to calculate the average salary in German Marks and British Pounds for these three employees.
SELECT AVG(GER_SAL) AS MARKS,AVG(UK_SAL) AS POUNDS FROM EMPLOYEE
WHERE EMPNO IN ('000010','000020','000030');
End of solutions
Exclusivo
formación proyecto
C.F.T.I.C.
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
© Copyright IBM Corp. 2007, 2013 Exercise 9. Performance 9-1