Oracle

SQL
Pocket Guide

THIRD EDITION

SQL
Pocket Guide

Jonathan Gennick

Beijing • Cambridge • Farnham • Köln • Sebastopol • Tokyo

SQL Pocket Guide, Third Edition
by Jonathan Gennick
Copyright © 2011 Jonathan Gennick. All rights reserved.
Printed in the United States of America.
Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North,
Sebastopol, CA 95472.
O’Reillybooks may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://my.safari
booksonline.com). For more information, contact our corporate/institutional
sales department: (800) 998-9938 or corporate@oreilly.com.

Editor: Julie Steele
Copyeditor: Teresa Elsey
Production Editor: Teresa Elsey
Proofreader: Emily Quill
Indexer:Ellen Troutman Zaig
Cover Designer: Karen Montgomery
Interior Designer: David Futato
Illustrator: Robert Romano
Printing History:
March 2004:
April 2006:
November 2010:

First Edition.
Second Edition.
Third Edition.

Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are
registered trademarks of O’Reilly Media, Inc. The Pocket Guide series designations, SQL PocketGuide, the image of a chameleon, and related trade dress
are trademarks of O’Reilly Media, Inc.
Many of the designations used by manufacturers and sellers to distinguish
their products are claimed as trademarks. Where those designations appear
in this book, and O’Reilly Media, Inc., was aware of a trademark claim, the
designations have been printed in caps or initial caps.
While everyprecaution has been taken in the preparation of this book, the
publisher and author assume no responsibility for errors or omissions, or for
damages resulting from the use of the information contained herein.
ISBN: 978-1-449-39409-7
[TG]
1288815268

Download from Wow! eBook

Contents

SQL Pocket Guide
Introduction
Analytic Functions
CASE Expressions: Simple
CASE Expressions: SearchedCAST Function
CONNECT BY Queries
Data Type Conversion
Data Types: Binary Integer
Data Types: Character String
Data Types: Datetime
Data Types: Decimal
Datetime Conversions: DB2
Datetime Conversions: MySQL
Datetime Conversions: Oracle
Datetime Conversions: PostgreSQL
Datetime Conversions: SQL Server
Datetime Functions: DB2
Datetime Functions: MySQL
Datetime Functions: Oracle
DatetimeFunctions: PostgreSQL
Datetime Functions: SQL Server

1
1
7
7
7
8
8
15
15
15
16
19
21
24
28
31
34
38
39
40
43
45
v

Deleting Data
EXTRACT Function
GREATEST
Grouping and Summarizing
Hierarchical Queries
Indexes, Creating
Indexes, Removing
Inserting Data
Joining Tables
LEAST
Literals
Merging Data
Nulls
Numeric Conversions: DB2
Numeric Conversions: MySQLNumeric Conversions: Oracle
Numeric Conversions: PostgreSQL
Numeric Conversions: SQL Server
Numeric/Math Functions
OLAP Functions
Pivoting and Unpivoting
Predicates
Recursive Queries
Regular Expressions
Selecting Data
String Functions
Subqueries
Tables, Creating
Tables, Dropping
Tables, Modifying
Transaction Management
Union Queries
vi | Table of Contents

47
51
52
52
62
66
6767
72
82
82
86
88
93
95
95
97
98
99
101
101
109
112
113
124
134
139
143
148
149
154
162

Updating Data
Window Functions
Index

168
173
181

Table of Contents | vii

SQL Pocket Guide

Introduction
This book is an attempt to cram the most useful information
about SQL into a pocket-size guide. It covers commonly used
syntax for the following platforms:IBM DB2 Release 9.7,
MySQL 5.1, Oracle Database 11g Release 2, PostgreSQL 9.0,
and Microsoft SQL Server 2008 Release 2.
Not all syntax will work on all platforms, and some features
may not be available in earlier releases of these products.
Whenever possible, I’ve tried to note any product or release
dependencies.

Organization of This Book
Topics are organized alphabetically, with many...