The American Programmer

Table of Contents Top of Page 1: Sample Tables EMP table DEPT table 2: Datatypes for the EMP and DEPT Tables 3: Concept of a Table 4: SQL, the Language 5: The Clauses of SQL 7: Datatypes 8: Selecting All Columns and All Rows 9: Displaying Some Columns 10: Eliminating Duplicates in a SELECT 11: Arithmetic expressions in a SELECT Operators Arithmetic Examples 12: SELECTing a Literal Value 13: Concatenating Two Columns in a SELECT 14: Limiting What is Selected: WHERE Example Row Expression in Simple Predicate (Version 7 only) 15: The Operators of the WHERE Clause 16: WHERE with Numeric and Character Datatypes. 17: AND with WHERE 18: OR with WHERE 19: AND + OR in the Same WHERE 20: NOT Negates the Condition 21: NULLS 22: LIKE 23: BETWEEN 24: IN 25: Sorting - ORDER BY 26: Changing Column names on a SELECT 27: Primary Key (PK) 28: Foreign Key (FK) 29: Joining Tables (All Versions) 30: Table Joining with DB2 Version 4 and Later 31: The Left Outer JOIN with DB2 Version 4 and Later 32: The Full Outer JOIN with DB2 Version 4 and Later 33: The Right Outer Join with DB2 Version 4 and Later 34: Qualifying 35: Column functions 36: GROUP BY 37: The HAVING Clause 38: Scalar Functions: List and Summary 39: Scalar Functions: Explanations and Examples 40: INSERT 41: DELETE 42: UPDATE 43: Referential Integrity 44: Subqueries 45: Predicates Used With the Subquery 46: EXISTS/NOT EXISTS 47: The Correlated Subquery 48: UNION ALL 49: UNION 50: View 51: COMMIT 52: ROLLBACK 53: CASE with DB2 Version 4 and Higher 54: SQL Special Registers (Built-in Variables) 55: Labeled Durations 56: Date Arithmetic Appendix A: Using SPUFI to Execute Your SQL on DB2 Appendix B: Executing SQL with QMF Appendix C: Using QMF to See How a Table Was Defined Appendix D: Vocabulary Appendix E: Loading the Sample Tables Index

---

