CE1319XSTUD

Business Intelligence

Data Centre

Cloud

Mobility

Enterprise Computing Solutions

Student Manual

Dirección General de Formación CONSEJERÍA DE EMPLEO, TURISMO Y CULTURA

Comunidad de Madrid

UNIÓN EUROPEA FONDO SOCIAL EUROPEO El Fondo Social Europeo invierte en tu futuro

EDUCATION S

E

R

V

I

C

E

S

V6.0

cover

u oy si ec vo to fo C rm .F a .T ció .I. n C .

Front cover

DB2 SQL Workshop for Experienced Users (Course code CE131)

Student Exercises

pr

Ex

cl

ERC 9.4

IBM certified course material

Student Exercises

Trademarks The reader should recognize that the following terms, which appear in the content of this training document, are official trademarks of IBM or other companies: IBM® and the IBM logo are registered trademarks of International Business Machines Corporation.


The following are trademarks of International Business Machines Corporation, registered in many jurisdictions worldwide: AIX® DB2 Connect™ iSeries® OS/390® WebSphere® 400®

AS/400® DB2® MVS™ OS/400® z/OS®

Command Center® IMS™ Notes® QMF™ zSeries®

Intel and Intel Core are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle and/or its affiliates.

VMware and the VMware "boxes" logo and design, Virtual SMP and VMotion are registered trademarks or trademarks (the "Marks") of VMware, Inc. in the United States and/or other jurisdictions.

Ex

cl

Other product and service names might be trademarks of IBM or other companies.

pr

April 2013 edition

The information contained in this document has not been submitted to any formal IBM test and is distributed on an “as is” basis without any warranty either express or implied. The use of this information or the implementation of any of these techniques is a customer responsibility and depends on the customer’s ability to evaluate and integrate them into the customer’s operational environment. While each item may have been reviewed by IBM for accuracy in a specific situation, there is no guarantee that the same or similar results will result elsewhere. Customers attempting to adapt these techniques to their own environments do so at their own risk.

© Copyright International Business Machines Corporation 2007, 2013. This document may not be reproduced in whole or in part without the prior written permission of IBM. Note to U.S. Government Users — Documentation related to restricted rights — Use, duplication or disclosure is subject to restrictions set forth in GSA ADP Schedule Contract with IBM Corp.

V6.0 Student Exercises

Contents Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v


Exercises description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii General Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii General Information for z/OS or OS/390 Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ix General Information for DB2 for Linux, UNIX, and Windows Users . . . . . . . . . . . . . . . . . xi Table descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiv Table content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvi Content of VIEWS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv Exercise 1. SQL Basics Review, CUBE, and ROLLUP . . . . . . . . . . . . . . . . . . . . . . . 1-1 Exercise 2. Create Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2-1

Exercise 3. Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3-1 Exercise 4. CASE and CAST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4-1

Exercise 5. Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5-1 Exercise 6. Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6-1

Exercise 7. Table Expressions and Recursive SQL . . . . . . . . . . . . . . . . . . . . . . . . . 7-1 Exercise 8. UDT and UDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8-1

cl

Exercise 9. Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9-1

pr

Ex

TOC

© Copyright IBM Corp. 2007, 2013 Course materials may not be reproduced in whole or in part without the prior written permission of IBM.

Contents

iii

pr

Ex

cl


Student Exercises

iv

DB2 SQL Workshop for Experienced Users

© Copyright IBM Corp. 2007, 2013

Course materials may not be reproduced in whole or in part without the prior written permission of IBM.


Trademarks The reader should recognize that the following terms, which appear in the content of this training document, are official trademarks of IBM or other companies:


IBM® and the IBM logo are registered trademarks of International Business Machines Corporation.

The following are trademarks of International Business Machines Corporation, registered in many jurisdictions worldwide: AIX® DB2 Connect™ iSeries® OS/390® WebSphere® 400®

AS/400® DB2® MVS™ OS/400® z/OS®

Command Center® IMS™ Notes® QMF™ zSeries®

Intel and Intel Core are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both. UNIX is a registered trademark of The Open Group in the United States and other countries.

Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle and/or its affiliates.

cl

VMware and the VMware "boxes" logo and design, Virtual SMP and VMotion are registered trademarks or trademarks (the "Marks") of VMware, Inc. in the United States and/or other jurisdictions. Other product and service names might be trademarks of IBM or other companies.

pr

Ex

TMK


Trademarks

v

pr

Ex

cl


Student Exercises

vi





Exercises description Be sure to read the General Information section and the description of the lab tables before attempting the exercises.


Feel free to consult with the instructor if you need a hint or encounter difficulties while you are formulating the query.

Expected Results — Repeats the problem and shows the results that you should get if you run the query. In most cases, the complete result is shown. In cases where the result set is too large, an ellipsis (...) is shown to indicate that there are additional rows in the result. In these cases, the correct number of rows for the result set is shown so that you can be sure that you got the right result.

Solutions — Contains the correct query that solves the problem. Remember that sometimes there can be several correct answers. Your solution may be different than the one shown in the document. If you have any doubts about whether your solution is correct or if it is as good as the given solution from a performance point of view, please consult your instructor.

General Information

This lab guide provides the information necessary to complete the required and optional labs for the IBM DB2 SQL Workshop for Experienced Users course.

cl

In countries that provide several query tools for each environment, this lab guide will be supplemented by additional documentation that describes the basics of each query tool.

You can do the exercises for this course in z/OS or OS/390 or in a Workstation environment. Depending on where you are taking the course, the Workstation platform available for your use may be on any of Linux, UNIX, or Windows. Be sure to read the instructions for the operating system you chose before attempting the exercises. All of the workstation platforms use the same instructions.

You will be able to use any of the query tools that we have installed on each operating system. Please note that the emphasis of this course is on teaching you SQL, not how to use the tools. Therefore, you will only see the basics of using the tool in this course. If you want more information about these tools, ask the instructor for recommendations on the best manuals to read or courses to take for this purpose.

pr

Ex

pref


Exercises description


vii

Student Exercises

You do not need to save your solutions or show them to the instructor but feel free to do so if you like.

pr

Ex

cl


Full solutions are provided for every question but if you wish to save your own solutions to diskette or print them, ask your instructor when you begin the first exercise. In some cases, diskettes or printers may not be available.

viii





General Information for z/OS or OS/390 Users There are many different query tools that work with DB2 for z/OS, including DB2 Connect, SPUFI, QMF, and others. You can use any tool installed to do the labs. The following query tools are available for this course:


• __________________________ • __________________________ • __________________________

The instructor will tell you how to log on to the z/OS system and what specific user ID and password to use. In addition, you will probably be given a team number: this number will be used occasionally during the exercises so that you can distinguish your files or objects from those of other students. Your logon ID is __________________________________________. Your password is __________________________________________. Your team number is __________________________________________.

The instructor will give you brief verbal or written instructions on how to use the tool you choose so that you will have enough information to get started. If you want to know more about a tool, for example how to save a query, be sure to ask the instructor. You will need a partitioned dataset in order to complete the exercises. This dataset already exists.

cl

The dataset name is __________________________________________.

Each of the tables described in the Table Relationships section of this document already exists and has the same qualifier. The qualifier is _______________.

When you refer to these tables in your SQL, you will need to prefix the table names with the qualifier identified above. For example, if you want to display the complete contents of the EMPLOYEE table, enter the following, substituting the qualifier you were given:

pr

Ex

pref

SELECT * FROM qualifier.EMPLOYEE

Note: Using member CRTAB to create table TESTEMP, if the table has not been provided in advance. You may need to add an IN clause to your CREATE TABLE statements that identifies where the table is being created.




ix

Student Exercises

The IN clause you need to add is: IN _________________________________. When you are using the TESTEMP table, you will have to omit the qualifier or use your logon ID as qualifier. For example, if you are trying to read the TESTEMP table, you will have to write:


SELECT * FROM TESTEMP or SELECT * FROM logon ID.TESTEMP

pr

Ex

cl

You do not have to save your SQL but if you want to save it, you may put it in your partitioned dataset.

x





General Information for DB2 for Linux, UNIX, and Windows Users


There are many different query tools that work with DB2 on the workstation platforms, including Command Editor, CLP, and others. You can use any tool installed to do the labs. The following query tools are available for this course: • __________________________ • __________________________ • __________________________

Students doing the exercises on workstation platforms do not need a team number. Each workstation student has a complete standalone environment and will not share any files or tables with other students. Each student will have to sign on with the following information:

USERID is ______________________ PASSWORD is ____________________

The instructor will give you brief verbal or written instructions on how to use the tool you choose so that you have enough information to get started. If you want to know more about a tool, for example how to save a query, be sure to ask the instructor. A database named sample has already been created for you.

• To connect in a single-user environment, enter the following at a command prompt: db2 connect to sample

• To connect in a client/server environment, enter the following at a command prompt, substituting the userid and password you were assigned:

cl

db2 connect to sample user userid using password

Each of the tables described in the Table Relationships section of this document already exists and has the same qualifier. The qualifier is _________________.

When you refer to these tables in your SQL, you will need to prefix the table names with the qualifier identified above. For example, if you want to display the complete contents of the EMPLOYEE table, enter the following, substituting the qualifier you were given:

pr

Ex

pref

SELECT * FROM qualifier.EMPLOYEE




xi

Student Exercises

Note: Using member CRTAB to create table TESTEMP, if the table has not been provided in advance. Using the following command to create table TESTEMP: DB2 -tvf CRTAB


In cases where you are using your TESTEMP table you need to omit the qualifier or use your logon ID as qualifier. For example, if you are trying to read the TESTEMP table, you will need to write: SELECT * FROM TESTEMP or SELECT * FROM logon ID.TESTEMP

pr

Ex

cl

If you choose to do the labs in CLP, you may create your SQL in the \CE13 directory.

xii





pref

Table relationships DEPARTMENT Table MGRNO


DEPTNO

EMPLOYEE Table EMPNO

WORKDEPT

PROJECT Table PROJNO

DEPTNO

RESPEMP

EMP_ACT Table EMPNO

PROJNO

© Copyright IBM Corporation 2007, 2012

Figure -1. Table relationships

CE1319.4

Notes:

pr

Ex

cl

This diagram illustrates the relationships between the tables used in the exercises for this course. The lines show the connection between the tables.




xiii

Student Exercises

Table descriptions The tables are described in hierarchical order, as shown in the Table Relationships on the previous page.


DEPARTMENT There is one row in the DEPARTMENT table for each department in the company. Column Name

Meaning

Data Type

NULLS allowed

DEPTNO

Department Number

CHAR(3)

N

DEPTNAME

Department Name

VARCHAR(36)

N

MGRNO

Employee Number of the Responsible Manager

CHAR(6)

Y

ADMRDEPT

Department Number of the Department to which the Department reports

CHAR(3)

N

LOCATION

Location Number

CHAR(5)

Y

EMPLOYEE

There is one row in the EMPLOYEE table for each of the employees in the company. Meaning

Data Type

NULLS allowed

EMPNO

Employee Number

CHAR(6)

N

FIRSTNME

First Name

VARCHAR(20)

N

MIDINIT

Middle Initial

CHAR(1)

N

LASTNAME

Last Name

VARCHAR(15)

N

WORKDEPT

Department in which the Employee Works

CHAR(3)

Y

PHONENO

Phone Number

CHAR(4)

Y

HIREDATE

Date of Hire

DATE

Y

Job

CHAR(8)

Y

EDLEVEL

Number of Years of Formal Education

SMALLINT

Y

SEX

Sex (M male, F female)

CHAR(1)

Y

BIRTHDATE

Date of Birth

DATE

Y

SALARY

Yearly Salary

DECIMAL(9, 2)

Y

BONUS

Yearly Bonus

DECIMAL(9, 2)

Y

DECIMAL(9, 2)

Y

cl

Column Name

pr

Ex

JOB

COMM

xiv

Yearly Commission





PROJECT There is one row in the PROJECT table for each project. Meaning

Data Type

NULLS allowed

PROJNO

Project Number

CHAR(6)

N

PROJNAME

Project Name

VARCHAR(24)

N


Column Name

DEPTNO

Responsible Department

CHAR(3)

N

RESPEMP

Employee Number of the Responsible Employee

CHAR(6)

N

PRSTAFF

Estimated Mean Staffing

DECIMAL(5, 2)

Y

PRSTDATE

Estimated Start Date

DATE

Y

PRENDATE

Estimated End Date

DATE

Y

MAJPROJ

Major Project for a Subproject

CHAR(6)

Y

EMP_ACT

There are many rows in the EMP_ACT table for any employee or any project. Meaning

Data Type

NULLS allowed

EMPNO

Employee Number of Employee Performing the Activity

