[USflag] The American Programmer [USflag]
Home Programming Books for Computer Professionals Privacy Terms
           Home   > Programming   > SQL Book

Teach Yourself SQL in 30,421 Minutes

Teach Yourself SQL in 30,421 Minutes

All the SQL generally needed by the programmer or systems person using DB2 on mainframe systems such as MVS, OS390, ZOS.
About 130 pages. Includes some of the new features of Version 7.

This book gets you writing mainframe SQL quickly.
It uses data tables that are installed along with DB2 and are found on essentially every company's system.
The book shows you about 130 examples of SQL and the results.
You can try out the examples on your system, and see for yourself the effects of variations.
Practice problems show you the expected results and invite you to write the code and obtain the same results.
Thoroughly tested suggested solutions are given so you can compare them to your solution.
An included diskette contains code for loading the practice tables if they can't be found, all the examples and solutions, and a supplementary set of practice problems.




Teach Yourself SQL  

in 30,421 Minutes  
 
 
 
 
 
 
 
 
  By Gabriel F. Gargiulo
 
 


Teach Yourself SQL in 30,421 Minutes

By Gabriel F. Gargiulo


You can check our selling reputation at Amazon.com and Ebay. We are Webmaster1652.

Gabe Gargiulo is the author of several mainframe books:
REXX Quick Reference     REXX in the TSO Environment (Available new and used)     The REXX Language on TSO
MVS JCL (Out of Print)
   Mastering OS2/REXX (Available new and used)    ISPF Services: Using the Dialogue Manager, with REXX    MVS/TSO (Available new and used)


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

[Books Computer]

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