Introduction Top of Page This book is about the Structured Query Language (SQL) found on IBM mainframe computers. It is used on the Z/OS, OS/390 or MVS operating system with the DB2 UDB Version 7 database management system. The SQL shown is consistent with Version 7 of DB2. Users of systems other than DB2 will note differences in language syntax. It is a reference book. You can look up individual subjects without reading the entire book. Each subject is as complete as possible, with examples. But since subjects are arranged in logical order rather than alphabetical order you can start at the beginning and read towards the end. It is a book for hands-on learning with practice problems that you can figure out, type in, and run on your system. To make this easy I chose Tables for examples and practice problems that come with the system. If you can’t find these Tables on your system please read Appendix F for instructions on what to do. The data tables shown in the book are found on most systems. They come with DB2 and are used by systems personnel for initial testing of installations. Having this data allows you to type in and execute the examples given in this book, and to actually do the practice problems given and to obtain the same results as shown in the book. If these data tables are not on your system, get and execute the code found on the diskette in the file CRTABLS.TXT. The book believes an example is worth 1000 syntax diagrams. There are approximately 130 examples of SQL in the book. All the SQL code for the examples is contained on the diskette, in the file EXAMPLES.TXT. It is a book that respects your intelligence. The index lists subjects by the names used in the book, by the standard IBM term used and by the normal English-language word where possible. This means that you can look up “WHERE” as you would expect. You can also look up “IF” and be referred to “WHERE”. This is because SQL has a WHERE which performs almost the same function as an “IF” in other programming languages. You don’t have to know all the lingo to use this book. Contents of the diskette included with the book: crtabls.txt - to load practice tables (if you can't find them on your system) sql.examples.txt - all the examples shown in the book sql.solutions.txt - all the solutions to practice problems shown in the book. sql.solutions.doc - solutions in Word for Windows format sql.book.extra.doc - extra practice problems you can use on a DB2 system. sql.problems.doc sql.problems.txt This book is for: the mainframe programmer on Z/OS, MVS or OS/390. the mainframe systems person anyone who needs to create and execute SQL on DB2 using SPUFI or QMF the user of DB2 systems. anyone needing to use Table joining Subqueries Special registers Labeled durations Functions The following SQL statements or verbs are covered: COMMIT DELETE INSERT ROLLBACK SELECT UPDATE The book does not cover: Using QMF to produce finely tailored reports from SQL. Embedded SQL in an application program. (see http://www.theamericanprogrammer.com/programming/embededsql.shtml for a description of how it’s done). Facilities needed primarily by a Database Administrator or technical support person. Nested Tables Dynamic SQL http://www.theamericanprogrammer.com/programming/manuals.shtml has links to manuals. IBM, DB2, Z/OS, MVS, OS/390, TSO, ISPF, CICS, IMS/DC and QMF are registered trademarks of the IBM Corporation. Revised Fall 2002 to include Version 7.

---

Sample Pages 4: The Clauses of SQL (DB2) Top of Page SELECT WORKDEPT, SUM(SALARY) FROM EMP WHERE JOB > 'MANAGER' AND SALARY > 20000 GROUP BY WORKDEPT HAVING SUM(SALARY) > 20000 ORDER BY WORKDEPT FETCH FIRST 1 ROW ONLY -- v7 OPTIMIZE FOR 1 ROW FOR READ ONLY-- /FOR UPDATE OF colum WITH UR --/ RR / RS / CS; The clauses of an SQL statement, (if used) should be in the following order. It is suggested that you use only one clause per line. SELECT required. Displays a Table or creates a result Table which is acted on by another SQL statement FROM required. Indicates the desired Table or Tables WHERE optional. Selects only those rows that meet these criteria GROUP BY optional. Crunches data into groups so you can produce subtotals or averages by groups HAVING optional. Applies to the GROUP BY: limits which groups are selected ORDER BY optional. Sorts the result Table FETCH FIRST 1 ROW ONLY; optional restricts the actual numbers that are SELECTed Usually used with an ORDER BY so you know what order rows are in and can choose what is the ‘first’ row OPTIMIZE FOR n ROWS optional. Optimizes the statement for this number of rows FOR FETCH ONLY optional you are telling the system that there will be no changes FOR READ ONLY same as FOR FETCH ONLY. WITH UR optional. "Uncommitted" Read if the table is read-only You may also use WITH CS "Cursor stability" share locks released after reading Or WITH RR "Repeatable read" share locks kept until commit/rollback Or WITH RS "Read stability" others can insert/update rows that you have read These SQL language features are covered. Examples of SQL syntax Examples of SQL Clauses Examples of SQL/DB2 Datatypes Examples of SQL WHERE in a SELECT Examples of SQL Row Expression in Simple Predicate (Version 7 only) Examples of SQL NULLS Examples of SQL LIKE Examples of SQL BETWEEN Examples of SQL IN Examples of SQL Sorting - ORDER BY Examples of SQL Primary Key Examples of SQL Foreign Key Examples of SQL Joining Tables Examples of SQL Left Outer JOIN Examples of SQL Full Outer JOIN with DB2 Version 4 and Later Examples of SQL GROUP BY Examples of SQL HAVING Clause Examples of SQL Scalar Functions Examples of SQL INSERT Examples of SQL DELETE Examples of SQL UPDATE Examples of SQL Subqueries Examples of SQL EXISTS/NOT EXISTS Examples of SQL The Correlated Subquery Examples of SQL UNION ALL Examples of SQL UNION Examples of SQL View Examples of SQL COMMIT Examples of SQL ROLLBACK Examples of SQL CASE Examples of SQL SQL Special Registers Examples of SQL Labeled Durations Examples of SQL Date Arithmetic

---

SQL Links Top of Page Search390. A great place to find interesting information on DB2 and other S390 topics. DB2 Magazine DB2 Magazine article on scrollable cursors Personal page with many interesting links for DB2/UDB. Check the back issues too. IDUG: International DB2 Users Group. Good articles in Solutions Journal db2link.com - source of information on DB2/UDB Interesting articles on DB2 in Mainframe Week

|  Home |  Programming |  Books for Computer Professionals |  Privacy |  Terms |  Contact |
|  Site Map and Site Search |  Programming Manuals and Tutorials |  The REXX Files | Top of Page |