CHAR(6)

N

PROJNO

Project Number

CHAR(6)

N

ACTNO

Activity Number

SMALLINT

N

EMPTIME

Proportion of Employee's Time Spent on Project

DECIMAL(5, 2)

Y

EMSTDATE

Date Activity Starts

DATE

Y

EMENDATE

Date Activity Ends

DATE

Y

cl

Column Name

pr

Ex

pref




xv

Student Exercises

Table content DEPARTMENT Table DEPTNAME -----------------------------SPIFFY COMPUTER SERVICE DIV. PLANNING INFORMATION CENTER DEVELOPMENT CENTER MANUFACTURING SYSTEMS ADMINISTRATION SYSTEMS SUPPORT SERVICES OPERATIONS SOFTWARE SUPPORT

MGRNO -----000010 000020 000030 000060 000070 000050 000090 000100

ADMRDEPT LOCATION -------- -------A00 A00 A00 A00 D01 D01 A00 E01 E01

pr

Ex

cl


DEPTNO -----A00 B01 C01 D01 D11 D21 E01 E11 E21

xvi





EMPLOYEE Table FIRSTNME ---------CHRISTINE MICHAEL SALLY JOHN IRVING EVA EILEEN THEODORE VINCENZO SEAN DOLORES HEATHER BRUCE ELIZABETH MASATOSHI MARILYN JAMES DAVID WILLIAM JENNIFER JAMES SALVATORE DANIEL SYBIL MARIA ETHEL JOHN PHILIP MAUDE RAMLAL WING JASON

MIDINIT ------I L A B F D W Q G

LASTNAME ---------HAAS THOMPSON KWAN GEYER STERN PULASKI HENDERSON SPENSER LUCCHESI O'CONNELL QUINTANA NICHOLLS ADAMSON PIANKA YOSHIMURA SCOUTTEN WALKER BROWN JONES LUTZ JEFFERSON MARINO SMITH JOHNSON PEREZ SCHNEIDER PARKER SMITH SETRIGHT MEHTA LEE GOUNOT

WORKDEPT -------A00 B01 C01 E01 D11 D21 E11 E21 A00 A00 C01 C01 D11 D11 D11 D11 D11 D11 D11 D11 D21 D21 D21 D21 D21 E11 E11 E11 E11 E21 E21 E21

PHONENO ------3978 3476 4738 6789 6423 7831 5498 0972 3490 2167 4578 1793 4510 3782 2890 1682 2986 4501 0942 0672 4265 3780 0961 8953 9001 8997 4502 2095 3332 9990 2103 5698

HIREDATE ---------1965-01-01 1973-10-10 1975-04-05 1949-08-17 1973-09-14 1980-09-30 1970-08-15 1980-06-19 1958-05-16 1963-12-05 1971-07-28 1976-12-15 1972-02-12 1977-10-11 1978-09-15 1973-07-07 1974-07-26 1966-03-03 1979-04-11 1968-08-29 1966-11-21 1979-12-05 1969-10-30 1975-09-11 1980-09-30 1967-03-24 1980-05-30 1972-06-19 1964-09-12 1965-07-07 1976-02-23 1947-05-05

cl


EMPNO -----000010 000020 000030 000050 000060 000070 000090 000100 000110 000120 000130 000140 000150 000160 000170 000180 000190 000200 000210 000220 000230 000240 000250 000260 000270 000280 000290 000300 000310 000320 000330 000340

M A

R J S H

T K J M S V L R R X F V R

pr

Ex

pref




xvii

Student Exercises

EMPLOYEE Table (Cont)

pr

Ex

cl


JOB EDLEVEL SEX BIRTHDATE SALARY BONUS COMM --------- ------- --- ---------- --------- -------- -------PRES 18 F 1933-08-14 52750.00 1000.00 4220.00 MANAGER 18 M 1948-02-02 41250.00 800.00 3300.00 MANAGER 20 F 1941-05-11 38250.00 800.00 3060.00 MANAGER 16 M 1925-09-15 40175.00 800.00 3214.00 MANAGER 16 M 1945-07-07 32250.00 600.00 2580.00 MANAGER 16 F 1953-05-26 36170.00 700.00 2893.00 MANAGER 16 F 1941-05-15 29750.00 600.00 2380.00 MANAGER 14 M 1956-12-18 26150.00 500.00 2092.00 SALESREP 19 M 1929-11-05 46500.00 900.00 3720.00 CLERK 14 M 1942-10-18 29250.00 600.00 2340.00 ANALYST 16 F 1925-09-15 23800.00 500.00 1904.00 ANALYST 18 F 1946-01-19 28420.00 600.00 2274.00 DESIGNER 16 M 1947-05-17 25280.00 500.00 2022.00 DESIGNER 17 F 1955-04-12 22250.00 400.00 1780.00 DESIGNER 16 M 1951-01-05 24680.00 500.00 1974.00 DESIGNER 17 F 1949-02-21 21340.00 500.00 1707.00 DESIGNER 16 M 1952-06-25 20450.00 400.00 1636.00 DESIGNER 16 M 1941-05-29 27740.00 600.00 2217.00 DESIGNER 17 M 1953-02-23 18270.00 400.00 1462.00 DESIGNER 18 F 1948-03-19 29840.00 600.00 2387.00 CLERK 14 M 1935-05-30 22180.00 400.00 1774.00 CLERK 17 M 1954-03-31 28760.00 600.00 2301.00 CLERK 15 M 1939-11-12 19180.00 400.00 1534.00 CLERK 16 F 1936-10-05 17250.00 300.00 1380.00 CLERK 15 F 1953-05-26 27380.00 500.00 2190.00 OPERATOR 17 F 1936-03-28 26250.00 500.00 2100.00 OPERATOR 12 M 1946-07-09 15340.00 300.00 1227.00 OPERATOR 14 M 1936-10-27 17750.00 400.00 1420.00 OPERATOR 12 F 1931-04-21 15900.00 300.00 1272.00 FIELDREP 16 M 1932-08-11 19950.00 400.00 1596.00 FIELDREP 14 M 1941-07-18 25370.00 500.00 2030.00 FIELDREP 16 M 1926-05-17 23840.00 500.00 1907.00

xviii DB2 SQL Workshop for Experienced Users




PROJECT Table DEPT PR PROJNAME NO RESPEMP STAFF --------------------------------- ----ADMIN SERVICES D01 000010 6.50 GENERAL AD SYSTEMS D21 000070 6.00 PAYROLL PROGRAMMING D21 000230 2.00 PERSONNEL PROGRAMMG D21 000250 1.00 ACCOUNT.PROGRAMMING D21 000270 2.00 QUERY SERVICES C01 000030 2.00 USER EDUCATION C01 000030 1.00 WELD LINE AUTOMATION D01 000010 12.00 W L PROGRAMMING D11 000060 9.00 W L PROGRAM DESIGN D11 000220 2.00 W L ROBOT DESIGN D11 000150 3.00 W L PROD CONT PROGS D11 000160 3.00 OPERATION SUPPORT E01 000050 6.00 OPERATION E11 000090 5.00 GEN SYSTEMS SERVICES E01 000050 5.00 SYSTEMS SUPPORT E21 000100 4.00 SCP SYSTEMS SUPPORT E21 000320 1.00 APPLICATIONS SUPPORT E21 000330 1.00 DB/DC SUPPORT E21 000340 1.00 WELD LINE PLANNING B01 000020 1.00

PRSTDATE ---------1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-02-15 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01 1982-01-01

PRENDATE ---------1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1982-12-01 1982-12-01 1982-12-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1982-09-15

MAJPROJ ------AD3100 AD3110 AD3110 AD3110 MA2100 MA2110 MA2110 MA2110 OP1000 OP2000 OP2010 OP2010 OP2010 MA2100

cl


PROJNO -----AD3100 AD3110 AD3111 AD3112 AD3113 IF1000 IF2000 MA2100 MA2110 MA2111 MA2112 MA2113 OP1000 OP1010 OP2000 OP2010 OP2011 OP2012 OP2013 PL2100

pr

Ex

pref




xix

Student Exercises

EMP_ACT Table PROJNO ACTNO EMPTIME EMSTDATE EMENDATE ------ ------ -------- ---------- ---------AD3100 10 0.50 1982-01-01 1982-07-01 AD3110 10 1.00 1982-01-01 1983-02-01 AD3111 60 1.00 1982-01-01 1982-03-15 AD3111 60 0.50 1982-03-15 1982-04-15 AD3111 70 0.50 1982-03-15 1982-10-15 AD3111 80 0.50 1982-04-15 1982-10-15 AD3111 180 1.00 1982-10-15 1983-01-01 AD3111 70 1.00 1982-02-15 1982-09-15 AD3111 80 1.00 1982-09-15 1983-01-01 AD3112 60 0.50 1982-02-01 1982-03-15 AD3112 60 1.00 1982-01-01 1982-02-01 AD3112 60 1.00 1983-01-01 1983-02-01 AD3112 60 0.50 1982-12-01 1983-01-01 AD3112 70 1.00 1982-03-15 1982-08-15 AD3112 70 0.50 1982-02-01 1982-03-15 AD3112 70 0.25 1982-08-15 1982-10-15 AD3112 80 0.25 1982-08-15 1982-10-15 AD3112 80 0.50 1982-10-15 1982-12-01 AD3112 180 0.50 1982-08-15 1983-01-01 AD3113 70 0.50 1982-06-15 1982-07-01 AD3113 70 1.00 1982-07-01 1983-02-01 AD3113 80 1.00 1982-01-01 1982-03-01 AD3113 80 0.50 1982-03-01 1982-04-15 AD3113 180 0.50 1982-03-01 1982-04-15 AD3113 180 0.50 1982-06-01 1982-07-01 AD3113 180 1.00 1982-04-15 1982-06-01 AD3113 60 0.25 1982-09-01 1982-10-15 AD3113 60 1.00 1982-04-01 1982-09-01 AD3113 60 0.50 1982-03-01 1982-04-01 AD3113 70 0.75 1982-09-01 1982-10-15 AD3113 70 1.00 1982-10-15 1983-02-01 AD3113 80 1.00 1982-01-01 1982-03-01 AD3113 80 0.50 1982-03-01 1982-04-01 IF1000 10 0.50 1982-06-01 1983-01-01 IF1000 90 1.00 1982-01-01 1982-10-01 IF1000 100 0.50 1982-10-01 1983-01-01 IF1000 90 0.50 1982-10-01 1983-01-01

pr

Ex

cl


EMPNO -----000010 000070 000230 000230 000230 000230 000230 000240 000240 000250 000250 000250 000250 000250 000250 000250 000250 000250 000250 000260 000260 000260 000260 000260 000260 000260 000270 000270 000270 000270 000270 000270 000270 000030 000130 000130 000140

xx





EMP_ACT Table (Cont) EMENDATE ---------1983-01-01 1982-03-01 1982-07-01 1982-07-01 1983-01-01 1982-11-01 1982-03-01 1983-02-01 1982-06-15 1983-02-01 1983-02-01 1982-07-15 1983-02-01 1983-06-01 1983-02-01 1982-10-01 1983-10-01 1983-02-01 1983-02-01 1982-06-15 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1983-02-01 1982-09-15 twice in the Sample database

cl


EMPNO PROJNO ACTNO EMPTIME EMSTDATE ------ ------ ------ -------- ---------000030 IF2000 10 0.50 1982-01-01 000140 IF2000 100 1.00 1982-01-01 000140 IF2000 100 0.50 1982-03-01 000140 IF2000 110 0.50 1982-03-01 000140 IF2000 110 0.50 1982-10-01 000010 MA2100 10 0.50 1982-01-01 000110 MA2100 20 1.00 1982-01-01 000010 MA2110 10 1.00 1982-01-01 000200 MA2111 50 1.00 1982-01-01 000200 MA2111 60 1.00 1982-06-15 000220 MA2111 40 1.00 1982-01-01 000150 MA2112 60 1.00 1982-01-01 000150 MA2112 180 1.00 1982-07-15 000170 MA2112 60 1.00 1982-01-01 000170 MA2112 70 1.00 1982-06-01 000190 MA2112 70 1.00 1982-02-01 000190 MA2112 80 1.00 1982-10-01 000160 MA2113 60 1.00 1982-07-15 000170 MA2113 80 1.00 1982-01-01 000180 MA2113 70 1.00 1982-04-01 000210 MA2113 80 0.50 1982-10-01 000210 MA2113 180 0.50 1982-10-01 000050 OP1000 10 0.25 1982-01-01 000090 OP1010 10 1.00 1982-01-01 000280 OP1010 130 1.00 1982-01-01 000290 OP1010 130 1.00 1982-01-01 000300 OP1010 130 1.00 1982-01-01 000310 OP1010 130 1.00 1982-01-01 000050 OP2010 10 0.75 1982-01-01 000100 OP2010 10 1.00 1982-01-01 000320 OP2011 140 0.75 1982-01-01 000320 OP2011 150 0.25 1982-01-01 000330 OP2012 140 0.25 1982-01-01 000330 OP2012 160 0.75 1982-01-01 000340 OP2013 140 0.50 1982-01-01 000340 OP2013 170 0.50 1982-01-01 000020 PL2100 30 1.00 1982-01-01 The last row for employee 000020 has been added for DB2 UDB for Windows.

