SQL Pocket Guide

by Gennick, Jonathan

• ISBN13: 9780596005122
• ISBN10: 0596005121
• Format: Paperback
• Copyright: 2004-03-01
• Publisher: Oreilly & Associates Inc

Summary

This unique reference can be a major help to programmers who use SQL in their day-to-day work. Not just an endless collection of syntax diagrams, this small guide addresses the language's complexity head on and leads by example. The book covers the three most commonly used database products: Oracle, IBM's DB2, and Microsoft's SQL Server. It's a concise reference to frequently used SQL statements and commonly used SQL functions.

Author Biography

Jonathan Gennick is an O'Reilly & Associates editor specializing in database and programming titles. Prior to joining O'Reilly, Jonathan amassed some 17 years of programming and database management experience. During the latter part of his career he specialized in relational database programming, and more recently in database management. Jonathan got his start with relational database systems in 1990, first working with Ingres, and later with Digital's Rdb software. During that period he developed a fondness for SQL, and for the challenge of applying SQL creatively in ways that leveraged it's set-oriented capabilities. In 1994 Jonathan made the leap to Oracle, and is now often found writing about it. Recent projects include Oracle SQLLoader (O'Reilly & Associates, 2001); Oracle SQLPlus Pocket Reference (O'Reilly & Associates, 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly & Associates, 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly & Associates, 2002) and editing Practical PostgreSQL (O'Reilly & Associates, 2002). Jonathan is certified as an Oracle DBA and is a member of MENSA and the Oracle Technology Network. He holds a Bachelor of Arts degree in Information and Computer Science, with a Business Administration minor, from Andrews University in Berrien Springs, Michigan. Jonathan currently resides in Munising, Michigan with his wife Donna and their two children: Jenny and Jeff. Jonathan may be contacted by email at [e-mail], and you can learn more about him personally by visiting his website at http://gennick.com.

Table of Contents

Introduction	p. 1
Organization of This Book	p. 2
Feedback Needed!	p. 2
Conventions	p. 3
Acknowledgments	p. 4
Example Data	p. 4
CASE Expressions	p. 7
Simple CASE Expressions	p. 7
Searched CASE Expressions	p. 8
Datatype Conversion	p. 9
ANSI/ISO CAST Function	p. 9
ANSI/ISO EXTRACT Function	p. 10
Datetime Conversions (Oracle)	p. 11
Numeric Conversions (Oracle)	p. 15
Miscellaneous Conversions (Oracle)	p. 18
Datetime Conversions (DB2)	p. 18
Numeric Conversions (DB2)	p. 21
Miscellaneous Conversions (DB2)	p. 23
Datetime Conversions (SQL Server)	p. 23
Numeric Conversions (SQL Server)	p. 27
Miscellaneous Conversions (SQL Server)	p. 29
Datetime Conversions (MySQL)	p. 29
Numeric Conversions (MySQL)	p. 34
Deleting Data	p. 35
Deleting All Rows	p. 36
Deleting from Views and Subqueries	p. 37
Deleting from Partitions (Oracle)	p. 37
Returning the Deleted Data (Oracle)	p. 38
Double-FROM (SQL Server)	p. 39
Flashback Queries (Oracle)	p. 40
Functions	p. 41
Date Functions	p. 42
Numeric and Math Functions	p. 48
Trigonometric Functions	p. 50
String Functions	p. 50
Miscellaneous Functions (Oracle)	p. 56
Grouping and Summarizing	p. 56
Aggregate Functions	p. 56
GROUP BY	p. 57
Useful GROUP BY Techniques	p. 59
HAVING	p. 60
GROUP BY Extensions (Oracle)	p. 61
GROUP BY Extensions (SQL Server)	p. 64
Hierarchical Queries	p. 66
ANSI/ISO Recursive WITH (DB2)	p. 66
CONNECT BY Syntax (Oracle)	p. 67
Inserting Data	p. 72
Single-Row Inserts	p. 72
Insert Targets	p. 73
Subquery Inserts	p. 73
Direct-Path Inserts (Oracle)	p. 74
RETURNing Inserted Values (Oracle)	p. 74
Multi-Table Inserts (Oracle)	p. 75
Joining Tables	p. 77
The Concept of a Join	p. 77
Cross Joins	p. 79
Inner Joins	p. 79
Non-Equi-Joins	p. 83
Outer Joins	p. 83
Literals	p. 87
Text Literals	p. 88
Numeric Literals	p. 89
Datetime Literals	p. 90
Datetime Interval Literals	p. 90
Merging Data	p. 91
NULLs	p. 92
Predicates for NULLs	p. 92
Using CASE with NULLs	p. 93
Functions for NULLs (Oracle)	p. 94
Functions for NULLs (DB2)	p. 95
Functions for NULLs (SQL Server)	p. 95
Functions for NULLs (MySQL)	p. 95
Predicates	p. 96
Group Comparison Predicates	p. 97
Multiple Values on the Left (Oracle)	p. 98
EXISTS Predicates	p. 99
IN Predicates	p. 99
BETWEEN Predicates	p. 100
LIKE Predicates	p. 100
Recursive Queries	p. 101
Regular Expressions	p. 102
Regular Expressions (Oracle)	p. 102
Regular Expressions (SQL Server)	p. 104
Regular Expressions (MySQL)	p. 105
Selecting Data	p. 106
The SELECT Clause	p. 107
ALL and DISTINCT	p. 114
The FROM Clause	p. 116
The WHERE Clause	p. 119
The GROUP BY Clause	p. 119
The HAVING Clause	p. 119
The ORDER BY Clause	p. 120
Subqueries	p. 121
The WITH Clause	p. 122
WITH with Correlated Subqueries	p. 123
Transaction Management	p. 125
Autocommit Mode	p. 125
Starting a Transaction	p. 126
Ending a Transaction	p. 129
Aborting a Transaction	p. 130
Aborting to a Transaction Savepoint	p. 131
Union Queries	p. 132
UNION and UNION ALL	p. 132
Order of Evaluation	p. 134
EXCEPT (or MINUS)	p. 134
INTERSECT	p. 136
Updating Data	p. 137
Simple Updates	p. 137
New Values from a Subquery	p. 138
Updating Through a Cursor	p. 139
Updating Views and Subqueries	p. 139
Updating a Partition (Oracle)	p. 140
Returning the Updated Data (Oracle)	p. 140
UPDATE FROM Clause (SQL Server)	p. 140
Index	p. 143
Table of Contents provided by Ingram. All Rights Reserved.