SQL

by Gennick, Jonathan

• ISBN13: 9780596526887
• ISBN10: 0596526881
• Edition: 2nd
• Format: Paperback
• Copyright: 2006-04-30
• Publisher: Oreilly & Associates Inc

Summary

Now available in an updated second edition, our very popular "SQL Pocket Guide" is 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 information in this edition is updated to reflect the latest versions of Oracle Database 10g, Release 2; the free Oracle Database 10g Express Edition (XE); Microsoft SQL Server 2005; MySQL 5; and IBM DB2 8.2. There is also added coverage of the PostreSQL 8.1 database. The "SQL Pocket Guide" is a concise reference to frequently used SQL statements and commonly used SQL functions.

Author Biography

Jonathan Gennick is an O'Reilly Media, Inc. editor specializing in database and programming titles. He writes extensively on database topics, and is the author of such successful books as: Oracle SQLPlus: The Definitive Guide, Oracle SQLPlus Pocket Reference, and Oracle SQL*Loader: The Definitive Guide. Jonathan contributes regularly to Oracle Magazine, and frequently explores interesting SQL topics on his own website, http://gennick.com. Jonathan is also a member of The Oak Table Network (http://www.oaktable.net), a group of Oracle experts known for their scientific approach to administering and developing Oracle based systems.

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 Media, Inc., 2001); Oracle SQLPlus Pocket Reference (O'Reilly Media, Inc., 2000); Oracle SQL*Plus: The Definitive Guide (O'Reilly Media, Inc., 1999); More recently, Jonathan has made forays into other database products, coauthoring Transact-SQL Cookbook (O'Reilly Media, Inc., 2002) and editing Practical PostgreSQL (O'Reilly Media, Inc., 2002).

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
Analytic Functions	p. 6
CASE Expressions	p. 6
Simple CASE Expressions	p. 6
Searched CASE Expressions	p. 6
Datatypes	p. 7
Character String Types	p. 7
Decimal Types	p. 8
Binary Integer Types	p. 8
Datetime Types	p. 9
Dataype Conversion	p. 11
Standard CAST Function	p. 1
Standard EXTRACT Function	p. 12
Datetime Conversions (Oracle)	p. 12
Numeric Conversions (Oracle)	p. 16
Datetime Conversions (DB2)	p. 17
Numeric Conversions (DB2)	p. 20
Datetime Conversions (SQL Server)	p. 21
Numeric Conversions (SQL Server)	p. 25
Datetime Conversions (MySQL)	p. 26
Numeric Conversions (MySQL)	p. 30
Datetime Conversions (PostgreSQL)	p. 31
Numeric Conversions (PostgreSQL)	p. 33
Deleting Data	p. 35
Deleting in Order (MySQL)	p. 35
Deleting All Rows	p. 36
Deleting from Views and Subqueries	p. 37
Returning Deleted Data (Oracle, DB2)	p. 37
Double-FROM (SQL Server)	p. 38
Functions	p. 39
Datetime Functions (Oracle)	p. 39
Datetime Functions (DB2)	p. 42
Datetime Functions (SQL Server)	p. 43
Datetime Functions (MySQL)	p. 44
Datetime Functions (PostgreSQL)	p. 45
Numeric and Math Functions (All Platforms)	p. 48
Trigonometric Functions (All Platforms)	p. 50
String Functions	p. 50
Greatest and Least (Oracle, PostgreSQL)	p. 55
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, DB2)	p. 61
Group by Extensions (SQL Server)	p. 64
Hierarchical Queries	p. 65
Recursive With (SQL Server, DB2)	p. 66
Connect by (Oracle)	p. 68
Indexes, Creating	p. 74
Indexes, Removing	p. 74
Inserting Data	p. 75
Single-Row Inserts	p. 75
Multi-Row Inserts (DB2, MySQL)	p. 76
Insert Targets	p. 76
Subquery Inserts	p. 76
Returning Inserted Values (Oracle, DB2)	p. 77
Multi-Table Inserts (Oracle)	p. 78
Joining Tables	p. 80
The Concept of a Join	p. 80
Cross Joins	p. 81
Inner Joins	p. 82
The Using Clause (Oracle, MySQL, PostgreSQL)	p. 83
Natural Joins (Oracle, MySQL, PostgreSQL)	p. 84
Non-Equi-Joins	p. 85
Outer Joins	p. 86
Literals	p. 89
Text Literals	p. 90
Numeric Literals	p. 92
Datetime Literals (Oracle, MySQL, PostgreSQL)	p. 92
Datetime Interval Literals	p. 93
Merging Data (Oracle, DB2)	p. 93
Nulls	p. 96
Predicates for Nulls	p. 96
Using CASE with Nulls	p. 96
Using the Coalesce Function	p. 97
Functions for Nulls (Oracle)	p. 97
Functions for Nulls (DB2)	p. 98
Functions for Nulls (SQL Server)	p. 98
Functions for Nulls (MySQL)	p. 99
Functions for Nulls (PostgreSQL)	p. 100
OLAP Functions	p. 100
Predicates	p. 100
Exists Predicates	p. 101
In Predicates	p. 102
Between Predicates	p. 102
Like Predicates	p. 103
Recursive Queries	p. 104
Regular Expressions	p. 104
Regular Expressions (Oracle)	p. 104
Regular Expressions (SQL Server)	p. 108
Regular Expressions (MySQL)	p. 108
Regular Expressions (PostgreSQL)	p. 110
Selecting Data	p. 115
The Select Clause	p. 115
All and Distinct	p. 120
The From Clause	p. 121
The Where Clause	p. 124
The Group by Clause	p. 124
The Having Clause	p. 124
The Order by Clause	p. 125
Subqueries	p. 126
The With Clause (Oracle, SQL Server, DB2)	p. 127
Tables, Creating	p. 130
Creating a Table (Oracle)	p. 130
Creating a Table (DB2)	p. 132
Creating a Table (SQL Server)	p. 133
Creating a Table (MySQL)	p. 133
Creating a Table (PostgreSQL)	p. 134
Tables, Modifying	p. 135
Modifying a Table (Oracle)	p. 135
Modifying a Table (DB2)	p. 136
Modifying a Table (SQL Server)	p. 137
Modifying a Table (MySQL)	p. 138
Modifying a Table (PostgreSQL)	p. 139
Tables, Removing	p. 140
Transaction Management	p. 141
Autocommit Mode (SQL Server, MySQL, PostgreSQL)	p. 141
Starting a Transaction	p. 142
Ending a Transaction	p. 146
Aborting a Transaction	p. 147
Aborting to a Transaction Savepoint	p. 148
Union Queries	p. 148
Union and Union All	p. 149
Order by in Union Queries	p. 150
Names and Datatypes in a Union	p. 150
Order of Evaluation	p. 151
Except (or Minus)	p. 152
Intersect	p. 153
Updating Data	p. 155
Simple Updates	p. 155
New Values from a Subquery	p. 156
Updating Views and Subqueries	p. 157
Update From Clause (SQL Server, PostgreSQL)	p. 157
Returning Updated Data (Oracle, DB2)	p. 158
Window Functions	p. 158
Defining a Summary Window (Oracle, DB2, SQL Server)	p. 159
Ordering and Ranking Within a Window (Oracle, DB2, SQL Server)	p. 160
Comparing Values Across Rows (Oracle)	p. 162
Summarizing over a Moving Window (Oracle, DB2)	p. 164
Window Function Syntax (Oracle)	p. 164
Window Function Syntax (DB2)	p. 165
Window Function Evaluation and Placement	p. 166
Index	p. 167
Table of Contents provided by Ingram. All Rights Reserved.