pr

Ex

pref




xxi

Student Exercises

CARS Table The CARS table is to use in unit 7 (recursive SQL). ASSEMBLY ------------------------HONDA ACCORD HONDA ACCORD ENGINE COMPARTMENT ENGINE COMPARTMENT PASSENGER COMPARTMENT PASSENGER COMPARTMENT PASSENGER COMPARTMENT ENGINE ENGINE ENGINE ENGINE SEAT GROUP SEAT GROUP DASHBOARD DASHBOARD DASHBOARD DASHBOARD TRUNK STEREO SYSTEM STEREO SYSTEM GAUGE CLUSTER GAUGE CLUSTER CONTROL CLUSTER CONTROL CLUSTER CONTROL CLUSTER FRONT SEAT ASSEMBLY FRONT SEAT ASSEMBLY REAR SEAT ASSEMBLY REAR SEAT ASSEMBLY REAR SEAT ASSEMBLY

COMPONENT QUANTITY ------------------------- -------ENGINE COMPARTMENT 1 PASSENGER COMPARTMENT 1 ENGINE 1 IGNITION SYSTEM 1 SEAT GROUP 3 DASHBOARD 1 TRUNK 1 CAMSHAFT 1 CRANKSHAFT 1 PISTON 4 CONNECTING ROD 4 FRONT SEAT ASSEMBLY 2 REAR SEAT ASSEMBLY 1 VENT 4 STEREO SYSTEM 1 GAUGE CLUSTER 1 CONTROL CLUSTER 1 TOOL KIT 1 RADIO 1 CD PLAYER 1 SPEEDOMETER 1 ODOMETER 1 FOG LAMP CONTROL 1 FOUR WAY FLASHER CONTROL 1 TURN SIGNAL CONTROL 1 FRONT SEAT 1 LAP/SHOULDER BELT 1 REAR SEAT 1 LAP/SHOULDER BELT 3 SHOULDER BELT 3

pr

Ex

cl


LEVEL -----1 1 2 2 2 2 2 3 3 3 3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4 4 4 4

xxii DB2 SQL Workshop for Experienced Users




TESTEMP Table The TESTEMP table is to use in unit 2 (views). WORKDEPT -------C01 A00 B01 C01 E01

HIREDATE ---------25.06.1998 01.01.1965 10.10.1973 05.04.1975 17.08.1949

SALARY BONUS -------- ------25000.00 .00 52750.00 1000.00 41250.00 800.00 38250.00 800.00 40175.00 800.00

cl


EMPNO LASTNAME ---------------000111 SMITH 000010 HAAS 000020 THOMPSON 000030 KWAN 000050 GEYER

pr

Ex

pref




xxiii

Student Exercises

Content of VIEWS The listed views have to be used for the exercises in unit 3. Data types are listed in the table description.


VEMPLOYEE View EMPNO -----000010 000030 000120 000130 000140

LASTNAME --------HAAS KWAN O'CONNELL QUINTANA NICHOLLS

SALARY -------52750.00 38250.00 29250.00 23800.00 28420.00

WORKDEPT -------A00 C01 A00 C01 C01

VDEPARTMENT View DEPTNO -----A00 C01 D01

DEPTNAME ---------------------------SPIFFY COMPUTER SERVICE DIV. INFORMATION CENTER DEVELOPMENT CENTER

MGRNO -----000010 000030 ------

VPROJECT View

PROJNAME -------------ADMIN SERVICES QUERY SERVICES USER EDUCATION

DEPTNO -----D01 C01 C01

RESPEMP ------000010 000030 000030

pr

Ex

cl

PROJNO -----AD3100 IF1000 IF2000

xxiv DB2 SQL Workshop for Experienced Users




Uempty

Exercise 1. SQL Basics Review, CUBE, and ROLLUP What this exercise is about


This exercise provides an opportunity to review some basic topics.

What you should be able to do

At the end of the lab, you should be able to:

• Code SELECT statements with multiple conditions

• Use the GROUP BY clause • Use column functions

• Use the new super groups features, CUBE, ROLLUP, and RANK

Introduction

See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.

Requirements

pr

Ex

cl

• Student handout • SQL Reference


Exercise 1. SQL Basics Review, CUBE, and ROLLUP


1-1

Student Exercises

Problem list Ron is the SQL specialist in the department. Joe is a colleague of Ron and does not have SQL knowledge. Joe asked Ron for help to solve the following problems.


Note See the data model at the beginning of this Exercise Guide for descriptions of the tables used in the labs and for their column names.

Problem 1

Joe's manager wants information about employees which match the following criteria: • Their yearly salary is between 22000 and 24000. • They work in departments D11 or D21.

List the employee number, last name, yearly salary, and department number of the appropriate employees.

Problem 2

Now, Joe's manager wants information about the yearly salary. He wants to know the minimum, the maximum, and average yearly salary of all employees with an education level of 16. He also wants to know how many employees have this education level.

Problem 3

Ex

cl

Joe's manager is interested in some additional salary information. This time, he wants information for every department that appears in the EMPLOYEE table, provided that the department has more than five employees. The report needs to show the department number, the minimum, maximum, and average yearly salary, and the number of employees who work in the department.

Problem 4

pr

Joe's manager wants information about employees grouped by department, grouped by sex and in addition by the combination of department and sex. List only those who work in a department which start with the letter D. Refer to the expected result for this query. Which of CUBE, ROLLUP and GROUPING SETS would give this result?

1-2





Uempty

Note


The SQL syntax needed to solve this problem is not yet available on the current version of DB2 for z/OS. This problem can be solved with SQL only on DB2 for Linux, Unix, and Windows.

Problem 5

Joe's manager wants to rank employees by the bonus they earn, only for departments A00, B01 and C01. List in department order: the employee last name, department, bonus and ranking by bonus, highest first. This means to order the rows first by department, and then to order each department's rows by highest bonus value (not the ranking number). Hint

Use RANK() OVER (ORDER BY BONUS DESC)

Now compare the results for the same query using DENSE_RANK and ROW_NUMBER. In other words, modify this query and execute it using the DENSE_RANK and ROW_NUMBER functions.

pr

Ex

cl

End of Problem List




1-3

Student Exercises

Problem List with Expected Results Problem 1 Joe's manager wants information about employees which match the following criteria:


• Their yearly salary is between 22000 and 24000. • They work in departments D11 or D21.

List the employee number, last name, yearly salary, and department number of the appropriate employees.

Result

EMPNO -----000160 000230

LASTNAME --------PIANKA JEFFERSON

SALARY -------22250.00 22180.00

WORKDEPT --------D11 D21

Problem 2

Now, Joe's manager wants information about the yearly salary. He wants to know the minimum, the maximum, and average yearly salary of all employees with an education level of 16. He also wants to know how many employees have this education level.

Result

MIN -------17250.00

MAX -------40175.00

AVG -------------26777.91666666

COUNT ----12

When returning the result of a calculation, the number of digits in the fractional part (scale) may differ for DB2 on different platforms.

cl

Problem 3

Ex

Joe's manager is interested in some additional salary information. This time, he wants information for every department that appears in the EMPLOYEE table, provided that the department has more than five employees. The report needs to show the department number, the minimum, maximum, and average yearly salary, and the number of employees who work in the department.

pr

Result

WORKDEPT -------D11 D21

1-4

MIN -------18270.00 17250.00

MAX -------32250.00 36170.00


AVG -------------24677.77777777 25153.33333333

COUNT ----9 6




Uempty

Problem 4 Joe's manager wants information about employees grouped by department, grouped by sex and in addition by the combination of department and sex. List only those who work in a department which start with the letter D.


Refer to the expected result for this query. Which of CUBE, ROLLUP and GROUPING SETS would give this result? Note

The SQL syntax needed to solve this problem is not yet available on the current version of DB2 for z/OS. This problem can be solved with SQL only on DB2 for Linux, Unix, and Windows.

Result

SEX MIN MAX SUM --- ----------- ----------- ---------------------F 17250.00 36170.00 154230.00 M 18270.00 32250.00 218790.00 17250.00 36170.00 373020.00 18270.00 32250.00 222100.00 17250.00 36170.00 150920.00 F 21340.00 29840.00 73430.00 M 18270.00 32250.00 148670.00 F 17250.00 36170.00 80800.00 M 19180.00 28760.00 70120.00

pr

Ex

cl

WORKDEPT -------D11 D21 D11 D11 D21 D21




1-5

Student Exercises

Problem 5 Joe's manager wants to rank employees by the bonus they earn, only for departments A00, B01 and C01. List in department order: the employee last name, department, bonus and ranking by bonus, highest first.


Hint Use RANK() OVER (ORDER BY BONUS DESC)

Result

With RANK

LASTNAME --------------O'CONNELL LUCCHESSI HAAS THOMPSON QUINTANA NICHOLLS KWAN

WORKDEPT BONUS BONUS_RANKING -------- ----------- -------------------A00 600.00 5 A00 900.00 2 A00 1000.00 1 B01 800.00 3 C01 500.00 7 C01 600.00 5 C01 800.00 3

Now compare the results for the same query using DENSE_RANK and ROW_NUMBER. In other words, modify this query and execute it using the DENSE_RANK and ROW_NUMBER functions.

Result

With DENSE_RANK

WORKDEPT BONUS BONUS_RANKING -------- ----------- -------------------A00 600.00 4 A00 900.00 2 A00 1000.00 1 B01 800.00 3 C01 500.00 5 C01 600.00 4 C01 800.00 3

pr

Ex

cl


1-6





Uempty

Result With ROW_NUMBER WORKDEPT BONUS BONUS_RANKING -------- ----------- -------------------A00 600.00 5 A00 900.00 2 A00 1000.00 1 B01 800.00 4 C01 500.00 7 C01 600.00 6 C01 800.00 3



pr

Ex

cl

End of Problem List with Expected Results




1-7

Student Exercises

Solution Problem 1


SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE WHERE WORKDEPT IN ('D11', 'D21') AND SALARY BETWEEN 22000 AND 24000;

Problem 2

SELECT MIN(SALARY) AS MIN, MAX(SALARY) AS MAX, AVG(SALARY) AS AVG, COUNT(*) AS COUNT FROM EMPLOYEE WHERE EDLEVEL = 16;

Problem 3

SELECT WORKDEPT, MIN(SALARY) AS MIN, MAX(SALARY) AS MAX, AVG(SALARY) AS AVG, COUNT(*) AS COUNT FROM EMPLOYEE GROUP BY WORKDEPT HAVING COUNT(*) > 5;

Problem 4

SELECT WORKDEPT,SEX,MIN(SALARY) AS MIN,MAX(SALARY) AS MAX, SUM(SALARY) AS SUM FROM EMPLOYEE WHERE WORKDEPT LIKE 'D%' GROUP BY CUBE (WORKDEPT,SEX);

Ex

cl

As you can see, CUBE would give the desired result. ROLLUP would give fewer rows. GROUPING SETS is more flexible, so it can be used to give a similar result to CUBE: GROUP BY GROUPING SETS((WORKDEPT, SEX), WORKDEPT, SEX, () ) but the result would appear in a slightly different order.

Problem 5 With RANK

pr

SELECT LASTNAME, WORKDEPT, BONUS AS BONUS, RANK() OVER (ORDER BY BONUS DESC) AS BONUS_RANKING FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B01', 'C01') ORDER BY WORKDEPT, BONUS_RANKING DESC;

1-8





Uempty

With DENSE_RANK


SELECT LASTNAME, WORKDEPT, BONUS AS BONUS, DENSE_RANK() OVER (ORDER BY BONUS DESC) AS BONUS_RANKING FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B01', 'C01') ORDER BY WORKDEPT, BONUS_RANKING DESC; With ROW_NUMBER

SELECT LASTNAME, WORKDEPT, BONUS AS BONUS, ROW_NUMBER() OVER (ORDER BY BONUS DESC) AS BONUS_RANKING FROM EMPLOYEE WHERE WORKDEPT IN ('A00', 'B01', 'C01') ORDER BY WORKDEPT, BONUS_RANKING DESC;

pr

Ex

