CE1319XSTUD

Full text

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

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

(3) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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..

(4) V6.0 Student Exercises. Contents Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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.

(5) pr. Ex. cl. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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..

(6) V6.0 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:. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013 Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. Trademarks. v.

(7) pr. Ex. cl. u oy si ec vo to fo C rm .F a .T ció .I. n C .. Student Exercises. vi. 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..

(8) V7.0 Student Exercises. Exercises description Be sure to read the General Information section and the description of the lab tables before attempting the exercises.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. vii.

(9) 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(10) V7.0 Student Exercises. 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:. u oy si ec vo to fo C rm .F a .T ció .I. n C .. • __________________________ • __________________________ • __________________________. 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.. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. ix.

(11) 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:. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(12) V7.0 Student Exercises. General Information for DB2 for Linux, UNIX, and Windows Users. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xi.

(13) 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(14) V7.0 Student Exercises. pref. Table relationships DEPARTMENT Table MGRNO. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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.. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xiii.

(15) Student Exercises. Table descriptions The tables are described in hierarchical order, as shown in the Table Relationships on the previous page.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(16) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xv.

(17) 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. DEPTNO -----A00 B01 C01 D01 D11 D21 E01 E11 E21. xvi. 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..

(18) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xvii.

(19) Student Exercises. EMPLOYEE Table (Cont). pr. Ex. cl. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(20) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. PROJNO -----AD3100 AD3110 AD3111 AD3112 AD3113 IF1000 IF2000 MA2100 MA2110 MA2111 MA2112 MA2113 OP1000 OP1010 OP2000 OP2010 OP2011 OP2012 OP2013 PL2100. pr. Ex. pref. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xix.

(21) 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(22) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xxi.

(23) 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(24) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. EMPNO LASTNAME ---------------000111 SMITH 000010 HAAS 000020 THOMPSON 000030 KWAN 000050 GEYER. pr. Ex. pref. © Copyright IBM Corp. 2007, 2013. Exercises description. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. xxiii.

(25) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(26) V7.0 Student Exercises. Uempty. Exercise 1. SQL Basics Review, CUBE, and ROLLUP What this exercise is about. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 1. SQL Basics Review, CUBE, and ROLLUP. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 1-1.

(27) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(28) V7.0 Student Exercises. Uempty. Note. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 1. SQL Basics Review, CUBE, and ROLLUP. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 1-3.

(29) Student Exercises. Problem List with Expected Results Problem 1 Joe's manager wants information about employees which match the following criteria:. u oy si ec vo to fo C rm .F a .T ció .I. n C .. • 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. DB2 SQL Workshop for Experienced Users. AVG -------------24677.77777777 25153.33333333. COUNT ----9 6. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(30) V7.0 Student Exercises. 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 1. SQL Basics Review, CUBE, and ROLLUP. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 1-5.

(31) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. LASTNAME --------------O'CONNELL LUCCHESSI HAAS THOMPSON QUINTANA NICHOLLS KWAN. 1-6. 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..

(32) V7.0 Student Exercises. 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. u oy si ec vo to fo C rm .F a .T ció .I. n C .. LASTNAME --------------O'CONNELL LUCCHESSI HAAS THOMPSON QUINTANA NICHOLLS KWAN. pr. Ex. cl. End of Problem List with Expected Results. © Copyright IBM Corp. 2007, 2013. Exercise 1. SQL Basics Review, CUBE, and ROLLUP. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 1-7.

(33) Student Exercises. Solution Problem 1. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(34) V7.0 Student Exercises. Uempty. With DENSE_RANK. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 1. SQL Basics Review, CUBE, and ROLLUP. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 1-9.

(35) pr. Ex. cl. u oy si ec vo to fo C rm .F a .T ció .I. n C .. Student Exercises. 1-10 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..

(36) V7.0 Student Exercises. Uempty. Exercise 2. Create Objects What this exercise is about. u oy si ec vo to fo C rm .F a .T ció .I. n C .. This exercise gives you an opportunity to implement a small data model.. What you should be able to do. 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. See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.. 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.. • Student handout • SQL Reference. pr. Ex. Requirements. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-1.

(37) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(38) V7.0 Student Exercises. Uempty. Problem 2 After creating the table, you should add referential constraints.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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?. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-3.

(39) Student Exercises. Problem 6 Harvey wants to see if the trigger works.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(40) V7.0 Student Exercises. 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-5.

(41) Student Exercises. Problem List with Expected Results Problem 1 Create the table EMPDEPT with these columns: EMPNO LASTNAME SALARY DEPTNO DEP_NAME. u oy si ec vo to fo C rm .F a .T ció .I. n C .. • • • • •. 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. 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..

(42) V7.0 Student Exercises. Uempty. Problem 2 After creating the table, you should add referential constraints.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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.. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-7.

(43) 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?. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. 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..

(44) V7.0 Student Exercises. Uempty. Problem 7. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-9.

(45) 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(46) V7.0 Student Exercises. 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-11.

(47) Student Exercises. Solutions Problem 1 NOT NULL, NOT NULL, CHECK(SALARY <= 50000), NOT NULL, NOT NULL);. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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;. 2-12 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..

(48) V7.0 Student Exercises. Uempty. Problem 3. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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.. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-13.

(49) Student Exercises. Problem 6 UPDATE EMPDEPT SET SALARY = 25000 WHERE EMPNO = '000160';. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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';. 2-14 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..

(50) V7.0 Student Exercises. Uempty. Problem 9 Reset the salary for Thompson: UPDATE TESTEMP SET SALARY = 41250 WHERE EMPNO = '000020';. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © Copyright IBM Corp. 2007, 2013. Exercise 2. Create Objects. Course materials may not be reproduced in whole or in part without the prior written permission of IBM.. 2-15.

(51) pr. Ex. cl. u oy si ec vo to fo C rm .F a .T ció .I. n C .. Student Exercises. 2-16 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..

(52) V7.0 Student Exercises. Uempty. Exercise 3. Joins What this exercise is about. u oy si ec vo to fo C rm .F a .T ció .I. n C .. This exercise will give you the opportunity to code inner and outer joins.. What you should be able to do. At the end of the lab, you should be able to: • Code Inner and Outer Joins. Introduction. See the data model at the start of this Exercise Guide to get the column names and descriptions for each table.. Note. Use the views VDEPARTMENT, VEMPLOYEE, and VPROJECT for problems 1 through 6.. Requirements. pr. Ex. cl. • Student handout • SQL Reference. © 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-1.

(53) Student Exercises. Problem list Problem 1. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. Use the views VDEPARTMENT, VEMPLOYEE, and VPROJECT for problems 1 through 6.. 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. 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..

(54) V7.0 Student Exercises. Uempty. Problem 6. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © 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-3.

(55) Student Exercises. Problem list with Expected Results Problem 1. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. Use the views VDEPARTMENT, VEMPLOYEE, and VPROJECT for problems 1 through 6.. 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. 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..

(56) V7.0 Student Exercises. 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. © 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-5.

(57) Student Exercises. Problem 5. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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 -. DB2 SQL Workshop for Experienced Users. PROJNAME -----------------------QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION QUERY SERVICES USER EDUCATION ADMIN SERVICES. © Copyright IBM Corp. 2007, 2013. Course materials may not be reproduced in whole or in part without the prior written permission of IBM..

(58) V7.0 Student Exercises. 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.. u oy si ec vo to fo C rm .F a .T ció .I. n C .. 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. End of Problem list with Expected Results. © 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-7.

No results found