cl

End of solutions




1-9

pr

Ex

cl


Student Exercises

1-10 DB2 SQL Workshop for Experienced Users




Uempty

Exercise 2. Create Objects What this exercise is about


This exercise gives you an opportunity to implement a small data model.


At the end of the lab, you should be able to: • Create tables with check constraints • Create index

• Use referential integrity • Create triggers • Create views

• Understand the effect of CHECK OPTION in a view • Appreciate some of the advantages of using views

Introduction


cl

z/OS Users: Be sure to add the correct IN clause to your CREATE TABLE statements. Ask the instructor if you forgot which IN clause to use.


pr

Ex

Requirements


Exercise 2. Create Objects


2-1

Student Exercises

Problem list Harvey needs your help to create a database for his test environment. He has accurately defined the requirements, but he does not know the SQL syntax. You should help him to do the subsequent steps.


Problem 1 Create the table EMPDEPT with these columns: • • • • •

EMPNO LASTNAME SALARY DEPTNO DEP_NAME

The data types and null characteristics for these columns should be the same as for the columns with the same names in the EMPLOYEE and DEPARTMENT tables. These tables are described in our course data model.

The definition of the table should limit the values for the yearly salary (SALARY) column to ensure that: No employee in any department may have a yearly salary that exceeds 50000. The values in the EMPNO column should be unique. The uniqueness should be guaranteed via a unique index. Create the table HIGH_SALARY_RAISE with the following columns: • EMPNO • PREV_SAL • NEW_SAL

pr

Ex

cl

The data type for column EMPNO is CHAR(6). The other columns should be defined as DECIMAL(9,2). All columns in this table should be defined with NOT NULL.

2-2





Uempty

Problem 2 After creating the table, you should add referential constraints.


Your EMPLOYEE and DEPARTMENT tables should have primary keys, on the EMPNO and DEPTNO columns respectively. Check to make sure they each have a primary key by adding them; if you get a message saying they are already there, then they have already been set up for you, otherwise they will now have been created successfully. You should now add the primary key for the EMPDEPT table, on the EMPNO column, and implement the following rules:

• The EMPDEPT table should only allow values in column EMPNO which exist in the EMPLOYEE table. If an employee is deleted from the EMPLOYEE table, the corresponding row in the EMPDEPT table should also be immediately deleted.

• The EMPDEPT table should only allow values in column DEPTNO which exist in the DEPARTMENT table. It should not be possible to delete a department from the DEPARTMENT table as long as a corresponding DEPTNO exists in the EMPDEPT table.

Problem 3

Klaus must update the yearly salaries for the employees of the EMPDEPT table. If the new value for a salary exceeds the previous value by 10 percent or more, Harvey wants to insert a row into the HIGH_SALARY_RAISE table. The values in this row should be the employee number, the previous salary, and the new salary. Create something in DB2 that will ensure that a row is inserted into the HIGH_SALARY_RAISE table whenever an employee of the EMPDEPT table gets a raise of 10 percent or more.

Problem 4

cl

Now, you should insert data in the EMPDEPT table. Use the combined contents of tables EMPLOYEE and DEPARTMENT as the source for your data.

Ex

Did your insert work?

If not, correct your INSERT statement so that you get only rows which satisfy the check constraints on the EMPDEPT table.

pr

Problem 5

Harvey wants to test the table-level check constraint on the EMPDEPT table. Ethel Schneider works in the operations department. Her department number is E11, and her employee number is 000280. Try to set her yearly salary to the value of 51000. Does it work?




2-3

Student Exercises

Problem 6 Harvey wants to see if the trigger works.


Elizabeth Pianka, whose employee number is 000160, has been given a raise. Set her yearly salary to 25000. Inspect the HIGH_SALARY_RAISE table to see if the trigger worked.

Problem 7

Create a view named VEMPPAY that contains one row for each employee in the company. Each row should contain employee number, last name, department number, and total earnings for the corresponding employee. Total earnings means salary plus bonus plus commission for the employee. Then, determine the average of the earnings for the departments by using the view you just created.

Problem 8

Create a view named VEMP1 containing employee number, last name, yearly salary, and work department based on your TESTEMP table. Only employees with a yearly salary less than 50000 should be displayed when you use the view. Note

It is very important that you base this view on the TESTEMP table that was created for you or you created with the CRTAB member. Otherwise, you may get incorrect results in a later lab.

Display the rows in the view in employee number sequence.

cl

Our employee with the employee number 000020 (Thompson) changed jobs and will get a new salary of 51000. Update the data for employee number 000020 using the view VEMP1. Display the view again, arranging the rows in employee number sequence.

Ex

What happened? Is Thompson still in the view?

Query the row of employee number 000020 in your TESTEMP table.

pr

Did the update work?

2-4





Uempty

Problem 9 Reset the salary of employee Thompson (empno = '000020') to the value of 41250. Create a view named VEMP2 which has the same definition as in problem 8, but add a CHECK OPTION. Again, base the view on your TESTEMP table. Display the rows in the view in employee number sequence.


Our employee with the employee number 000050 (Geyer) also changed jobs and will have a new salary of 55000. Update the data for employee number 000050 using the view VEMP2. Does the UPDATE statement work? Display the view again, arranging the rows in employee number sequence. Query Geyer's row in your TESTEMP table. Did the data in the base table change?

pr

Ex

cl

End of Problem list




2-5

Student Exercises

Problem List with Expected Results Problem 1 Create the table EMPDEPT with these columns: EMPNO LASTNAME SALARY DEPTNO DEP_NAME


• • • • •

The data types and null characteristics for these columns should be the same as for the columns with the same names in the EMPLOYEE and DEPARTMENT tables. These tables are described in our course data model.

The definition of the table should limit the values for the yearly salary (SALARY) column to ensure that: No employee in any department may have a yearly salary that exceeds 50000. The values in the EMPNO column should be unique. The uniqueness should be guaranteed via a unique index. Create the table HIGH_SALARY_RAISE with the following columns: • EMPNO • PREV_SAL • NEW_SAL

The data type for column EMPNO is CHAR(6). The other columns should be defined as DECIMAL(9,2). All columns in this table should be defined with NOT NULL.

Result

pr

Ex

cl

The CREATE statements for the EMPDEPT table, the unique index on the EMPDEPT table, and the HIGH_SALARY_RAISE table should all be successful.

2-6





Uempty

Problem 2 After creating the table, you should add referential constraints.


Your EMPLOYEE and DEPARTMENT tables should have primary keys, on the EMPNO and DEPTNO columns respectively. Check to make sure they each have a primary key by adding them; if you get a message saying they are already there, then they have already been set up for you, otherwise they will now have been created successfully. You should now add the primary key for the EMPDEPT table, on the EMPNO column, and implement the following rules:

• The EMPDEPT table should only allow values in column EMPNO which exist in the EMPLOYEE table. If an employee is deleted from the EMPLOYEE table, the corresponding row in the EMPDEPT table should also be immediately deleted.

• The EMPDEPT table should only allow values in column DEPTNO which exist in the DEPARTMENT table. It should not be possible to delete a department from the DEPARTMENT table as long as a corresponding DEPTNO exists in the EMPDEPT table.

Result

The ALTER statements which add the primary keys and the foreign keys should execute successfully.

Problem 3

cl

Klaus must update the yearly salaries for the employees of the EMPDEPT table. If the new value for a salary exceeds the previous value by 10 percent or more, Harvey wants to insert a row into the HIGH_SALARY_RAISE table. The values in this row should be the employee number, the previous salary, and the new salary. Create something in DB2 that will ensure that a row is inserted into the HIGH_SALARY_RAISE table whenever an employee of the EMPDEPT table gets a raise of 10 percent or more.

Result

pr

Ex

The CREATE TRIGGER statement should execute successfully.




2-7

Student Exercises

Problem 4 Now, you should insert data in the EMPDEPT table. Use the combined contents of tables EMPLOYEE and DEPARTMENT as the source for your data. Did your insert work?


If not, correct your INSERT statement so that you get only rows which satisfy the check constraints on the EMPDEPT table.

Result

The initial INSERT should fail due to a check constraint violation. The INSERT should work after it has been modified so that none of the rows violates a check constraint.

Problem 5

Harvey wants to test the table-level check constraint on the EMPDEPT table.

Ethel Schneider works in the operations department. Her department number is E11, and her employee number is 000280. Try to set her yearly salary to the value of 51000. Does it work?

Result

The UPDATE for Ethel Schneider will fail because it violates a check constraint.

Problem 6

Harvey wants to see if the trigger works.

Elizabeth Pianka, whose employee number is 000160, has been given a raise. Set her yearly salary to 25000. Inspect the HIGH_SALARY_RAISE table to see if the trigger worked.

Result

Ex

cl

The UPDATE for Elizabeth Pianka should succeed and should fire the trigger. You can verify that the trigger has fired by examining the contents of the HIGH_SALARY_RAISE table. You should see a row there for Elizabeth Pianka (EMPNO = 000160). NEW_SAL -------25000.00

pr

EMPNO PREV_SAL ------ -------000160 22250.00

2-8





Uempty

Problem 7


Create a view named VEMPPAY that contains one row for each employee in the company. Each row should contain employee number, last name, department number, and total earnings for the corresponding employee. Total earnings means salary plus bonus plus commission for the employee. Then, determine the average of the earnings for the departments by using the view you just created. Result

pr

Ex

cl

WORKDEPT AVG_TOTAL_EARNINGS -------- -----------------A00 47093.333333 B01 45350.000000 C01 33202.666666 D11 27151.666666 D21 27648.666666 E01 44189.000000 E11 23097.800000 E21 26208.750000




2-9

Student Exercises

Problem 8 Create a view named VEMP1 containing employee number, last name, yearly salary, and work department based on your TESTEMP table. Only employees with a yearly salary less than 50000 should be displayed when you use the view.


Note It is very important that you base this view on the TESTEMP table that was created for you or you created with the CRTAB member. Otherwise, you may get incorrect results in a later lab. Display the rows in the view in employee number sequence.

Our employee with the employee number 000020 (Thompson) changed jobs and will get a new salary of 51000. Update the data for employee number 000020 using the view VEMP1. Display the view again, arranging the rows in employee number sequence.

What happened? Is Thompson still in the view?

Query the row of employee number 000020 in your TESTEMP table. Did the update work?

Result

You should see the following when you first display the view contents: LASTNAME -------THOMPSON KWAN GEYER SMITH

cl

EMPNO -----000020 000030 000050 000111

SALARY -------41250.00 38250.00 40175.00 25000.00

WORKDEPT -------B01 C01 E01 C01

You should see the following when you display the view contents after the update: LASTNAME -------KWAN GEYER SMITH

pr

Ex

EMPNO -----000030 000050 000111

SALARY -------38250.00 40175.00 25000.00

WORKDEPT -------C01 E01 C01

You should see the following when you query the TESTEMP table: EMPNO LASTNAME ------ -------000020 THOMPSON

SALARY WORKDEPT -------- -------51000.00 B01

The update worked. 2-10 DB2 SQL Workshop for Experienced Users




Uempty

Problem 9 Reset the salary of employee Thompson (empno = '000020') to the value of 41250. Create a view named VEMP2 which has the same definition as in Problem 8, but add a CHECK OPTION. Again, base the view on your TESTEMP table. Display the rows in the view in employee number sequence.


Our employee with the employee number 000050 (Geyer) also changed also jobs and will have a new salary of 55000. Update the data for employee number 000050 using the view VEMP2. Does the UPDATE statement work? Display the view again, arranging the rows in employee number sequence.

Query Geyer's row in your TESTEMP table. Did the data in the base table change?

Result

You should see the following when you first display the view contents:

EMPNO -----000020 000030 000050 000111

LASTNAME -------THOMPSON KWAN GEYER SMITH

SALARY -------41250.00 38250.00 40175.00 25000.00

WORKDEPT -------B01 C01 E01 C01

The SQLCODE (-161 in z/OS, SQL0161N in Linux, Unix and Windows) indicates that the UPDATE did not work. The desired value for Geyer's SALARY, that is, 55000, was outside of the scope of the view (SALARY < 50000). The CHECK OPTION prevented the UPDATE. You should see the following when displaying the view after the update: LASTNAME -------THOMPSON KWAN GEYER SMITH

SALARY -------41250.00 38250.00 40175.00 25000.00

WORKDEPT -------B01 C01 E01 C01

Ex

cl

EMPNO -----000020 000030 000050 000111

You should see the following when you query the TESTEMP table after the update:

pr

EMPNO LASTNAME SALARY WORKDEPT ------ -------- -------- -------000050 GEYER 40175.00 E01

The data in the base table did not change. Remember that the data seen through the view is actually the base table data so this should not be a surprise.

End of Problem list with Expected Results




2-11

Student Exercises

Solutions Problem 1 NOT NULL, NOT NULL, CHECK(SALARY <= 50000), NOT NULL, NOT NULL);


CREATE TABLE EMPDEPT (EMPNO CHAR(6) LASTNAME VARCHAR(15) SALARY DECIMAL(9,2) DEPTNO CHAR(3) DEP_NAME VARCHAR(36) --IN clause (z/OS users only)

CREATE UNIQUE INDEX EMPIND ON EMPDEPT(EMPNO); CREATE TABLE HIGH_SALARY_RAISE (EMPNO CHAR(6) NOT NULL, PREV_SAL DECIMAL(9,2) NOT NULL, NEW_SAL DECIMAL(9,2) NOT NULL); --IN clause (z/OS users only)

Problem 2

ALTER TABLE EMPDEPT ADD PRIMARY KEY (EMPNO);

ALTER TABLE EMPDEPT ADD FOREIGN KEY (EMPNO) REFERENCES EMPLOYEE ON DELETE CASCADE;

pr

Ex

cl

ALTER TABLE EMPDEPT ADD FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT ON DELETE RESTRICT;





Uempty

Problem 3


CREATE TRIGGER HIGH_SAL AFTER UPDATE OF SALARY ON EMPDEPT REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL WHEN (N.SALARY >= O.SALARY * 1.1) INSERT INTO HIGH_SALARY_RAISE VALUES (N.EMPNO, O.SALARY, N.SALARY);

Remember, OLD identifies the value that is in column SALARY before the update and NEW the value after the update.

Problem 4

The correct version of the INSERT is as follows:

INSERT INTO EMPDEPT SELECT E.EMPNO, E.LASTNAME, E.SALARY, D.DEPTNO, D.DEPTNAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.WORKDEPT = D.DEPTNO AND E.SALARY <= 50000;

The AND part of the WHERE condition satisfies the check constraint on the SALARY column of the EMPDEPT table (SALARY <= 50000). HAAS has a salary of over 50000, so you need to exclude that specifically in the WHERE clause in order to satisfy the check constraint.

Problem 5

UPDATE EMPDEPT SET SALARY = 51000 WHERE EMPNO = '000280';

cl

The update fails because of the check constraint MAXSALARY again only allowing salaries of under 50000 to be added.

Ex

UPDATE EMPDEPT SET SALARY = 27999 WHERE EMPNO = '000280'

pr

This statement works.




2-13

Student Exercises

Problem 6 UPDATE EMPDEPT SET SALARY = 25000 WHERE EMPNO = '000160';


SELECT * FROM HIGH_SALARY_RAISE;

Problem 7

Create the view:

CREATE VIEW VEMPPAY (EMPNO, LASTNAME, WORKDEPT, TOTAL_EARNINGS) AS SELECT EMPNO, LASTNAME, WORKDEPT, SALARY + BONUS + COMM FROM EMPLOYEE; Use the view to determine the average total earnings for each department: SELECT WORKDEPT, AVG(TOTAL_EARNINGS) AS AVG_TOTAL_EARNINGS FROM VEMPPAY GROUP BY WORKDEPT;

Problem 8

Create the view:

CREATE VIEW VEMP1 AS SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM TESTEMP WHERE SALARY < 50000; Determine which employees appear in the view: SELECT * FROM VEMP1 ORDER BY EMPNO;

cl

Update the data using the view:

Ex

UPDATE VEMP1 SET SALARY = 51000 WHERE EMPNO = '000020'; Display the view again.

pr

SELECT * FROM VEMP1 ORDER BY EMPNO;

See if the update worked: SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM TESTEMP WHERE EMPNO = '000020';





Uempty

Problem 9 Reset the salary for Thompson: UPDATE TESTEMP SET SALARY = 41250 WHERE EMPNO = '000020';


Create the view:

CREATE VIEW VEMP2 AS SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM TESTEMP WHERE SALARY < 50000 WITH CHECK OPTION; Determine which employees appear in the view: SELECT * FROM VEMP2 ORDER BY EMPNO;

Update the data using the view: UPDATE VEMP2 SET SALARY = 55000 WHERE EMPNO = '000050'; Display the view again. SELECT * FROM VEMP2 ORDER BY EMPNO;

See if the update worked:

cl

SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM TESTEMP WHERE EMPNO = '000050';

pr

Ex

End of solutions




2-15

pr

Ex

cl


Student Exercises





Uempty

Exercise 3. Joins What this exercise is about


This exercise will give you the opportunity to code inner and outer joins.


At the end of the lab, you should be able to: • Code Inner and Outer Joins

Introduction


Note

Use the views VDEPARTMENT, VEMPLOYEE, and VPROJECT for problems 1 through 6.

Requirements

pr

Ex

cl



Exercise 3. Joins

3-1

Student Exercises

Problem list Problem 1


Display all employees who work in the INFORMATION CENTER department. Show department number, employee number and last name for all employees in that department. The list should be ordered by employee number.  Use the old SQL syntax that puts the join condition in the WHERE clause. Note


Problem 2

Solve problem 1 again using the newer SQL syntax that places the join condition in the ON clause.

Problem 3

Bill needs a list of those employees whose departments are involved in projects. The list needs to show employee number, last name, department number, and project name. The list should be ordered by project names within employee numbers.

Problem 4

cl

Now Bill wants to see all employees, whether or not their departments are involved a project. The list needs to show the employee number, last name, department number, and project name. If the department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within employee number.

Ex

Problem 5

pr

Now Bill wants to see all projects, including those assigned to departments without employees. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number, employee number and last name. The list should be ordered by employee number within project name.

3-2





Uempty

Problem 6


Bill wants to see all projects and all employees in one report. Projects not assigned to departments having employees should also be listed as well as employees who work in departments which are not involved in projects. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number, employee number and last name. If the department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within last name.

Problem 7

Which employees in department A00 were hired before their manager? Please note that you should use the real tables (rather than the views) for this problem in the exercise. List department number, the manager's last name, the employee's last name, and the hiring dates of both the manager and the employee. Order the list by the employee's last name.

Problem 8

List the department number and department name of the departments which do not have any employees assigned to them. You may use any of the three "anti-join" methods discussed in the Unit 3 lecture.

pr

Ex

cl

End of problem list


Exercise 3. Joins

3-3

Student Exercises

Problem list with Expected Results Problem 1


Display all employees who work in the INFORMATION CENTER department. Show department number, employee number and last name for all employees in that department. The list should be ordered by employee number.  Use the "old" SQL syntax that puts the join condition in the WHERE clause. Note


Result

WORKDEPT -------C01 C01 C01

EMPNO -----000030 000130 000140

LASTNAME ---------KWAN QUINTANA NICHOLLS

Problem 2

Solve problem 1 again using the newer SQL syntax that places the join condition in the ON clause.

Result

EMPNO -----000030 000130 000140

LASTNAME ---------KWAN QUINTANA NICHOLLS

pr

Ex

cl

WORKDEPT -------C01 C01 C01

3-4





Uempty

Problem 3 Bill needs a list of those employees whose departments are involved in projects. The list needs to show employee number, last name, department number, and project name. The list should be ordered by project names within employee numbers.


Result EMPNO -----000030 000030 000130 000130 000140 000140

LASTNAME --------------KWAN KWAN QUINTANA QUINTANA NICHOLLS NICHOLLS

WORKDEPT -------C01 C01 C01 C01 C01 C01

PROJNAME -----------------------QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION

Problem 4

Now Bill wants to see all employees, whether or not their departments are involved a project. The list needs to show the employee number, last name, department number, and project name. If the department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within employee number.

Result

LASTNAME --------------HAAS KWAN KWAN O'CONNELL QUINTANA QUINTANA NICHOLLS NICHOLLS

WORKDEPT -------A00 C01 C01 A00 C01 C01 C01 C01

PROJNAME -----------------------QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION

pr

Ex

cl

EMPNO -----000010 000030 000030 000120 000130 000130 000140 000140


Exercise 3. Joins

3-5

Student Exercises

Problem 5


Now Bill wants to see all projects, including those assigned to departments without employees. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number, employee number and last name. The list should be ordered by employee number within project name.

Result

EMPNO -----000030 000130 000140 000030 000130 000140

LASTNAME --------------KWAN QUINTANA NICHOLLS KWAN QUINTANA NICHOLLS

WORKDEPT -------C01 C01 C01 C01 C01 C01

PROJNAME -----------------------ADMIN SERVICES QUERY SERVICES QUERY SERVICES QUERY SERVICES USER EDUCATION USER EDUCATION USER EDUCATION

Problem 6

Bill wants to see all projects and all employees in one report. Projects not assigned to departments having employees should also be listed as well as employees who work in departments which are not involved in projects. The list needs to show employee number, last name, department number, and project name. If a project is not assigned to a department having employees, NULLS should be displayed instead of the department number, employee number and last name. If the department of an employee is not involved in a project, display NULLs instead of the project name. The list should be ordered by project name within last name.

Result

LASTNAME --------------HAAS KWAN KWAN NICHOLLS NICHOLLS O'CONNELL QUINTANA QUINTANA -

pr

Ex

cl

EMPNO -----000010 000030 000030 000140 000140 000120 000130 000130 -

3-6

WORKDEPT -------A00 C01 C01 C01 C01 A00 C01 C01 -


PROJNAME -----------------------QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION ADMIN SERVICES




Uempty

Problem 7 Which employees in department A00 were hired before their manager? Please note that you should use the real tables (rather than the views) for this problem in the exercise.


List department number, the manager's last name, the employee's last name, and the hiring dates of both the manager and the employee. Order the list by the employee's last name.

Result

DEPTNO MANAGER EMPLOYEE M_HIREDATE E_HIREDATE ---------+---------+---------+---------+---------+------A00 HAAS LUCCHESI 1965-01-01 1958-05-16 A00 HAAS O'CONNELL 1965-01-01 1963-12-05

Problem 8

List the department number and department name of the departments which do not have any employees assigned to them. You may use any of the three "anti-join" methods discussed in the Unit 3 lecture.

Result

DEPTNO DEPTNAME ---------+---------+---------+---------+-----D01 DEVELOPMENT CENTER

pr

Ex

cl



Exercise 3. Joins

3-7

Student Exercises

Solutions Problem 1


SELECT WORKDEPT, EMPNO, LASTNAME FROM VEMPLOYEE, VDEPARTMENT WHERE WORKDEPT = DEPTNO AND DEPTNAME = 'INFORMATION CENTER' ORDER BY EMPNO;

Problem 2

SELECT WORKDEPT, EMPNO, LASTNAME FROM VEMPLOYEE INNER JOIN VDEPARTMENT ON WORKDEPT = DEPTNO WHERE DEPTNAME = 'INFORMATION CENTER' ORDER BY EMPNO;

Problem 3

SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE INNER JOIN VPROJECT ON WORKDEPT = DEPTNO ORDER BY EMPNO, PROJNAME;

Problem 4

SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE LEFT OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO ORDER BY EMPNO;

cl

Problem 5

pr

Ex

SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE RIGHT OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO ORDER BY PROJNAME;

3-8





Uempty

Problem 6 SELECT EMPNO, LASTNAME, WORKDEPT, PROJNAME FROM VEMPLOYEE FULL OUTER JOIN VPROJECT ON WORKDEPT = DEPTNO ORDER BY LASTNAME, PROJNAME;


Problem 7

SELECT D.DEPTNO, M.LASTNAME AS MANAGER, E.LASTNAME AS EMPLOYEE, M.HIREDATE AS M_HIREDATE, E.HIREDATE AS E_HIREDATE FROM EMPLOYEE M, EMPLOYEE E, DEPARTMENT D WHERE D.MGRNO = M.EMPNO AND E.WORKDEPT = D.DEPTNO AND M.HIREDATE > E.HIREDATE AND E.WORKDEPT = 'A00' ORDER BY EMPLOYEE;

OR

SELECT D.DEPTNO, M.LASTNAME AS MANAGER, E.LASTNAME AS EMPLOYEE, M.HIREDATE AS M_HIREDATE, E.HIREDATE AS E_HIREDATE FROM EMPLOYEE AS E JOIN DEPARTMENT AS D ON E.WORKDEPT = D.DEPTNO JOIN EMPLOYEE AS M ON M.EMPNO = D.MGRNO WHERE M.HIREDATE > E.HIREDATE AND E.WORKDEPT = 'A00' ORDER BY E.LASTNAME;

cl

Problem 8

Ex

SELECT D.DEPTNO, D.DEPTNAME FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO WHERE E.WORKDEPT IS NULL;

pr

Alternate Solution #1

SELECT DEPTNO, DEPTNAME FROM EMPLOYEE E RIGHT OUTER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO EXCEPT SELECT DEPTNO, DEPTNAME © Copyright IBM Corp. 2007, 2013 Course materials may not be reproduced in whole or in part without the prior written permission of IBM.

Exercise 3. Joins

3-9

Student Exercises

FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.WORKDEPT = D.DEPTNO ; Alternate Solution #2


SELECT DEPTNO, DEPTNAME FROM DEPARTMENT WHERE DEPTNO NOT IN (SELECT WORKDEPT FROM EMPLOYEE);

pr

Ex

cl

End of solutions





Uempty

Exercise 4. CASE and CAST What this exercise is about


This exercise give you the possibility to work with CASE and CAST expressions.



• Use CASE expressions in the SELECT list

• Use CASE expressions in the WHERE clause • Use CAST to convert between data types

Introduction


Requirements

pr

Ex

cl



Exercise 4. CASE and CAST


4-1

Student Exercises

Problem list Problem 1 For all employees in department D11, display their employee number, first name, last name and text specifying if the employee's salary is low, high or normal.


Problem 2

For all employees in department D11, display their last name, salary and the absolute difference (in other words, display the difference as a positive number, whether or not the salary is greater than or less than 30,000) between their salary and 30000. Sort the list in order by the difference with the lowest difference first.

Problem 3

List the employees who have a commission greater than 8 percent of their salary. Protect from division by 0 in case someone has 0 salary. List the last name and use CAST to list percentage with three decimals.

pr

Ex

cl

End of problem list

4-2





Uempty

Problem list with Expected Results Problem 1 For all employees in department D11, display their employee number, first name, last name and text specifying if the employee's salary is low, high or normal. INCOME -------25000 LOW 25000 and 30000 NORMAL 30000 HIGH


SALARY -------less than between higher than

Result

EMPNO -----000060 000150 000160 000170 000180 000190 000200 000210 000220

FIRSTNME -----------IRVING BRUCE ELIZABETH MASATOSHI MARILYN JAMES DAVID WILLIAM JENNIFER

LASTNAME --------------STERN ADAMSON PIANKA YOSHIMURA SCOUTTEN WALKER BROWN JONES LUTZ

INCOME -----HIGH NORMAL LOW LOW LOW LOW NORMAL LOW NORMAL

Problem 2

For all employees in department D11, display their last name, salary and the absolute difference (in other words, display the difference as a positive number, whether or not the salary is greater than or less than 30,000) between their salary and 30000. Sort the list in order by the difference with the lowest difference first.

cl

Result

pr

Ex

LASTNAME SALARY DIFFERENCE --------------- ----------- -----------LUTZ 29840.00 160.00 STERN 32250.00 2250.00 BROWN 27740.00 2260.00 ADAMSON 25280.00 4720.00 YOSHIMURA 24680.00 5320.00 PIANKA 22250.00 7750.00 SCOUTTEN 21340.00 8660.00 WALKER 20450.00 9550.00 JONES 18270.00 11730.00




4-3

Student Exercises

Problem 3 List the employees who have a commission greater than 8 percent of their salary. Protect from division by 0 in case someone has 0 salary. List the last name and use CAST to list percentage with three decimals. Result


LASTNAME COMM_PERCENTAGE --------------- --------------NICHOLLS 8.001 JONES 8.002 MARINO 8.000 LEE 8.001

pr

Ex

cl


4-4





Uempty

Solutions Problem 1


SELECT EMPNO, FIRSTNME, LASTNAME, CASE WHEN SALARY < 25000 THEN 'LOW' WHEN SALARY BETWEEN 25000 AND 30000 THEN 'NORMAL' WHEN SALARY > 30000 THEN 'HIGH' ELSE NULL END AS INCOME FROM EMPLOYEE WHERE WORKDEPT = 'D11';

Problem 2

SELECT LASTNAME, SALARY, CASE WHEN SALARY >= 30000 THEN SALARY - 30000 ELSE 30000 - SALARY END AS DIFFERENCE FROM EMPLOYEE WHERE WORKDEPT='D11' ORDER BY DIFFERENCE;

Problem 3

Ex

cl

SELECT LASTNAME, CAST(100.0 * COMM / SALARY AS DECIMAL(8,3)) AS COMM_PERCENTAGE FROM EMPLOYEE WHERE CASE WHEN COMM=0 OR SALARY=0 THEN NULL ELSE COMM/SALARY END > 0.08;

pr

End of solutions




4-5

pr

Ex

cl


Student Exercises

4-6





Uempty

Exercise 5. Subqueries What this exercise is about


This exercise gives you the opportunity to work with subqueries.



• Code simple subqueries using the = and > operators

• Code more complex subqueries using the keywords ANY, ALL, IN • Code the NOT EXISTS subquery

• Code correlated subqueries

Introduction


Requirements

pr

Ex

cl



Exercise 5. Subqueries


5-1

Student Exercises



Retrieve all employees who are not involved in a project. Not involved in a project are those employees who have no row in the EMP_ACT table. Display employee number, last name, and department name. Sort the result by employee number.

Problem 2

Retrieve all employees whose yearly salary is more than the average salary of the employees in their department. For example, if the average yearly salary for department E11 is 20998, show all people in department E11 whose individual salary is higher than 20998. Display employee number, yearly salary, and department number. Sort the result by department number and employee number.

Problem 3

Retrieve all departments having the same number of employees as department A00. List department number and number of employees. Department A00 should not be part of the result.

Problem 4

Display employee number, last name, salary, and department number of employees who earn more than at least one employee in department D11. Employees in department D11 should not be included in the result. In other words, report on any employees in departments other than D11 whose individual yearly salary is higher than that of at least one employee of department D11. List the employees in employee number sequence.

Problem 5

Ex

cl

Display employee number, last name, salary, and department number of all employees who earn more than everybody belonging to department D11. Employees in department D11 should not be included in the result. In other words, report on all employees in departments other than D11 whose individual yearly salary is higher than that of every employee in department D11. List the employees in employee number sequence.

pr

Problem 6

Display employee number, last name, and number of activities of the employee with the largest number of activities. Each activity is stored as one row in the EMP_ACT table.

5-2





Uempty

Problem 7


Display employee number, last name, and activity number of all activities in the EMP_ACT table. However, the list should only be produced if there were any activities in 1982. Sort the result by employee number.

pr

Ex

cl

End of problem list




5-3

Student Exercises



Retrieve all employees who are not involved in a project. Not involved in a project are those employees who have no row in the EMP_ACT table. Display employee number, last name, and department name. Sort the result by employee number. Result

EMPNO LASTNAME ----------------000060 STERN 000120 O'CONNELL

DEPTNAME ---------------------------MANUFACTURING SYSTEMS SPIFFY COMPUTER SERVICE DIV.

Problem 2

Retrieve all employees whose yearly salary is more than the average salary of the employees in their department. For example, if the average yearly salary for department E11 is 20998, show all people in department E11 whose individual salary is higher than 20998. Display employee number, yearly salary, and department number. Sort the result by department number and employee number.

Result

SALARY -------52750.00 46500.00 38250.00 32250.00 25280.00 24680.00 27740.00 29840.00 36170.00 28760.00 27380.00 29750.00 26250.00 26150.00 25370.00 23840.00

pr

Ex

cl

EMPNO -----000010 000110 000030 000060 000150 000170 000200 000220 000070 000240 000270 000090 000280 000100 000330 000340

5-4

WORKDEPT -------A00 A00 C01 D11 D11 D11 D11 D11 D21 D21 D21 E11 E11 E21 E21 E21





Uempty

Problem 3 Retrieve all departments having the same number of employees as department A00. List department number and number of employees. Department A00 should not be part of the result. Result


WORKDEPT EMP_COUNT -------- --------C01 3

Problem 4

Display employee number, last name, salary, and department number of employees who earn more than at least one employee in department D11. Employees in department D11 should not be included in the result. In other words, report on any employees in departments other than D11 whose individual yearly salary is higher than that of at least one employee of department D11. List the employees in employee number sequence.

Result

LASTNAME --------HAAS THOMPSON KWAN GEYER PULASKI HENDERSON SPENSER LUCCHESI O'CONNELL QUINTANA NICHOLLS JEFFERSON MARINO SMITH PEREZ SCHNEIDER MEHTA LEE GOUNOT

pr

Ex

cl

EMPNO -----000010 000020 000030 000050 000070 000090 000100 000110 000120 000130 000140 000230 000240 000250 000270 000280 000320 000330 000340


SALARY -------52750.00 41250.00 38250.00 40175.00 36170.00 29750.00 26150.00 46500.00 29250.00 23800.00 28420.00 22180.00 28760.00 19180.00 27380.00 26250.00 19950.00 25370.00 23840.00

WORKDEPT -------A00 B01 C01 E01 D21 E11 E21 A00 A00 C01 C01 D21 D21 D21 D21 E11 E21 E21 E21



5-5

Student Exercises

Problem 5


Display employee number, last name, salary, and department number of all employees who earn more than everybody belonging to department D11. Employees in department D11 should not be included in the result. In other words, report on all employees in departments other than D11 whose individual yearly salary is higher than that of every employee in department D11. List the employees in employee number sequence.

Result

EMPNO -----000010 000020 000030 000050 000070 000110

LASTNAME -------HAAS THOMPSON KWAN GEYER PULASKI LUCCHESI

SALARY -------52750.00 41250.00 38250.00 40175.00 36170.00 46500.00

WORKDEPT -------A00 B01 C01 E01 D21 A00

Problem 6

Display employee number, last name, and number of activities of the employee with the largest number of activities. Each activity is stored as one row in the EMP_ACT table.

Result

pr

Ex

cl

EMPNO LASTNAME COUNT_ACT ------ -------- --------000250 SMITH 10

5-6





Uempty

Problem 7 Display employee number, last name, and activity number of all activities in the EMP_ACT table. Duplicate rows should be avoided. However, the list should only be produced if there were any activities in 1982. Sort the result by employee number. Result LASTNAME ACTNO --------------- -----HAAS 10 THOMPSON 30 KWAN 10 GEYER 10 PULASKI 10 HENDERSON 10 SPENSER 10 LUCCHESSI 20 QUINTANA 90 QUINTANA 100 NICHOLLS 90 NICHOLLS 100 NICHOLLS 110 ADAMSON 60 ADAMSON 180 PIANKA 60 YOSHIMURA 60 YOSHIMURA 70 YOSHIMURA 80 SCOUTTEN 70 WALKER 70 WALKER 80 BROWN 50 BROWN 60 JONES 80 JONES 180 LUTZ 40 JEFFERSON 60 JEFFERSON 70 JEFFERSON 80 .................. ...

53 record(s) selected.

pr

Ex

cl


EMPNO -----000010 000020 000030 000050 000070 000090 000100 000110 000130 000130 000140 000140 000140 000150 000150 000160 000170 000170 000170 000180 000190 000190 000200 000200 000210 000210 000220 000230 000230 000230 ......





5-7

Student Exercises

Solutions Problem 1


SELECT EMPNO, LASTNAME, DEPTNAME FROM EMPLOYEE , DEPARTMENT WHERE WORKDEPT = DEPTNO AND EMPNO NOT IN (SELECT EMPNO FROM EMP_ACT) ORDER BY EMPNO;

Problem 2

SELECT EMPNO, SALARY, WORKDEPT FROM EMPLOYEE E WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE WORKDEPT = E.WORKDEPT) ORDER BY WORKDEPT, EMPNO;

Problem 3

SELECT WORKDEPT, COUNT(*) AS EMP_COUNT FROM EMPLOYEE WHERE WORKDEPT <> 'A00' GROUP BY WORKDEPT HAVING COUNT(*) = (SELECT COUNT(*) FROM EMPLOYEE WHERE WORKDEPT = 'A00');

Problem 4

pr

Ex

cl

SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE WHERE WORKDEPT <> 'D11' AND SALARY > ANY (SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'D11') ORDER BY EMPNO; An alternate solution is: SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE WHERE WORKDEPT <> 'D11' AND SALARY > (SELECT MIN(SALARY) FROM EMPLOYEE WHERE WORKDEPT = 'D11') ORDER BY EMPNO;

5-8





Uempty

Problem 5


SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE WHERE WORKDEPT <> 'D11' AND SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'D11') ORDER BY EMPNO; An alternate solution is:

SELECT EMPNO, LASTNAME, SALARY, WORKDEPT FROM EMPLOYEE WHERE WORKDEPT <> 'D11' AND SALARY > (SELECT MAX(SALARY) FROM EMPLOYEE WHERE WORKDEPT = 'D11') ORDER BY EMPNO;

Problem 6

SELECT E.EMPNO, LASTNAME, COUNT(*) AS COUNT_ACT FROM EMPLOYEE E, EMP_ACT EA WHERE E.EMPNO = EA.EMPNO GROUP BY E.EMPNO, LASTNAME HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM EMP_ACT GROUP BY EMPNO);

Problem 7

pr

Ex

cl

SELECT DISTINCT E.EMPNO, LASTNAME, ACTNO FROM EMPLOYEE E JOIN EMP_ACT EA ON E.EMPNO = EA.EMPNO WHERE EXISTS (SELECT * FROM EMP_ACT WHERE 1982 BETWEEN YEAR(EMSTDATE) AND YEAR(EMENDATE)) ORDER BY EMPNO;

End of solutions




5-9

pr

Ex

cl


Student Exercises





Uempty

Exercise 6. Scalar Functions What this exercise is about


This exercise gives you the opportunity to work with scalar functions.


At the end of the lab, you should be able to: • Code queries using scalar functions

Introduction


Requirements

pr

Ex

cl



Exercise 6. Scalar Functions


6-1

Student Exercises



Produce a report listing all employees whose last name ends with 'N'. List the employee number, the last name, and the last character of the last name used to control the result. The LASTNAME column is defined as VARCHAR. There is a function which provides the length of the last name. Sort the result by employee number.

Problem 2

For each project, display the project number, project name, department number, and project number of its associated major project (COLUMN = MAJPROJ). If the value in MAJPROJ is NULL, show a literal of your choice instead of displaying a null value. List only projects assigned to departments D01 or D11. The rows should be listed in project number sequence.

Problem 3

The salaries of the employees in department E11 will be increased by 3.75 percent. What will be the increase in dollars? Display the last name, actual yearly salary, and the salary increase rounded to the nearest dollar. Do not show any cents.

Problem 4

For each female employee in the company present her department, her job and her last name with a colon followed by one blank between job and last name.

Problem 5

pr

Ex

cl

Calculate the difference between the date of birth and the hiring date for all employees for whom the hiring date is more than 30 years later than the date of birth. Display employee number and calculated difference. The difference should be shown in years, months, and days - each of which should be shown in a separate column. Make sure that the rows are in employee number sequence.

6-2





Uempty

Problem 6 Display project number, project name, project start date, and project end date of those projects whose duration was less than 10 months. Display the project duration in days.


Problem 7 List the employees in department D11 who had activities. Display employee number, last name, and first name. Also, show the activity number and the activity duration (in days) of the activities started last. Multiple activities may have been started on the same day.

Problem 8

How many weeks are between the first manned landing on the moon (July 20, 1969) and the first day of the year 2000?

Problem 9

Find out which employees were hired on a Saturday or a Sunday. List their last names and their hiring dates.

pr

Ex

cl

End of Problem list




6-3

Student Exercises



Produce a report listing all employees whose last name ends with 'N'. List the employee number, the last name, and the last character of the last name used to control the result. The LASTNAME column is defined as VARCHAR. There is a function which provides the length of the last name. Sort the result by employee number.

Result

EMPNO -----000020 000030 000060 000090 000150 000180 000200 000230 000260

LASTNAME --------THOMPSON KWAN STERN HENDERSON ADAMSON SCOUTTEN BROWN JEFFERSON JOHNSON

LASTCHAR -------N N N N N N N N N

Problem 2

For each project, display the project number, project name, department number, and project number of its associated major project (COLUMN = MAJPROJ). If the value in MAJPROJ is NULL, show a literal of your choice instead of displaying a null value. List only projects assigned to departments D01 or D11. The rows should be listed in project number sequence.

Result

PROJNAME -------------------ADMIN SERVICES WELD LINE AUTOMATION W L PROGRAMMING W L PROGRAM DESIGN W L ROBOT DESIGN W L PROD CONT PROGS

pr

Ex

cl

PROJNO -----AD3100 MA2100 MA2110 MA2111 MA2112 MA2113

DEPTNO -----D01 D01 D11 D11 D11 D11

MAJPROJ ---------------NO MAJOR PROJECT NO MAJOR PROJECT MA2100 MA2110 MA2110 MA2110

<----note

The first row in the result set above will not have the text 'NO MAJOR PROJECT' when the query is run on DB2 for Linux, UNIX, and Windows as MAJPROJ is blank and not null for PROJNO AD3100.

6-4





Uempty

Problem 3 The salaries of the employees in department E11 will be increased by 3.75 percent. What will be the increase in dollars? Display the last name, actual yearly salary, and the salary increase rounded to the nearest dollar. Do not show any cents. Result LASTNAME --------HENDERSON SCHNEIDER PARKER SMITH SETRIGHT

WORKDEPT -------E11 E11 E11 E11 E11

SALARY AMOUNT -------- -----29750.00 1116. 26250.00 984. 15340.00 575. 17750.00 666. 15900.00 596.


EMPNO -----000090 000280 000290 000300 000310

Problem 4

For each female employee in the company present her department, her job and her last name with a colon followed by one blank between job and last name.

Result

Ex

cl

WORKDEPT -------A00 C01 D21 E11 C01 C01 D11 D11 D11 D21 D21 E11 E11

LISTING --------------------------PRES: HAAS MANAGER: KWAN MANAGER: PULASKI MANAGER: HENDERSON ANALYST: QUINTANA ANALYST: NICHOLLS DESIGNER: PIANKA DESIGNER: SCOUTTEN DESIGNER: LUTZ CLERK: JOHNSON CLERK: PEREZ OPERATOR: SCHNEIDER OPERATOR: SETRIGHT

pr

Problem 5

Calculate the difference between the date of birth and the hiring date for all employees for whom the hiring date is more than 30 years later than the date of birth. Display employee number and calculated difference. The difference should be shown in years, months, and days - each of which should be shown in a separate column. Make sure that the rows are in employee number sequence.




6-5

Student Exercises

Result YEARS ----31 33 45 30 31 38 30 33 35 33 32 34

MONTHS -----4 10 10 10 5 11 11 10 7 4 10 7

DAYS ---8 25 13 27 22 6 27 21 23 21 27 5


EMPNO -----000010 000030 000130 000140 000230 000260 000280 000290 000300 000310 000320 000330

Problem 6

Display project number, project name, project start date, and project end date of those projects whose duration was less than 10 months. Display the project duration in days.

Result

PROJNO -----MA2113 PL2100

PROJNAME ------------------W L PROD CONT PROGS WELD LINE PLANNING

PRSTDATE ---------1982-02-15 1982-01-01

PRENDATE DAYS_DURATION ---------- ------------1982-12-01 289 1982-09-15 257

Problem 7

pr

Ex

cl

List the employees in department D11 who had activities. Display employee number, last name, and first name. Also, show the activity number and the activity duration (in days) of the activities started last. Multiple activities may have been started on the same day.

6-6





Uempty

Result LASTNAME --------ADAMSON PIANKA YOSHIMURA SCOUTTEN WALKER BROWN JONES JONES LUTZ

FIRSTNME ACTNO EMSTDATE DAYS_DURATION --------- ----- ---------- ------------BRUCE 180 1982-07-15 201 ELIZABETH 60 1982-07-15 201 MASATOSHI 70 1982-06-01 245 MARILYN 70 1982-04-01 75 JAMES 80 1982-10-01 365 DAVID 60 1982-06-15 231 WILLIAM 80 1982-10-01 123 WILLIAM 180 1982-10-01 123 JENNIFER 40 1982-01-01 396


EMPNO -----000150 000160 000170 000180 000190 000200 000210 000210 000220

Problem 8

How many weeks are between the first manned landing on the moon (July 20, 1969) and the first day of the year 2000?

Result

WEEKS ----1588

Problem 9

Find out which employees were hired on a Saturday or a Sunday. List their last names and their hiring dates.

Result

LASTNAME --------------KWAN HENDERSON ADAMSON SCOUTTEN SETRIGHT

pr

Ex

cl

HIREDATE ---------1975-04-05 1970-08-15 1972-02-12 1973-07-07 1964-09-12





6-7

Student Exercises

Solutions Problem 1


SELECT EMPNO, LASTNAME, SUBSTR(LASTNAME,LENGTH(LASTNAME),1) AS LASTCHAR FROM EMPLOYEE WHERE LASTNAME LIKE '%N' ORDER BY EMPNO; An alternate solution is:

SELECT EMPNO, LASTNAME, SUBSTR(LASTNAME,LENGTH(LASTNAME),1) AS LASTCHAR FROM EMPLOYEE WHERE SUBSTR(LASTNAME, LENGTH(LASTNAME),1) = 'N' ORDER BY EMPNO;

An even better solution, that would work with LASTNAME being defined as either CHAR(x) or VARCHAR(x), is: WHERE RTRIM(LASTNAME) LIKE '%N'

Problem 2

SELECT PROJNO, PROJNAME, DEPTNO, COALESCE(MAJPROJ,'NO MAJOR PROJECT') AS MAJPROJ FROM PROJECT WHERE DEPTNO IN ('D01','D11') ORDER BY PROJNO;

Problem 3

cl

SELECT EMPNO, LASTNAME, WORKDEPT, SALARY, DECIMAL(SALARY * 0.0375 + 0.5, 5,0) AS AMOUNT FROM EMPLOYEE WHERE WORKDEPT = 'E11';

Ex

or by using ROUND():

pr

SELECT EMPNO, LASTNAME, WORKDEPT, SALARY, DECIMAL(ROUND(SALARY * 0.0375 ,0), 5, 0) AS AMOUNT FROM EMPLOYEE WHERE WORKDEPT = 'E11';

Round() gives in DB2 for z/OS, a DECIMAL; in DB2 for Linux, UNIX, and Windows, a FLOATING point value.

6-8





Uempty

Problem 4 SELECT WORKDEPT, CAST(RTRIM(JOB) AS VARCHAR(10)) !! ': '!! LASTNAME AS LISTING FROM EMPLOYEE WHERE SEX='F';


Problem 5

SELECT EMPNO, YEAR(HIREDATE - BIRTHDATE) AS YEARS, MONTH(HIREDATE - BIRTHDATE) AS MONTHS, DAY(HIREDATE - BIRTHDATE) AS DAYS FROM EMPLOYEE WHERE HIREDATE - BIRTHDATE > 300000 ORDER BY EMPNO; An alternate solution is:

SELECT EMPNO, YEAR(HIREDATE - BIRTHDATE) AS YEARS, MONTH(HIREDATE - BIRTHDATE) AS MONTHS, DAY(HIREDATE - BIRTHDATE) AS DAYS FROM EMPLOYEE WHERE HIREDATE > BIRTHDATE + 30 YEARS ORDER BY EMPNO;

Problem 6

SELECT PROJNO, PROJNAME, PRSTDATE, PRENDATE, DAYS(PRENDATE) - DAYS(PRSTDATE) AS DAYS_DURATION FROM PROJECT WHERE PRENDATE - 10 MONTHS < PRSTDATE;

Problem 7

pr

Ex

cl

SELECT E.EMPNO, LASTNAME, FIRSTNME, ACTNO, EMSTDATE, DAYS(EMENDATE) - DAYS(EMSTDATE) AS DAYS_DURATION FROM EMPLOYEE E JOIN EMP_ACT EA ON E.EMPNO = EA.EMPNO WHERE EMSTDATE = (SELECT MAX(EMSTDATE) FROM EMP_ACT WHERE EMPNO = E.EMPNO) AND E.WORKDEPT = 'D11' ORDER BY E.EMPNO;




6-9

Student Exercises

Problem 8 SELECT (DAYS('2000-01-01') - DAYS('1969-07-20')) / 7 AS WEEKS FROM EMPLOYEE WHERE EMPNO = '000010';


Note that the scalar function WEEK only gives the week-number of the year for a date.

Problem 9

pr

Ex

cl

SELECT HIREDATE,LASTNAME FROM EMPLOYEE WHERE DAYOFWEEK(HIREDATE) IN (1,7);





Uempty

Exercise 7. Table Expressions and Recursive SQL What this exercise is about


This exercise gives you the opportunity to work with table expressions and recursive SQL.



• 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


Requirements

pr

Ex

cl



Exercise 7. Table Expressions and Recursive SQL


7-1

Student Exercises



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.

pr

Ex

cl

SELECT * FROM CARS ORDER BY 1, 2, 3, 4

7-2





Uempty

The CARS table should contain the following data:


---------+---------+---------+---------+---------+---------+---------+---------+ LEVEL ASSEMBLY COMPONENT QUANTITY ---------+---------+---------+---------+---------+---------+---------+---------+ 1 HONDA ACCORD ENGINE COMPARTMENT 1 1 HONDA ACCORD PASSENGER COMPARTMENT 1 2 ENGINE COMPARTMENT ENGINE 1 2 ENGINE COMPARTMENT IGNITION SYSTEM 1 2 PASSENGER COMPARTMENT DASHBOARD 1 2 PASSENGER COMPARTMENT SEAT GROUP 3 2 PASSENGER COMPARTMENT TRUNK 1 3 DASHBOARD CONTROL CLUSTER 1 3 DASHBOARD GAUGE CLUSTER 1 3 DASHBOARD STEREO SYSTEM 1 3 DASHBOARD VENT 4 3 ENGINE CAMSHAFT 1 3 ENGINE CONNECTING ROD 4 3 ENGINE CRANKSHAFT 1 3 ENGINE PISTON 4 3 SEAT GROUP FRONT SEAT ASSEMBLY 2 3 SEAT GROUP REAR SEAT ASSEMBLY 1 3 TRUNK TOOL KIT 1 4 CONTROL CLUSTER FOG LAMP CONTROL 1 4 CONTROL CLUSTER FOUR WAY FLASHER CONTROL 1 4 CONTROL CLUSTER TURN SIGNAL CONTROL 1 4 FRONT SEAT ASSEMBLY FRONT SEAT 1 4 FRONT SEAT ASSEMBLY LAP/SHOULDER BELT 1 4 GAUGE CLUSTER ODOMETER 1 4 GAUGE CLUSTER SPEEDOMETER 1 4 REAR SEAT ASSEMBLY LAP/SHOULDER BELT 3 4 REAR SEAT ASSEMBLY REAR SEAT 1 4 REAR SEAT ASSEMBLY SHOULDER BELT 3 4 STEREO SYSTEM CD PLAYER 1 4 STEREO SYSTEM RADIO 1

Ex

cl


pr

End of problem list




7-3

Student Exercises



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. Result

HIREYEAR AVG_TOTAL_EARNINGS ----------- -----------------1970 32730.000000 1971 26204.000000 1972 23686.000000 1973 34775.666666 1974 22486.000000 1975 30520.000000 1976 29597.000000 1977 24430.000000 1978 27154.000000 1979 25896.500000 1980 28860.500000

Problem 2

Now, use a common table expression to meet the requirements of problem 1.

Result

pr

Ex

cl

HIREYEAR AVG_TOTAL_EARNINGS ----------- -----------------1970 32730.000000 1971 26204.000000 1972 23686.000000 1973 34775.666666 1974 22486.000000 1975 30520.000000 1976 29597.000000 1977 24430.000000 1978 27154.000000 1979 25896.500000 1980 28860.500000

7-4





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 SALARY -------52750.00 46500.00 29250.00 41250.00 38250.00 23800.00 28420.00 32250.00 25280.00 22250.00 24680.00 21340.00 20450.00 ........

AVG_SAL -------------42833.33333333 42833.33333333 42833.33333333 41250.00000000 30156.66666666 30156.66666666 30156.66666666 24677.77777777 24677.77777777 24677.77777777 24677.77777777 24677.77777777 24677.77777777 ..............

WORKDEPT -------A00 A00 A00 B01 C01 C01 C01 D11 D11 D11 D11 D11 D11 ...


EMPNO -----000010 000110 000120 000020 000030 000130 000140 000060 000150 000160 000170 000180 000190 .....

The complete result set contains 32 rows.

Problem 4

cl

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.

Ex

You can determine the contents of the CARS table with the following SELECT statement.

pr

SELECT * FROM CARS ORDER BY 1, 2, 3, 4




7-5

Student Exercises

The CARS table should contain the following data:


---------+---------+---------+---------+---------+---------+---------+---------+ LEVEL ASSEMBLY COMPONENT QUANTITY ---------+---------+---------+---------+---------+---------+---------+---------+ 1 HONDA ACCORD ENGINE COMPARTMENT 1 1 HONDA ACCORD PASSENGER COMPARTMENT 1 2 ENGINE COMPARTMENT ENGINE 1 2 ENGINE COMPARTMENT IGNITION SYSTEM 1 2 PASSENGER COMPARTMENT DASHBOARD 1 2 PASSENGER COMPARTMENT SEAT GROUP 3 2 PASSENGER COMPARTMENT TRUNK 1 3 DASHBOARD CONTROL CLUSTER 1 3 DASHBOARD GAUGE CLUSTER 1 3 DASHBOARD STEREO SYSTEM 1 3 DASHBOARD VENT 4 3 ENGINE CAMSHAFT 1 3 ENGINE CONNECTING ROD 4 3 ENGINE CRANKSHAFT 1 3 ENGINE PISTON 4 3 SEAT GROUP FRONT SEAT ASSEMBLY 2 3 SEAT GROUP REAR SEAT ASSEMBLY 1 3 TRUNK TOOL KIT 1 4 CONTROL CLUSTER FOG LAMP CONTROL 1 4 CONTROL CLUSTER FOUR WAY FLASHER CONTROL 1 4 CONTROL CLUSTER TURN SIGNAL CONTROL 1 4 FRONT SEAT ASSEMBLY FRONT SEAT 1 4 FRONT SEAT ASSEMBLY LAP/SHOULDER BELT 1 4 GAUGE CLUSTER ODOMETER 1 4 GAUGE CLUSTER SPEEDOMETER 1 4 REAR SEAT ASSEMBLY LAP/SHOULDER BELT 3 4 REAR SEAT ASSEMBLY REAR SEAT 1 4 REAR SEAT ASSEMBLY SHOULDER BELT 3 4 STEREO SYSTEM CD PLAYER 1 4 STEREO SYSTEM RADIO 1

pr

Ex

cl


7-6





Uempty

Result ASSEMBLY --------------------PASSENGER COMPARTMENT PASSENGER COMPARTMENT PASSENGER COMPARTMENT DASHBOARD DASHBOARD DASHBOARD DASHBOARD SEAT GROUP SEAT GROUP TRUNK

COMPONENT QUANTITY ------------------ -------DASHBOARD 1 SEAT GROUP 3 TRUNK 1 CONTROL CLUSTER 1 GAUGE CLUSTER 1 STEREO SYSTEM 1 VENT 4 FRONT SEAT ASSEMBLY 6 REAR SEAT ASSEMBLY 3 TOOL KIT 1


LEVEL ----0 0 0 1 1 1 1 1 1 1

pr

Ex

cl





7-7

Student Exercises

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

pr

Ex

cl

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;

7-8





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

Ex

cl

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;

pr

End of solutions




7-9

pr

Ex

cl


Student Exercises





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).


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

pr

Ex

cl



Exercise 8. UDT and UDF


8-1

Student Exercises



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 000010 000020 000030

GER_SAL 100000 80000 72000

UK_SAL 40000 31000 29000

Use your new user-defined functions to calculate the average salary in German marks and British Pounds for these three employees.

pr

Ex

cl

End of Problem list

8-2





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) RETURNS UK_POUND SOURCE AVG(DECIMAL(9,2)); CREATE FUNCTION MIN(UK_POUND) RETURNS UK_POUND SOURCE MIN(DECIMAL(9,2)); CREATE FUNCTION MAX(UK_POUND) RETURNS UK_POUND SOURCE MAX(DECIMAL(9,2)); CREATE FUNCTION AVG(GER_MARK) RETURNS GER_MARK SOURCE AVG(DECIMAL(9,2));

Ex

cl

CREATE FUNCTION MIN(GER_MARK) RETURNS GER_MARK SOURCE MIN(DECIMAL(9,2));

pr

CREATE FUNCTION MAX(GER_MARK) RETURNS GER_MARK SOURCE MAX(DECIMAL(9,2));


Exercise 8. UDT and UDF


8-3

Student Exercises

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');

pr

Ex

cl

End of solutions

8-4





Uempty

Exercise 9. Performance What this exercise is about


This exercise will enable you to predict the access paths that the DB2 Optimizer will choose for specific SQL statements.


At the end of the lab, you should:

• Have a basic understanding of efficient and inefficient access paths • Understand that DB2 performance is dependent upon many factors

Introduction

This is intended to be a paper lab. For the problems in this exercise, assume that the EMPLOYEE table has the following indexes: • A unique index on EMPNO • A non-unique multi-column index on LASTNAME, FIRSTNME • A non-unique index on WORKDEPT

Requirements

pr

Ex

cl

• Student handout


Exercise 9. Performance


9-1

Student Exercises

Problem list Problem 1 Can DB2 use an index for the following query? Why?


SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE PHONENO = '1793';

________________________________________________________________ ________________________________________________________________

Problem 2

Can DB2 use an index for the following query? Why? SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT FROM EMPLOYEE;

________________________________________________________________ ________________________________________________________________

Problem 3

Can DB2 use an index for the following query? Why?

SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO, HIREDATE FROM EMPLOYEE WHERE LASTNAME LIKE 'J%';

________________________________________________________________ ________________________________________________________________

cl

Problem 4

Can DB2 use an index for the following query? Why?

Ex

SELECT FIRSTNME, LASTNAME FROM EMPLOYEE WHERE SUBSTR(LASTNAME,1,1) = 'J';

________________________________________________________________

pr

________________________________________________________________

9-2





Uempty

Problem 5 Can DB2 use only an index to obtain the result when performing the following query? This means that table EMPLOYEE would not be accessed. Why?


SELECT FIRSTNME FROM EMPLOYEE WHERE LASTNAME = 'STERN'; ________________________________________________________________ ________________________________________________________________

Problem 6

Can DB2 use an index for the following query? Why? SELECT COUNT(*) FROM EMPLOYEE;

________________________________________________________________ ________________________________________________________________

Problem 7

Can DB2 use the index on WORKDEPT for the following query? Why? SELECT * FROM EMPLOYEE WHERE WORKDEPT BETWEEN 'C01' AND 'D11';

________________________________________________________________ ________________________________________________________________

pr

Ex

cl

End of Problem list




9-3

Student Exercises

Solutions Problem 1


DB2 is unlikely to use an index in this case because there is no index on column PHONENO.

Visual Explain – Problem 1

pr

Ex

cl


9-4





Uempty

Problem 2


DB2 is unlikely to use an index in this case because the result set will contain all rows of the table. Using an index would not reduce the size of the result set and would only add extra reads to the processing. DB2 normally reads the whole table without using an index when all of the rows are required for the result set.


pr

Ex

cl





9-5

Student Exercises

Problem 3 DB2 can use an index in this case because the leftmost part of the last name is known and the index of LASTNAME and FIRSTNME has LASTNAME as its leftmost column.



pr

Ex

cl


9-6





Uempty

Problem 4


This query is logically equivalent to the one in Problem 3. However, it uses the SUBSTR() function, which is considered an expression. Normally, DB2 does not consider an index when a predicate contains an expression. However, in this specific case (SUBSTR starting at the first character of the column), DB2 for Linux, UNIX, and Windows will consider an index on the column in question (the index starting with LASTNAME).

cl


pr

Ex





9-7

Student Exercises

Problem 5 Rows do not have to be read from the EMPLOYEE table because the index on LASTNAME, FIRSTNME contains the selected column FIRSTNME. This is called index only access.



pr

Ex

cl


9-8





Uempty

Problem 6


DB2 uses an index in this case if the cost of counting index entries is less than the cost of counting the rows in the table. Normally the table object is much larger than the index object. The size and fragmentation of the table and of the index are an important factor here.


pr

Ex

cl





9-9

Student Exercises

Problem 7


DB2 might use the index on the WORKDEPT column if its analysis shows that there are relatively few departments in the range from 'C01' to 'D11'. Otherwise, it is unlikely to use an index. For instance, if the range of departments is from A00 to Z99, there are probably very few departments that match the predicate in this query. However, if the departments only range from B99 to D15, then nearly all of the departments match the predicate. The more rows that qualify for the result set, the less likely it becomes for DB2 to use the index. DB2 can collect distribution statistics which are used in cases like this one to determine whether or not there is any significant benefit in using the index on the WORKDEPT column. The actual statistics kept vary somewhat from platform to platform and version to version.

Ex

cl


pr


End of solutions





backpg

pr

cl

Ex V6.0

Back page

pr


cl

Ex

CONTACTO Teléfono 91 761 21 78 Póngase en contacto con nuestro equipo y le informaremos de cualquier duda o cuestión que pueda surgirle.

Email [email protected] Mándenos un email y le atenderemos enseguida.

Online @Arrow_Edu_ES O bien puede contactarnos a través de nuestro perfil en Twitter.

Visítenos Arrow ECS Education Services Avenida de Europa 21, Parque Empresarial La Moraleja 28108 Alcobendas, Madrid

EDUCATION S

E

R

V

I

C

E

S

CE1319XSTUD

Recommend Documents